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:

=PV(8%,15,0,100000)

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:

=PV(10%/12,60,200,60000,1)

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

discount rate.

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

●