Abhishek: Dude, I was looking at my mutual fund chart yesterday and saw this statistic called XIRR. I know you work in finance. Can you help me understand this?
Anand: Naturally. XIRR stands for Extended Internal Rate of Return. But before you understand what XIRR is, you must first understand the concept of IRR.
Abhishek: Go on.
Anand: The textbook definition is as follows: IRR is the discount rate at which the present value of cash inflows equals the present value of cash outflows.
Abhishek: I got it. But how do they calculate this discount rate?
Anand: We have built-in Excel formulas to do this easily, which I will discuss later. However, are you wondering why IRR exists?
Abhishek: Actually yes! Don’t we already have enough ways to measure returns, such as CAGR (compound annual growth rate), absolute returns and so on?
Anand: Of course we do. However, CAGR and absolute returns have their limitations. When you look at CAGR, it is used to measure the return between point A and point B. Point A is the time you invested your capital and point B is the maturity date. What if your investment has intermediate cash flows, such as a bond that pays annual interest or a mutual fund combined with a monthly SWP option? There is simply no room to apply CAGR to such cases. Therefore, it is best suited for fixed-amount investments with a single term in the future.
The absolute return, also called the holding period return, has a more limited scope. While CAGR at least takes the time factor into account, absolute returns do not distinguish between a two-year investment and a five-year investment.
So we need a flexible measure that can work regardless of the timing of the cash flows – lump sums or SIPs, interim or cumulative payouts – and help make a comparison of returns between two investment products. This is exactly the purpose of using IRR. Furthermore, the returns you get with IRR are already time-value adjusted, making it a superior and more accurate measure than CAGR and holding period returns.
Abhishek: Sounds cool. But are IRR and XIRR the same?
Anand: Well, fundamentally, the formula for XIRR in Excel is based on the textbook concept of IRR, which I mentioned earlier. However, there is another formula called IRR in Excel, which only works when the interval between two cash flows is uniform. Think quarterly, semi-annually, annually and so on. But if the interval is less than one year, the result must be annualized.
If the cash flows occur randomly, the IRR function will not work. Here you should use the XIRR function, which has a more extensive scope and is more flexible in terms of the timing of cash flows.
Abhishek: Interesting! Last week I looked at a bond option. It promised semi-annual interest payments for three years and repayment at face value. But I remember the website mentioned both XIRR and coupon rate. The XIRR was 8.15 percent, while the coupon rate was only 8 percent. 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 are reinvesting the interest until maturity at the interest rate of XIRR itself. For example, when you receive the first interest payment, the formula assumes you reinvest it at 8.15 percent for 2.5 years. And so on, and so on for the other interest receipts. This is the reason for the difference.
If you spend or let the interest languish in a savings account with 2-3 percent interest, your net realized return at maturity will not be as high as the claimed XIRR. Therefore, set your expectations accordingly.
But you can use the XIRR to compare and gain perspective.
By the way, in the context of fixed income securities, XIRR is also called YTM or yield to maturity.
Abhishek: Okay. I’ll keep that in mind. Can you teach me how to calculate the XIRR for my index fund? It is a six-monthly SIP for ₹50,000. I started on January 1, 2024. I sold units worth ₹20,000 on September 30 this year. The statement states that the current value of my assets is ₹1,95,000.
Anand: Certainly. XIRR calculation is simple. All it takes is two inputs: the cash flows in one column and the respective dates on which they occur in another. Please note that cash outflows such as principal, purchase price and SIPs should have a minus sign. You may also have a third column describing what the cash flows mean for reference.
Once this is done, call the XIRR function and define the range of cash flows, followed by the date range, and press enter. That’s it. Your XIRR is calculated.
You can apply this method not only to mutual funds, but to any investment product with cash flows.
The screen glowed bright green and said “Fully charged” when Anand was done.
Published on December 13, 2025
#XIRR