Why was there a need for XIRR in Mutual Funds?
Why do we invest? To generate returns: income and/or capital appreciation. We can calculate Mutual Funds’ returns using Compounded Annual Growth Rate (CAGR) and Extended Internal Rate of Return (XIRR).
CAGR is a financial metric that calculates an investment or business’s average annual growth rate over a certain period. The formula for CAGR is as follows:
For example, if an investment starts with a value of Rs.10,000 and grows to Rs.20,000 over 5 years, the CAGR would be:
CAGR = (20,000/10,000) ^ (1/5) – 1 = 14.87%
However, one of the most significant limitations of Compounded Annual Growth Rate (CAGR) is that it cannot be used in cases with multiple cash flows because it assumes that all investments are made at the beginning of the year. It neglects periodical instalments and does not treat them as separate. But in real life, this is not how things work. Very often, investments are made periodically, not in a lump sum. For example, SIP, additional purchases, SWP, dividends, partial redemptions, etc., all involve multiple cash flows. Hence, there was a need for another measurement to account for these various transactions at different points in time while calculating the returns on mutual funds. This is where XIRR comes in.
What is XIRR in Mutual Funds?
XIRR calculates the annualized return an investor has earned on their investment in the mutual fund based on the timing and amount of their purchases and redemptions, as well as any dividends or capital gains distributions received during the investment period. By calculating the XIRR at regular intervals, investors can evaluate the performance of their mutual fund investment over time and make informed decisions about their portfolio.
But why not simply use IRR?
Internal Rate of Return (IRR) assumes a fixed interval between cash flows. At the same time, XIRR takes into account the actual dates of cash flows, which may be irregular. For example, in a monthly SIP, the interval between SIP instalments will vary from month to month (28, 29, 30 or 31 days). Similarly, suppose your SIP date falls on a holiday or weekend in any month. In that case, the transaction will change the interval on the next working day.
Since XIRR can account for more complex compounding intervals and schedules, it is a more versatile tool for analyzing investment performance in real-world scenarios.
How to calculate XIRR in Mutual Funds using MS Excel?
- Make a single column with cash outflows (SIP instalments, lump-sum purchases, etc.) depicted with a negative sign and cash inflows (SWP, dividends, redemptions, etc.) for all your purchases depicted with a positive sign.
- In the next column, enter all the dates associated with the transactions.
- You need to mention the existing value of your holding and the date in the last row.
- Now use the XIRR function in Excel, which looks something like this:
- Choose values for a set of cash flows that match a payment schedule given in dates which denote when the initial investment was made and when the cash flows were obtained. The Guess parameter is not compulsory (if you do not put any value, excel uses 0.1 by default).
Here is an example for further clarity:
SIP = Rs. 1000
SIP dates = between 1/1/2022 and 1/1/2023
Liquidation date = 1/1/2023
Maturity amount = Rs. 14,500
| A | B | |
| 1 | SIP Date | Amount |
| 2 | 01-01-2022 | -1000 |
| 3 | 10-02-2022 | -1000 |
| 4 | 13-03-2022 | -1000 |
| 5 | 13-04-2022 | -1000 |
| 6 | 14-05-2022 | -1000 |
| 7 | 14-06-2022 | -1000 |
| 8 | 15-07-2022 | -1000 |
| 9 | 15-08-2022 | -1000 |
| 10 | 15-09-2022 | -1000 |
| 11 | 16-10-2022 | -1000 |
| 12 | 16-11-2022 | -1000 |
| 13 | 17-12-2022 | -1000 |
| 14 | 17-01-2023 | -1000 |
| 15 | 17-02-2023 | 14500 |
| 16 | XIRR | 19.8% |
- All transaction dates have been inputted in the “SIP Date” left column.
- All SIP values are mentioned in the right column titled “Amount” with a negative sign as they are cash outflows.
- The redemption amount is mentioned in the row with the date 17-02-2023 as a positive amount as it is a cash inflow.
- In the last row, we calculated the XIRR as “=XIRR (B2:B15, A2:A15) *100” After pressing the enter button, we obtained the value of 19.8%.
Conclusion
In the world of investing, returns are a primary consideration for investors. Traditionally, Compounded Annual Growth Rate (CAGR) has been used to calculate mutual fund returns. However, CAGR cannot account for multiple cash flows, and thus, the Extended Internal Rate of Return (XIRR) was developed. XIRR considers the timing and amount of purchases, redemptions, dividends, and capital gains distributions in mutual funds, making it more accurate for investors with investments involving multiple cash flows. XIRR is a versatile tool for evaluating investment performance in real-world scenarios, which is why it has become a popular way to calculate mutual fund returns. Excel has an inbuilt XIRR function, which makes calculating XIRR straightforward. Ultimately, understanding how to calculate XIRR in mutual funds allows investors to evaluate their investments and make informed decisions about their portfolios.
FAQs
Q1. What is a good XIRR in Mutual Funds?
A1. A good XIRR for an equity mutual fund may be 11-14%, whereas debt funds may range between 7% and 9%.
Q2. Does XIRR give annualized returns?
A2. Yes, XIRR is an annualized form of return.
Q3. What is the XIRR formula?
A3. The formula in Excel is =XIRR (values, dates, [guess]).
