RATE Function - Calculate an Interest Rate in Excel

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

[] 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.