IRR vs XIRR vs MIRR in Excel: The Complete Guide (2026)

May 12, 2026 · VeloraAI Team
Formulas Financial Modeling Excel

A private equity associate emailed the IC deck on Friday. The headline IRR was 28.4%. By Monday, the partner had re-run the numbers in XIRR and the deal pencilled at 22.1% — a 630 basis-point gap that almost killed the investment. The culprit wasn't fraud, sloppy math, or a bad assumption. It was a single function name.

Choosing between IRR vs XIRR vs MIRR in Excel is one of the most consequential — and most misunderstood — decisions in financial modeling. The three functions look interchangeable. They are not. Use the wrong one on irregular cash flows and you'll overstate returns by hundreds of basis points. This guide breaks down what each function actually does, when to use it, and the formula traps that catch even senior analysts.

What's the Difference Between IRR, XIRR, and MIRR?

IRR assumes cash flows occur at equal, evenly-spaced periods. XIRR uses actual transaction dates, so it works on irregular cash flows and always returns an annualized rate. MIRR replaces IRR's unrealistic reinvestment assumption with a separate reinvestment rate, producing a more conservative — and often more defensible — return number.

In one sentence each:

  • IRR = the rate that sets NPV to zero, assuming equal periods between every cash flow.
  • XIRR = the rate that sets NPV to zero, using the actual dates of each cash flow.
  • MIRR = IRR adjusted for a realistic reinvestment rate on interim cash flows.

If your model has monthly, quarterly, or irregular cash flows — which is true for almost every private equity, real estate pro forma, project finance, and venture deal — XIRR is the default. IRR is only correct when periods are perfectly equal and you've already converted everything to a uniform interval.

⚠️ Warning: Excel's IRR function returns a periodic rate. If you feed it monthly cash flows and forget to annualize, you'll report a monthly IRR as if it were yearly. This is the #1 source of inflated return claims in junior analyst models.

How Does the IRR Function Work in Excel?

The IRR function solves for the discount rate that makes the net present value of a cash-flow stream equal to zero. The syntax is simple, but the assumptions are not.

IRR Syntax

=IRR(values, [guess])
  • values: A range containing at least one negative and one positive cash flow.
  • guess (optional): Your initial estimate for the rate. Default is 10%. Useful when IRR returns #NUM! because Excel's iterative solver can't converge.

A Working IRR Example

Suppose a $1,000,000 investment returns $300,000, $400,000, $500,000, and $600,000 over four years. The cash flows live in cells B2:B6:

Cell Year Cash Flow
B2 0 -1,000,000
B3 1 300,000
B4 2 400,000
B5 3 500,000
B6 4 600,000 
=IRR(B2:B6)

Result: 24.89%. Because the cash flows are exactly one year apart, IRR's equal-period assumption holds and the answer is correct.

💡 Pro Tip: Always sort cash flows chronologically before feeding them to IRR. Excel uses the position in the range to infer time — row 2 is period 0, row 3 is period 1, and so on. Out-of-order cash flows produce silently wrong results.

When IRR Breaks Down

IRR fails in three common scenarios:

  1. Irregular intervals. Monthly distributions, quarterly capital calls, or mid-period exits violate the equal-period assumption.
  2. Multiple sign changes. A cash-flow stream that goes negative → positive → negative can have multiple mathematically valid IRRs. Excel returns the one closest to your guess.
  3. No solution. If every cash flow has the same sign, no IRR exists. Excel returns #NUM!.

What Is XIRR and When Should You Use It?

XIRR calculates the annualized internal rate of return using a paired list of cash flows and the exact date of each one. Use it whenever cash flows are irregular, which is almost always the case in real-world finance. XIRR returns a yearly rate by default, so results are directly comparable across deals with different timing.

XIRR Syntax

=XIRR(values, dates, [guess])
  • values: Cash flows including at least one negative and one positive.
  • dates: A matching range of dates — one for each cash flow.
  • guess (optional): Initial estimate, defaults to 10%.

