Microsoft Office Tutorials and References

In Depth Information

**Chapter 12: Discounting and Depreciation Formulas**

Figure 12-1:
Three methods of computing NPV.

The formulas in row 9 are as follows:

B9: =NPV(8%,B4:B8)

C9: =NPV(8%,C5:C8)+C4

D9: =NPV(8%,D4:D8)*(1+8%)

The formula in B9 produces a result that differs from the other two. It assumes the $20,000

investment is made one month from now. There are applications where this is useful, but they

rarely if ever involve an initial investment. The other two formulas answer the question of

whether a $20,000 investment right now will earn 8%, assuming the future cash flows. The

formulas in C9 and D9 produce the same result and can be used interchangeably.

NPV function examples

This section contains a number of examples that demonstrate the NPV function.

All the examples in this section are available in the workbook
net present value.

xlsx
on the companion CD-ROM.

Initial investment

Many NPV calculations start with an initial cash outlay followed by a series of inflows. In this

example, the Time 0 cash flow is the purchase of a snowplow. Over the next ten years, the plow

will be used to plow driveways and earn revenue. Experience shows that such a snowplow lasts

ten years. After that time, it will be broken-down and worthless. Figure 12-2 shows a worksheet

set up to calculate the NPV of the future cash flows associated with buying the plow.

The NPV calculation in cell B18 uses the following formula, which returns –$19,880.30:

=NPV($B$3,B7:B16)+B6