How to Calculate NPV in Excel: The Off-By-One-Period Mistake Almost Everyone Makes

How-to · Updated July 2026 · ~14 min read

Two people model the same machine purchase. Same cash flows, same 10% hurdle rate, same afternoon. One returns an NPV of 50,849, the other 46,227. Neither can find an error, because there isn't one in the arithmetic — one of them put the year-0 outlay inside NPV() and the other didn't. It is a 9% gap on every project in the pipeline, and it turns up in capital-appraisal packs constantly.

The cause is one design decision that never surfaces on screen: Excel's NPV function assumes the first value in your range arrives at the end of period 1, not today. This post builds the cash flow table, calculates NPV by hand so you can see what the function is doing, puts the wrong and right versions side by side, then covers the blank-cell trap, XNPV for real dates, the PV sign convention that catches lease schedules, choosing a discount rate, and when NPV and IRR disagree.

What Excel's NPV function actually does

=NPV(rate, value1, [value2], ...)

Microsoft documents an upper bound of 254 value arguments (as of 2026 — check the current function reference if you are anywhere near it), though one range is far more useful than a list. Three behaviours define it:

1. Cash flows must be equally spaced, one per period, in the order they appear in the range.
2. Every value is discounted at least one period. value1 is treated as arriving at the end of period 1.
3. rate is the discount rate per period. Annual rows need an annual rate; monthly rows need a monthly rate.

Point 2 is the whole problem, and a two-second test proves it. In any blank cell:

=NPV(10%,100)

It returns 90.91, not 100. One cash flow of 100, and Excel has already discounted it by a year. The function is misnamed: it is not a net present value function, it is a present-value-of-a-future-series function. The "net" — the outlay you make today — is your job, and it goes outside the brackets.

Build the cash flow table first

Before any formula, get the row right, because a perfectly discounted wrong number is still wrong. The line you discount is incremental free cash flow, not accounting profit:

• Start from the project's operating profit, then add back depreciation and amortisation — they are not cash (the schedule itself: depreciation in Excel). Deduct cash tax, and capex in the year it is paid.
• Include movements in working capital — the stock and debtors a new product ties up are real cash out, and the release at the end is real cash in.
• Exclude interest and financing flows. The cost of debt is already in the discount rate; putting it in the cash flow row charges for it twice.
• Exclude sunk costs. The feasibility study you have already paid for is irrelevant to the decision in front of you.

Lay it out horizontally, one column per period, with the rate in its own labelled cell:

Row 5 is =1/(1+$B$1)^B3 filled across — the period number in row 3 does the work, the absolute reference anchors the rate. Row 6 is =B4*B5. Then:

=SUM(B6:G6)     →   50,849.42

That is the NPV, done by hand, and it is the number every other method here has to reproduce. Keep the row even after you switch to NPV(): it is the only layout where a reviewer can point at one cell and see which year carries the project.

The correct NPV formula — and the one everybody writes

With the outlay in B4 and the future flows in C4:G4:

Correct:   =NPV($B$1,C4:G4)+B4          →   50,849.42
Wrong:     =NPV($B$1,B4:G4)             →   46,226.75

The second version hands the year-0 outlay to a function that discounts everything it is given, so every cash flow — including the one you are spending this morning — slides a year to the right. The result is not a random error. It is exactly:

true NPV ÷ (1 + rate)

50,849.42 ÷ 1.1 = 46,226.75. At 10% the mis-specified formula shaves 9.1% off the answer; at 6%, 5.7%; at 15%, 13.0%.

Two consequences worth being precise about, because this one gets overstated in both directions:

It will not usually flip an accept/reject decision on its own. Dividing by (1 + rate) preserves the sign, so a positive NPV stays positive. What it does is misstate the value created — and on a loss-making project it makes the loss look 9% smaller.

It absolutely can flip a ranking. Two projects appraised at different divisional hurdle rates get divided by different factors:

On the correct figures B wins by 1,000. On the broken ones A wins by 2,453. Same cash flows, reversed decision, and nothing on the face of the schedule looks odd.

