Page 238 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

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

212
Part 4: Advanced VBA