Microsoft Office Tutorials and References

In Depth Information

**The FV function**

The result, –139.48, tells you not to expect a net profit from this investment. Note that the

negative values in this formula indicate the money you spend on your investment. (You can

use the Goal Seek command to determine what initial cost or interest rate would justify

the investment. For more information about this command, see “Using the Goal Seek

command” in Chapter 18, “Performing a what-if analysis.”)

This formula does not include the up-front cost of the investment as an argument for the

NPV function. However, if you fund the initial $250,000 investment at the end of the first

year instead of at the beginning, the formula is =NPV(8%,(–250000–85000), 95000, 140000,

185000). The result, $18,379.04, suggests that this might be a profitable investment.

The FV function

The FV function determines the future value of an investment and is essentially the

opposite of present value, computing the value at some future date of an investment that makes

payments as a lump sum or as a series of equal periodic payments. This function takes the

arguments
rate
,
number of periods
,
payment
,
present value
, and
type
; for definitions of these

arguments, see Table 16-1. Use the
payment
argument to compute the future value of a

series of payments and the
present value
argument to compute the future value of a

lumpsum payment.

Suppose you’re thinking about starting an IRA. You plan to deposit $5,000 in the IRA at the

beginning of each year, and you expect the average rate of return to be 3 percent per year

for the entire term. Assuming you’re now 30 years old, how much money will your account

accumulate by the time you’re 65? Use the formula =FV(3%, 35, –5000,, 1) to learn that

your IRA balance will be $311,379.72 at the end of 35 years.

Now assume you started an IRA account three years ago and have already accumulated

$12,500 in your account. Use the formula =FV(3%, 35, –5000, –12500, 1) to learn that your

IRA will grow to $346,553.00 at the end of 35 years.

In both of these examples, the
type
argument is 1 because payments occur at the

beginning of the period. Including this argument is particularly important in financial calculations

that span many years. If you omit the
type
argument (1) in the preceding formula, Excel

assumes you add money to your account at the end of each year and returns the value

$337,483.69—a difference of $9,069.31.

The PMT function

The PMT function computes the periodic payment required to amortize a loan over a

specified number of periods. This function takes the arguments
rate
,
number of periods
,
present

value
,
future value
, and
type
; for definitions of these arguments, see Table 16-1.