Microsoft Office Tutorials and References
In Depth Information
The Basic Excel Financial Functions
For the next example, assume a wealthy relative wants to give you $100,000, but that you can’t
collect it until your 40th birthday. If you are 25 years old now, the value of that future gift would
be $31,524.17 and is computed as follows:
The payment is an inflow (a positive $100,000) that will occur 15 years from now. If you had
some money now, you think you could make 8% investing it. Because there are no payments, the
type argument is irrelevant.
The result of this formula means that if you had $31,524 now and you invested it at 8%, it would
be worth $100,000 in 15 years. See Figure 11-2.
Figure 11-2: Calculating the present value of a lump sum.
Present value of an annuity with a lump sum
In some cases, future cash flows are followed by a single, large future cash flow.
Assume that your brother-in-law wants you to invest in his carpet-cleaning business. If you’ll
invest $50,000 now, he will pay you $200 per month for five years and then also pay you
$60,000 at the end of the five years. To determine whether this is a good deal, find the present
value of all your future cash inflows:
Let’s look at each of these arguments (see Figure 11-3):
h You determined that you could make 10% on your money elsewhere, so 10% is the
h All the arguments must cover the same time period. Because the $200 payment is made
monthly, all the arguments must be converted to months:
The rate argument is divided by 12 (for 12 months).
The nper argument is expressed as 60 (for 60 months; not 5 for 5 years).