NPV vs IRR in Excel: Which Wins for Capital Budgeting (2026)

July 13, 2026 · VeloraAI Team
Data Analysis Financial Modeling Formulas

A 2024 Duke CFO survey found that 75% of large-cap CFOs use IRR as their primary capital budgeting metric — despite decades of academic research showing IRR can rank projects incorrectly, produce multiple answers, or disappear entirely on non-conventional cash flows. If you are comparing NPV vs IRR in Excel to choose between projects, understanding when the two metrics agree — and more importantly, when they disagree — is the difference between a defensible investment recommendation and a career-limiting one.

This guide walks through every NPV and IRR formula in Excel, explains why they can point in opposite directions, and gives you the decision framework senior analysts actually use in front of an investment committee.

What Is NPV in Excel?

Net Present Value (NPV) is the sum of a project's cash flows discounted at the required rate of return, minus the initial investment. In Excel, NPV() assumes equal-period cash flows starting at period 1, while XNPV() handles irregular dates. A positive NPV means the project creates value above the discount rate; a negative NPV means it destroys value.

The mathematical definition:

NPV = Σ CFₜ / (1 + r)ᵗ − Initial Investment

Where CFₜ is the cash flow in period t, r is the WACC or project-specific discount rate, and t is the period number.

The Excel NPV Function (and Its Off-by-One Trap)

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

Excel's NPV() assumes the first cash flow occurs at the end of period 1 — not at time zero. This trips up nearly every analyst at least once. If your initial investment is in cell B2 and future cash flows are in C2:H2, the correct formula is:

=NPV(10%, C2:H2) + B2

Note the + B2 — the initial investment (typically negative) is added outside the NPV function because it occurs at t = 0, not t = 1. Wrapping B2:H2 inside NPV() double-discounts the initial outlay and understates the true NPV by roughly (1 + r).

⚠️ Warning: The most common Excel NPV mistake is including the t=0 cash flow inside NPV(). Always exclude the initial investment from the range and add it back outside the function.

XNPV for Real-World Dates

=XNPV(rate, values, dates)

XNPV() uses actual dates and computes daily discounting — the correct approach for lumpy or irregular cash flows (M&A closings, project milestones, real estate stabilization). Use it whenever periods are not exactly 12 months apart:

=XNPV(10%, B2:B10, A2:A10)

Where A2:A10 holds transaction dates and B2:B10 holds signed cash flows (initial investment negative).

What Is IRR in Excel?

Internal Rate of Return (IRR) is the discount rate at which NPV equals zero — the effective compound annual return the project generates on invested capital. In Excel, IRR() assumes equal periods, XIRR() handles actual dates, and MIRR() corrects for the unrealistic reinvestment assumption baked into standard IRR.

The Three IRR Functions

=IRR(values, [guess])
=XIRR(values, dates, [guess])
=MIRR(values, finance_rate, reinvest_rate)

Unlike NPV(), the IRR() family expects the initial investment inside the range — the sign convention (negative outflow, positive inflows) is what tells Excel where t = 0 lives. Example for a five-year project:

=IRR(B2:B7)
=XIRR(B2:B7, A2:A7)

💡 Pro Tip: Always prefer XIRR() over IRR() in production models. XIRR handles quarter-close timing, deal delays, and mid-period closings correctly; IRR silently assumes every cash flow lands on an anniversary of period zero.

The Reinvestment Rate Assumption

Standard IRR implicitly assumes intermediate cash flows are reinvested at the IRR itself. If your project has a 42% IRR, IRR math assumes every dollar it throws off gets reinvested at 42% — which is almost never true. MIRR() fixes this by letting you specify a realistic reinvestment rate (typically your cost of capital):

=MIRR(B2:B7, 10%, 8%)

Here 10% is the financing rate for negative cash flows and 8% is the reinvestment rate for positive cash flows. MIRR is always closer to your true realized return than IRR when the project generates significant intermediate cash flows. For a deeper comparison of IRR, XIRR, and MIRR syntax — including the monthly annualization trap and real-world date handling — see our complete guide to IRR vs XIRR vs MIRR in Excel.

How Do You Calculate NPV and IRR in Excel Step by Step?

