Excel 2010: The RATE Function

Updated 2012.03.08

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

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

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?

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

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?

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