Excel 365: Why Your NPV Is Wrong (and the XNPV Fix Analysts Swear By)

Okay, real talk: =NPV(rate, values) is one of the most confidently wrong formulas in corporate finance. You select your cash flows, type the function, get a clean dollar figure, drop it in the board deck — and you’ve just discounted your entire project by one period too many. Excel’s NPV assumes the first number in your range arrives one full period from today, not today. Feed it your day-one capital outlay and it dutifully discounts money you’re spending right now as if it happens next year. The result is off by thousands, and it never once shows an error.

Let’s fix it properly — the classic patch that’s been correct since forever, and the XNPV/XIRR pair that every analyst reaches for once real dates enter the picture.

What NPV is supposed to do

Net present value answers one question: is this project worth more than it costs, in today’s money? You discount every future cash flow back to the present at your required rate of return, add them all up (the day-one investment included, as a negative), and if the total is positive, the project creates value. Simple idea. The entire game is in when each cash flow happens — and that’s precisely where Excel’s NPV trips you.

Why NPV() gets it wrong (with receipts)

Excel’s NPV discounts the first value by one period, the second by two, and so on. It has no concept of “time zero.” So if you include your initial outlay inside the range, it gets discounted a year — money spent today, valued as if it’s a year away.

Take a $100,000 investment returning $30k, $40k, $50k and $30k over four years at a 10% discount rate:

Same inputs, a $1,815 gap — about 10% of the answer, because the entire project got shoved one year into the future. On a bigger model that error is the difference between a project clearing its hurdle rate and getting killed in committee.

The fix, two ways

Option 1 — keep NPV(), but discount only the future flows

The moment-of-clarity rule: NPV is for future cash flows only. Your time-zero outlay is already in today’s money, so it doesn’t get discounted — you add it outside the function:

=Initial + NPV(Rate, future_flows)

Initial is your day-one number (negative), added straight in. NPV then discounts only the flows that genuinely arrive in period 1 onward. This is correct, backward-compatible to Excel 2003, and the version you should reach for whenever your cash flows land at tidy one-year intervals.

For the internal rate of return, the plain IRR follows the same even-period assumption — it’s fine as long as every flow is exactly one period apart:

=IRR(all_flows_including_initial)

Option 2 — XNPV and XIRR: for cash flows on real dates

Real projects don’t pay on neat anniversaries. Money arrives on the 14th, the quarter-end, 47 days later. The instant timing is irregular, NPV and IRR are simply the wrong tools — they can’t see dates. XNPV and XIRR can:

=XNPV(Rate, values, dates)
=XIRR(values, dates)

Two differences make these the professional default. First, you pass an actual dates column, so each flow is discounted by its exact day count — no even-period fiction. Second, XNPV treats the first date as time zero automatically, so you do include your initial outlay in the range, on its own date, and it’s correctly left undiscounted. Put simply: XNPV fixes the day-one bug and handles messy real-world timing, in one function. Once a model has real dates in it, analysts basically stop using NPV altogether.

Where this shows up in real decisions

• Capital budgeting — approving or killing capex projects against a hurdle rate.
• DCF valuation — the engine under every company and equity valuation.
• Lease-vs-buy and make-vs-outsource — comparing options with different cash-flow timing.
• Real estate and infrastructure — long, irregular cash flows where XNPV is non-negotiable.
• Loan and bond pricing — solving for the rate that sets present value to par.

Download the workbook

The four-year example above lives in a free workbook that shows all four numbers next to each other — the wrong NPV, the corrected Initial + NPV, and the XNPV/XIRR pair on real dates — so you can see exactly how much the timing assumption moves the answer. Blue cells are inputs; change a cash flow or the discount rate and every method updates live, with the error gap called out in red.

Download the NPV / XNPV calculator (.xlsx)

Takeaways

• Excel’s NPV discounts the first value by a full period — it has no “time zero,” so never put your day-one outlay inside it.
• Classic fix: =Initial + NPV(Rate, future_flows) — add the undiscounted outlay outside the function. Correct in every Excel version.
• IRR shares the same even-period assumption; it’s only valid when every cash flow is exactly one period apart.
• For real dates and irregular timing, XNPV(Rate, values, dates) and XIRR(values, dates) are the professional default — they discount by exact day count and treat the first date as time zero for you.
• A single wrong period assumption can swing NPV by ~10%, which is often the entire margin between a project that gets funded and one that doesn’t.