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