Working with Future Value
The concept of future value is quite simple and is based on the fact that a given amount of money received today will be worth more at some time in the future. It’s easy to understand why this is true—money you have now can be invested and earn interest, hence its value increases.
Future value calculations are useful in a variety of situations. For example, you plan to invest $10,000 in a certificate of deposit at 4% for three years; how much will you have at the end of the three years? Another example is putting $50 a month in your daughter’s college fund. How much will you have when she goes to college in 12 years, assuming the rate of return is 5%? You use the FV function for future value calculations.
The FV function has the following syntax:
The first three arguments are required:
rate is the projected rate of return per period.
nper is the number of periods.
pmt is the payment per period.
The other two arguments are optional:
pv is the present value (the amount you are starting out with). If omitted, the function assumes 0.
type is 1 if the payments are made at the start of each period, 0 or omitted if payments are made at the end of each period.
The FV function is quite flexible. If you have a situation where you are starting with nothing and making regular payments, you will set pv to zero and enter a value for pmt. On the other hand, if you are starting with a lump sum and not making any payments, set pv to the initial value and enter 0 for pmt. You can have both an initial amount and regular payments, too, of course.
To try out the FV function, start with a new worksheet and then follow these steps:
Enter the labels Initial amount, Rate of return, Monthly payment, Number of months, and Future value in cells B2 through B6, in order.
Format cells C2, C4, and C6 as currency with two decimal places.
Format cell C3 as percentage with two decimal places.
Enter the following formula in cell C6: =FV(C3/12,C5,C4,C2).
The resulting worksheet is shown with some data entered in Figure 3.4. You can see that if you put $1,000 in an account paying 5% interest and add $40 every month, you’ll have $1,542.32 at the end of the year.
Please note that in keeping with Excel’s cash flow model, the initial amount and monthly payment are entered as negative values because this is money you are paying out. The future value is correctly calculated as a positive value because this is money you will receive.
Figure 3.4 Using the FV function to calculate the future value of an investment.