NPV vs IRR: Which Investment Metric Should You Trust?

"Tell me about a time when NPV and IRR ranked two projects differently. Why does this happen, and which would you trust?" This is the classic investment banking and private equity interview question—and the answer separates junior analysts from deal professionals.

At its core, the NPV vs IRR debate is about what we measure: NPV asks "How much money do we make?" while IRR asks "How fast do we earn it?" Both are useful. Both are taught in business schools. But when faced with competing investments, they can give you opposite answers—and one is almost always right.

This guide walks you through the mathematics, the pitfalls, and the practitioner's truth: when to trust each metric, why they disagree, and how the Modified IRR (MIRR) fixes the problem once and for all.

NPV vs IRR vs MIRR: Choosing the Right Metric for Capital Decisions

NPV Defined: The Bedrock of Finance

Net Present Value is the sum of all future cash flows discounted back to today at a given discount rate, minus the initial investment.

Formula:

NPV = -Initial Investment + (CF₁ / (1 + r)¹) + (CF₂ / (1 + r)²) + ... + (CFₙ / (1 + r)ⁿ)

Where r is the discount rate (typically the cost of capital or required return).

In Excel:

=NPV(discount_rate, CF1:CFn) - Initial_Investment

// Example: 10% discount rate, cash flows in B2:B6, initial investment in B1 (shown as negative)
=NPV(10%, B2:B6) + B1

// Alternatively, if you have specific dates, use XNPV for non-annual periods
=XNPV(10%, B1:B6, dates_B1:B6)

Why NPV Works:

NPV is grounded in the principle that a dollar today is worth more than a dollar tomorrow. By discounting all cash flows to a common point in time (today), NPV tells you the absolute economic value created by the investment, in today's dollars.

• NPV > 0: The investment is worth undertaking; it creates economic value.
• NPV = 0: You earn exactly your required return; you are indifferent.
• NPV < 0: The investment destroys value; reject it.

The Discount Rate Matters: A higher discount rate shrinks NPV (future cash flows are worth less today), while a lower discount rate inflates it. The most common choice is the Weighted Average Cost of Capital (WACC), which reflects what the firm pays for its capital on average.

Key Assumption: NPV implicitly assumes that all positive cash flows are reinvested at the discount rate. This is critical and often overlooked.

IRR Defined: The Percentage Return

Internal Rate of Return is the discount rate at which NPV equals zero. In other words, it is the interest rate that makes the present value of future cash flows exactly equal to the initial investment.

Formula:

IRR is the r that solves this equation:

0 = -Initial Investment + (CF₁ / (1 + r)¹) + (CF₂ / (1 + r)²) + ... + (CFₙ / (1 + r)ⁿ)

There is no closed-form solution for IRR; it is found through trial and error (Newton-Raphson iteration).

In Excel:

=IRR(B1:B6, guess)

// Example: cash flows in B1 (negative initial) to B6
=IRR(B1:B6)

// For non-annual periods with specific dates, use XIRR
=XIRR(B1:B6, dates_B1:B6)

Why IRR Fails:

1. Reinvestment Assumption: IRR assumes all positive cash flows are reinvested at the IRR rate itself. For a high-return project, this can be unrealistic. If your project earns 40% IRR but your company's average reinvestment rate is 8%, assuming 40% reinvestment will overstate the true economic value.

2. Scale Indifference: IRR is a percentage, so it ignores the absolute size of the investment. A small project with 50% IRR might be ranked above a large project with 20% IRR, even if the large project creates far more total cash.

3. Multiple IRRs: For non-conventional cash flows (where signs change more than once), there can be multiple valid IRR solutions. Excel's =IRR() will return one arbitrarily, but the economic interpretation becomes meaningless.

4. Mutually Exclusive Projects: When choosing between two projects, ranking by IRR is mathematically incorrect. NPV is the only criterion that correctly identifies the value-maximising choice.

Why They Can Disagree: A Tale of Scale and Timing

Two core reasons cause NPV and IRR to rank projects differently:

1. Scale (Size) Differences

Consider two projects:

• Project A: Invest $1M, receive $300k annually for 5 years. IRR = 15.24%.
• Project B: Invest $5M, receive $1.4M annually for 5 years. IRR = 8.63%.

