CAGR measures the yearly growth rate of a single investment with one start date and one end date, while XIRR measures the yearly return when money goes in or out on several different dates. For a lump sum the two give almost the same answer, but for a SIP only XIRR gives a meaningful annual return.
This guide defines absolute return, CAGR and XIRR, works through a 12-month SIP with its full cash-flow table, and shows how to calculate XIRR in Excel or Google Sheets. It also explains why your app’s XIRR can look strange in the first few months, what counts as a good XIRR, and the mistakes people make when reading it.
Three ways to measure returns
Absolute return
Absolute return is the total gain as a percentage of the money invested, with no regard for time.
Absolute return = (Current value − Amount invested) ÷ Amount invested × 100
If ₹1,00,000 grows to ₹1,80,000, the absolute return is 80%. That sounds the same whether it took two years or twenty, which is the problem. Absolute return is fine for holdings under a year, but it cannot compare investments held for different periods.
CAGR (compound annual growth rate)
CAGR is the steady yearly rate that would turn the starting amount into the ending amount over the holding period, assuming gains are reinvested.
CAGR = (Ending value ÷ Starting value)^(1 ÷ Years) − 1
Suppose ₹1,00,000 invested on 1 October 2021 is worth ₹1,80,000 on 1 October 2026. CAGR = (1,80,000 ÷ 1,00,000)^(1/5) − 1 = 1.8^0.2 − 1 = 12.47%. The 80% absolute return works out to 12.47% a year, compounded. Our article on the power of compounding explains why 12.47% a year adds up to 80% rather than 62%.
CAGR assumes one amount goes in at the start and stays untouched. Add more money midway, or withdraw some, and the formula no longer fits.
XIRR (extended internal rate of return)
XIRR is the single annual rate at which all your cash flows, each discounted from its own date, add up to zero. Put simply, it is the yearly rate that would have turned every rupee you invested, on the day you invested it, into the value you have today.
The “X” means it works with irregular dates. Plain IRR assumes equal gaps between cash flows; XIRR uses the actual number of days between each one, on a 365-day year.
| Measure | Accounts for time? | Handles multiple investments? | Best used for |
|---|---|---|---|
| Absolute return | No | Only as a total | Holdings under one year |
| CAGR | Yes | No | A single lump sum held for over a year |
| XIRR | Yes, to the day | Yes | SIPs, top-ups, partial withdrawals, whole portfolios |
Why SIP returns need XIRR
In a 12-month SIP, the first instalment is invested for a full year but the last for only a month. On average each rupee is invested for about six and a half months. Dividing the gain by the total invested, or running CAGR from the first instalment date, treats all the money as if it had been invested for the full year, which understates the true rate.
XIRR fixes this by giving each instalment credit only for the days it was actually invested.
Worked example: XIRR on a 12-month SIP
Suppose Priya invests ₹10,000 on the 5th of every month from January to December 2025 in an equity fund. The NAVs (net asset value per unit) below are hypothetical, chosen to keep the arithmetic easy to follow. On 5 January 2026 the NAV is ₹112.
| Date | Cash flow (₹) | NAV (₹) | Units bought | Total units |
|---|---|---|---|---|
| 5 Jan 2025 | −10,000 | 100 | 100.0000 | 100.0000 |
| 5 Feb 2025 | −10,000 | 98 | 102.0408 | 202.0408 |
| 5 Mar 2025 | −10,000 | 95 | 105.2632 | 307.3040 |
| 5 Apr 2025 | −10,000 | 97 | 103.0928 | 410.3968 |
| 5 May 2025 | −10,000 | 101 | 99.0099 | 509.4067 |
| 5 Jun 2025 | −10,000 | 104 | 96.1538 | 605.5605 |
| 5 Jul 2025 | −10,000 | 103 | 97.0874 | 702.6479 |
| 5 Aug 2025 | −10,000 | 106 | 94.3396 | 796.9875 |
| 5 Sep 2025 | −10,000 | 102 | 98.0392 | 895.0267 |
| 5 Oct 2025 | −10,000 | 105 | 95.2381 | 990.2648 |
| 5 Nov 2025 | −10,000 | 108 | 92.5926 | 1,082.8574 |
| 5 Dec 2025 | −10,000 | 110 | 90.9091 | 1,173.7665 |
| 5 Jan 2026 | +1,31,461.85 | 112 | Current value | 1,173.7665 |
Units bought = ₹10,000 ÷ NAV. Current value = 1,173.7665 units × ₹112 = ₹1,31,461.85. Investments are entered as negative numbers and the current value as a positive number, because that is how XIRR reads money going in and coming out.
- Absolute return: (1,31,461.85 − 1,20,000) ÷ 1,20,000 = 9.55%.
- CAGR from the first instalment: exactly one year passed, so (1,31,461.85 ÷ 1,20,000)^1 − 1 = 9.55%. This is wrong for a SIP, because it assumes all ₹1.2 lakh was invested on 5 January 2025.
- XIRR: 18.02%.
Checking the 18.02%
XIRR solves for the rate r in: sum of [cash flow ÷ (1 + r)^(days from first date ÷ 365)] = 0. An easier way to check it is to grow each instalment at 18.02% a year for the days it was invested and confirm the total matches the current value.
- 5 Jan 2025 instalment, 365 days: ₹10,000 × 1.18017^(365/365) = ₹11,801.67
- 5 Jun 2025 instalment, 214 days: ₹10,000 × 1.18017^(214/365) = ₹11,019.97
- 5 Dec 2025 instalment, 31 days: ₹10,000 × 1.18017^(31/365) = ₹10,141.69
- All 12 instalments grown this way add up to ₹1,31,461.84, matching the current value to within a rupee of rounding.
The 18.02% XIRR is nearly double the 9.55% absolute return because the average rupee was invested for only 198.5 days, a little over half a year. Our SIP calculator works the other way round: give it an expected annual return and it projects the corpus.
When XIRR and CAGR give the same answer
For a single lump sum with no further cash flows, XIRR and CAGR measure the same thing. In the earlier example, ₹1,00,000 on 1 October 2021 and ₹1,80,000 on 1 October 2026, XIRR gives 12.467% and CAGR gives 12.475%.
The tiny gap comes from day counting. Five calendar years from October 2021 contain 1,826 days because of the 2024 leap day, and XIRR divides by 365, so it sees 5.003 years instead of exactly five. You can project lump sum outcomes with our lumpsum calculator.
How to calculate XIRR in Excel or Google Sheets
Both Excel and Google Sheets have a built-in XIRR function. The syntax is XIRR(values, dates, [guess]) in Excel and XIRR(cashflow_amounts, cashflow_dates, [rate_guess]) in Sheets. The guess is optional and defaults to 10%.
| Row | Column A: Date | Column B: Amount (₹) |
|---|---|---|
| 1 | Date | Amount |
| 2 | 05-01-2025 | -10000 |
| 3 | 05-02-2025 | -10000 |
| 4 to 12 | 05-03-2025 to 05-11-2025 | -10000 each |
| 13 | 05-12-2025 | -10000 |
| 14 | 05-01-2026 | 131461.85 |
| 15 | Result | =XIRR(B2:B14, A2:A14) |
Format cell B15 as a percentage and it shows 18.02%. A few rules make the formula work:
- Enter every purchase, SIP instalment or top-up as a negative number on its actual date.
- Enter redemptions and dividend payouts you received as positive numbers on their dates.
- Add the current value as the last positive amount, dated the day you are measuring.
- Make sure the dates are real date values, not text. A #VALUE! or #NUM! error usually means a text date or no positive amount.
- Keep the two ranges the same length and include at least one negative and one positive value.
To get a portfolio-level XIRR, list the cash flows of every fund in the same two columns and add a single current-value row for the whole portfolio.
Why your app’s XIRR looks odd in the first few months
XIRR annualises, so a small move over a few weeks is scaled up to a full-year rate. Suppose a SIP of ₹10,000 started on 5 August 2026, with a second instalment on 5 September. On 3 October 2026 the ₹20,000 is worth ₹20,600, an absolute gain of 3%. The XIRR is 28.1%. If it were worth ₹19,400 instead, a 3% loss, the XIRR would be −22.6%.
Neither figure means much. A single good or bad week can swing an early XIRR by tens of percentage points. Treat XIRR with caution until the SIP has run for at least a year, and look at absolute return in the meantime.
Apps can also differ from each other, and from your own spreadsheet, because they may use different NAV dates, count a pending instalment, or treat switches and dividends differently. Small gaps are normal.
What is a good XIRR?
There is no single number. A good XIRR is one that beats your alternatives after tax and stays ahead of inflation over the period you invested. These are useful reference points:
| Benchmark | Figure | Notes |
|---|---|---|
| CPI inflation, India | 4.82% (August 2026) | MoSPI data as reported; an XIRR below inflation means your money lost buying power |
| SBI fixed deposit, 1 to 3 years | 6.25% to 6.40% | General public, below ₹3 crore, rates effective 15 December 2025 (as of October 2026); interest taxed at slab rate |
| 10-year SIP in Nifty 50 TRI, median | 13.7% | Our calculation over 208 rolling windows from June 1999 to September 2026; before costs and tax |
| 10-year SIP in Nifty 50 TRI, range | 4.7% to 23.2% | Worst window started March 2010, best September 2000 |
| 10-year SIP in Nifty 50 TRI, latest | 10.2% | September 2016 to September 2026 |
The Nifty 50 figures come from our analysis of NSE Indices’ Total Return Index data, which includes dividends. They show how wide the range of outcomes is even over 10 years, so a “good” XIRR depends heavily on when you started.
The fairest test for an equity fund is its own benchmark. Run the same SIP dates and amounts through the fund’s benchmark index or a low-cost index fund and compare the two XIRRs. If an actively managed fund trails its index over five years or more, that is worth knowing. Our comparison of ETFs and index mutual funds covers the low-cost options for that benchmark.
XIRR vs absolute returns: which to look at
Use absolute return for anything held under a year and for a quick sense of total profit in rupees. Use XIRR for anything over a year that involves more than one cash flow, and CAGR for a single lump sum. For goal planning, the rupee value matters most: a high XIRR on a small amount still leaves a small corpus.
Common mistakes when reading XIRR
- Using CAGR or absolute return for a SIP. Both understate the rate, as the worked example shows.
- Getting the signs wrong in a spreadsheet. Money in must be negative and money out positive, or the function errors or gives nonsense.
- Judging a fund on a three-month XIRR. Early figures are dominated by short-term noise.
- Comparing XIRRs over different periods. A SIP started in 2020 and one started in 2024 faced different markets. Compare funds over the same dates.
- Forgetting tax. App XIRR is pre-tax. Equity fund gains held over 12 months are taxed at 12.5% above ₹1.25 lakh a year, and short-term gains at 20%. Our guide on reducing capital gains tax covers the options.
- Treating past XIRR as a forecast. It describes what happened over your dates, not what will happen next.
FAQ
What is the meaning of XIRR in a mutual fund?
XIRR is the annualised return on all your investments in a fund, taking into account the exact date of each purchase and withdrawal. It is the standard way to measure SIP returns, and most apps and statements show it for holdings with several transactions.
Is XIRR better than CAGR?
Neither is better in general; they suit different situations. CAGR is right for one investment held untouched. XIRR is needed when there are several cash flows, and for a single lump sum it gives almost the same result as CAGR.
What is the XIRR formula?
XIRR is the rate r that makes the sum of each cash flow ÷ (1 + r)^(days since the first cash flow ÷ 365) equal to zero. It has no direct algebraic solution, so Excel, Google Sheets and apps find it by trial and error. In a spreadsheet, use =XIRR(amounts, dates).
Why is my XIRR much higher than my absolute return?
In a SIP, much of your money has been invested for only a short time. XIRR annualises the gain on each instalment over the days it was invested, so it is higher than the absolute return when you are in profit, and more negative when you are in loss.
What is a good XIRR for a SIP?
Over 5 years or more, an equity SIP should at least beat FD rates and inflation after tax, and be close to its benchmark index over the same dates. Across rolling 10-year windows since 1999, a Nifty 50 TRI SIP had a median XIRR of 13.7%, but results ranged from 4.7% to 23.2%.
Ramesh Iyer is the pen name of the founder and editor of StocksInfo.AI, an independent investor in Indian equities, mutual funds and ETFs since 2020. Every article is researched from primary sources such as SEBI, AMFI, NSE and the Income Tax Department, and fact-checked before publishing. About us