XIRR vs CAGR: How to Calculate Your SIP’s Real Return (With Excel Steps)
Why CAGR can make a 13.4% SIP look like 6.9%, which return to trust, and how to calculate your SIP XIRR in Excel in two minutes.
On this page 10 sections
- 01Absolute return: the simplest number
- 02CAGR: perfect for a lump sum
- 03Why CAGR gives the wrong answer for a SIP
- 04XIRR vs CAGR: the same SIP, two very different answers
- 05What XIRR actually does
- 06How to calculate XIRR for your SIP in Excel
- 07XIRR vs CAGR: which number should you use?
- 08Plan forward with expected returns, check backward with XIRR
- 09Why use Paisa24’s calculators?
- 10FAQs on XIRR vs CAGR
XIRR vs CAGR is where most SIP investors get confused. Your app says your fund returned 14% CAGR, yet your own SIP shows a gain of just 22% after three years. Is someone lying? No. They are measuring different things. This guide explains absolute return, CAGR and XIRR in plain words, shows the right way to calculate your SIP’s real return, and how to do it in Excel in two minutes.
⚡ XIRR vs CAGR: quick answer
- Absolute return: total gain ÷ total invested. Ignores time.
- CAGR: yearly growth rate for one single investment (a lump sum).
- XIRR: yearly return for many investments on different dates, like a SIP.
- Rule: use CAGR for lump sums and XIRR for SIPs.
- Example: the same 3-year SIP shows 22.2% absolute, 6.9% “CAGR”, but 13.4% XIRR. Only XIRR is correct.
Absolute return: the simplest number
Absolute return is just your total gain divided by your total investment.
Absolute return = (current value − amount invested) ÷ amount invested × 100
For example, you invest ₹1,80,000 and it is now worth ₹2,20,000. Your absolute return is ₹40,000 ÷ ₹1,80,000 = 22.2%. However, this number says nothing about time. A 22% gain in one year is excellent, but in ten years it is poor.
CAGR: perfect for a lump sum
CAGR, or compound annual growth rate, tells you the steady yearly rate at which one investment grew.
CAGR = (final value ÷ initial value)1/years − 1
Say you put ₹1,00,000 in a fund three years ago, and it is now ₹1,40,000. Then CAGR = (1.40)1/3 − 1 = 11.9% a year. This works because all your money went in on one date. That is also why fund factsheets show CAGR: it describes how the fund itself performed.
Why CAGR gives the wrong answer for a SIP
In a SIP, your money goes in a little every month. Your first instalment has been invested for three years, while your latest one has been invested for only a month. So if you apply the CAGR formula to your total SIP amount, you pretend all ₹1,80,000 was invested on day one. As a result, the return looks far lower than it really is.
XIRR vs CAGR: the same SIP, two very different answers
Let us take a real-style example. You invest ₹5,000 on the 5th of every month from January 2024 to December 2026, which is 36 instalments. On 5 January 2027, your SIP is worth ₹2,20,000.
| Method | Result | Is it right for a SIP? |
|---|---|---|
| Absolute return | 22.2% | Correct, but ignores time |
| CAGR on total invested | 6.9% a year | Wrong: assumes ₹1.8 lakh went in on day one |
| XIRR | 13.4% a year | Correct: counts each instalment’s actual date |
In other words, CAGR understates this SIP’s return by almost half. That is why fund apps and CAS statements show XIRR for your own portfolio.
What XIRR actually does
XIRR, or extended internal rate of return, finds the one yearly rate that makes all your cash flows balance out. Each instalment is a cash outflow on its own date, and today’s value is a cash inflow. XIRR then answers a simple question: “At what single annual rate would all these payments grow into my current value?”
There is no neat formula to solve by hand, because XIRR is found by trial and error. Fortunately, Excel and Google Sheets do it for you instantly.
How to calculate XIRR for your SIP in Excel
- Column A: enter each SIP date, for example 05-01-2024, 05-02-2024 and so on.
- Column B: enter each instalment as a negative number, like -5000, because money left your pocket.
- Last row: enter today’s date in column A and your current value as a positive number in column B, like 220000.
- In any empty cell, type:
=XIRR(B1:B37, A1:A37) - Format the cell as a percentage. You will see about 13.4%.
A short SIP works the same way. For instance, 12 instalments of ₹5,000 worth ₹64,000 a month after the last one show just 6.7% absolute return, yet an XIRR of about 12.5% a year.
XIRR vs CAGR: which number should you use?
| Situation | Use |
|---|---|
| Comparing two funds’ performance | CAGR (fund factsheet returns) |
| A one-time lump sum investment | Lump sum CAGR |
| Checking your own SIP’s return | XIRR |
| SIP with top-ups, pauses or partial withdrawals | Always XIRR |
| Comparing your SIP with an FD or PPF rate | Your SIP’s XIRR, since it is an annual rate like FD interest |
Plan forward with expected returns, check backward with XIRR
XIRR looks back at what already happened. To plan ahead, you need the opposite: an estimate of what a SIP could become. For that, use the SIP formula, or simply the Paisa24 SIP calculator. Enter your monthly amount, years and an expected return, and it shows the future value, total gain and a year-by-year table. Then, once a year, check your actual XIRR in Excel to see whether you are on track.
Wondering whether a SIP is worth the risk at all? Compare it with a safe option using our FD vs SIP calculator. Also, read how to calculate SIP returns yourself for the formula behind the calculator.
Why use Paisa24’s calculators?
- Free forever: no sign-up, no OTP and no advisor calls.
- Private: your numbers stay on your device.
- Visual: charts and tables, not just one big number.
- 11 tools in one place: SIP, FD, PPF, EMI, income tax, salary and more on the calculators page.
Prefer quick videos? We explain topics like XIRR vs CAGR in 60-second reels. Follow @paisa24_in on Instagram.
FAQs on XIRR vs CAGR
Is XIRR better than CAGR?
Neither is better in general. CAGR suits a single lump sum, while XIRR suits investments made on many dates, such as a SIP.
What is a good XIRR for a SIP?
It depends on the fund type and time period. For long-term equity SIPs, many investors compare their XIRR with the fund’s benchmark and with safe options like FDs. Over short periods, XIRR can swing a lot, so judge it over 5 years or more.
Why is my SIP XIRR negative?
If markets fell after you started, your current value may be below what you invested. This is normal in the early years of a SIP. In fact, you buy more units at lower prices, which can help later.
Examples use illustrative values. Mutual fund returns are not guaranteed. This is general information, not investment advice.
This guide is for information only and isn’t financial advice. Rates, fees and rules change — always confirm on the official website before you decide. Read our disclaimer.