By IRR, Project A wins (15.24% > 8.63%). But calculate NPV at a 10% discount rate:

• Project A NPV: $1M initial + ($300k × [1 − (1.1)⁻⁵] / 0.1) = $137,241
• Project B NPV: $5M initial + ($1.4M × [1 − (1.1)⁻⁵] / 0.1) = $297,411

By NPV, Project B wins decisively. Why? Because it is larger. Even though its percentage return is lower, it creates more absolute value.

2. Timing Differences

Consider a conventional DCF vs. a project with back-loaded cash flows:

• Project C: Invest $10M, receive $5M in Year 1, $3M in Year 2, $2M in Year 3. Early cash flows = fast payoff.
• Project D: Invest $10M, receive $2M in Year 1, $3M in Year 2, $5M in Year 3. Back-loaded cash flows = slower payoff.

IRR will rank the earlier-cash-flow project higher, even if total NPV is similar, because IRR implicitly weights the timing of cash flows by the IRR rate itself. NPV uses the more conservative discount rate and may rank the back-loaded project higher, showing that value creation depends on the cost of capital.

3. Non-Conventional Cash Flows and Multiple IRRs

Imagine a mining project:

• Year 0: Invest $10M (outflow)
• Year 1–4: Earn $4M annually (inflows)
• Year 5: Spend $2M on environmental remediation (outflow)

This cash flow stream changes sign twice (negative → positive → negative). By Descartes' Rule of Signs, there can be up to two solutions for IRR. Excel's =IRR() will return one, but the other is equally valid mathematically—yet economically nonsensical. NPV sidesteps this entirely.

Comparison Table: NPV, IRR, MIRR, and Payback

MIRR: The Better IRR

Modified Internal Rate of Return was invented to fix IRR's flaws. Instead of assuming all cash flows are reinvested at the IRR, MIRR uses two explicit rates:

