Microsoft Office Tutorials and References

In Depth Information

**The Basic Excel Financial Functions**

h
The payment amount and the lump sum amount were laid out in the deal.

h
The type argument is 1 because the brother-in-law wants the first payment now (in

advance).

Figure 11-3:
Calculating a present value of an annuity with a lump sum.

The formula tells us that the value of all those future cash flows is $45,958.83. According to the

terms of this deal and your assumptions, you could make more money investing your $50,000

elsewhere.

You can plug in different values for the arguments until you find a solution that is

favorable — and then make a counter proposal to your brother-in-law. You can even use

Excel’s Goal Seek feature (Data

➜

Data Tools

➜

What-If Analysis

➜

Goal Seek) to find the

value of an argument that results in your desired present value.

Calculating future value

The future value is the other side of the time value of money coin. It calculates how much a

known quantity of money (or a known series of payments) will be worth at some point in the

future. The syntax for the FV function follows. Arguments in bold are required arguments.

FV(rate, nper, pmt, pv, type)

Future value of payments

For this example, assume you start a savings account for your new baby’s college education.

Starting next month, you’ll put $50 per month in the account, and you’ll earn 3% interest. The

formula that follows shows that, in 18 years, the account will have $14,297.02 (see Figure 11-4):

=FV(3%/12,18*12,–50,0,0)