A Real XIRR Example

A growth-equity fund makes a $5M investment on Jan 15, 2024. The portfolio company distributes $1.2M in Jul 2024, $1.8M in Mar 2025, and the fund exits with a $5.5M sale on Oct 22, 2026.

Date Cash Flow
2024-01-15 -5,000,000
2024-07-30 1,200,000
2025-03-15 1,800,000
2026-10-22 5,500,000

With dates in A2:A5 and cash flows in B2:B5:

=XIRR(B2:B5, A2:A5)

Result: 27.94%. If you ran =IRR(B2:B5) on the same numbers, treating them as four equal annual periods, you'd get 23.51% — a 443 basis-point error baked into a return number that ends up on a fund tear sheet. This same date-weighting problem applies to XNPV versus NPV in a DCF model built in Excel — always use date-explicit functions when cash flows don't fall on annual boundaries.

ℹ️ Note: XIRR always returns an annualized rate, regardless of how short or long the actual period is. Even a 6-month investment is expressed as the equivalent yearly return.

Why XIRR Is the Default for Private Markets

Private equity, venture capital, infrastructure, real estate, and project finance all share one trait: cash flows hit on the day the wire clears, not on a textbook calendar. Capital calls land on the dates GPs request them. Distributions come when realizations close. Operating cash flows from a toll road don't politely arrive on Dec 31.

The CFA Institute's GIPS standards and most institutional LP reporting frameworks require time-weighted or money-weighted returns based on actual transaction dates — which is exactly what XIRR computes.

graph TD
    A[Cash Flow Stream] --> B{Are intervals<br/>exactly equal?}
    B -->|Yes| C[Use IRR]
    B -->|No| D{Do you need to model<br/>reinvestment rate?}
    D -->|No| E[Use XIRR]
    D -->|Yes| F[Use MIRR with<br/>finance and reinvest rates]
    C --> G[Periodic rate -<br/>remember to annualize]
    E --> H[Annualized rate -<br/>directly comparable]
    F --> I[More conservative<br/>defensible return]

MIRR: Solving the Reinvestment Rate Problem

Both IRR and XIRR carry a hidden assumption that drives finance professors crazy: interim cash flows are reinvested at the IRR itself. If your deal earns 35% IRR, the math implicitly assumes every dollar of distribution gets reinvested at 35% — which is rarely realistic.

