Microsoft Office Tutorials and References

In Depth Information

In the preceding formula, once again,
rate
is the interest rate per period (usually an annual

rate divided by 12),
nper
is the total number of payment periods (in this case, 12 * 15, or 180),

pmt
is the payment made each period (1186.19 in this case),
fv is the future value, or a cash

balance you want to attain after the last payment is made (the future value of a loan is 0), and

type
indicates if a payment is due at the end of the month (0, the default, means you pay at the

end of the month, and 1 means you pay at the beginning of the month). So, for the loan

described above, you would create this formula:

=PV(5%/12, 180, -1186.19)

The preceding formula generates a present value of $150,000, the total amount of the loan.

The sum total of the payments is actually $213,514.20, but the present value of the loan is

lower because, when the loan is made, the interest hasn’t had time to compound. After 10

years of payments, for example, a homeowner would have paid $142,342.80 but would still

be facing another 60 payments!

Determining the Net Present Value of an Investment

A function that’s related to PV is NPV, which calculates the
net present value
of an investment.

The primary difference between present value and net present value is that net present value

assumes the value of investment decreases over time due to inflation or another discount

rate. That assumption is reflected in the NPV function’s syntax.

NPV(rate, value1, value2, value3…)

Another difference between present value and net present value calculations is that the values

in an NPV formula can vary, but in a PV calculation the payments must be constant over the

life of the annuity. For example, you might consider making a $5,000 investment in a security

that would pay you $1,000 after the first year, $1,500 after the second year, $2,000 after the

third year, and $2,500 after the fourth year. Assuming 4 percent inflation, you would create

the following formula to evaluate the investment:

=NPV(4%, -5000, 1000, 1500, 2000, 2500)

The preceding formula generates a result of $1,214.78. There’s no magical threshold where

the net present value of an investment makes it worthwhile; you need to compare the return

of several investments to determine whether and how to invest your money.

For more information on using Excel to perform in-depth financial analysis, see
Data Analysis and

Business Modeling with Microsoft Excel
, by Wayne L. Winston (Microsoft Press, 2004).