Excel 2013 & 2016: The FV (Future Value) function

Updated 2014.01.09

·
Learn how to use the **FV** function to
determine how much an investment will be worth at the end of a period of time
if you make regular, constant deposits at a constant interest rate.

·
RATE: Rate __per period__, not rate per year.

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

·
PMT: Payment. If you are doing a savings
problem, the amount depositing each period. If you are doing a loan problem,
the amount you are paying to re-pay the loan each period. This number is __always
negative__.

· TYPE: Type of payment. 1=payments at the beginning of the period, 0=at the end of the period.

How much will you have in the bank if
you __start with $20,000__ and save __$200 per month__ (at the __beginning__
of each month) at an __annual rate of 10%__ for __40 years__. Interest is
compounded __monthly__.

Identify the numbers:

·
"start with $20,000" is the **Present
Value:** The amount currently in savings = 20,000.

·
"$200 per month" is the **Payment:**
The amount you pay (save) at the beginning of each period: $200 (this will be a
negative argument)

·
"beginning of each month" is the **Type:**
Payments are made at the beginning of each month, so this is 1.

· "interest is compounded monthly" tells us that there are 12 periods per year, so the annual rate must be divided by 12 to get the rate per period and the number of years must be multiplied by 12 to get the number of periods.

·
"annual rate of 10%" is the **Rate:**
10% annually, but payments are monthly, so rate per period is .10/12 =
.0083333333

·
"40 years" tells the **Number of
Periods:** Time is 40 years, but payments are monthly (12 per year), so total
number of periods is: 40 * 12 = 480

The FV arguments are: FV(**rate**, **nper**, **pmt**, pv, type)

So the formula is:

=FV(.10/12, 40*12, -200, -20000, 1)

The **pmt**
is negative because it represents money that you are "paying" out
(even though it is going into *your* bank account).

The **pv**
value is negative because it represents the sum of all of the money that you
have "paid out" up to this point.

It is __never__ a good idea to build
numbers into a formula. Use __references to the cells__ that hold the
numbers: