IRR vs XIRR — what's the difference?

Every finance professional has used IRR() in Excel. Fewer have paused to ask what it's actually assuming — and whether those assumptions hold for the cash flows they're feeding it. The short version: IRR assumes your cash flows arrive at perfectly equal intervals. Real investments almost never work that way. That's where XIRR() comes in.

This piece explains both functions from first principles, walks through a worked example with actual dates, and tells you exactly when to use which — and what can still go wrong with either.

What IRR actually is

The Internal Rate of Return is the discount rate at which the Net Present Value (NPV) of a series of cash flows equals zero. In plain terms: it's the annualised return implied by a sequence of inflows and outflows, assuming you earn that same rate on every rupee throughout the holding period.

Mathematically, for cash flows C₀, C₁, C₂ … Cₙ, IRR solves for r in:

0 = C₀ + C₁/(1+r) + C₂/(1+r)² + … + Cₙ/(1+r)ⁿ

In Excel, the syntax is =IRR(values, [guess]), where values is a range of cash flows. You give it a list of numbers — outflows as negatives, inflows as positives — and it returns the period rate. If the periods are years, you get an annual IRR. If they're months, you get a monthly rate (multiply by 12 to annualise — though even that isn't precise for compounding).

The critical assumption baked into IRR() is that each cash flow in the array represents one equally spaced period. The function has no concept of dates. It sees a list and assumes the gaps between entries are identical. Invest today, get a return at t=1, get another at t=2 — uniform spacing, no exceptions.

Why that assumption breaks in real life

Think about how investments actually work. A Systematic Investment Plan (SIP) in a mutual fund might have you investing on the 5th of each month — but months are not equal in length. A private equity fund calls capital in three tranches across 14 months, then makes distributions 3, 5, and 7 years later, none of them evenly spaced. A loan is disbursed in one shot but repaid on EMI dates that shift slightly when weekends fall. A startup founder receives a seed cheque in March and a Series A in November — neither at a tidy annual boundary.

If you plug these irregular cash flows into IRR() without any date adjustment, you're computing a return as though each entry is one period apart. The numerics look like they work — the function returns a number — but that number is not the correct annualised return. It could be higher or lower than reality, depending on whether your actual gaps are shorter or longer than the assumed equal period. The result is plausible-looking, hard to detect, and wrong.

What XIRR does differently

XIRR() solves the same underlying equation — find the rate where NPV = 0 — but it takes one additional input: the actual date of each cash flow. The syntax is =XIRR(values, dates, [guess]).

With dates in hand, Excel computes the exact fraction of a year between each cash flow and the first one, then discounts each entry by the precise time elapsed. The result is always an annualised rate, regardless of how irregular the spacing. A cash flow 47 days after the first is discounted at (1+r)^(47/365), not at (1+r)^1.

This is why XIRR is the right tool for almost every real portfolio analysis. It doesn't assume anything about spacing — it uses the actual calendar.

Dates are data. The moment you strip dates off a cash flow schedule, you lose information that directly changes the computed return. IRR discards that information by design; XIRR preserves it.

A worked example — SIP portfolio

Let me make this concrete. Suppose I invested in a diversified equity portfolio through an SIP, with three instalments on the dates below. By 31-Mar-2026, the portfolio is worth ₹1,85,000. I want to know my actual annualised return.

Date	Cash Flow (₹)	Description
01-Apr-2024	−50,000	First SIP investment
12-Sep-2024	−50,000	Second SIP investment
03-Feb-2025	−50,000	Third SIP investment
31-Mar-2026	+1,85,000	Current portfolio value (redemption / terminal value)

Total invested: ₹1,50,000. Current value: ₹1,85,000. Absolute gain: ₹35,000 — a 23.3% absolute return. But what's the annualised rate, accounting for the fact that my three tranches sat in the market for different lengths of time?

In Excel, the XIRR formula would be:

=XIRR(B2:B5, A2:A5)

Where column A holds the dates and column B holds the cash flows (investments as negative, final value as positive). XIRR works out the exact time-weighted gaps: the first ₹50,000 was invested for almost exactly two years; the second for about 18.6 months; the third for about 13.9 months. Discounting each precisely, XIRR returns approximately 14.2% per annum.

Now try =IRR(B2:B5) on the same four numbers, ignoring dates entirely. IRR sees three equal outflows and one inflow and assumes each is one period apart — effectively treating this as a 3-period problem with equal spacing. It returns a per-period rate that, even annualised, produces a meaningfully different — and date-inaccurate — figure. The number IRR spits out has no reliable relationship to your real experience.

Sign convention — get this right

Both IRR and XIRR require at least one negative and at least one positive cash flow, or they'll return an error. The convention is straightforward:

• Outflows (investments, capital calls, loan disbursements): negative values.
• Inflows (dividends, distributions, redemptions, final portfolio value): positive values.

The terminal value — the current market value of a portfolio you haven't sold — counts as a positive cash flow on the valuation date. This is what makes XIRR work for mark-to-market portfolios: you treat today's value as a hypothetical liquidation, even if you haven't actually sold.

A common mistake is forgetting to enter the terminal value at all, or entering it as negative. Either way, XIRR will either error out or return nonsense.

When IRR is still fine

I don't want to suggest IRR is useless. For annual project cash flows with genuinely equal spacing, IRR is perfectly appropriate and widely understood. Capital budgeting in large corporates — where a project has an upfront capex and then produces annual operating cash flows for, say, ten years — is a natural fit. If the cash flows truly arrive every twelve months on the dot, IRR is accurate and simpler to explain to stakeholders.

IRR is also fine for quick back-of-the-envelope thinking where you're not trying to be precise about dates — comparing two similarly-structured projects on the same approximate timeline, for instance.

The rule: if your cash flow schedule has even annual periods and no irregular gaps, IRR works. For everything else, default to XIRR.

When you must use XIRR

In practice, this covers almost all real portfolio or investment analysis:

• SIPs and systematic plans — monthly instalments on irregular calendar dates.
• Private equity and venture capital — capital calls staggered over a fund's investment period, distributions whenever realisations occur.
• Loan and EMI schedules — EMI dates shift with weekends and holidays; the gap is never exactly equal.
• Startup funding rounds — seed, bridge, Series A on dates driven by milestone achievement, not a calendar.
• Real estate investments — purchase cost, renovation spend, rental income, and eventual sale all on arbitrary dates.
• Any multi-year portfolio with partial redemptions or top-ups.

Gotchas to watch for

XIRR is more reliable than IRR for date-irregular flows, but it still has failure modes worth knowing.

Convergence failures

Both IRR and XIRR solve numerically by iteration. If the cash flow pattern has multiple sign changes — for example, you invest, get a partial return, invest more, get a final return — there can be multiple mathematically valid solutions. The function may return whichever root it finds first, which might not be the economically meaningful one. Provide a [guess] argument (e.g., =XIRR(values, dates, 0.1) for a 10% initial guess) to steer convergence toward the expected range.

Short periods can look extreme

Because XIRR always annualises, a very short holding period with a good return can produce a dramatically high annualised figure. If you buy something and sell it for a 5% gain six weeks later, XIRR will return something close to 55% per annum. That's mathematically correct — it's what you'd earn if you compounded that 6-week rate for a full year — but it can look misleading without context. Always sense-check XIRR results against the actual holding period.

The reinvestment rate assumption

Like IRR, XIRR implicitly assumes that intermediate cash flows — any distributions you receive before the final date — are reinvested at the same computed rate. This is rarely true. If you receive a dividend and it sits in a savings account at 6%, but your XIRR is 14%, the actual blended return is lower than XIRR suggests.

Excel's MIRR (Modified IRR) addresses this by letting you specify a separate reinvestment rate and finance rate. It's less commonly used — and there isn't a date-accurate MXIRR in Excel natively — but if reinvestment timing matters to your analysis, it's worth knowing the limitation exists.

Tying it back to CAGR

A question I often get: how does XIRR relate to CAGR? They're measuring the same thing — compounded annualised return — but for different cash flow structures.

CAGR is for a single lump-sum investment: one outflow at the start, one inflow at the end. It's a special case where the math is simple enough to solve directly without iteration. XIRR generalises CAGR to multiple dated cash flows — multiple investments, multiple distributions, any timing. If you run XIRR on a single investment and a single redemption, you'll get the same answer as CAGR.

Think of it as a hierarchy: CAGR for single in/out, XIRR for multiple dated flows. Once you have more than one cash flow or any irregular timing, CAGR is no longer valid and XIRR is the right tool.

The next time someone hands you a return figure computed with plain IRR on an SIP portfolio or a PE fund, ask to see the dates. If the dates weren't used in the calculation, the return figure is approximate at best. Run it through XIRR — the difference is often material, and always more honest.