XIRR Meaning: What It Is, Formula, and How to Calculate It
XIRR gives you the actual annualised return on any investment, SIPs, lump sums, or a mix when cash flows happen at irregular dates. Here's what it means and how to use it.
XIRR (Extended Internal Rate of Return) is a formula that calculates the annualised return on any investment with irregular cash flows, multiple SIP installments, top-ups, or partial withdrawals at different dates. It is the most accurate way to measure your real return on a mutual fund SIP.
If you invest every month through a SIP and want to know your actual return, CAGR will give you the wrong number. CAGR assumes one investment at the start and one exit at the end. Real investing doesn't work that way — you put in money at different times, and you may take some out in between. XIRR handles exactly that.
What does XIRR mean?
XIRR stands for Extended Internal Rate of Return. It is a financial function that calculates the rate of return for a series of cash flows that do not occur at regular intervals. Each cash flow is associated with a specific date, so XIRR accounts for the exact timing of every investment and redemption.
In simple terms: XIRR tells you what annual interest rate would make the present value of all your investments equal to the current value of your portfolio.
XIRR formula
XIRR solves for the rate r in the following equation:
0 = P1 / (1+r)d1/365 + P2 / (1+r)d2/365 + ... + Pn / (1+r)dn/365
Where:
- P = each cash flow (negative for investments made, positive for redemptions/current value)
- d = number of days from the first cash flow date to each subsequent date
- r = the XIRR (what you're solving for)
You don't need to solve this manually. Excel and Google Sheets have a built-in XIRR function.
How to calculate XIRR in Excel or Google Sheets
The syntax is simple:
=XIRR(values, dates, [guess])
- values — a range of cash flows. Investments are negative (money going out); redemptions/current value are positive.
- dates — the corresponding dates for each cash flow.
- guess — optional starting estimate (usually 0.1 or 10%).
Example: SIP of ₹5,000/month for 12 months
| Date | Cash Flow (₹) | Description |
|---|---|---|
| 01-Jan-2024 | -5,000 | SIP installment |
| 01-Feb-2024 | -5,000 | SIP installment |
| 01-Mar-2024 | -5,000 | SIP installment |
| ... (monthly) | -5,000 | SIP installment |
| 01-Dec-2024 | -5,000 | SIP installment |
| 01-Jan-2025 | +67,500 | Current value (redemption) |
Total invested: ₹60,000. Current value: ₹67,500. Running =XIRR(values, dates) on this data gives approximately 23.5% XIRR.
Compare that to a naive calculation: gain of ₹7,500 on ₹60,000 = 12.5% absolute return. XIRR annualises it correctly, accounting for the fact that your first SIP was invested for a full year but your last one was invested for just one month.
XIRR vs CAGR vs IRR
| Feature | CAGR | IRR | XIRR |
|---|---|---|---|
| Cash flows | Single invest + single exit | Multiple, periodic | Multiple, irregular dates |
| Date-sensitive? | No | Assumes equal intervals | Yes — exact dates |
| Best for | Lump sum investments | Fixed-interval cash flows | SIPs, partial redemptions |
| Accuracy for SIPs | Low | Medium | High |
Why XIRR matters for mutual fund investors
Most mutual fund apps and websites in India show XIRR as the return figure for SIP portfolios. When you see "your SIP has returned 14.2%", that's XIRR — not CAGR, not absolute return.
This makes XIRR the most relevant number for evaluating how your actual investments have performed, because it reflects your real cash flow pattern. It also lets you compare two different funds where you invested different amounts at different times.
What is a good XIRR?
| XIRR range | Interpretation |
|---|---|
| Below 7% | Underperforming inflation; reassess |
| 7–12% | Decent; in line with long-term index returns |
| 12–18% | Good; above-market equity returns |
| Above 18% | Excellent; often seen in bull markets or small/mid-cap funds |
Keep in mind that XIRR is time-period dependent. A 25% XIRR over 6 months in a strong bull market doesn't mean the fund will sustain that. Always evaluate XIRR over a full market cycle (at least 5 years) for a meaningful picture.
Common mistakes when using XIRR
- Forgetting to make investments negative — cash going out (your SIP debit) must be negative; current value or redemptions must be positive. If you get this wrong, Excel will return an error or a nonsensical number.
- Using XIRR on too short a period — a 3-month XIRR can look like 80%+ annualised in a rally. It's not meaningful. Use XIRR only for periods over 1 year.
- Confusing XIRR with absolute returns — XIRR is always annualised. If your 3-year SIP shows 14% XIRR, it does not mean you made a 14% total gain — it means you earned the equivalent of 14% per year.
Frequently asked questions
What is XIRR in mutual funds?
In mutual funds, XIRR (Extended Internal Rate of Return) is the standard metric used to calculate the annualised return on SIP investments. Since SIP instalments are invested on different dates, XIRR accounts for the exact timing of each cash flow to give you a single, accurate annualised return figure. Most mutual fund apps and portfolio trackers display XIRR as the performance metric for SIP portfolios.
What is the difference between XIRR and CAGR?
CAGR (Compound Annual Growth Rate) measures the return on a single lump sum investment from start to end. It assumes one investment at the beginning and one exit at the end. XIRR, on the other hand, handles multiple cash flows at different dates — making it suitable for SIPs, top-ups, and partial redemptions. For lump sum investments, CAGR and XIRR give the same result. For SIPs, only XIRR gives an accurate picture.
How do I calculate XIRR in Excel?
In Excel or Google Sheets, use the formula =XIRR(values, dates). List all cash flows in one column — mark each SIP instalment as a negative number (money going out) and the current value or redemption amount as a positive number. In the adjacent column, list the exact date of each cash flow. Select the values range and dates range in the formula. The result is your annualised XIRR expressed as a decimal — multiply by 100 to get the percentage.
Can XIRR be negative?
Yes. A negative XIRR means your investment has lost value — the current portfolio value is less than what you put in, adjusted for the time value of money. For example, if you invested in a fund during a market peak and the market fell significantly since then, your XIRR could be negative. This is normal during bear markets or in the early months of a SIP. XIRR tends to normalise over longer holding periods as markets recover.
What is a good XIRR for a mutual fund SIP?
For a diversified equity mutual fund SIP held over 5+ years, an XIRR of 12–15% is generally considered good in the Indian market. Large-cap index funds have historically delivered 11–13% XIRR over long periods. Active mid-cap and small-cap funds can deliver 15–20%+ in strong bull cycles, but with higher volatility. Anything below 7–8% over a 5-year period should prompt you to review whether the fund is worth continuing. Always compare XIRR against the benchmark index return for the same period.
Investments in the securities market are subject to market risks. Read all related documents carefully before investing. This post is for learning, not a recommendation.
