Excel 2013 & 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: