You invested ₹5,000 every month for three years — ₹1.8 lakh in total — and your portfolio now shows ₹2.3 lakh. What return did you earn? The obvious answer of 27.8% is wrong, and understanding why is the point of XIRR.
What is XIRR?
XIRR stands for Extended Internal Rate of Return. It calculates the annualised return on an investment when money goes in or comes out at irregular intervals — which is exactly what a SIP does.
The key difference from simpler measures: XIRR accounts for when each rupee was invested. Money invested three years ago has had three years to compound. Money invested last month has had one. Treating them identically overstates your return substantially.
Why Simple Percentages Fail for SIPs
| Item | Value |
|---|---|
| Monthly SIP | ₹5,000 |
| Duration | 36 months |
| Total invested | ₹1,80,000 |
| Current value | ₹2,30,000 |
| Absolute gain | ₹50,000 |
| Naive calculation | 50,000 ÷ 1,80,000 = 27.8% |
| Actual XIRR | roughly 16–17% |
The 27.8% figure treats your final instalment — invested one month ago — as though it had been working for three full years. Only the first instalment had the full period. The average rupee in a 36-month SIP has been invested for about 18 months, not 36.
XIRR corrects for this by weighting each cash flow by its actual time in the market.
XIRR vs CAGR
| CAGR | XIRR | |
|---|---|---|
| Full form | Compound Annual Growth Rate | Extended Internal Rate of Return |
| Handles | One investment, one final value | Many investments and withdrawals at any dates |
| Accounts for timing | No — only start and end | Yes — each cash flow dated individually |
| Use for | Lump sum, FD, single stock purchase | SIP, SWP, irregular top-ups, real portfolios |
| Both give the same answer when | There is exactly one inflow and one outflow | |
The rule is simple: lump sum uses CAGR, SIP uses XIRR. They converge only in the single-investment case.
This is also why a fund’s published “3-year return” and your personal SIP return in the same fund will differ. The fund quotes CAGR for a hypothetical lump sum invested three years ago. Your XIRR reflects your actual instalment dates.
How XIRR Works Conceptually
XIRR finds the single annual rate that would make all your dated cash flows balance out to your current value.
Think of it as asking: “If every rupee I invested had earned the same annual rate from its own investment date until today, what rate would produce exactly the value I have now?”
There is no closed-form formula to solve this — it requires iteration, which is why Excel and portfolio apps compute it rather than you doing it by hand. The underlying equation sets the sum of all cash flows, each discounted by the time it has been invested, equal to zero.
Calculating XIRR in Excel
Two columns: dates and amounts. Investments are negative, the current value is positive. This sign convention trips up most people on their first attempt.
| Cell A (Date) | Cell B (Amount) | Meaning |
|---|---|---|
| 01-04-2023 | -5000 | SIP instalment (money out) |
| 01-05-2023 | -5000 | SIP instalment |
| 01-06-2023 | -5000 | SIP instalment |
| … | … | continue for every instalment |
| 01-03-2026 | -5000 | Final instalment |
| 15-08-2026 | 230000 | Current value (money in) |
Then apply:
=XIRR(B1:B37, A1:A37)
Format the result as a percentage. Google Sheets uses the same function with the same syntax.
Three things that cause errors: dates stored as text rather than date values, all amounts entered with the same sign, and a date range that does not match the amount range in length.
Reading Your XIRR
| XIRR | Context for Indian equity funds |
|---|---|
| Below 6% | Trailing typical fixed deposit returns |
| 6% – 10% | Modest; may reflect a difficult market period |
| 10% – 14% | Broadly in line with long-term index returns |
| 14% – 18% | Strong performance |
| Above 18% | Excellent — check whether the period was unusually favourable |
Two important caveats. XIRR over short periods is volatile and misleading — a one-year SIP XIRR can swing wildly with a single month’s market move. It becomes meaningful over three years or more.
And XIRR measures your outcome, not the fund’s skill. A poor XIRR in a good fund usually means you started at a market peak or stopped during a fall. The same fund can produce very different XIRRs for two investors depending entirely on when they invested.
Where XIRR Applies
- SIP returns — the primary use case
- SWP — systematic withdrawals, where money flows out at intervals
- Irregular investing — lump sums added whenever you had surplus
- Whole-portfolio returns across multiple funds and dates
- Real estate — purchase, registration costs, rental income, eventual sale
- Insurance-linked products — comparing premium outflows against maturity value
That last one is genuinely useful. Endowment and money-back policies quote large-sounding maturity amounts. Running an XIRR on the actual premium dates and maturity value usually reveals a return far below what the presentation implied.
Key Takeaways
- XIRR = Extended Internal Rate of Return
- It annualises returns when money is invested or withdrawn at irregular dates
- Lump sum uses CAGR; SIP uses XIRR
- Simple gain-over-investment badly overstates SIP returns by ignoring timing
- In Excel: investments negative, current value positive, then
=XIRR(amounts, dates) - Short-period XIRR is volatile — three years or more makes it meaningful
- XIRR reflects your timing, not the fund’s quality
- Useful for testing what insurance-linked investment products actually return
Frequently Asked Questions (FAQ)
Q: What is XIRR full form?
XIRR stands for Extended Internal Rate of Return. It calculates the annualised return on investments made at irregular intervals, accounting for exactly when each amount was invested or withdrawn.
Q: What is the difference between XIRR and CAGR?
CAGR works for a single investment with one start and end value. XIRR handles multiple cash flows on different dates. Use CAGR for a lump sum or fixed deposit, and XIRR for SIPs, SWPs and any portfolio with irregular contributions.
Q: How do I calculate XIRR for my SIP?
List every SIP instalment date with the amount as a negative number, then add today’s date with your current portfolio value as a positive number. Apply =XIRR(amounts_range, dates_range) in Excel or Google Sheets and format the result as a percentage.
Q: What is a good XIRR for a mutual fund SIP?
For Indian equity funds, 12% to 15% over long periods is broadly in line with historical market returns. Above 15% is strong. Judge XIRR only over three years or more — shorter periods swing too much on recent market movements to be meaningful.
Q: Why is my XIRR lower than the fund’s advertised return?
Fund returns are quoted as CAGR for a hypothetical lump sum invested at the start of the period. Your SIP instalments were invested at many different dates, so your average rupee has been invested for roughly half the total period. The two figures measure different things.
Q: Can XIRR be negative?
Yes. A negative XIRR means your current value is less than what you invested, adjusted for timing. This is common in the early years of an equity SIP started before a market decline, and it usually recovers as the SIP continues through lower prices.
Q: Does XIRR account for taxes and expense ratios?
Expense ratios are already reflected, since they are deducted from NAV before your portfolio value is calculated. Taxes are not — XIRR uses your pre-tax portfolio value. To see post-tax returns, use the value net of expected capital gains tax as the final cash flow.
Related Reading: