XIRR tells you the growth of your mutual fund investments and takes into account all the irregular inflows and outflows
XIRR is a more realistic and accurate measure than absolute returns
Let’s say you have started investing in mutual funds and want to compare the returns between two funds. If you only see the absolute returns, you might not get the real picture. This is one scenario in which XIRR, which stands for Extended Internal Rate of Return, comes to the rescue.
XIRR in Mutual Funds
In mutual funds, when one choses the SIP (Systematic Investment Plan) option, the investments made are spread over different time periods. Also, many investors choose to withdraw some amount which is known as partial withdrawal. Even when it comes to lumpsum investments, the inflows and outflows can be spread across different dates. To measure the returns, XIRR is used.
What is XIRR?
XIRR tells you the growth of your mutual fund investments and takes into account all the irregular inflows and outflows. Let’s understand this with the help of a following example:
January 1, 2024: Shilpa starts investing in ABC mutual fund with an initial investment of Rs 10,000.
June 1, 2024: She has some extra funds and invests Rs 10,000 more.
January 1, 2025: The total amount invested is Rs 20,000 (10,000 + 10,000). The value of her portfolio is now Rs 25,000. She decides to check her returns.
Now, her absolute returns are Rs 5,000 (Portfolio value 25,000 minus Investment value 20,000). In percentage terms, her absolute returns are 25%. But since she spread her investments, it’s not really accurate. Using the XIRR tool in Excel, XIRR comes out to be 32.15%.
Why Does it Make Sense for Mutual Fund Investments?
XIRR is a very useful tool when it comes to mutual funds. Here are some reasons:
1) It accounts for irregular cash flows and outflows.
2) Helps you evaluate and compare your mutual fund schemes.
3) It’s a more realistic and accurate measure than absolute returns.
4) It’s useful even when you redeem units from any mutual fund scheme.
Calculation
XIRR can be calculated using Microsoft Excel which has an inbuilt XIRR calculation function.
XIRR formula in excel is: = XIRR (value, dates, guess)
Step by Step Process to Calculate in Excel
- Create a spreadsheet with two columns — dates and corresponding cash flows.
- The amount you have invested should be in negative as cash is going out. Similarly, the amount you gave redeemed should be in positive. The current value of your portfolio should also be in positive.
- Use the XIRR function in Excel by selecting your cash flow range and date range.
- The resulting percentage is your XIRR.
Example of How to Use the Function in Excel
Let’s take an example of Kabir making an SIP of Rs 10,000 every quarter in the year of 2024.
His transaction dates are:
- January 1, 2024
- April 1, 2024
- July 1, 2024
- October 1, 2024
- December 1, 2024
His total amount invested is Rs 50,000 and let’s assume the value of his investment to be Rs 60,000 on January 1, 2025. To calculate the XIRR in excel, follow these:
- In column A enter the dates and in column B, enter the SIP amounts. The SIP amounts should start with minus sign.
- Enter the date of January 1, 2025 in column A and the amount of Rs 60,000 in column B as that is the value of the portfolio.
- In the box below 60,000, type in =XIRR(select the amounts in column B, then select the date in in column A,).
- It will look like =XIRR(B2:B7,A2:A7,)
- The XIRR will be displayed in point terms. You can covert it in percentage terms. It comes out to 40%.
Can We Use CAGR Instead for Calculating Returns?
CAGR (Compound Annual Growth Rate) is commonly used when evaluating mutual funds with regular cash flows. It is best suited when the investment pattern is constant like in lumpsum. On the other hand, XIRR is used when the inflows are irregular. It’s ideal for SIP investments and also accounts for irregular withdrawals. It is a more accurate measure of returns and is also used to compare two or more mutual fund schemes.
Final Thoughts
XIRR is a great tool for evaluating mutual fund returns. The biggest advantage of XIRR is that it accounts for the timing and amount of each investment and also each withdrawal to give the real picture. Investors can also compare two or more mutual fund schemes they are invested in and take corrective measures if necessary.
FAQs
In Excel, use =XIRR(values, dates) where values include all cash flows (investments as negatives, redemptions as positives) and dates are corresponding transaction dates.
Yes, XIRR considers all cash flows, including partial withdrawals or redemptions.
No. Since mutual funds are long-term investments, monitoring XIRR quarterly or bi-annually is sufficient.
Yes. A negative XIRR means the current value of your investment is less than your total investment, indicating a loss.
Yes. XIRR reflects portfolio value changes and thus fluctuates with market movements.
Leave a Reply