XIRR, or Extended Internal Rate of Return, is a method for calculating the annualized return on mutual fund investments that involve multiple cash flows at different times. If you invest through a Systematic Investment Plan (SIP), make additional lump sum purchases, or withdraw money at various points, XIRR gives you a single percentage that reflects your actual return across all those transactions. It’s the most accurate way to measure how your mutual fund investments have truly performed.
Why XIRR Matters for Mutual Fund Investors
Most mutual fund investors don’t put in one lump sum and leave it untouched for years. They add money monthly through SIPs, occasionally invest a bonus, or redeem units when they need cash. Each of those transactions happens on a different date, and the money invested earlier has more time to grow than money invested later. A simple return calculation ignores all of this complexity.
XIRR solves the problem by factoring in the exact date and size of every transaction. It calculates a single annualized return that accounts for the fact that your January investment had 12 months to compound while your November investment only had two. Without XIRR, you’d have no reliable way to know whether your SIP in a mid-cap fund actually outperformed your lump sum in a debt fund.
XIRR vs. CAGR
CAGR (Compound Annual Growth Rate) measures the annual rate at which an investment grows over a specific period, but it assumes a single investment at the start with no further inflows or outflows. That makes CAGR perfectly fine for evaluating a one-time lump sum investment. If you invested ₹1,00,000 five years ago and it’s now worth ₹1,80,000, CAGR tells you the smooth annualized growth rate between those two points.
XIRR, on the other hand, considers every cash flow individually: every SIP installment, every additional purchase, every partial redemption. It assigns weight based on when each transaction occurred. For an SIP investor making 60 monthly investments over five years, CAGR would be misleading because it would treat the entire investment as though it was made on day one. XIRR gives you the real picture. Use CAGR for lump sum investments and XIRR for anything involving multiple transactions over time.
How XIRR Is Calculated
You don’t need to understand the underlying math to use XIRR, but knowing what goes into the calculation helps you set it up correctly. XIRR needs two things: a list of cash flows (money in and money out) and the exact date of each transaction.
Every investment you make is entered as a negative number, because that’s money leaving your pocket. Every redemption or the current value of your holdings is entered as a positive number, because that’s money coming back to you. The formula then finds the annualized interest rate that would make the net present value of all those cash flows equal to zero. In plain terms, it’s asking: “What single annual return rate would explain all these ins and outs?”
If your investment is still active and you haven’t redeemed anything, you include the current market value of your holdings as a positive cash flow on today’s date. This lets you estimate your return as of right now.
Calculating XIRR in Excel or Google Sheets
The easiest way to calculate XIRR is in a spreadsheet. Here’s how to set it up:
- Column A: List every transaction date, starting with your first investment. Use a proper date format (like 01-Jan-2023). Include the current date as the last entry if the investment is ongoing.
- Column B: Enter the cash flow for each date. Investments go in as negative numbers (for example, -5000 for a ₹5,000 SIP). Redemptions or the current portfolio value go in as positive numbers.
- The formula: In an empty cell, type =XIRR(B1:B25, A1:A25), adjusting the cell range to match your data. The result is a decimal that you can format as a percentage.
Suppose you invested ₹5,000 monthly for 12 months starting January 2024, and your holdings are worth ₹65,000 today. You’d have 12 rows showing -5000 with each month’s date, plus a 13th row showing 65000 with today’s date. The XIRR result would tell you your annualized return for that SIP.
The formula also accepts an optional “guess” parameter, which helps Excel converge on the answer faster. If you leave it out, it defaults to 10%. For most mutual fund calculations, you won’t need to touch this.
What the XIRR Result Tells You
The output is an annualized percentage. If your XIRR is 14%, it means your various investments, weighted by their timing and amounts, collectively grew at a rate equivalent to 14% per year. This makes it easy to compare across different investments, even if the investment patterns were completely different. You can compare an SIP in an equity fund against irregular lump sum investments in a balanced fund and get an apples-to-apples annual return for each.
Keep in mind that XIRR reflects your personal return based on when you put money in and took money out. Two investors in the same mutual fund can have different XIRR values if they invested on different dates or in different amounts. This is a feature, not a flaw. It tells you how your specific decisions played out, not just how the fund performed in general.
Where XIRR Can Be Misleading
XIRR is reliable for most mutual fund scenarios, but it has a few weak spots worth knowing about.
For very short holding periods, XIRR can produce exaggerated results. If you invested ₹10,000 and it became ₹10,500 in two weeks, the annualized XIRR would be an enormous number, because it’s projecting that two-week gain across a full year. That doesn’t mean you’ll actually earn that much over 12 months. For investments held less than a year, take the annualized figure with some skepticism.
Complex cash flow patterns with large losses can also cause problems. When you have a mix of investments and withdrawals where the total amount withdrawn exceeds what was invested (perhaps due to timing of partial redemptions during a downturn), more than one mathematical solution may exist. In these cases, Excel’s XIRR function might return an error, or worse, it might return one of several possible answers without alerting you. If you see a result that looks unreasonably high or low relative to how the fund actually moved, the calculation may have landed on the wrong solution.
Accuracy depends heavily on getting dates right. XIRR calculations are sensitive to transaction dates, so even small errors in dates can distort the result. Pull your transaction history directly from your mutual fund statement or portfolio tracker rather than entering dates from memory.
Where to Find Your XIRR Without Calculating It
Most mutual fund platforms and portfolio trackers in India now calculate XIRR automatically. Apps and websites that aggregate your mutual fund holdings typically pull transaction data from your folio and display an XIRR for each fund and for your overall portfolio. Your fund house’s account statement or consolidated account statement (CAS) from the registrar provides the raw transaction data if you want to verify the number yourself in a spreadsheet.
If you use a portfolio tracker, check whether it includes your current holdings’ market value as the final positive cash flow. Some tools only show returns on redeemed investments, which would give you an incomplete picture if most of your money is still invested.