Yesterday I used the RATE function in Excel for a post. A reader asked me if I could explain the RATE function. This post will attempt to do that.

First off, you use the RATE function when you want to calculate what rate of return would be required to meet a certain goal, based on a few assumptions. Let’s use the information from yesterday’s post:

Retirement Goal: $1,000,000

Years until retirement: 20 (240 months)

Current retirement account balance: $100,000

Monthly contribution amount: $500

You can set it up like this in Excel:

Then, to solve for the rate, you simply put your click on cell E1 and then choose Insert > Function from the menu. You should then see this:

If you don’t see the RATE function listed in the center menu, then you might need to select “All” from the drop down menu above and then scroll down in the main menu until you see RATE.

Once you click on RATE and then “Okay”, you’ll see a menu that looks like this:

Then, you simply fill in the information, referencing the appropriate cells like this:

Here’s a quick explanation of each input:

**Nper – **the number of periods involved in this example. It’s 20 years but we’re making monthly contributions, so it’s more accurate to use 240 months. So we reference cell C1 and multiply it by 12.

**Pmt – **the monthly contribution amount expressed as a negative number. Think of it like a cash flow amount. The $500 is flowing out each month. So, we reference cell D1 and multiply it by -1 to get a negative number.

**Pv – **the present value of the retirement account—again expressed as a negative number. We reference cell A1 and multiply it by -1.

**Fv – **the future value of the retirement account. This value is expressed as a positive number. We reference cell B1.

**Type – **input 1 if the payment is made at the beginning of the month or 0 or leave it blank if the payment is made at the end of the month. I chose the beginning of the month and therefore inserted 1.

**Guess – **(you’ll have to scroll down to see this input as it is located under Type) This function requires a rate guess in order work properly. The default is 10 percent. I left this blank.

After you enter all the necessary information, click “OK.” You should see .78%, although you might see 1%. In that case, simply go into cell format and change the number settings to two decimal places.

Also, the formula will give you a monthly return. To convert it to an annual number, simply convert the percentage to a decimal and raise that number to the 12th power. Like this:

**(1 + .0078)**

^{12}– 1**(1.0078)**

^{12}– 1**1.09786 – 1**

**.09786 or 9.79%**

Hopefully, if I have done a good job, you now know how to use Excel’s RATE function. If you have any questions, please feel free to leave a comment.

Thanks for the tutorial. I always use my HP 12c so I’ve never learned how to do it in Excel. I think my HP is quicker ðŸ™‚

Stacey,

The cool thing about using Excel is that once you get it set up, you can run all sorts of comparisons. You can even cut and paste the formula I showed and have different inputs for each row.

Pretty cool stuff (I’m easily entertained).

Thanks! Perfect!

-Mikey

OK, I’ll trust you and start trying it your way!

Thanks for the tutorial but I’m confused on one point. When I’ve looked at a number of examples about the RATE function in some basic texts and the Microsoft site they always convert the monthly interst figure to an annual one by multiplying by 12. You instead raise the number to the 12th power giving a quite different result. Why do you use the 12th power instead of multiplying by 12?

If the number is an average monthly return and you want to annualize it, you have to compound the return. You do this by taking the monthly return, adding one to it, and raising that to the 12th power (since there are twelve months in a year). It’s really no different than when you have a year rate of return and you want to find out how much you would have 5 or 10 years down the road.

how about different payment and it is not fixed can we get it?

What’s the actual math formula for the RATE function?

I was using this formula for a slightly different situation for a finance assingment, but the way you explained it allowed me to work out how to apply it to my own calculations. Thanks!