Microsoft Office Tutorials and References

In Depth Information

The formulas in column D use the discount rate (in cell D3) to calculate the present values. For example, the

formula in cell D6 is

=PV($D$3,A6,0,–B6)

The sum of the values in column D is equal to the NPV.

For serious applications of NPV and IRR functions, it is an excellent idea to use this type of cross-checking.

Irregular Cash Flows

All the functions discussed so far — NPV, IRR, and MIRR — deal with cash flows that are
regular.
That is,

they occur monthly, quarterly, yearly, or at some other periodic interval. Excel provides two functions for deal-

ing with cash flows that don't occur regularly: XNPV and XIRR.

Net present value

The syntax for XNPV is

XNPV(rate,values,dates)

The difference between XNPV and NPV is that XNPV requires a series of dates to which the values relate. In

the example shown in Figure 12-11, the NPV of a series of irregular cash flows is found using XNPV.