« Liz Pulliam Weston – Debt: And you think you’ve got it bad? | Main | Schwab is Introducing ETFs That Trade Commission-Free »
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:
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:
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 | 4 Comments »
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
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).
November 4th, 2009 at 10:51 am
Thanks! Perfect!
-Mikey
November 4th, 2009 at 12:11 pm
OK, I’ll trust you and start trying it your way!