Search


Subscribe to AFM


Subscribe to AllFinancialMatters
by Email

All Financial Matters

Promote Your Page Too

The American's Creed

Site Sponsors

Books I Recommend


AFM in the Media


Money Magazine May 2008

Real Simple March 2008

Blogroll (Daily Reads)

« | Main | »

How to Use the RATE Function in Excel

By JLP | November 3, 2009

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:

Excel RATE Function - 1

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:

Excel RATE Function - 2

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:

Excel RATE Function - 3

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

Exel RATE Function - 4

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.

Topics: Financial Math Basics | 9 Comments »


9 Responses to “How to Use the RATE Function in Excel”

  1. Stacey Says:
    November 4th, 2009 at 8:15 am

    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 :)

  2. JLP Says:
    November 4th, 2009 at 8:21 am

    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).

  3. Mikey Says:
    November 4th, 2009 at 10:51 am

    Thanks! Perfect!

    -Mikey

  4. Stacey Says:
    November 4th, 2009 at 12:11 pm

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

  5. Tony Says:
    March 4th, 2010 at 7:57 am

    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?

  6. JLP Says:
    March 4th, 2010 at 12:02 pm

    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.

  7. gilbert Says:
    June 24th, 2010 at 9:37 pm

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

  8. Brian Says:
    November 1st, 2010 at 10:48 am

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

  9. Dni Says:
    June 25th, 2013 at 2:28 am

    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!

Comments