XIRR (Extended Internal Rate of Return) calculates the annualised return for irregular cash flows––like SIPs, top-ups, partial redemptions, and dividends paid––by considering the exact date of each flow. It answers the real question: “At what yearly rate did my money grow, given when I invested and when I withdrew?”
Why does XIRR make sense for Mutual Fund investments?
SIPs and ad-hoc purchases are transacted at multiple dates, so simple return or CAGR on a single purchase price can be misleading. XIRR weights each instalment by time in the market. For example, a Rs. 10,000 SIP made two years ago gets more weight than one made two months ago––hence, XIRR shows the real time adjusted returns.
The importance of XIRR in evaluating mutual fund returns
-
Fair measurement: Compares funds or strategies on a like-for-like, time-weighted basis.
-
Actionable: Shows whether your execution (SIPs, pauses, redemptions) added or diminished results.
-
Goal tracking: Aligns to financial-planning needs that are annualised (e.g., “I need 10–12% a year”).
- Performance hygiene: Stops cherry-picking “since inception” or one-off point-to-point numbers which may paint an unrealistic picture.
Example of XIRR in Mutual Funds
Suppose you invest via SIP: Rs. 10,000 on the 5th of every month from Jan 2023 to Dec 2023 (12 instalments, Rs. 1,20,000 total). On 10 Jan 2025, you redeem for Rs. 1,55,000. An absolute return of Rs. 35,000 looks like 29.2%.
But over two calendar years with staggered cash flows, the XIRR may be around 17.7% (illustrative). The difference arises because each SIP tranche spent a different amount of time invested.
Calculation of XIRR
Conceptually, XIRR is the single annual rate r that sets the present value of all dated cash flows to zero. Treat money you pay as negative and money you receive (redemptions/dividends) as positive. Because dates are uneven, you must discount each flow by the exact day count.
The formula used for XIRR calculation is as below:
=XIRR (values, dates, guess)
In this function, the variable ‘values’ represents the range of data for inflows & outflows, with inflows shown as positive cash flows and outflows as negative cash flows.
The variable ‘dates’ represents the data range for dates for each cash flow, and the variable ‘guess’ is an optional parameter for the expected IRR estimate.
If the parameter is left blank, MS-Excel uses the default value of 0.10. Once the dates and cash flows have been plotted in a table in the excel worksheet, it is easier to calculate XIRR through the in-built function.
Step by step process to calculate XIRR in Microsoft Excel
-
List dates in one column (each SIP, any switches/withdrawals, and the final redemption).
-
List cash flows in the next column: negative numbers for investments, positive for money received.
-
Ensure there is at least one negative and one positive value.
-
If there are no outflows, consider the last available value of the investment as a outflow number
-
In a blank cell, type =XIRR(values, dates) or =XIRR(values, dates, guess) (guess is optional, e.g., 0.12).
-
Format the result as Percentage. That’s your annualised return capturing irregular timings.
- If Excel throws an error, check signs, dates, or provide a different guess (e.g., 0.10 or 0.20).
Example of How to use the XIRR function in Microsoft Excel
-
A2:A14 (Dates): enter 01-Jan-2023, 05-Feb-2023, … 05-Dec-2023, and 10-Jan-2025.
-
B2:B13 (SIPs): enter (minus) 10000 for each SIP row.
-
B14 (Redemption): enter 155000.
-
In C2, type: = XIRR(B2:B14, A2:A14) → returns ~17.7% (illustrative).
- If you received dividends credited to bank, add those dates/amounts as positive rows for accuracy.
What are the benefits of calculating XIRR in a mutual fund?
-
Works with SIPs/SWPs: Handles uneven amounts and dates accurately.
-
Compares choices: Evaluate two funds or two behaviours (monthly SIP vs lump sum) on a uniform annual rate.
-
Clean reporting: The same method AMCs and platforms use in statements, easy to audit and explain.
- Behaviour check: A dipping XIRR after frequent market-timing moves is a red flag; staying systematic often improves it.
XIRR in account statements (AS)
Most mutual fund statements and app dashboards already display scheme-level XIRR for your folio. Interpret it as your personal annualised return after considering when you invested and withdrew. If your XIRR doesn’t match the fund returns shown on websites, that’s normal. Website returns show how the fund performed overall, while XIRR reflects your actual cash flows.
Why can’t you use CAGR instead? With a solved example:
CAGR assumes one investment and one exit––suitable for a single lump sum held throughout, but not suitable for SIPs or multiple cashflows in an investment.
-
Example: Invest Rs. 60,000 via 12 monthly SIPs of Rs. 5,000 in 2024. Value on 31-Dec-2024 is Rs. 66,000.
-
Absolute return = 10%.
-
CAGR over one calendar year = 10%, which overstates reality because much of the money was invested only for a few months.
- XIRR, using dated cash flows, might read over 22% annualised for that short span (illustrative) or a lower/higher number depending on entry months.
The key is: XIRR reflects timing; CAGR does not. Use CAGR only for a single dated purchase; otherwise XIRR.
FAQs
How is XIRR Calculated?
By finding the annual rate r that makes the present value of all dated cash flows zero. In Excel use =XIRR(values, dates). Enter investments as negative and receipts as positive; include the final value/redemption.
What is a good XIRR?
There’s no universal “good” number. Compare your XIRR with your goal requirement (say, 10–12% for long-term equity goals), the fund’s long-term category averages, and alternatives of similar risk.
What is Difference Between XIRR and Absolute Return?
Absolute return ignores time; it’s just gain ÷ cost. XIRR annualises returns and accounts for when each rupee was invested or withdrawn. For SIPs and staggered flows, XIRR is the more suitable metric; use absolute return only for quick snapshots on one date.
Mutual Fund investments are subject to market risks, read all scheme related documents carefully.