Three ways to write it correctly

=NPV($B$1,C4:G4)+B4          Outlay outside — the standard form
=NPV($B$1,B4:G4)*(1+$B$1)    Whole range inside, rescaled by (1+rate)
=SUM(B6:G6)                  The manual discount-factor row above

All three return 50,849.42. Use the first: it is the version a reviewer recognises. The second is worth knowing for the moment you inherit a workbook, spot the bug, and want a fix that leaves the range alone.

To audit someone else's model, click the NPV cell and look at the highlighted range. If the highlight starts on the year-0 column, the model is wrong — unless the formula ends in *(1+rate). That is the whole check.

The blank-cell trap, which really does overstate

This one is nastier than the off-by-one, because it changes the shape of the timeline. NPV ignores empty cells and text inside a range. It does not treat them as zero, it drops them — and every later cash flow moves one period closer to today.

Take the same project with a shutdown year: no net cash in year 3.

The blank version discounts years 4 and 5 as if they were years 3 and 4, turning a clear no into a coin toss. The same happens with a text placeholder — "n/a", or a dash typed by hand rather than applied by a number format, both get skipped.

Two rules follow, and they belong in your model-review checklist:

• Type a hard 0 in every period with no cash flow. Never leave the cell empty, never type a dash.
• Check the count. =COUNT(C4:G4) should equal the number of periods. If it returns 4 where you expect 5, you have found it.

XNPV: when the cash flows land on real dates

NPV() assumes tidy annual periods. Real deals do not: completion falls on 14 March, milestone payments land on contract dates, and the first year is rarely twelve months long.

=XNPV(rate, values, dates)

Three things behave differently from NPV():

1. The first date is the valuation date, and it is discounted by zero days. So the year-0 outlay goes inside the range, with its own date. XNPV has no off-by-one problem — it has a first-date problem instead.
2. Discounting is by actual days over a fixed 365-day year: each flow is divided by (1+rate)^((date − first date)/365). There is no day-count convention to choose, and no leap-year adjustment.
3. rate is always the annual effective rate, regardless of how far apart the flows are.

Two errors to know: #VALUE! when values and dates hold different numbers of entries, and #NUM! when any date precedes the first one — so the earliest date must sit at the top of the list.

Run our project on calendar dates (outlay 31/12/2025, inflows each 31 December through 2030) and XNPV returns 50,801.30 against NPV()'s 50,849.42. The 48 difference is the 2028 leap day: XNPV counts 1,461 actual days to year 4 and divides by 365 anyway, discounting that flow for 4.0027 years. Immaterial here, material on a large deal, and a good reason for two teams to fail to tie out.

Use XNPV whenever the timing is genuinely irregular — staged capex, quarterly receipts, a project starting in September — and NPV when the model is a clean annual grid and everyone has agreed "year 1" means end of year 1. What you must not do is mix them in one pack and compare the outputs.

Sidebar: PV, and the sign convention that catches lease schedules

For a level stream — rent, a loan, a lease — PV() is quicker than laying out every period:

=PV(rate, nper, pmt, [fv], [type])

Two arguments cause the trouble:

pmt sign. PV returns the opposite sign to the payment. Enter the rent you pay as negative and you get a positive liability: =PV(6%,5,-24000) returns 101,096.73. Enter it positive and you get −101,096.73 and a schedule full of minus signs.

type. Omitted or 0 means payments at the end of each period; 1 means the beginning. Most leases are payable in advance, which is type 1:

A 6,065.80 difference — exactly one period of discounting — on a five-year lease at 24,000 a year. Getting type wrong understates the right-of-use asset and the lease liability from day one, and every unwinding entry after it inherits the error.

Rule of thumb: level payments, one rate, no gaps → PV(). Uneven cash flows → NPV(). Uneven cash flows on real dates → XNPV().

Choosing the discount rate

The rate does more to the answer than any single cash flow, and it is the assumption reviewers challenge first. Four practical points:

