Ask investors how their investments have done and most will quote a total gain: up 30 percent, or up two lakh. That number says very little on its own, because it ignores how long the money was invested and when each rupee went in.
XIRR is the measure that fixes this. This article explains why simple returns mislead, how CAGR and XIRR differ, works through examples with monthly instalments, shows how to calculate it in a spreadsheet, and covers common mistakes.
Why absolute return misleads
Absolute return is simply the gain divided by the amount invested. If you invested one lakh and it is now worth 1.6 lakh, the absolute return is 60 percent.
But 60 percent over two years is excellent, while 60 percent over ten years is modest. Without the time period, the figure cannot be compared with anything — a fixed deposit, an index, or another fund.
The problem gets worse with regular investing. When money goes in at different dates, some of it has been invested for years and some for weeks. A single total gain blends all of that together and usually understates how hard the money actually worked.
CAGR: the right measure for a single investment
Compound annual growth rate (CAGR) converts a gain over several years into an equivalent yearly rate.
For the example above — one lakh growing to 1.6 lakh over four years — CAGR is 1.6 raised to the power of one quarter, minus one, which is about 12.5 percent a year. That figure can be compared directly with other annual rates.
CAGR works when there is one investment at the start and one value at the end. It does not work when money goes in or comes out at different times, which describes most real investors.
What XIRR does
XIRR, the extended internal rate of return, finds the single annual rate that, applied to every cash flow from its own date, produces the current value. Each deposit is treated as having been invested only from the day it went in, and each withdrawal as having left on its date.
When there is only one deposit and one final value, the result equals CAGR. With many cash flows at irregular dates, it is the honest way to annualise the result.
Mutual fund statements and many investing platforms show this figure for each holding, and it is the number to use when comparing your SIP results with a fixed deposit, a PPF account, or an index.
Why your figure differs from the fund's published return. A fund's factsheet shows how its NAV changed between two dates, as if one sum had been invested at the start. Your own figure depends on when you actually put money in. Two investors in the same fund can see very different personal results: one who invested heavily before a fall, and one who kept investing through it. Neither number is wrong; the published one measures the fund, yours measures your experience of it.
Worked examples
Example 1: a one-year SIP. You invest 10,000 at the start of each month for twelve months, a total of 1.2 lakh. One year after the first instalment, the holding is worth 1.3 lakh.
The absolute return is 10,000 on 1.2 lakh, or about 8.3 percent. That looks ordinary. But the last instalment was invested for only one month, and on average the money was invested for about half a year. The annualised rate, accounting for each instalment's date, is roughly 15.7 percent.
Both numbers are correct; they answer different questions. The 8.3 percent tells you the total gain. The 15.7 percent tells you the yearly rate your money earned while it was invested, which is what you would compare with a fixed deposit rate.
Example 2: irregular lump sums. You invest 50,000, then another 50,000 six months later. Two years after the first investment, the total is worth 1.15 lakh. The absolute gain is 15 percent, but the annualised rate is only about 8.3 percent, because half the money had been invested for eighteen months rather than two years and the period is longer than one year.
The same absolute gain can mean very different annual rates depending on timing.
Calculating it yourself
Any common spreadsheet program has an XIRR function.
Column A: the amounts, with each investment entered as a negative number and the current value, or any withdrawal, as a positive number.
Column B: the date of each amount. The current value uses today's date.
The formula: =XIRR(A2:A14, B2:B14) gives the annual rate. Format the cell as a percentage.
For a complete picture, list every purchase, every redemption, and dividends received in cash, then the current value. Getting signs and dates right is the main source of errors.
A useful habit is to calculate this once a year for your whole set of investments, and for each major holding. It turns a vague sense of how things are going into a number you can compare.
Common mistakes
Judging very short periods. Over a few months, annualised figures swing wildly; a two percent gain in one month annualises to an impressive-looking number that means little. Look at periods of at least a few years.
Comparing with the wrong benchmark. Compare your equity investments with an index measured the same way — as if the same amounts had gone into an index fund on the same dates.
Ignoring costs and taxes. Statement figures are usually before tax. Your real return is after tax and after inflation.
Treating it as a forecast. A high past rate says nothing reliable about the next period.
Leaving out cash flows. Forgetting a redemption or an extra purchase distorts the result.
An account that reports returns per holding
Track instalments and their annualised results in one place. Free to open
The examples are illustrations, not projections. This is educational material, not advice, and no investment is recommended. We run no tips group and no signal service, we manage nobody's money, and no return is promised.
Disclosure: the account-opening link on this page is under Atul Shrivastava's Zerodha Authorised Person registration (NSE AP Reg: AP2516003481; Zerodha Broking Ltd. SEBI Reg: INZ000031633) and earns a revenue share. TheFinBaba is not a SEBI-registered Investment Adviser — this content is educational, not investment advice.
Frequently Asked Questions
What is XIRR?
The extended internal rate of return: the single annual rate that, applied to each cash flow from its own date, produces the current value. It is the right way to annualise returns from investments made at different times.
What is the difference between CAGR and XIRR?
CAGR annualises one investment from start to end. XIRR handles many deposits and withdrawals on different dates. With a single deposit, the two give the same result.
Why is my SIP XIRR higher than the absolute return?
Because later instalments have been invested for only a short time. The annualised rate accounts for this, while the absolute return treats all money as if invested for the whole period.
How do I calculate XIRR in Excel?
List investments as negative amounts and the current value as positive, with dates in the next column, then use =XIRR(amounts, dates) and format as a percentage.
Is a high XIRR over a few months meaningful?
Not much. Annualising short periods exaggerates small gains or losses. Judge returns over several years and against a benchmark measured the same way.
Related Reading
- A government-backed long-term scheme
- Instalments versus one investment
- What remains after inflation
- A once-a-year check of results
- Regular withdrawals from a fund
- A benchmark to compare against
Disclaimer: TheFinBaba provides educational content only - this is not investment advice. Trading involves risk of loss.
