Excel 2016: Using RATE to determine the interest rate on a savings account

Updated 2014.01.09

Objective:

·         Learn to use the RATE function to determine the interest rate (per period) that will be required to reach a given investment goal

RATE arguments

·         PMT: If doing a savings problem, the amount you will deposit in savings each period. If doing a loan problem, the amount you will re-pay each period. This numbers is always negative.

·         NPER: Number of periods, not number of years.

·         PV: Present value. If doing a savings problem, the amount you have in the bank at the beginning (a negative number). If doing a loan problem, the amount you have borrowed (a positive number).

·         FV: Future value. The amount you will have at the end of the number of periods if you are doing a savings problem, or the amount you will owe at the end of the number of periods if you are doing a loan problem (usually, but not always, 0).

·         TYPE: Type of payment. 1=payments at the beginning of the period, 0=at the end of the period.

Example: What interest rate (per period) do you need to reach a savings goal?

You have $20,000 and you want it to grow into $500,000 in 25 years with monthly deposits of $200. The interest is compounded monthly at the beginning of each period. What interest rate will you have to receive to achieve this?

Solution

·         "You have $20,000…". This is the amount that you currently have in the account, so it is the present value. Since it represents payments that you have already made, it will have to be negative in the formula: -20,000.

·         "…you want it to grow into $500,000…" This is the future Value: Your savings goal: $500,000.

·         "…in 25 years. The interest is compounded monthly…" 25 years, 12 periods per year, so the number of periods is 25*12= 300.

·         "…with monthly deposits of $200." You are making any regular deposits (payments), so this value is 200. The payment argument is always negative, so it will be negative in the formula: -200.

·         "…at the beginning of each period." This is the type: 0 if payments (deposits) are made at the end of each period, 1 if the payments are made at the beginning, so the value is 1.

 

The RATE arguments are: RATE(nper, pmt, pv, fv, type)

 

So the formula is:

=RATE(300, -200, -20000, 500000, 1)

 

The pmt argument is negative because it represents money that is being "paid out" to the bank (even though it is to your account).

 

The pv argument is negative because it represents the sum of all of the money that has previously been "paid out".

 

Remember that the RATE function gives us the rate per period. Since there are 12 periods per year in this case, we need to multiply the result by 12 to get the annual rate which is usually how interest rates are quoted. In the worksheet below, the rate per period is in E2, and the annual rate is in E3.

 

It is never a good idea to build numbers into a formula. Use references to the cells that hold the numbers:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/018-Rate-Function_files/image001.jpg