Start from WACC. The weighted average cost of capital — equity and after-tax debt weighted by their share of capital — is the base rate for a project carrying the same risk as the business. Riskier than that (new market, unproven technology) and it needs a premium on top; the premium is a judgement, so put it in a labelled cell rather than burying it in a rounded-up number.

Never mix real and nominal. Cash flows inflated to the money you expect to bank need a nominal rate; cash flows stated in current prices need a real rate. Inflated flows against a real rate is the commonest way an appraisal comes out badly too high.

Match the periodicity. Monthly rows need a monthly rate. From 12% a year, =(1+12%)^(1/12)-1 gives 0.9489% (effective, compounding to exactly 12%) while =12%/12 gives 1.00% (nominal). Both conventions are defensible; using one while describing the other is not.

Test it. Put the NPV formula in a cell, list candidate rates down the column to its left, select the block, then Data tab > Forecast group > What-If Analysis > Data Table and set Column input cell to the rate cell. You get NPV across every rate at once — and the point where it crosses zero is your IRR, read straight off the table. The two-variable version (rate against a growth assumption) is where appraisal becomes modelling proper; the layout discipline for that is in building a financial model in Excel.

How HISAB 360 helps

Capital appraisal is not hard arithmetic, it is bookkeeping about your own assumptions — a cash flow row that reconciles to the ledger, a rate cell nobody has hard-coded over, hard zeros where a period has no cash, the outlay outside the brackets. That is a lot of small discipline to hold across a pipeline of proposals.

HISAB 360 is a paid Excel add-in with an AI chat panel docked inside Excel that builds the appraisal in the live workbook: ask for a five-year capex model with a discount-factor row, an NPV() cross-check and a data table over rates, and it writes the sheet — formulas, layout and formatting — rather than handing you text to paste. Because it also runs Python inside Excel from a bundled runtime, scenario work that would be a forest of nested data tables (a hundred rate-and-volume combinations, a spread of outcomes) can be computed and written back as a table you can chart.

The cash flows underneath can come from the ledger rather than a stale export: it connects to QuickBooks Online, Xero, Zoho Books, Odoo, FreshBooks and Sage Accounting, pulling invoices, bills and GL activity into sheets, so a project's actual spend can sit beside the appraisal that approved it — the post-implementation review most capex processes promise and skip.

Honest limits: Windows desktop Excel only (Microsoft 365 / 2016+, 64-bit), not Mac and not Excel on the web, and it is paid — the trial is 15 days with 50 AI credits, no card. A generated model earns the same review as a junior's: check the range in the NPV cell, check the rate, tie one year back by hand.

NPV vs IRR: what each one answers

They are the same equation solved for different unknowns. NPV fixes the rate and returns value in currency. IRR fixes value at zero and returns the rate.

For a conventional project — one outflow, then inflows — they agree on accept or reject, every time. Where they part company is ranking mutually exclusive options:

S wins on IRR by seven percentage points and creates barely a fifth of the value. A rate is blind to scale — it cannot tell you that 18% on 100,000 beats 25% on 10,000 when you can only do one. IRR also assumes interim cash is reinvested at the IRR itself, optimistic on a high-IRR project, and it breaks outright when cash flows change sign more than once (a decommissioning cost in the final year), where there may be several valid IRRs or none.

The rule when they conflict: NPV decides. Report IRR alongside it — boards think in percentages, and it is a useful sanity check on the hurdle rate — but accept, reject and rank come off the NPV column. The mechanics of IRR, XIRR and MIRR are in calculating IRR in Excel.

Once approved, the appraisal stops being a decision tool and becomes a baseline: actual cash against the case that won the money, which is ordinary variance analysis on a longer reporting period, off the same ledger that produces your P&L.

The one-page version

Frequently asked questions

Try HISAB 360 on your own workbook

HISAB 360 is an AI assistant inside Excel for accountants and finance teams — it writes macros, Power Query and formulas from plain English, and connects two-way to QuickBooks, Xero, Zoho Books, Odoo, FreshBooks and Sage. The 15-day trial is the full product, no card required.