To calculate NPV and IRR in Excel for a capital budgeting decision: (1) lay out signed cash flows with dates, (2) apply XNPV() and XIRR() to the full series, (3) compare NPV to zero and IRR to your hurdle rate, and (4) rank projects by NPV (not IRR) when they are mutually exclusive.

Here is a concrete example. Assume a project requires a $500,000 investment today and produces the following cash flows:

Date Cash Flow
2026-01-01 ($500,000)
2026-12-31 $120,000
2027-12-31 $150,000
2028-12-31 $180,000
2029-12-31 $200,000
2030-12-31 $220,000

Step 1: Structure the Data

Put dates in column A (A2:A7) and cash flows in column B (B2:B7). Use negative signs for outflows and positive signs for inflows. Never use text like "($500,000)" — Excel needs real numbers for XNPV and XIRR to work.

Step 2: Set the Discount Rate

Place your WACC or hurdle rate in a named cell, say WACC = 10%. Using a named range makes sensitivity testing trivial and keeps the formula readable.

Step 3: Compute the Four Core Metrics

NPV       =XNPV(WACC, B2:B7, A2:A7)
IRR       =XIRR(B2:B7, A2:A7)
MIRR      =MIRR(B2:B7, WACC, WACC)
Payback   =MATCH(TRUE, SCAN(0, B2:B7, LAMBDA(a,v, a+v))>=0, 0)-1

The SCAN() + LAMBDA() combo computes cumulative cash flow and finds the first period where it turns non-negative — a modern replacement for the clunky cumulative-column payback method most templates still use.

Step 4: Interpret Against Your Hurdle Rate

  • NPV > 0 → project earns more than WACC; accept on a standalone basis.
  • IRR > WACC → same signal, expressed as a rate rather than a dollar amount.
  • MIRR → the return you would realistically pocket if intermediate cash flows earn WACC.

For the numbers above with WACC = 10%: NPV ≈ $154,000, IRR ≈ 19.9%, MIRR ≈ 15.2%. All three say "accept" — but notice IRR overstates the realized return by nearly 500 basis points versus MIRR.

Example: A $500K investment producing $870K of undiscounted cash flows over 5 years shows a 19.9% IRR, but MIRR at 15.2% reflects the actual compounded return when reinvestment happens at cost of capital, not at the project's own IRR.

NPV vs IRR vs MIRR: A Direct Comparison

The three metrics answer different questions. Choosing the wrong one leads to ranking errors that compound across a portfolio of investment decisions.

Metric Excel Function Reinvestment Assumption Output Units Best For Fails When
NPV NPV, XNPV At discount rate (WACC) Dollars Mutually exclusive projects, absolute value creation Never — always unique and correct
IRR IRR, XIRR At IRR itself (unrealistic) Percent Independent projects with similar scale Non-conventional cash flows, mutually exclusive projects, sign changes
MIRR MIRR At user-specified rate Percent Adjusting IRR for realistic reinvestment Requires assumption about reinvestment rate
Payback Cumulative sum None (ignores time value) Years Liquidity screens, quick filters Ignores everything after payback point
Discounted Payback Cumulative XNPV At discount rate Years Risk-focused screens Still ignores post-payback cash flows

When Do NPV and IRR Give Conflicting Signals?

NPV and IRR always agree on accept/reject for a single project with conventional cash flows. They disagree on ranking when you have to choose between mutually exclusive projects — and the disagreement comes from four specific sources.

1. Scale Differences (The Small-High-IRR Trap)

A $1M investment with a 50% IRR generates less absolute wealth than a $50M investment with a 20% IRR, but IRR rankings will pick the smaller project. When your capital budget is not constrained, always rank by NPV.

Project A: −$1M → +$1.5M in Year 1        NPV @10% = $364K   IRR = 50%
Project B: −$50M → +$60M in Year 1        NPV @10% = $4.5M   IRR = 20%

Project B creates 12× the shareholder value despite lower IRR.

2. Timing Differences (The Reinvestment Wedge)

