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

Updated 2014.01.09

·
Learn to use the RATE function to determine the
interest rate (__per period__) that you will pay on a loan.

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

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?

·
**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: