FV (Future Value)

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.

Arguments:

• Rate per period
• Number of periods
• Regular Payment (a negative number)
• Present Value (how much you have in the bank now, also a negative number)
• Type of payment (0=end of month, 1 = beginning of month)

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

How much will you have in the bank if you save $200 per month (at the beginning of each month) at an annual rate of 10% for 40 years. Interest is compounded monthly.

Solution 1

Rate: 10% annually, payments are annual = .10

Number of Periods: Years (40) times payments per year (12) = 480

Payment: The amount you pay (save) at the beginning of each period: $200 (this will be a negative argument)

Present Value: The amount currently in savings = 0.

Type: Payments are made at the beginning of each year, so this is 1.

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

So the formula is:

=FV(.10/12, 40*12, -200, -0, 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 (although in this case, it doesn't matter because the present value is 0).

It is never a good idea to build numbers into a formula. Use references to the cells that hold the numbers:

Example 2: How much will you have in the bank if you start with something and never add to it?

Modify the previous problem to change the regular deposit to 0 and the beginning balance to $20,000. How much will you have in your savings account at the end of the 40 years?

Solution 2

Because we set up the solution to the first problem using cell references instead of numbers, all we need to do is change the values in B1 and B2!

Example 3: How much will you have in the bank if you start with something AND make regular deposits?

Make one more change to the first example. Leave the beginning balance at $20,000 but change the regular deposit back to $200 per month. How much will you have in your savings account at the end of the 40 years?

Solution 3

Updated 2007.01.26