Excel 2010: The RATE Function

Updated 2012.03.08

Objective:

·         Use the RATE function to determine the interest rate (per period) that will be required to reach a given investment goal, or to determine the rate that you will pay on a loan.

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.

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

Example 1: 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 D7, and the annual rate is in D1.

 

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

Example 2: Calculating the rate (per period) on a loan

You are borrowing $10,000 for 5 years. Your monthly payments (at the beginning of each month) are $275. The balance of the loan will be 0 at the end of the 5 years. What is the annual interest rate that you are paying?

Solution

·         Number of Periods (nper): There are 12 periods per year for 5 years. 12 * 5 = 60 periods.

·         Payment (pmt): You are making any regular payments of $200 (will be negative as a function argument): -200

·         Present Value (pv): This is the amount that you currently owe. Since it represents money that you have in your pocket now, it is a positive number: $10,000.

·         Future Value (fv): The amount you owe when the loan is paid off: $0.

·         Type: Payments are made at the beginning of the month, so this is 1 (beginning = 1, end = 0).

 

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

 

So the formula is:

=RATE(60, -200, 10000, 0, 1)

 

The pmt argument is negative because it represents money that is being "paid out" to the bank.

 

The pv argument is positive because it represents money that you currently have in your pocket.

 

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 D7, and he annual rate is in D1.

 

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