RATE Function - Calculate an Interest Rate in Excel

Add to Favorites

The Rate function in Excel (RATE) calculates the interest rate for a financial transaction, such as for an annuity or lump sum.  An annuity is simply an equal series of payments.

You tell the RATE function the payments that are made, for how long they are made, and the present value and this function will give you the interest rate for that equation.  This also works when you know the future value of these payments or when there are no payments to be made at all.

Syntax

RATE(nper, pmt, pv, [fv], [type], [guess])

Argument

Description

Nper

The number of payment periods used in the calculation.  If each year is broken into two periods and you are calculating the Rate for 10 years, then this number would be 20 because there are 20 periods in the total calculation.

Pmt

The payment made each period.  This assumes the payment is the same each period.  This argument is actually optional, but only if you include the FV argument in its place, though you can include both of them if you need to do so.

Pv

This is the Present Value or the current value of the payments/investment/annuity.  This is what that amount is worth right now.

[Fv]

This is optional.  This is the Future Value argument and this is the amount that the investment/annuity will be worth in the future.  If this value is left empty, it is assumed to be zero.  This argument is not required unless you omit the Pmt argument.

[Type]

Optional argument. This tells the RATE function when the payment in the Pmt argument will be made, either 0, for the end of the period, or 1, for the beginning of the period.  If you leave this argument empty, Excel will assume it is 0, or that payments are made at the end of each period.

[Guess]

This is optional.  It allows you to guess what the final interest rate will be.  If you leave this blank, Excel assumes it is 10%.  To be honest, I rarely, if ever, have used this argument in finance related calculations.

[] means the argument is optional.

Now, let's get to some interest rate calculation examples.

Basic Loan Interest Rate Calculation

Here is the sample spreadsheet:

We have calculated the interest rate for a simple loan where we were given $2,200 today and must make payments of $100 per period for 24 periods.  Now, the spreadsheet doesn't tell you much, but let's assume that each period is per month; that means the calculations are being made for a 2 year repayment period.

To perform the above calculation, we only need to fill-out three arguments, the nper, pmt and pv:

Notice how the cell reference for B3 has a negative sign in front of it within the RATE function.  You must do this in order to get a correct result.  This is simply how the function works and you can think of it like making payments to someone, which takes money away from you, which is a negative.

Also, you can see just how simple this function can be in its most basic form as the last three arguments for it are all optional.

Important Note on Periods

The interest rate that we see above is 0.71% but REMEMBER that is PER PERIOD.  Since each period is 1 month, if we wanted to get the yearly rate, we could do a simple calculation of multiplying .71 by 12 to get 8.50%.

Interest Rate for a Lump Sum

Here, we will have a lump sum present value amount, $1,000, and want to get a certain amount in the future, $8,000, and we have 60 periods in which to do this.  If each period is 1 month, this means we have 5 years.

In this example, we have nothing for the Pmt argument, in fact it was removed from the equation, and we have included the Future Value argument.

You can see how we have left the second argument, Pmt, completely empty in the RATE function, and how we now have a negative sign in front of the Present Value argument.

If we wanted, we could have also just put a zero in for the Pmt argument or kept the cell reference to B3 from the previous example.

As usual, don't forget that the rate returned by this function, 3.53%, is the rate per period and NOT per year.  I repeat this often because it is a mistake that is very easy to make.

Type Argument and Guess Argument

You use these arguments just like you would any other argument except that they are often not needed.

Just make sure that, if you are given a finance problem you pay attention to when the payments are to be made and then account for that using the Type argument in this function.

Notes on Periods

When you are calculating the interest rate, you need to make sure that you keep period calculations consistent.

The Nper argument is not the total number of years for an investment but the total number of periods.  So, if there are two periods per year and you have an investment time of 5 years, you need to put 10 in for the Nper argument because, in this case, there are 10 periods in 5 years (5 years*2 periods = 10 total periods).

If there are two periods in a year but you have a payment amount that is a yearly rate, you need to divide that rate by two so that you have the correct amount for the period.

Also, remember that the result of the RATE function will be an interest rate per period.  If you want to get that rate in a different time frame, you will need to perform additional calculations on the rate.

Conclusion

The RATE function is a very useful financial function that helps to build the foundation of more complex calculations and financial models in Excel.  Master this function, along with the other basic financial calculations in Excel, and you will be on your way to becoming proficient in working with financial problems in Excel.

Don't forget to download the accompanying spreadsheet so you can see this function in action.


Excel Function: RATE()
Downloadable Files: Excel File