Excel’s XNPV function

XNPV works a little bit like XIRR. The formula requires you to assign dates to cash flows. It’s easy to use.

The benefit of Excel’s XNPV function

Excel’s XNPV function is immediately attractive. Unlike the NPV function, XNPV will not discount the first cash flow you point it to. It side-steps a big potential trap there but, in doing so, creates a possible trap of its own.

The problem with the XNPV formula

XNPV doesn’t discount the initial cash flow (great). However, no matter when that cash flow occurs (no matter how far into the future that cash flow occurs, no matter what date you expect that cash flow to happen, no matter how long your wonderful project ends up being delayed) XNPV still won’t discount that first cash flow. Really, without telling you, what the XNPV formula is doing is assuming that your valuation date is the same as the date of that very first cash flow.

Discounting to today’s date

If you want to discount to a particular valuation date (or today’s date) you have to be a bit careful how you set up and apply Excel’s XNPV formula. The good news is the adjustment is easy (but you need to know about it!). All you need to do is incorporate a first zero cash flow attached to your valuation date. See example (iii) in the Excel spreadsheet attached: XNPV. That example shows you how to apply XNPV for a particular valuation date.

Excel modelling with the XNPV formula

Watch out for Excel’s financial functions in your modelling

All of these Excel modelling functions (IRR, XIRR, NPV and XNPV) are designed to be quick and easy to use. Unless you understand exactly how they work though, there is a risk you make a mistake in applying them (and reach the wrong decision).

Excel financial maths: course summary

Click here for the Excel financial modelling course summary. There’s also a short multi-choice quiz you can sit should you want to test your knowledge.