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 have 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 are spending right now as though it happens next year. On the four-year project below that is $1,671.52, or 9% of the answer, and it never once shows an error.

Grab a coffee. We’ll fix it properly: the classic patch that has been correct since forever, then the XNPV/XIRR pair every analyst reaches for the moment real dates enter the picture.

Who this quietly bites

You, if anything you build ends in a go/no-go decision. Capital budgeting, DCF valuation, lease-versus-buy, infrastructure, loan pricing — all of it is the same machine, and all of it runs on the assumption that Excel discounted each flow by the number of periods you meant.

The pattern is always the same. Someone lays out a cash-flow row with the investment in the first cell, because that is where the investment goes. They select the whole row, type =NPV(WACC, ...), and the model returns a positive number. The project clears its hurdle. Nobody checks, because there is nothing to check — the formula is spelled correctly, the range is right, and the answer is plausible.

It is also 9% low, in a direction that kills good projects rather than approving bad ones. The version of this error that approves a bad project is the same mistake pointed the other way, and it is just as easy to make.

Why NPV() gets it wrong (with receipts)

NPV discounts the first value by one period, the second by two, and so on. It has no concept of “time zero” and no way to be told about one. Put your initial outlay inside the range and it gets discounted a full year — money spent today, valued as though it is a year away.

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

Same inputs, a $1,671.52 gap — 9.1% 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 — and it moves in step with the discount rate, so the higher your cost of capital, the more it costs you.

The fix, two ways

The scenario, in full. $100,000 out on day one. $30,000, $40,000, $50,000 and $30,000 back over the four years that follow. Required return 10%. Two versions of the same money: one on tidy annual dates, one on the dates a real project actually pays on — 15 Jan 2026, 31 Dec 2026, 30 Nov 2027, 31 Dec 2028, 30 Sep 2029.

Option 1 — Classic (backward-compatible functions, step by step)

Step 1 — Keep the time-zero flow out of NPV.

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

