NPV and IRR in Excel: The Off-By-One Error Almost Everyone Makes
Excel NPV does not do what its name suggests. It discounts the first value by one period, which quietly misprices every project where the outlay happens today. Here is the correct build, plus XNPV, IRR, and when IRR lies.
NPV and IRR in Excel
Net present value discounts every future cash flow back to today and subtracts what the project costs. Positive means the project earns more than your cost of capital; negative means it does not. It is the cleanest single measure of whether an investment is worth making.
Excel's NPV function computes this correctly, but not for the range most people hand it. It assumes the first value in the range arrives one period from now, which is almost never true of an initial outlay. Getting this wrong overstates or understates the answer by a full period of discounting, every time.
The off-by-one, demonstrated
A project: $120,000 spent today, then $35,000 back each year for six years, at a 10% discount rate. Put −120000 in B2 and the six inflows in B3 to B8.
Swipe sideways to compare columns.
| Formula | Result | Correct? |
|---|---|---|
| =NPV(0.10, B2:B8) | $27,769 | No, understated |
| =NPV(0.10, B3:B8) + B2 | $32,546 | Yes |
| =B2 + NPV(0.10, B3:B8) | $32,546 | Yes, same thing |
The reason is that Excel's NPV is really a present-value-of-an-annuity function. It discounts the first argument by one period, the second by two, and so on. An outlay that happens today should not be discounted at all, so it belongs outside.
XNPV: when the flows are not annual
NPV assumes evenly spaced periods. Real cash flows arrive on dates. XNPV takes an explicit date for each flow and discounts on actual days elapsed, which is both more accurate and easier to reason about.
XNPV is the safer default for anything irregular: a construction draw schedule, a project with a lumpy first year, or any model where the flows are not exactly twelve months apart. It also removes the off-by-one trap entirely, which is reason enough to prefer it.
IRR: the rate at which NPV hits zero
IRR is popular because a percentage is easier to talk about than a currency amount, and it needs no discount rate as an input. Both of those strengths are also where it misleads.
Three ways IRR misleads
It ignores scale
A $5,000 project returning 45% and a $500,000 project returning 18% have very different IRRs and very different value. IRR ranks the small one first. NPV, in currency, does not: 18% on half a million is worth vastly more. Where the two disagree and the projects differ in size, NPV is the one to follow.
It assumes reinvestment at the IRR
IRR implicitly assumes every interim cash flow is reinvested at the IRR itself. A project reporting 40% assumes you can redeploy each year's proceeds at 40%, which is rarely available. MIRR fixes this by taking an explicit reinvestment rate: =MIRR(values, finance_rate, reinvest_rate), and it usually returns a noticeably lower and more believable figure.
It can have several answers
Whenever the sign of the cash flows changes more than once, for example an outlay, inflows, a mid-life refurbishment, then more inflows, the equation can have multiple roots. Excel returns whichever it converges on from your guess, with no warning that others exist. On non-conventional flows, use NPV or MIRR rather than IRR.
Swipe sideways to compare columns.
| Situation | Use |
|---|---|
| Comparing projects of different sizes | NPV |
| Irregular or dated cash flows | XNPV and XIRR |
| Communicating a return to non-finance people | IRR, with NPV alongside |
| Interim cash cannot be reinvested at the IRR | MIRR |
| Cash flows change sign more than once | NPV or MIRR, never IRR alone |
| Deciding whether to proceed at all | NPV against your cost of capital |
Choosing the discount rate
The discount rate is the assumption that moves the answer most, and it is usually the least examined input in the model. For a company-wide project, the weighted average cost of capital is the standard starting point. For a riskier project than the business as a whole, add a premium and say so in the model rather than burying it.
Because the rate matters so much, run a one-way data table across a range of rates rather than presenting a single NPV. Put rates down a column, the NPV formula in the corner, and let the reader see where the project turns negative.
Swipe sideways to compare columns.
| Discount rate | NPV |
|---|---|
| 6% | $52,145 |
| 8%, | $41,769 |
| 10% | $32,546 |
| 14% | $16,896 |
| 18% | $4,167 |
| 20% | −$1,455 |
The row where NPV crosses zero is the IRR, which is why the two measures are the same statement read from different directions. Here it sits between 18% and 20%, consistent with the 18.4% IRR calculated above.
Try the capital expenditure ROI calculatorGet NPV, payback period, and ROI for a project without building the model first.A short checklist before you trust the output
- Is the period-zero flow outside NPV and inside IRR?
- Are the periods actually evenly spaced, or should this be XNPV?
- Is the discount rate stated as an input cell rather than typed into the formula?
- Do the cash flows change sign more than once? If so, distrust IRR.
- Are the cash flows after tax, and consistent about it?
- Is there a terminal value, and is it discounted like every other flow?
Why is my Excel NPV wrong?
Most likely you included the initial outlay inside the NPV range. Excel discounts the first value by one period, so an outlay made today gets discounted as if it were a year away. Run NPV over the future flows only and add the outlay outside the function.
What is the difference between NPV and XNPV?
NPV assumes evenly spaced periods and discounts the first value by one period. XNPV takes explicit dates and discounts by actual days elapsed from the first date, so the period-zero flow is included and undiscounted. XNPV is the safer choice for irregular timing.
Should I use NPV or IRR to choose between projects?
NPV, particularly where the projects differ in size. IRR ignores scale and can rank a small high-percentage project above a large valuable one. Report IRR alongside NPV for communication, but decide on NPV.
Why does IRR return #NUM!?
It failed to converge. Supply a guess as the second argument, such as 0.1, and check that the cash flows include at least one negative and one positive value. Multiple sign changes can also produce unstable results.
Written by
Do The Calculation Team
Do The Calculation
Do The Calculation is built by a small team of data analysts and spreadsheet developers. Where a guide depends on a published formula, standard, or government rule, the calculator it links to names that source directly so you can check the number yourself.
About the team