Excel's XNPV formula

Have you come across Excel's XNPV formula before?

It's a bit like Excel's XIRR function. To get it working what you have to do is assign dates to cash flows.

The problem with Excel's standard NPV formula.

The problem with the standard NPV formula is that it discounts the very first cash flow you point the formula to, whether you want it to discount that starting cash flow or not. If you made an investment right at the start of a project (often you would) the standard NPV formula could easily trip you up. You wouldn't normally want to discount that first cash flow.

XNPV has its own problems though

Immediately Excel's XNPV function becomes potentially attractive. It assigns dates to cash flows and, if you experiment with it, you'll find it doesn't discount the first cash flow you point it to. However, XNPV comes with a problem all its own. XNPV never discounts the first cash flow, no matter what date you have assigned to the initial investment. Even if your project is delayed and pushed out further, XNPV stays the same. Effectively the formula is assuming that the date of the first cash flow is your valuation date.

Fortunately there's a simple fix (but you have to know about it)! If you set your XNPV calculation up carefully you can easily discount the cash flows back to today or any valuation date that you choose. All you have to do, at the start of the calculation, is include a zero cash flow with your start valuation date attached.

If you click on the picture below, you can download an example where you can see all that working.

Watch out for Excel's financial functions in your modelling

All of these Excel modelling functions (IRR, XIRR, NPV and the XNPV formula) 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).