Guide
XIRR Explained: The Honest Way to Measure SIP Returns
If your investments went in at different times in different amounts, only one return number is honest: XIRR.
In shortXIRR is the annualised return for investments made on different dates and in different amounts, such as a SIP with top-ups or missed months. It finds the single yearly rate at which all your dated investments and today's value balance. Unlike CAGR or absolute return, it accounts for how long each rupee was actually invested.
Why simple returns mislead
Absolute return — total money out divided by total money in — ignores timing entirely. It treats a rupee invested ten years ago the same as one invested last month, which is obviously wrong: the older rupee had far longer to compound.
CAGR fixes timing but assumes a single investment and a single withdrawal. Real SIPs have dozens of cashflows on different dates, so CAGR doesn't fit either.
Absolute return vs CAGR vs XIRR
| Measure | What it answers | Use it for |
|---|---|---|
| Absolute return | How much did I gain in total, ignoring time? | Very short holdings only |
| CAGR | What steady yearly rate turns one starting amount into one ending amount? | A single lump-sum investment |
| XIRR | What yearly rate explains many investments and withdrawals on different dates? | SIPs, top-ups, partial withdrawals, your whole portfolio |
For a lump sum held without top-ups or withdrawals, XIRR and CAGR give the same answer. XIRR is simply the general version.
What XIRR does
XIRR — extended internal rate of return — finds the single annualised rate that makes all your dated cashflows balance to zero in present-value terms. It correctly weights each instalment by how long it was actually invested.
For a real SIP with skipped months, top-ups and partial withdrawals, XIRR is the only metric that tells you the true annualised return on the money as you actually invested it.
A worked example with a messy SIP
Here is a year of real-life investing: a skipped March, a ₹25,000 top-up in May, a ₹5,000 withdrawal in September, then a portfolio value of ₹68,000 in January.
| Date | Cash flow |
|---|---|
| 1 Jan 2024 | −₹10,000 |
| 1 Feb 2024 | −₹10,000 |
| 1 Mar 2024 | skipped |
| 1 Apr 2024 | −₹10,000 |
| 1 May 2024 | −₹25,000 (top-up) |
| 1 Jun 2024 | −₹10,000 |
| 1 Sep 2024 | +₹5,000 (withdrawal) |
| 1 Jan 2025 | +₹68,000 (value today) |
You put in ₹65,000 and got back ₹73,000 in total, an absolute gain of 12.3%. But most of the money went in during the spring and was invested for well under a year, so the annualised return is higher: an XIRR of 17.0%. Neither absolute return nor CAGR can give that figure.
How to calculate XIRR in Excel or Google Sheets
- Put dates in one column and amounts in the next.
- Enter every investment as a negative number and every withdrawal, plus today's value, as a positive number.
- Use
=XIRR(B2:B8, A2:A8), where column B holds the amounts and column A the dates, and format the cell as a percentage.
The formula searches for the rate by trial and error, as our XIRR calculator does. Both use actual days divided by 365, so they should agree to a decimal place or two.
Why XIRR can look strange
- Very short periods exaggerate it. A 3% gain in two months annualises to about 20%. XIRR on anything under a year says little about the future.
- It can be negative. A SIP started just before a fall shows a negative XIRR until prices recover. That is normal in the first year or two.
- It is sensitive to the final date. Measure after a rally and it looks great, after a fall poor. Rolling returns across many periods give a fairer picture, which is why our fund pages show every rolling SIP, not just one.
How to read your XIRR
Compare it to the return assumption you used when planning. If you assumed 12% but your XIRR is 9%, the gap is your timing and fund-selection reality versus the projection. Compare it also with a plain index fund over the same dates; that tells you whether your fund choices added anything.
Our XIRR calculator lets you enter your real, irregular schedule — including the messy bits — and returns the honest annualised figure, something a basic SIP calculator structurally can't.
Sources
- AMFI: Net Asset Value of mutual fund schemes Association of Mutual Funds in India
- SEBI Investor Website Securities and Exchange Board of India
Rules and rates were checked against these official sources at the last review.
Try the tools