When one project front-loads cash flows and another back-loads them, IRR favors the front-loaded project because it assumes reinvestment at IRR. NPV correctly discounts back-loaded cash flows at the risk-appropriate rate, so it often prefers the "slower" project when the discount rate is moderate.

3. Non-Conventional Cash Flows (Multiple IRRs)

If cash flows change sign more than once (e.g., mining projects with reclamation costs, ad campaigns with pulse spending), IRR can produce multiple mathematically valid answers or none at all. Excel's IRR() returns whichever solution it finds first based on the guess argument — a dangerous silent failure. NPV is always unique.

⚠️ Warning: If your cash flow series has more than one sign change (e.g., −, +, +, −, +), IRR is mathematically ambiguous. Use NPV plus MIRR instead — IRR should not appear in the recommendation.

4. Different Project Lives

Comparing a 3-year project to a 10-year project on IRR alone ignores what you do with the capital after the shorter project ends. Use equivalent annual annuity (EAA) or extend the shorter project's reinvestment assumption explicitly before ranking.

graph TD
    A[Two Projects to Rank] --> B{Mutually Exclusive?}
    B -->|Yes| C{Same Scale & Life?}
    B -->|No, Independent| D[Accept all with NPV > 0]
    C -->|Yes| E{Conventional Cash Flows?}
    C -->|No| F[Rank by NPV, use EAA if lives differ]
    E -->|Yes| G[NPV and IRR agree — use IRR for communication]
    E -->|No, Multiple Sign Changes| H[Use NPV + MIRR, discard IRR]
    F --> I[Recommend NPV winner]
    G --> I
    H --> I
    D --> I

Which Metric Should You Use for Capital Budgeting?

For capital budgeting decisions, NPV is the theoretically correct metric because it directly measures dollar value creation for shareholders. Use IRR as a communication tool alongside NPV — investment committees respond to percentage returns — but never rank mutually exclusive projects on IRR alone. When cash flows are non-conventional, replace IRR with MIRR entirely.

The Practical Decision Framework

  1. Compute NPV first. If positive at your hurdle rate, the project clears the base bar.
  2. Cross-check with IRR (or XIRR). If IRR > WACC and NPV > 0, both metrics agree — go.
  3. For mutually exclusive projects, rank by NPV. IRR ranking is unreliable across different scales and time profiles.
  4. If cash flows are non-conventional, use MIRR. Report MIRR in the deck, not raw IRR.
  5. Report both metrics in the memo. NPV for the finance audience, IRR for operators, MIRR when reinvestment matters.

ℹ️ Note: The academic consensus since Solomon (1956) and Bierman & Smidt (1966) is unambiguous — NPV maximizes shareholder wealth; IRR does not. The reason IRR persists in practice is communication, not correctness.

Advanced: Handling Circular References in NPV Models

Real-world capital budgeting models often involve circular references — WACC depends on capital structure, capital structure depends on funding needs, funding needs depend on NPV. Excel handles this via iterative calculation.

Enabling Iterative Calculation

Go to File → Options → Formulas → Enable iterative calculation. Set max iterations to 100 and max change to 0.001. This lets Excel converge on a stable WACC-NPV solution without throwing a circular reference error.

The Safer LET-Based Approach

For deterministic models, use the LET() function to eliminate circulars by decomposing the calculation into named steps:

=LET(
    cashflows, B2:B7,
    dates,     A2:A7,
    rate,      WACC,
    pv,        XNPV(rate, cashflows, dates),
    irr,       XIRR(cashflows, dates),
    mirr,      MIRR(cashflows, rate, rate),
    HSTACK(pv, irr, mirr)
)

This returns NPV, IRR, and MIRR in a single spilled row — easier to audit than three separate cells and immune to the copy-paste breakage that plagues traditional templates.

Common NPV and IRR Pitfalls to Avoid

