Excel 2013 & 2016: Using RATE to determine the interest rate on a savings account

Updated 2014.01.09

·
Learn to use the RATE function to determine the
interest rate (__per period__) that will be required to reach a given
investment goal

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