What is XIRR in mutual funds?
Why do you need to understand it? Let’s find out. Whenever you invest in real estate, stocks, mutual funds, etc., you usually measure the returns by calculating the value of your total principal investment.
For instance, let’s assume that you have invested INR 10 lakhs in a mutual fund scheme. Over a specific time period, it doubles and gives you INR 20 lakhs.
However, there are certain intricacies you should be aware of. The investor of a mutual fund scheme should know the time duration in which their investments doubled.
For instance, it is extremely worrying for investors if their funds double in 50 years.
Hence, the duration in which the investment doubles is a crucial factor. The two integral components of any mutual fund scheme are the Compound Annual Growth Rate (CAGR) and the Extended Internal Rate of Return (XIRR).
So, if you are eager to invest in mutual funds, it’s important for you to know about XIRR in mutual funds. Here are some significant points you should know about XIRR
How XIRR Works?
XIRR considers the timing and amount of each cash flow associated with your mutual fund investment. It calculates the annualized return that would make the present value of all cash inflows equal to the present value of all cash outflows.
XIRR Formula
Calculating the Extended Internal Rate of Return (XIRR) for mutual funds is essential for assessing the performance of your investments over time, especially when contributions are made at irregular intervals. Here’s a practical guide to calculating XIRR:
Practical Steps to Calculate XIRR in Mutual Funds
Step 1: Gather Your Data
You will need two sets of data:
- Cash Flows: This includes all your investments (inflows) and any redemptions (outflows).
- Dates: The corresponding dates for each cash flow.
| Date | Cash Flow |
|---|---|
| 01-Jan-2021 | -10,000 |
| 01-Apr-2021 | -5,000 |
| 01-Jul-2021 | -7,000 |
| 01-Oct-2021 | 15,000 |
Negative values indicate investments (outflows), while positive values indicate returns (inflows).
Step 2: Use Excel or Google Sheets
XIRR can be easily calculated using Excel or Google Sheets with the built-in XIRR function.
- Open Excel/Google Sheets.
- Input Your Data:
- In one column, enter the cash flows.
- In the adjacent column, enter the corresponding dates.
A B
1 Date Cash Flow
2 01-Jan-2021 -10000
3 01-Apr-2021 -5000
4 01-Jul-2021 -7000
5 01-Oct-2021 15000
Step 4: Interpret the Result
- The result will be displayed as a decimal. To convert it to a percentage, format the cell as a percentage or multiply by 100.
- For example, if the XIRR result is
0.12, it indicates an annualized return of 12%.
Overview of XIRR in mutual fund schemes
XIRR is a single rate of return that provides the current value of the entire investment when applied to each SIP. XIRR is your actual return on investment.
It is a tool for calculating returns where many transactions happen at different times.
Usually, there are a series of investments in a SIP. At times, one can redeem a small amount from their investments. On the other hand, investors can pause several months of investments. In such instances calculating the returns become easier with XIRR.
The necessity of XIRR in mutual funds
Investment cash flows are dynamic. In other words, they are never evenly spaced out. It is standard for an investment scheme to have early withdrawals or late deposits. Investors can skip a couple of months of installments.
In these conditions, calculating the return from your investment scheme becomes difficult. However, with XIRR, you can easily calculate your returns.
Fortunately, you can use the XIRR formula in Excel to calculate uneven cash flow intervals.
In case you don’t know, XIRR is the modification over the internal rate of return (IRR). It has additional flexibility as XIRR can assign dates to individuals’ cash flows in your portfolio.
Why does XIRR make sense in mutual funds schemes?
If you invest INR 4000, INR 9000, INR 4000, and INR 6500 in SIPs over the last five years and get INR 53000, your return is 22%. In most cases, the IRR is the resultant amount.
With this concept, you can conclude how much you’ve earned from your investments. But in most modern-day mutual funds, the cash flow is not evenly distributed.
Mutual funds are popular among investors as they allow them to invest and redeem at regular intervals. In other words, when the cash flows are distributed over a period, XIRR is an excellent feature to measure the returns.
Moreover, if you are investing in mutual funds via a systematic investment plan (SIP) or redeeming via a systematic withdrawal plan (SWP), it is crucial to know about XIRR.
XIRR will handle all these inconsistent cash flows. Furthermore, it can offer you clear insights into the status of your investments in mutual funds.
How can you derive XIRR in Excel?
Here is the step-by-step process of calculating XIRR for your mutual funds in MS Excel.
- Enter all your SIPs or transactions in one column in negative. Wherever there is an interest inflow, show it as a positive figure. The additional lump sum amount should be entered as negative again.
- In the corresponding column, add the date of the transaction.
- In the last row, mention the current value of your portfolio in mutual funds. You should also input the correct date.
- Now you can use the XIRR function (in Excel). XIRR usually contains values, dates, etc.
- You should choose values for a series of cash flows that relate to payments based on dates. The column made separately for the date is applicable. Input the values accordingly. Note that the guessing parameter is optional.
Use of CAGR
Most investors inquire whether they can use CAGR to calculate their returns. When we want to invest in a mutual fund, we usually check its returns over the past three or five years.
These returns are typically point-to-point returns. In lots of mutual funds, investors use CAGR to calculate returns. However, if you have personal investments in mutual funds, it is worthwhile to rely on XIRR.
As you can see, XIRR is the most appropriate way to know the investment returns. While CAGR helps you to select mutual funds properly, XIRR is essential to assess the returns you get from your investments in mutual funds.
In other words, when you have a series of investments over time, always rely on XIRR. XIRR is probably the best method to calculate returns for your mutual fund.
Importance of XIRR in Mutual Funds
For calculating returns of a Systematic Investment Plan (SIP) in a mutual fund, XIRR is used. XIRR considers period investment made over time and then adjusts for the time value.
You can calculate XIRR using Microsoft Excel by using the XIRR formula to arrive at a particular value. The formula is = XIRR (value, dates, guess).
Let us see the example and interpretation –
- SIP amount = Rs. 5,000
- SIP investment dates: Start – 1 January 2022
- Date of redemption: 01 July 2022
- Maturity sum = Rs. 31453.34
Let’s assume cash flows as mentioned in the table below:
| 01-01-2022 | -5000.00 |
| 01-02-2022 | -5000.00 |
| 01-03-2022 | -5000.00 |
| 01-04-2022 | -5000.00 |
| 01-05-2022 | -5000.00 |
| 01-06-2022 | -5000.00 |
| 01-07-2022 | 31453.34 |
| XIRR | 17.62% |
What is a good XIRR in mutual funds?
It is difficult to tell which XIRR is good as it is subjective to every individual, their objective, investment time horizon, investment process, investment philosophy, investment instruments, and risk appetite.
Generally, for equity mutual funds – an XIRR over 12% is considered good and for debt mutual funds anything above 7.5% is considered good.
Difference between CAGR and XIRR?
| XIRR | CAGR |
| Average annualized return. | Absolute annualized return. |
| Takes into account cash inflow and outflow | Uses the invested amount, redemption amount, and time period. |
| Best for SIP investment return evaluation | Best for lumpsum investment return evaluation |
| Adjusts for the time value of money. | Does not adjust for the time value |
Consult an expert advisor to get the right plan for you
Conclusion
XIRR is a powerful tool for evaluating the performance of your mutual fund investments. By understanding how XIRR works, you can make informed decisions about your investment portfolio. Remember that XIRR is just one metric, and it’s essential to consider other factors like risk, fees, and market conditions when making investment choices.
