NPV vs XNPV: Key Differences, When to Use Each Formula
NPV is Excel’s standard Net Present Value formula that discounts cash flows at equal intervals. XNPV does the same but accepts actual, uneven cash-flow dates, using daily discounting for precision.
Finance managers often paste the same cash-flow list into both formulas and wonder why the numbers differ—until a quarterly payment lands on a weekend or holiday. The mismatch is usually discovered during the last-minute board-pack check.
Key Differences
NPV assumes equal periods and needs only a single discount rate. XNPV demands every cash-flow date, turning irregular calendars into exact day counts. The result: XNPV yields sharper valuations for projects with milestone payments or seasonal income.
Which One Should You Choose?
If your model uses yearly or monthly columns, NPV is simpler and faster. For real-world deal sheets—rentals, construction draws, or M&A earn-outs—switch to XNPV to avoid hidden rounding errors that can swing IRR by basis points.
Examples and Daily Life
Imagine buying a rental condo: rent arrives on the 5th of each month, but the down payment left your account on a random Tuesday. NPV would round the timeline; XNPV logs the exact 73-day gap before the first rent, giving you a truer profit picture.
Can I use XNPV without exact dates?
No—XNPV throws a #VALUE! error; every cash flow needs a matching calendar date.
Does NPV include the initial investment?
Not automatically. Enter the outflow as period 0, or subtract it outside the formula.
Which formula do auditors prefer?
Auditors favor XNPV when cash-flow timing is irregular, because it minimizes period-length assumptions.