Understanding XIRR in SIPs — What It Means and How to Calculate It

If you have ever looked at your mutual fund statement and tried to calculate your SIP returns, you have probably hit a wall. The “12% return” the fund house advertises is the fund’s CAGR — but that is not your actual return, because you did not invest everything on day one. You invested in chunks, on different dates, at different NAVs. To measure your real return, you need a different metric: XIRR. This article explains what XIRR is, why CAGR fails for SIPs, and how to calculate it for yourself in Excel or Google Sheets in under two minutes.

Why CAGR does not work for SIPs

CAGR (Compound Annual Growth Rate) measures the annualised return of a single investment between two dates. The formula is:

CAGR = (Ending Value / Beginning Value)^(1 / Years) − 1

CAGR works perfectly for a lump sum investment. If you invested ₹1,00,000 on 1 January 2020 and it became ₹1,80,000 on 31 December 2024, your CAGR is (1,80,000 / 1,00,000)^(1/5) − 1 = 12.47%. Clean and correct.

But a SIP is not a single investment. It is a series of investments made on different dates. Consider this simple example:

• You invest ₹10,000 on 1 January 2024.
• You invest another ₹10,000 on 1 January 2025.
• On 31 December 2025, the total value is ₹22,000.

If you naively applied the CAGR formula: (22,000 / 20,000)^(1/2) − 1 = 4.88%. But that is wrong, because the second ₹10,000 was only invested for one year, not two. Treating both as if they were invested for two years understates your true return.

The fundamental problem: CAGR assumes a single cashflow at the start. A SIP has multiple cashflows at different times, each with a different holding period. You need a metric that respects the timing of each cashflow — and that metric is XIRR.

What XIRR actually is

XIRR (Extended Internal Rate of Return) is the annualised return that makes the net present value of a series of cashflows equal to zero. In plain English: it is the single annualised return figure that, if applied to every cashflow in your SIP, would produce exactly the ending value you see today.

The key insight is that XIRR weights each cashflow by its time in the market. A ₹10,000 instalment made 5 years ago counts heavily; a ₹10,000 instalment made last month counts barely at all. The result is a single percentage that honestly represents your experience as a SIP investor.

XIRR is also sometimes called the money-weighted return, because it depends on when you put money in (and took money out). This distinguishes it from the time-weighted return (which is what fund houses report as CAGR), which strips out the effect of cashflows to measure pure fund performance.

The mathematical intuition

Formally, XIRR is the value of r that satisfies this equation:

0 = Σ [ CFi / (1 + r)^((Di − D1) / 365) ]

Where CFi is the i-th cashflow, Di is the date of the i-th cashflow, and D1 is the date of the first cashflow. The exponent (Di − D1) / 365 is the time in years from the first cashflow.

You cannot solve this equation algebraically — it has to be solved numerically, by trying different values of r until both sides balance. This is exactly what Excel and Google Sheets do behind the scenes when you use the XIRR function. You do not need to do the maths yourself.

The intuition to hold onto: XIRR rewards you for investing early (more years of compounding) and penalises you for withdrawing early (less time for the money to grow). It is the most honest measure of your actual SIP experience.

Step-by-step Excel walkthrough

Let us walk through a real example. Suppose you started a SIP of ₹10,000 per month on 1 January 2024, continued for 12 months, and on 31 December 2024 the value was ₹1,32,000.

Step 1 — Set up two columns in Excel or Google Sheets:

Date	Cashflow
01-01-2024	-10000
01-02-2024	-10000
01-03-2024	-10000
01-04-2024	-10000
01-05-2024	-10000
01-06-2024	-10000
01-07-2024	-10000
01-08-2024	-10000
01-09-2024	-10000
01-10-2024	-10000
01-11-2024	-10000
01-12-2024	-10000
31-12-2024	132000

Important conventions: investments are entered as negative numbers (money going out), and the final value is positive (money coming back to you). The final row must be the current value or redemption, with the actual current date.

Step 2 — Apply the XIRR formula:

=XIRR(B2:B14, A2:A14)

Where B2:B14 is the cashflow range and A2:A14 is the date range. Excel returns a decimal (0.21xx), which you format as a percentage: approximately 21.4%.