=Initial + NPV(Disc_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. Correct, backward-compatible to Excel 2003, and the version to reach for whenever your cash flows land at tidy one-period intervals.

Step 2 — Prove it, by discounting every flow by hand.

This is the step that turns a rule you have memorised into a rule you believe. Two columns beside the cash flows:

D17  =C17/(1+Disc_Rate)^A17
E17  =C17/(1+Disc_Rate)^(A17+1)

Column D uses the period number as written — so period 0 gets an exponent of zero, is multiplied by 1, and passes through untouched. Column E adds one to every exponent.

Sum them. Column D lands on $18,386.72. Column E lands on $16,715.20. Column E is not a straw man: it is literally what NPV(rate, values) computes, written out. That single +1 is the entire bug.

Step 3 — Use IRR only where it applies.

=IRR(Flows)

Returns 18.03% here. IRR follows exactly the same even-period assumption as NPV, with one useful difference: the initial outlay does belong inside it, because IRR solves for the rate that makes the whole series net to zero and the series has to include what you paid.

Option 2 — Excel 365 spill (dynamic-array equivalent)

Same maths. But on 365 you can write the discounting rule out loud, which means the time-zero assumption stops being something you have to remember.

Step 1 — The whole valuation, in one cell.

=LET(
   cf,  Flows,
   per, SEQUENCE(ROWS(cf),,0),
   SUM(cf/(1+Disc_Rate)^per)
)

SEQUENCE(ROWS(cf),,0) is the fix stated explicitly: the exponents start at zero, so the day-one outlay is raised to the power of nothing, multiplied by 1, and left alone. NPV() starts them at one and cannot be told otherwise. Returns $18,386.72, and there is no “remember to add the outlay outside” step to forget.

Step 2 — Spill the whole discounting block.

=LET(
   cf,  Flows,
   per, SEQUENCE(ROWS(cf),,0),
   df,  1/(1+Disc_Rate)^per,
   pv,  cf*df,
   HSTACK(per, cf, df, pv, SCAN(0, pv, LAMBDA(a,b, a+b)))
)

Five columns from a single cell: period, cash flow, discount factor, present value, cumulative present value. Change a flow and the block redraws. Add a row to the named range and it grows one.

Step 3 — Ask the same question at every rate at once.

=LET(
   cf,    Flows,
   per,   SEQUENCE(ROWS(cf),,0),
   rates, SEQUENCE(9,,0.04,0.02),
   HSTACK(rates, MAP(rates, LAMBDA(r, SUM(cf/(1+r)^per))))
)

MAP runs the valuation once per rate and spills the answers beside them — a sensitivity ladder from 4% to 20% in one cell, no data table, no dialog box. Where that column crosses zero is the IRR; IRR() will tell you exactly, but seeing the slope is usually the more useful thing in a meeting.

XNPV and XIRR — for cash flows on real dates

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

=XNPV(Disc_Rate, Real_Flows, Real_Dates)
=XIRR(Real_Flows, Real_Dates)

On our real dates that is $19,605.89 and 18.93% — $1,219 more than the tidy annual version, because three of the four inflows actually land earlier than the anniversary model pretended.

Two differences make these the professional default. First, you pass an actual dates column, so each flow is discounted by its exact day count. 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 is correctly left undiscounted.

That second point is worth saying plainly, because it is the one that trips people who have just learned the NPV rule: the outlay goes OUTSIDE NPV and INSIDE XNPV. They are opposite, and both are right.

Written out, XNPV is this:

=LET(
   cf,  Real_Flows,
   d,   Real_Dates,
   d0,  INDEX(d,1),
   yrs, (d-d0)/365,
   HSTACK(d, cf, yrs, cf/(1+Disc_Rate)^yrs)
)

Subtract the first date from every date, divide by 365, discount by that fractional exponent. Sum the last column and you get XNPV to the cent.

Where else this pattern pays for itself

The mistake is not really about NPV. It is about assuming a function knows when your money moved:

• Capital budgeting — approving or killing capex against a hurdle rate, where 9% is often the entire margin.
• DCF valuation — the engine under every company and equity valuation, and the one place a systematic 9% understatement compounds into a very confident wrong answer.
• Lease-versus-buy and make-versus-outsource — comparing options whose cash flows land at genuinely different times, which is the whole reason you are comparing them.
• Real estate and infrastructure — long, irregular flows where XNPV is not a preference.
• Loan and bond pricing — solving for the rate that sets present value to par, which is XIRR wearing a different hat.
• Anything with a mid-year convention — a legitimate technique, and a completely different adjustment from this one. Applying half a period of discounting to money that has already left the bank is not a convention, it is an error.

Download the workbook

Everything above, live, with the classic and 365 builds side by side and a Check sheet that proves they agree.

Five sheets: Inputs (all yellow, all yours) holds the discount rate and both copies of the same money — one on tidy annual dates, one on the dates a real project pays on. Classic computes every method with functions that ship in any Excel, then reproduces both NPV answers from scratch in two hand-discounted columns so you can see the exponent each one used, and does the same for XNPV with explicit day counts. 365 writes the valuation as a single LET, spills the discounting block from one cell, and builds a rate-sensitivity ladder with MAP. Check ties the classic and 365 numbers to the cent, proves net present value at the IRR really is zero, confirms that the hand-built “one period too far” column reproduces NPV() exactly, and prices four wrong answers so you can watch them move.

Download the NPV / XNPV / IRR calculator (.xlsx)

The 365 sheet needs Microsoft 365 or Excel 2021+. The Classic sheet opens in anything back to 2003.

Takeaways

• Excel’s NPV discounts the first value by a full period. It has no time zero and cannot be given one, so never put your day-one outlay inside it.
• Classic fix: =Initial + NPV(Rate, future_flows). Correct in every Excel version, and worth proving once by hand so it stops being a rule you have to remember.
• The 0.909091 tell. If wrong ÷ right equals 1 / (1 + rate) exactly, you have this bug and not a data problem.
• IRR shares the same even-period assumption — valid only when every flow is exactly one period apart. It reads positions, not dates.
• XNPV and XIRR are the professional default once dates are real. They discount by exact day count and treat the first date as time zero for you — so the outlay goes outside NPV and inside XNPV. Opposite rules, both correct.
• XNPV’s day count is fixed at 365, leap years included. That is why it will never quite agree with NPV, and why reconciling it to a 30/360 system is a real difference rather than rounding.
• Your move: open the model you are closest to shipping and check one thing — is the first cell of the NPV range your investment? If it is, multiply the answer by (1 + rate) and see how much the recommendation moves.