MIRR fixes this. You specify a separate rate for reinvesting positive cash flows (typically your firm's WACC in Excel or a Treasury rate) and a separate rate for financing negative cash flows.

MIRR Syntax

=MIRR(values, finance_rate, reinvest_rate)
  • values: Cash flows in chronological order.
  • finance_rate: Interest rate paid on the money tied up in negative cash flows (your cost of capital).
  • reinvest_rate: Rate earned on positive cash flows as they're reinvested.

MIRR Example

Take the same $1M / 4-year stream from earlier. Assume a 7% cost of capital and a 4% reinvestment rate (a conservative Treasury blend):

=MIRR(B2:B6, 7%, 4%)

Result: 15.34% — a full 955 basis points below the unadjusted IRR of 24.89%. MIRR is almost always lower than IRR for the same cash flows, which is exactly why some analysts avoid it: the headline number gets less impressive.

Example: A PE fund reports a gross IRR of 32% on a deal. Apply MIRR with a 6% reinvestment rate and the effective compounded return drops to ~21%. That 21% is closer to what an LP actually earns on the capital lifecycle. For the full LBO returns model in Excel — including equity value waterfall, returns attribution, and exit multiple sensitivities — see our LBO model guide.

When MIRR Is Worth the Effort

Use MIRR when:

  • Cash flows are large enough that the reinvestment assumption materially affects returns (long-duration infrastructure, mining, multi-decade real estate).
  • You're presenting to sophisticated LPs who will challenge the reinvestment assumption.
  • You're comparing projects with very different cash-flow shapes (front-loaded vs. back-loaded).

Skip MIRR for short-duration deals or when you just need to rank-order opportunities and the IRR-vs-MIRR ordering won't change. For projects where LPs will stress-test the reinvestment rate, pair your MIRR calculation with a sensitivity analysis in Excel to show how returns move across rate assumptions.

Side-by-Side Comparison: IRR vs XIRR vs MIRR

Feature IRR XIRR MIRR
Handles irregular dates No Yes No (uses periodic data)
Returns annualized rate No (periodic) Yes No (periodic)
Assumes reinvestment at IRR Yes Yes No (you choose the rate)
Required inputs Cash flows Cash flows + dates Cash flows + 2 rates
Best for Textbook problems, annual budgets PE, VC, real estate, project finance Long-horizon, sensitivity-heavy analyses
Risk of overstating returns High on irregular flows Low Lowest
Excel function =IRR() =XIRR() =MIRR()

What Happens When XIRR Returns #NUM! or 0%?

XIRR's iterative solver occasionally fails to converge. Three fixes resolve 95% of cases:

  1. Provide a guess. Add a third argument — =XIRR(values, dates, 0.1) or =XIRR(values, dates, -0.1) — to nudge the solver. For deeply distressed deals where you expect a negative IRR, use a negative guess like -0.5.
  2. Check for sign changes. XIRR requires at least one negative and one positive cash flow. A stream of all-positive distributions with no initial outflow will fail.
  3. Inspect dates for duplicates or text. Dates stored as text strings (a common Power Query artifact) will silently break XIRR. Wrap with DATEVALUE() if needed.

💡 Pro Tip: For deals with a 0% or near-zero return, XIRR's default guess of 10% can fail to converge. Pass an explicit guess of 0.01 to force convergence: =XIRR(B2:B10, A2:A10, 0.01).

How Do You Calculate Monthly IRR in Excel?

To calculate a monthly IRR in Excel, run =IRR() on monthly cash flows to get the periodic rate, then annualize with =(1+IRR)^12-1. Alternatively, use =XIRR() with actual dates — it returns the annualized rate directly and avoids the annualization step entirely.

The Monthly IRR Trap

Junior analysts routinely report a monthly IRR as if it were annual. If your monthly IRR is 2%, the true annualized rate is:

=(1 + 0.02)^12 - 1

Result: 26.82%, not 24% (which is the wrong simple multiplication of 2% × 12). The compounding matters, and confusing the two costs careers.

Use XIRR to skip the annualization step entirely. Feed it your monthly dates and cash flows and it returns the annualized rate in one shot:

=XIRR(monthly_cash_flows, monthly_dates)

Real-World Modeling Workflow: Which to Choose

Here's the decision tree senior analysts actually use:

  1. Is every cash flow exactly one year apart? → IRR is fine.
  2. Are cash flows monthly or quarterly with strict regularity? → IRR for the periodic rate, then annualize manually. Or just use XIRR with dates and skip the math.
  3. Are cash flows on actual transaction dates (PE, VC, RE, project finance)? → XIRR. Always.
  4. Do reviewers question your reinvestment assumption? → Add MIRR as a secondary metric. Show both.
  5. Multiple sign changes in the stream? → IRR and XIRR may return one of several valid roots. Run MIRR and reconcile. Or split the model into sub-period IRRs.
graph LR
    A[Build cash flow<br/>schedule] --> B[Tag with<br/>actual dates]
    B --> C[Run XIRR<br/>as primary]
    C --> D[Run MIRR<br/>as sanity check]
    D --> E[Sensitize on<br/>reinvest rate]
    E --> F[Report range<br/>not point estimate]

Three Common XIRR Mistakes That Inflate Returns

1. Booking the Investment on Day Zero of Year 1

If you book the outflow on Jan 1, 2024 and the first distribution on Dec 31, 2024, XIRR sees a 365-day holding period. If you accidentally book the outflow on Dec 31, 2023 (a common copy-paste error), XIRR sees a 366-day period and shifts your return by ~30 bps. Tiny error, but it propagates.

2. Treating Capital Commitments as Capital Calls

In a fund-level XIRR, you should only include actual cash movements — capital calls and distributions — not committed but undrawn capital. Junior analysts sometimes book the full commitment as a Day-1 outflow, which understates IRR dramatically.

3. Including Unrealized Marks Without Discounting

If you want a "to-date" XIRR that includes unrealized NAV, you must include the NAV as a terminal cash flow at today's date. Adding it without a date — or with a stale date — produces nonsense.

⚠️ Warning: When showing a fund's interim XIRR to LPs, always disclose the as-of date of the NAV mark. An XIRR computed with a 6-month-old NAV is functionally different from one computed at quarter-end, and sophisticated LPs will notice.

Building an XIRR-Driven Returns Dashboard

A senior analyst's IRR workflow rarely stops at a single cell. The full pattern uses dynamic arrays and structured references:

=LET(
    cf,    FILTER(Table1[Amount], Table1[Status]="Realized"),
    dates, FILTER(Table1[Date],   Table1[Status]="Realized"),
    XIRR(cf, dates, 0.1)
)

This formula uses Excel's LET function for financial models to assign names to the filtered ranges, eliminating repeated expressions before passing them to XIRR. It recalculates automatically as deals are marked realized. Build a parallel formula for Status="Unrealized" and you have realized vs. unrealized XIRR side by side. For fund-level reporting where deal-level XIRR feeds the GP/LP distribution model, our PE waterfall model guide in Excel covers the MOIC, DPI, and carried interest mechanics that sit on top of these return calculations. Read more about Excel LAMBDA functions to wrap this into a reusable custom function.

Frequently Asked Questions

Why does my XIRR show a different result than my IRR on the same data?

Because IRR treats every cash flow as one period apart while XIRR uses the actual day count between dates. If your cash flows happen monthly but IRR sees them as annual, IRR will compound 12 imaginary years where only one actually elapsed. The gap is usually largest for short-duration deals and tight-clustered cash flows. XIRR is the correct answer when dates matter.

Can XIRR return a negative IRR?

Yes. If total distributions plus terminal value are less than total contributions, XIRR returns a negative annualized rate — exactly the right answer for losing investments. If the solver fails on a distressed deal, pass a negative guess like =XIRR(values, dates, -0.5) to help it converge.

Is XIRR the same as money-weighted return?

Effectively, yes. XIRR computes the dollar-weighted (money-weighted) return on a stream of irregular cash flows, which is the same concept used by GIPS-compliant performance measurement for private market funds. It differs from time-weighted return, which removes the impact of cash flow timing and is used for liquid portfolios.

When should I use MIRR over XIRR?

Use MIRR when the reinvestment assumption materially changes the story — typically long-horizon deals with large interim distributions. PE deals with significant dividend recaps, infrastructure with multi-decade tail flows, and any project being compared against an alternative-use benchmark all benefit from MIRR. For short-duration deals or rank-ordering exercises, XIRR is usually sufficient.

Does Excel have an XMIRR function?

No, Excel does not provide a built-in XMIRR. To replicate the MIRR concept on irregular dates, you'd need to manually discount each cash flow with XNPV using the finance and reinvest rates separately, then solve for the equivalent rate — or build a custom LAMBDA. For most analyses, XIRR plus a reinvestment-rate sensitivity table is the practical workaround.

The Bottom Line

If you remember one rule: default to XIRR. It handles real-world dates, returns an annualized rate, and matches institutional reporting standards. Use IRR only when periods are genuinely equal, and reach for MIRR when reviewers will question the reinvestment assumption. The 630-basis-point error that almost killed our opening anecdote happens every quarter at firms that don't enforce this discipline.

At VeloraAI we built our Excel add-in to flag exactly these mistakes — auto-detecting when an IRR formula is sitting on irregular cash flows and suggesting the XIRR or MIRR equivalent. The next time you inherit a model from a junior analyst, run it through the audit first. Your partner will thank you.

Sources: