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

Updated 2014.01.09

Objective:

·         Learn to use the RATE function to determine the interest rate (per period) 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 (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: Calculating the rate (per period) on a loan

You are borrowing \$10,000 for 5 years. Your monthly payments (at the end of each month) are \$200. 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 end of the month, so this is 0 (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 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: 