“Investing puts money to work. The only reason to save money is to invest it.”
– Grant Cardone
What is the first thing that comes to mind when you consider investing money? Undoubtedly it is the returns that you can earn from that investment.
If you consider a bank FD, it’s simple. You invest certain money today for a fixed time duration, and you earn a fixed rate of interest. Your capital does not grow, and you get the principal back at maturity.
But if you are investing in mutual funds, the matter is not so straightforward. Apart from lump sum investments, you can invest daily, weekly or monthly through the systematic investment plan (SIP) route. Returns can come in the form of both dividends & capital appreciation.
How do you calculate returns in such scenarios?
The answer is a concept called XIRR. The full form of XIRR is the Extended Internal Rate of Return.
What Does XIRR Mean?
‘Returns’ help us to analyze the performance of an investment over a time period. You need to know whether you have gained or lost money and how much. XIRR is a mathematical tool used to calculate returns in situations involving multiple transactions & cash flows at different dates.
E.g., A mutual fund scheme gets multiple inflows (additional investments, SIPs) and has multiple outflows (full/partial redemptions, dividends, etc.) on different dates. To compute returns where the investor makes periodic SIP investments over a long period, the XIRR is a convenient tool.
Advantages of XIRR
The Internal Rate of Return (IRR) is commonly used to calculate returns when the investment involves a series of cash flows (as in the case of mutual fund SIP).
But the drawback of IRR is that it assumes all the cash flows to occur at evenly spaced time intervals (E.g., the monthly SIP date is the first of every month).
It is not always the case. Months can have different days, and if the SIP date is a holiday, the next business day is considered. Funds declare dividends on different dates. An investor can also make lump sum investments during the period. So the cash flow stream is quite irregular, and IRR does not give you a correct picture.
XIRR meaning is extended IRR. So it’s a modified version of IRR that is flexible enough to account for uneven cash flows in your investment. The calculation of returns using XIRR is much more accurate.
How to Calculate Returns using XIRR?
You need the good old Microsoft Excel to calculate XIRR. It’s a built-in function of MS-Excel. The concept can be easily understood with the help of an example:
Suppose you have made a monthly SIP of Rs.10,000 for 1 year starting January 1st, 2021. The last SIP installment was due on December 1st, 2021. Let’s assume you receive a maturity amount of Rs.1,26,800 on December 5th, 2021.
Problem: How do you determine the returns using XIRR?
Solution:
Monthly SIP Amount: Rs.10,000 (12 installments)
Maturity Amount: Rs.126,800 (received on December 5th, 2021)
SIP Start Date: January 1st, 2021
SIP End Date: December 1st, 2021
Now open an Excel sheet and follow this procedure:
1. In the first column (A), enter the 12 monthly SIP dates one below the other
2. In the next column (B), enter the SIP amount of Rs.10,000 in negative to indicate that it’s your cash outflow
3. Against December 5th, 2021, enter the maturity amount of Rs.126,800 in the next column
4. The XIRR formula in Excel is: XIRR (values, dates, [guess]). In the cell below the maturity amount, type as follows:
= XIRR (B2:B14, A2:A14)*100 and press Enter
5. The answer (XIRR value) of 12.37% is displayed
| A | B | |
| 1 | Date | Cashflow (INR) |
| 2 | 1 Jan 2021 | -10,000 |
| 3 | 1 Feb 2021 | -10,000 |
| 4 | 3 March 2021 | -10,000 |
| 5 | 2 April 2021 | -10,000 |
| 6 | 1 May 2021 | -10,000 |
| 7 | 1 June 2021 | -10,000 |
| 8 | 1 July 2021 | -10,000 |
| 9 | 3 Aug 2021 | -10,000 |
| 10 | 2 Sept 2021 | -10,000 |
| 11 | 4 Oct 2021 | -10,000 |
| 12 | 1 Nov 2021 | -10,000 |
| 13 | 1 Dec 2021 | -10,000 |
| 14 | 5 Dec 2021 | 126800 |
| 15 | XIRR | 12.37% |
Note:
- Monthly SIP dates are irregular. It’s not the first of every month but can vary by 2-3 days
- The SIP amount has to be shown as negative as it is a cash outflow to the investor. The maturity amount received on the final date is shown positive as it is cash inflow to the investor
- If you have not withdrawn the investment, use the current market value of the investment for the maturity amount against the corresponding date
- You can also insert the XIRR formula from the Formulas tab on the Excel sheet
- You can get the exact details of your transaction from the account statement sent periodically by the fund house to its investors. You can also request the same. These details have to be used correctly to calculate XIRR.
XIRR vs. CAGR
Compounded Annual Growth Rate (CAGR) is another popular method to calculate mutual fund returns. But it is useful only if you want to know your investment’s growth over time.
E.g., you had invested Rs.1 Lakh in a mutual fund scheme on April 1st, 2012, and it has grown to Rs.4.50 Lakh as of April 1st, 2022. If you want to know the returns, you can use the CAGR method.
CAGR = [(Final Value/Initial Value) ^ (1/n) – 1] * 100 (n is number of years)
= [(450,000/100,000) ^ (1/10) – 1] * 100
= 16.23%
It means your initial investment of Rs.1 Lakh has grown at an annual rate of 16.23% over 10 years to become Rs.4.50 Lakh.
As you can see, CAGR cannot be used in situations having multiple cash flows over different dates like a SIP. XIRR is perfectly suited in such cases.
Conclusion:
The meaning of XIRR in mutual funds is straightforward. It helps you determine your fund’s performance by having numerous cash flows over different dates that are not evenly spaced in time. Apart from investments & redemptions, you have dividend payouts & switches between schemes that further complicate the matter. This issue is easily resolved by XIRR, which enjoys an advantage over other methods of computing returns.
Considering the importance of the return metric in assessing the performance of mutual funds, it is imperative to calculate it as accurately as possible. Never compromise on this exercise if you want the best out of your investments.
