The XIRR is ubiquitous. It’s there on mutual fund statements, bond platforms and in the insights your broker gives about your portfolio. But what does it mean? How to calculate it? Catch this conversation between two friends as they discuss the measure, while their electric vehicle charges.
Abhishek: Dude, I was looking at my mutual fund statement yesterday and noticed this metric called XIRR. I know you work in finance. Can you help me understand this?
Anand: Of course. XIRR stands for extended internal rate of return. But before understanding what XIRR is, you first need to know the concept of IRR.
Abhishek: Go on.
Anand: The textbook definition goes like this: IRR is the discount rate at which the present value of cash inflows equals the present value of cash outflows.
Abhishek: Got it. But how do they calculate this discount rate?
Anand: We have built-in Excel formulae to do it easily, which I’ll address later. However, did you wonder why IRR exists?
Abhishek: Actually, yes! Don’t we already have enough ways to measure returns – like CAGR (compounded annual growth rate) , absolute returns and so on?
Anand: Of course, we do. However, CAGR and absolute returns have their limitations. If you look at CAGR, it is used to measure returns between point A and point B. Point A being the time when you invested your capital and point B being the maturity date. What if your investment has interim cash flows — like a bond that pays annual interest or a mutual fund paired with a monthly SWP option? There is simply no scope to apply CAGR for such cases. Therefore, it is best suited for lump-sum investments with a single maturity value in the future.
The absolute return, also known as holding period return is more limited in scope. While CAGR at least considers the time factor, absolute return doesn’t differentiate between a two-year investment and five-year investment.
So, we need a flexible measure that can work regardless of the timing of cash flows – lump-sums or SIPs, interim or cumulative payouts, and help make an apple-to-apple comparison of returns between any two investment products. This is precisely the objective of using IRR. Also, with IRR, the returns you get are already adjusted for time value, making it a superior and more accurate measure than CAGR and holding period returns.
Abhishek: Sounds cool. But are IRR and XIRR the same thing?
Anand: Well, fundamentally, the formula for XIRR in Excel is based on the textbook concept of IRR, I mentioned before. However, there is another formula called IRR in Excel, which works only when the interval between two cash flows is uniform. Think quarterly, semi-annually, yearly and so on. But if the interval is less than a year, the result needs to be annualised.
In case the cash flows occur randomly, the IRR function doesn’t work. Here, you’ll have to use the XIRR function, which has an extended scope and is more flexible to the timing of cash flows.
Abhishek: Interesting! Last week, I was looking at a bond opportunity. It promised semi-annual interest payments for three years and redemption at face value. But I remember the website mentioning both XIRR and coupon rate. The XIRR was 8.15 per cent, while the coupon rate was just 8 per cent. Why is this difference?
Anand: That’s a good question. You see, when you receive interim cash flows – like the semi-annual interest you mentioned, XIRR assumes that you reinvest the interest till maturity at the rate of XIRR itself. For example, when you receive the first interest payment, the formula assumes you reinvest it at 8.15 per cent for 2.5 years. So on and so forth for the other interest receipts. This is the reason for the difference.
In case you spend the interest or let it languish in a savings account at 2-3 per cent interest, your net realised returns on maturity will not be as high as the claimed XIRR. Therefore, set your expectations accordingly.
But you can use the XIRR for comparing and to gain a perspective.
By the way, XIRR is also referred to as YTM or yield to maturity, in the context of fixed income securities.
Abhishek: All right. I’ll keep that in mind. Can you teach me to calculate XIRR for my index fund? It’s a semi-annual SIP for ₹50,000 I started on January 1, 2024. I sold units worth ₹20,000 on September 30 this year. The statement says the current value of my holdings is ₹1,95,000.
Anand: Sure. XIRR calculation is easy-peasy. All it needs is two inputs – the cash flows in one column and the respective dates they occur in another. Remember, cash outflows like principal, purchase price, SIPs should have a minus sign. You may also have a third column describing what the cash flows mean, for your reference.
If this is ready, call the XIRR function and define the range of cash flows, followed by the date range, and hit enter. That’s it. Your XIRR is calculated.
You can apply this method not just for mutual funds but for any investment product with cash flows.
The screen glowed bright green and read ‘Fully charged’, as Anand finished.
Published on December 13, 2025