That is your true XIRR for the year. Notice it is higher than the 32% gain you might naively calculate (₹1,32,000 ÷ ₹1,20,000 − 1 = 10%), because much of the money was invested for less than a year, and XIRR annualises the return correctly.

Wait — that example needs reconsideration. ₹1,20,000 invested becoming ₹1,32,000 is a 10% absolute gain. But because the money was invested gradually (average time in the market is only about 6.5 months), the annualised XIRR is roughly double the absolute gain. That is the magic of XIRR — it converts short holding periods into annualised equivalents.

Step 3 — Verify the result. A quick sanity check: if you had invested all ₹1,20,000 on 1 January 2024 and it became ₹1,32,000 on 31 December 2024, the CAGR would be exactly 10%. Since your money was invested for less time on average, the XIRR must be higher than 10% — and indeed it is, at around 21%.

Free online XIRR tools

If Excel is not your thing, several free Indian tools calculate XIRR for you:

• Kuvera — automatically shows XIRR for each of your funds on the dashboard.
• Zerodha Coin — displays XIRR for direct mutual fund holdings.
• Groww — shows XIRR per fund and for the overall portfolio.
• MoneyControl Portfolio — calculates XIRR for manually entered transactions.
• SIPlyy XIRR calculator — a clean, ad-free online tool where you paste dates and amounts.

For most retail investors, using the platform you already invest through is the easiest path. The XIRR shown on Kuvera or Coin matches what you would compute in Excel, because they use the same underlying formula.

What counts as a “good” XIRR for equity SIPs?

This is the question every SIP investor eventually asks. The honest answer depends on the time period and the type of fund.

For long-term equity SIPs (7+ years):

• Above 15%: Excellent. You have benefited from either a strong market cycle or genuinely good fund selection.
• 10%–15%: Solid. This is the historical long-term average of Indian equity markets, and most SIP investors land in this range.
• 7%–10%: Mediocre but acceptable. Likely indicates a choppy market period or conservative fund choices. Compare against the fund’s benchmark before judging.
• Below 7%: Disappointing. Likely means the SIP was started recently (XIRR over short periods is noisy), the fund underperformed its benchmark, or the investor timed redemptions poorly.

For debt or hybrid fund SIPs, expect XIRRs of 5%–8% over multi-year periods. These funds trade lower returns for lower volatility.

A few cautions when interpreting XIRR:

• Short-term XIRR is meaningless. A 6-month SIP showing 35% XIRR tells you nothing about long-term prospects. Markets can deliver wild short-term returns; only 5+ year XIRRs are reliable signals.
• XIRR mixes fund performance with your behaviour. If you paused your SIP during a market crash and resumed at the top, your XIRR will be lower than the fund’s CAGR — and that gap is your behaviour cost, not the fund’s fault.
• Redemptions complicate XIRR. If you withdrew partial amounts mid-way, enter them as positive cashflows on the withdrawal date. The maths handles it correctly.

A common mistake — comparing XIRR to CAGR

Beginners often compare their portfolio XIRR (say, 11%) to a fund’s advertised CAGR (say, 14%) and conclude the fund is short-changing them. This comparison is invalid because the two metrics measure different things.

• CAGR measures the fund’s performance, ignoring when you invested.
• XIRR measures your experience, including when you invested and when you redeemed.

If your XIRR is lower than the fund’s CAGR over the same period, the gap is usually explained by:

• Late start. You started the SIP when the market was already high, so your average purchase NAV is higher than the fund’s starting NAV.
• Paused or skipped SIPs. If you paused during corrections (when NAVs were low), you missed the cheap purchases that drive long-term SIP returns.
• Partial redemptions. Withdrawing during a downturn permanently locks in losses and lowers XIRR.

For a deeper read on SIP behaviour traps, see our article on common SIP myths debunked. And if you are just starting out, the SIP Calculator on SIPlyy shows projected CAGR-based outcomes, which are useful for planning — but always remember your actual XIRR will differ based on market timing and your own behaviour.

Conclusion

XIRR is the only honest way to measure your SIP returns, because it is the only metric that respects the timing of every rupee you invested. Spend ten minutes setting it up in Excel once, and you will have a tool that tells you the truth about your portfolio for the rest of your investing life. The truth is not always flattering — but it is always useful.

Mutual fund investments are subject to market risks. Please consult a SEBI-registered investment adviser before investing.