• Financing Rate (r_finance): The rate at which you borrow money for initial investments.
• Reinvestment Rate (r_reinvest): The rate at which you reinvest positive cash flows (often the company's WACC or hurdle rate).

Formula (Simplified):

MIRR = (FV of Inflows / PV of Outflows)^(1/n) − 1

Where:

• FV of Inflows = all positive cash flows compounded forward at the reinvestment rate
• PV of Outflows = all negative cash flows discounted back at the financing rate
• n = number of periods

In Excel:

=MIRR(values, finance_rate, reinvest_rate)

// Example: cash flows in B1:B6, 8% financing rate, 12% reinvestment rate
=MIRR(B1:B6, 8%, 12%)

Why MIRR is Superior:

1. Realistic Assumptions: You supply the rates based on actual company policy, not an implicit assumption.
2. Single Solution: Even with non-conventional cash flows, MIRR has only one answer.
3. Correct Ranking: MIRR respects both the size of the investment and the realistic opportunity cost of capital.
4. Comparable to IRR: MIRR is expressed as a percentage, so it is easy to compare across projects and communicate upwards.

MIRR vs IRR on Project A vs Project B:

Using 10% discount rate, 10% financing rate, 10% reinvestment rate:

• Project A: MIRR = 11.46% (vs IRR = 15.24%)
• Project B: MIRR = 9.85% (vs IRR = 8.63%)

MIRR still favors Project A (11.46% > 9.85%), and Project A does have a higher true return when we account for realistic reinvestment. But the gap is much smaller, and when we compare to NPV, we see that Project B creates more total value. The metrics now align: choose based on your constraint (is capital unlimited or constrained?).

Worked Example: The Ranking Conflict

Let us build a complete example with real numbers.

Project A: Small, High-Return

• Initial Investment: $1,000,000
• Year 1 Cash Flow: $300,000
• Year 2 Cash Flow: $300,000
• Year 3 Cash Flow: $300,000
• Year 4 Cash Flow: $300,000
• Year 5 Cash Flow: $300,000
• Discount Rate: 10%

Project B: Large, Moderate-Return

• Initial Investment: $5,000,000
• Year 1 Cash Flow: $1,400,000
• Year 2 Cash Flow: $1,400,000
• Year 3 Cash Flow: $1,400,000
• Year 4 Cash Flow: $1,400,000
• Year 5 Cash Flow: $1,400,000
• Discount Rate: 10%

Step 1: Calculate NPV

Project A:

=NPV(10%, 300000, 300000, 300000, 300000, 300000) - 1000000
=NPV(10%, B2:B6) - B1
= $1,137,241 - $1,000,000 = $137,241

Project B:

=NPV(10%, 1400000, 1400000, 1400000, 1400000, 1400000) - 5000000
=NPV(10%, B2:B6) - B1
= $5,297,411 - $5,000,000 = $297,411

NPV Ranking: Project B wins by $297,411 (more than twice Project A's NPV).

Step 2: Calculate IRR

Project A:

=IRR(-1000000, 300000, 300000, 300000, 300000, 300000)
= 15.24%

Project B:

=IRR(-5000000, 1400000, 1400000, 1400000, 1400000, 1400000)
= 8.63%

IRR Ranking: Project A wins by 6.61 percentage points.

Step 3: Calculate MIRR

Assuming 10% financing rate and 10% reinvestment rate:

Project A:

=MIRR(-1000000, 300000, 300000, 300000, 300000, 300000, 10%, 10%)
= 11.46%

Project B:

=MIRR(-5000000, 1400000, 1400000, 1400000, 1400000, 1400000, 10%, 10%)
= 9.85%

MIRR Ranking: Project A wins, but only by 1.61 percentage points.

Summary Table

What Happened?

IRR ranked Project A higher because its percentage return is superior. But Project A is much smaller; it creates only $137k of value. Project B, despite its lower IRR, creates $297k—more than double. If you are a PE firm with $10M to invest, you are choosing between deploying it into a $1M deal (and having $9M left in cash) or a $5M deal (and having $5M left). The $5M deal creates more value for your LPs.

MIRR gives a more balanced view: Project A's true economic return is 11.46%, and Project B's is 9.85%—still an advantage to A, but modest. The real story is told by absolute NPV.

Why They Disagree: The Deep Dive

Reinvestment Assumption

When you earn $300,000 from Project A in Year 1, what happens to it? IRR assumes you reinvest it at 15.24% (the project's IRR). NPV assumes you reinvest it at 10% (the discount rate). In the real world, your company's average reinvestment opportunity is probably closer to 10% (its cost of capital) than 15%. So NPV's assumption is more conservative and realistic.

For Project B, the gap is larger: IRR assumes 8.63% reinvestment, NPV assumes 10%. This assumption favours NPV's view of the project.

Scale Indifference

IRR is a percentage. Percentage returns are scale-agnostic. A project that turns $1,000 into $1,154 has the same IRR as one that turns $1,000,000 into $1,154,000, even though one creates $154 and the other creates $154,000. This is fine for personal investment decisions but breaks down in corporate finance where capital is limited and you want to maximise total value creation.

Non-Conventional Cash Flows

In the mining example above, the cash flow stream is: -$10M, +$4M, +$4M, +$4M, +$4M, -$2M. The sign changes twice, so the IRR equation may have two solutions (or none). NPV is always well-defined: you discount at your cost of capital and get a single, unambiguous answer.

When to Use Each Metric

Use NPV When:

• Comparing mutually exclusive projects (you can only pick one). NPV will always tell you which creates more economic value.
• Capital is not constrained. If you have enough funding, accept all projects with NPV > 0.
• You have a clear discount rate. If your WACC or hurdle rate is well-defined, NPV is the right answer.
• Projects have non-conventional cash flows (multiple sign changes). NPV handles these correctly; IRR becomes ambiguous.
• Projects differ greatly in scale. You want absolute value, not percentage return.

Use IRR When:

• Quick intuition is needed. IRR is easier to communicate to executives and board members ("We're earning 20% on this deal").
• Benchmarking against other companies. IRR allows for percentage-based comparisons without specifying a discount rate.
• Internal rate of return on an investment is the key question (e.g., a private equity fund reporting returns to LPs).

But: IRR should never be used alone for capital budgeting decisions. Always supplement with NPV.

Use MIRR When:

• You want the benefits of IRR (percentage return, easy communication) with realistic assumptions. MIRR is the best of both worlds.
• Capital is constrained and you need to rank projects by return per dollar invested. MIRR corrects IRR's reinvestment problem.
• Non-conventional cash flows exist. MIRR will always produce a single, meaningful answer.
• You are in PE or project finance. MIRR is industry standard in these fields.

Common Mistakes

Mistake 1: Using IRR Alone to Rank Projects

What happens: You rank Project A (15% IRR) above Project B (10% IRR) and recommend Project A. You later discover Project B would have created 5x more shareholder value.

Fix: Always calculate NPV. For mutually exclusive projects, NPV is the tiebreaker.

Mistake 2: Not Questioning the Discount Rate in NPV

What happens: You use a 5% discount rate because "that is what the model had," without verifying it matches the company's actual cost of capital. NPV swings wildly with discount rate changes.

Fix: Validate your discount rate against your WACC calculation or required return policy. Consider a sensitivity table showing NPV across a range of rates (e.g., 8%, 10%, 12%).

Mistake 3: Assuming IRR Reinvestment is Automatic

What happens: A project has 25% IRR, so you assume it will compound at 25% forever. But once the project ends, you have cash. You reinvest it at your WACC (8%), not 25%.

Fix: Use MIRR with explicit financing and reinvestment rates. Or adjust your IRR intuition: "The IRR is 25%, but I can only reinvest at 8%, so my true return is lower."

Mistake 4: Ignoring Multiple IRRs

What happens: Excel returns 12% IRR for a mining project. You do not realise there is also a valid 18% solution (or that the 12% is economically meaningless given the non-conventional cash flow).

Fix: When you suspect non-conventional cash flows (costs at the end of the project, environmental remediation, lease termination), calculate NPV instead. Or manually check for sign changes in the cash flow vector.

Mistake 5: Applying the Payback Period as a Decision Rule

What happens: You reject a project because it takes 4 years to pay back, even though it creates significant NPV in years 5+.

Fix: Use payback as a liquidity screen only ("We need projects to recover capital within 3 years"). Do not use it as your primary decision metric. Always reference NPV.

Common Worksheets: Quick References

NPV Calculation Template

// Column A: Period | Column B: Cash Flow | Column C: Discount Factor

A1: Period
B1: Cash Flow
C1: Discount Factor
D1: Present Value

A2: 0
B2: -1000000
C2: 1
D2: =B2*C2

A3: 1
B3: 300000
C3: 1/(1.1^A3)
D3: =B3*C3

// Continue for years 2–5...

A8: NPV
D8: =SUM(D2:D7)

IRR / MIRR / Payback Template

A1: IRR
B1: =IRR(B2:B7)  // Assumes cash flows in B2:B7

A2: MIRR (10% fin, 10% reinvest)
B2: =MIRR(B2:B7, 10%, 10%)

A3: Payback (years)
B3: // Manually calculate: find first period where cumulative CF > 0

Further Reading

• DCF Model Excel Tutorial — Learn how to build a full DCF model with NPV in Excel.
• NPV Calculator

The Interview Answer

When you are asked, "Tell me about a time when NPV and IRR ranked projects differently. Which would you trust?" here is the structure:

1. Acknowledge the conflict: "NPV and IRR can rank projects differently when they differ in scale or cash flow timing. I would always trust NPV for a capital budgeting decision."

2. Explain why: "NPV gives the absolute dollar value created, which is what shareholders care about. IRR is a percentage and does not account for the size of the investment. Also, IRR assumes reinvestment at the IRR rate itself, which is often unrealistic."

3. Give an example: "Imagine Project A costs $1M and earns $300k/year for 5 years (15% IRR, $137k NPV). Project B costs $5M and earns $1.4M/year for 5 years (8.6% IRR, $297k NPV). By IRR, A wins. By NPV, B wins. If I have $5M to invest, B creates twice the value."

4. Mention MIRR: "That said, MIRR is better than IRR because it uses realistic reinvestment assumptions. I would use both NPV and MIRR as a sanity check."

5. Close: "Bottom line: NPV is the theoretically correct metric. IRR and MIRR are useful for communication and quick checks, but NPV should drive the decision."

Conclusion

NPV and IRR are both essential tools, but they answer different questions. NPV tells you which project creates more value; IRR tells you the percentage return earned. When they conflict, NPV is correct. When you want the benefits of IRR (percentage return) with realistic assumptions, use MIRR.

Mastering this distinction is not just good finance—it is the foundation of sound capital allocation. In a world where management teams make billion-dollar decisions, using the wrong metric can destroy shareholder value. Now you know the difference.