Excel 2016: The FV (Future Value) function

Updated 2017.07.30

Objective

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

FV Arguments

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

Example: How much will you have in the bank if you start with some money and make regular payments?

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.

Solution

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:

 

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/018-FV-Function_files/image001.jpg