What is extended internal rate of return, how is XIRR calculated
If you are confused by personal finance terms, jargon and calculations, here’s a new series to simplify and deconstruct these for you. In the 17th part of this series, ET Wealth explains what extended internal rate of return is used for.
WHAT IS XIRR? Extended internal rate of return is the annualised return for investments where the cash flow is irregular or one that involves multiple cash flows with varying amounts. This is the reason XIRR is the most suitable for mutual funds, which can entail multiple transactions, especially if you invest via SIPs, or redeem via SWPs.
This method considers each SIP as a separate investment because the time for which every instalment is invested is different, the first SIP being invested for the longest period and the last one for the shortest time.
It’s called ‘internal rate’ because it does not consider the impact of any external factors like inflation or risks while calculating the return on the investment.
How is it calculated? XIRR is a modified form of IRR, or internal rate of return, which is used to calculate returns involving a series of cash flows. The best way to derive XIRR for mutual funds is by using Microsoft Excel since it has an inbuilt function to carry out the calculation. All you have to do is feed the transaction dates in the first column, SIPs in the second column with a negative sign, and finally the existing value of the holding.
Suppose you started a SIP of Rs.5,000 on 1 January 2023, continued till 1 May, missed three SIPs, started again from 10 September with a reduced SIP of Rs.3,000 till 10 December, and ended on 1 January with an SIP of Rs.5,000. On 10 January 2024, you decided to redeem the holding, at which point, it had grown to Rs.50,000. To calculate XIRR, open Microsoft Excel sheet. In Column A, enter SIP dates, and in Column B, enter SIP values with a negative sign. After the last SIP value, enter the redemption amount as a positive number. In the next row enter the following formula: Is it better than CAGR? CAGR is another metric that can be used to measure mutual fund performance, but XIRR is a more accurate method as it uses the timing and value of each cash flow. Here’s how the two differ and why XIRR is better.
Every mutual fund offers positive XIRR on SIP investments in 5 years. Did you exit too soon?2026-06-03T07:46:42Z
