RATE

 

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

 

The RATE arguments are:

 

Example 1: What rate do you need to reach your goal without saving any more?

You have $40,000 and you want it to grow into $100,000 in 10 years. The interest is compounded semiannually. What interest rate will you have to receive to achieve this?

 

Solution 1

Number of Periods: There are 2 periods per year for 10 years. 2 * 10 = 20 periods.

Payment: You are not making any regular payments (deposits), so this value is 0.

Present Value: This is the amount that you currently have in the account. Since it represents payments that you have already made, it will have to be negative in the formula: $40,000.

Future Value: Your savings goal: $100,000.

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

 

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

 

So the formula is:

=RATE(2*10, -0, -40000, 100000, 0)

 

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 2 periods per year in this case, we need to multiply the result by 2 to get the annual rate which is usually how interest rates are quoted.

 

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

 

 

Example 2: What rate do you need to reach your goal with regular deposits?

You currently have $20,000 in the bank and can afford to save $200 at the beginning of each month. Your investment goal is $500,000. What annual interest rate will you have to get to reach this goal in 25 years?

 

Solution 2

Number of Periods (nper): There are 12 periods per year for 25 years. 12 * 25 = 300 periods.

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

Present Value (pv): This is the amount that you currently have in the account. Since it represents payments that you have already made, it will have to be negative in the formula: $20,000.

Future Value (fv): Your savings goal: $500,000

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(12*25, -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 2 periods per year in this case, we need to multiply the result by 2 to get the annual rate which is usually how interest rates are quoted.

 

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

 

 

 

Example 3: Calculating the rate 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 3

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

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.

 

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

 

 

Updated 2006.01.10