Even senior analysts get these wrong under deadline pressure. A five-minute checklist before you send the recommendation memo:

  • Sign errors. Every outflow negative, every inflow positive — no exceptions. SUMPRODUCT((B2:B7<0)*B2:B7) gives you the sum of outflows to sanity-check the invested capital.
  • Missing terminal value. If your project has value beyond the forecast horizon, model it explicitly. Truncating at Year 5 with no TV understates NPV for going-concern investments.
  • Ignoring taxes and depreciation. NPV should be built on after-tax free cash flow (FCFF for enterprise value, FCFE for equity value), not EBITDA. Add back depreciation, subtract taxes on EBIT, subtract capex, subtract working capital changes.
  • Wrong discount rate. Use project-specific WACC when the project's risk differs from the firm's, especially for M&A or new business lines. Applying corporate WACC to a much riskier project systematically overstates NPV.
  • Nominal vs real inconsistency. Discount nominal cash flows at nominal rates, real at real. Mixing them is a silent 200+ bps error.
  • Payback masquerading as a decision rule. Payback is a liquidity screen, not a value metric. Never override a positive-NPV recommendation because payback exceeds an arbitrary threshold — quantify the risk instead.

Sensitivity Analysis: Testing the NPV vs IRR Recommendation

A capital budgeting memo without sensitivity analysis is incomplete. Run a two-way sensitivity table on WACC and terminal growth (or exit multiple) to show the range of NPV outcomes, and identify the breakeven WACC — the rate at which NPV = 0, which is by definition the IRR.

=IFERROR(XIRR(cashflows, dates), "No solution")

Report the sensitivity as a table in the appendix:

WACC NPV Verdict
6% $278,400 Strong accept
8% $212,900 Accept
10% $154,000 Accept
12% $101,200 Marginal accept
15% $32,800 Marginal — cross-check assumptions
19.9% $0 Breakeven (this is IRR)
22% ($26,700) Reject

This single table communicates NPV, IRR, and downside risk in a form investment committees actually read.

Frequently Asked Questions

Why does IRR sometimes give a higher answer than the actual return I earn?

Standard IRR assumes intermediate cash flows are reinvested at the IRR itself. If a project has a 40% IRR but you can only reinvest cash at 8%, your realized compound return will be well below 40%. Use MIRR with a realistic reinvestment rate — typically your firm's WACC or an available T-bill / money market rate — to see the true earnable return.

Can NPV and IRR ever give different accept/reject decisions?

For a single independent project with conventional cash flows, no — if NPV > 0 then IRR > discount rate, and vice versa. They can only disagree on ranking between mutually exclusive projects, or when non-conventional cash flows create multiple IRRs. In those cases, NPV is always correct.

What is the difference between NPV and XNPV in Excel?

NPV() assumes cash flows occur at equal, annual intervals starting at the end of period 1. XNPV() uses actual dates and computes daily discounting. XNPV is the correct choice whenever periods are irregular — quarterly, monthly, or lumpy deal timing. Even for annual cash flows, XNPV is more transparent because dates are explicit.

Should I use NPV or IRR for capital budgeting?

Use NPV as the primary decision metric — it directly measures dollar value creation and gives unambiguous rankings. Report IRR alongside NPV for communication with non-finance stakeholders, but never rank projects by IRR when they are mutually exclusive or have non-conventional cash flows. MIRR is a better secondary metric than IRR because it uses a realistic reinvestment rate.

What discount rate should I use for NPV?

For firm-level projects with average risk, use the company's WACC. For projects with different risk profiles (a new business line, an acquisition in a different industry, a high-risk R&D bet), use a project-specific WACC computed from comparable public companies. For personal or after-tax analyses, use an after-tax cost of capital. Getting the discount rate wrong by 100 basis points typically moves NPV by 5–15%.

Closing: Build the Habit of Reporting Both

The NPV vs IRR debate is not academic — it decides which projects get funded and which get killed. The discipline is simple: compute both, understand why they might disagree, and rank by NPV whenever they do. Every capital budgeting model you build should output NPV, IRR, MIRR, and discounted payback in one spilled block so the reviewer can see the full picture in a single glance.

Tools like VeloraAI can auto-generate this four-metric block from any cash flow series, flag when IRR and NPV disagree on ranking, and detect non-conventional sign changes that make IRR unreliable — the kind of checks that take five minutes manually and are the first thing to get skipped under deadline pressure. Add NPV, XIRR, and MIRR to your capital budgeting template today, and the next investment committee meeting will be a conversation about assumptions instead of a debate about which number to trust.