Suppose you invested Rs. 5 lakh in an equity mutual fund, and after 3 years, its value increased to Rs. 8 lakh. If you decide to redeem your investment now, you can calculate the returns you earned using the Compound Annual Growth Rate, or CAGR. But what if you didn’t invest the entire amount at once?
For example, you may have invested Rs. 25,000 each month over 20 months and then redeemed it at Rs. 8 lakh. What if you redeemed some units, or made some extra investment in that period? Would CAGR still be the most suitable method to calculate the returns in that scenario?
The answer is no, because when you invest through an SIP, each instalment counts as a separate cash flow, and the return for each instalment is different depending on the time it was invested. So how do you get an accurate annualised return for an SIP investment or any other irregular investment? The answer lies in XIRR.
The XIRR in mutual fund investments is special as it takes the timing of the investment into account, giving annualised returns on investments made at different times and in varying amounts. Let’s study the XIRR meaning in mutual fund investments in detail and understand how it can be calculated using the XIRR Excel function.
What is XIRR?
When you invest through a Systematic Investment Plan, each contribution counts as a separate investment. For example, let’s say you invest Rs. 1000 starting in January and continue this throughout the year till December. Now, here’s how it works:
- Your first instalment in January has had 12 months to grow by the time you redeem your investment at the end of the year.
- Your second instalment in February will have 11 months to grow.
- Similarly, your December instalment will have had just 1 month to earn returns by the time you redeem your investment.
The holding period for each instalment is different, which means compounding for each is different, ultimately this affects the returns you earn on each contribution. This makes the calculation of your overall return more complex. You can’t simply apply a single average rate of return like with a lump sum investment. In such cases, XIRR becomes the most accurate way to calculate your overall return.
So what is XIRR? Well, XIRR stands for Extended Internal Rate of Return, a measure which takes into account the timing and amount of each cash flow (in and out) to give you a true annualised return. So even if you made any extra lump sum investment in the same fund, or withdrew any funds at different points during your investment period in the above example, the mutual fund XIRR would accurately reflect how these varying cash flows have impacted your total return.
Why is XIRR Important in Mutual Funds?
The XIRR in mutual fund investments, specifically ones made through SIPs, is a valuable measure. CAGR does not consider periodic investments, which paints an unreliable picture of your overall returns. Whereas XIRR considers both cash flow as well as timing. An individual investing in an SIP for the long term may occasionally redeem some units due to an emergency. On the other hand, they might also decide to make a lump sum investment if they receive a bonus or any other windfall.
This creates an irregularity in the cash flow pattern, which makes measuring the true annualised return more complex. In such cases, mutual fund XIRR becomes important. It accurately accounts for both the timing and the amount of each cash flow, whether it’s a regular SIP instalment, a lump sum investment, or a redemption.
Difference Between XIRR and CAGR
Let’s first understand what CAGR is exactly. CAGR stands for Compound Annual Growth Rate, and it is one of the most popular metrics among investors when analysing mutual fund performance. For example, investors often compare the past 3-year, 5-year, or 7-year CAGR of different mutual funds and stocks to understand their historical performance. CAGR gives you an average annual growth rate of an investment over a specific period of time. Here’s how it is calculated:
CAGR = (Final Investment Amount / Initial Investment Amount)(1/n) – 1
Here n is the number of years.
Suppose Rekha invests a lump sum of Rs. 4 lakh in 2019. When she withdraws the investment after 5 years, its value rises to Rs. 7 lakh. The CAGR can be calculated:
- CAGR = (7,00,000 / 4,00,000)(1/5) – 1
- CAGR = (1.75)(1/5) – 1
- CAGR = 1.1184 – 1
- CAGR = 0.1184 or 11.84%
This rate assumes that the investment is a lump sum that is made at the beginning of the period, and also that there are no additional contributions or redemptions during the investment period. Thus, it does not factor in the timing of each cash flow. This means that while CAGR is good for lump sum investments, for SIPs it is not a very good measure because SIPs involve multiple contributions made at different times, each with a different holding period.
So the main difference between CAGR and XIRR in mutual fund investments is that XIRR accounts for all cash flows as well as timing, whereas CAGR does not. This makes CAGR suitable for getting accurate returns on lump sum investments and XIRR for irregular investments.
How to Calculate XIRR in Mutual Funds
Due to its complexity, we use a financial calculator or Excel function for XIRR calculation. Here are some steps you can follow:
1. Financial Calculator
This method is particularly useful for calculating XIRR in sip. You just need to input some values in the calculator:
- Investment frequency – This refers to how often you make contributions to your mutual fund scheme. It could be monthly, quarterly, annual, and so on.
- Start date – When you first made the investment.
- Maturity date – The date on which you redeemed your investment.
- Invested amount – The amount you invested per month, quarter, or year.
- Maturity amount – The present value of your investment.
Simply hit calculate and you will be presented with the XIRR and a detailed frequency breakdown! You may find that not all calculators allow you to input any withdrawals or irregular investments, so basically they end up being IRR calculators. The best way to calculate is the Excel function.
2. Excel Function
You can use this method on Excel or any other software like Google Sheets. Follow these steps:
- Create a ‘Date’ column. You’ll be entering every date of transaction here, which includes all redemptions and investments.
- Create a second column where you’ll enter the investment or redemption amount.
- Use this table to enter the data accurately. Against each date write the amount you invested or redeemed. Note that every investment amount should have a negative value in the table, and every redemption amount should be positive.
- The last date you’ll enter is the date on which you withdraw the investment. Against that date, enter the maturity amount or present value of your investment.
- In the final row, add the XIRR formula, which is =XIRR(values, dates, guess). We’ll understand how this works in just a minute.
- The resulting XIRR will be displayed in the cell with the formula.
Let’s go back to Rekha’s example to understand this function better. Imagine that instead of a lump sum, Rekha decided to invest via an SIP. She invests Rs. 20,000 every quarter starting from 1st January 2019. She makes 20 such payments over 5 years. On 1st January 2024, she decided to withdraw the investment. Look at the table below to understand how she can use the Excel function to calculate XIRR.
| A | B | |
| Date | Investment or Redemption Amount | |
| 1 | 1/1/2019 | -20000 |
| 2 | 1/4/2019 | -20000 |
| 3 | 1/7/2019 | -20000 |
| 4 | 1/10/2019 | -20000 |
| 5 | 1/1/2020 | -20000 |
| 6 | 1/4/2020 | -20000 |
| 7 | 1/7/2020 | -20000 |
| 8 | 1/10/2020 | -20000 |
| 9 | 1/1/2021 | -20000 |
| 10 | 1/4/2021 | -20000 |
| 11 | 1/7/2021 | -20000 |
| 12 | 1/10/2021 | -20000 |
| 13 | 1/1/2022 | -20000 |
| 14 | 1/4/2022 | -20000 |
| 15 | 1/7/2022 | -20000 |
| 16 | 1/10/2022 | -20000 |
| 17 | 1/1/2023 | -20000 |
| 18 | 1/4/2023 | -20000 |
| 19 | 1/7/2023 | -20000 |
| 20 | 1/10/2023 | -20000 |
| 21 | 1/1/2024 | 700000 |
| 22 | ||
| 23 | XIRR | 0.2184816445 |
The XIRR formula is: =XIRR(values, dates, guess). In place of ‘values’, enter the cash flows, which in this table range from cell B1 to cell B21. In place of ‘dates’, select the cells containing the dates corresponding to the cash flows. In this case, these are cells from A1 to A21. The ‘guess’ is the initial rate of return that Excel assumes before calculating the exact XIRR, which is 10%. Since this is an optional field you can leave this blank. The formula would look like this:
=XIRR(B1:B21, A1:A21)
The result is displayed instantly as 0.2184. You can multiply the result by 100, which means the XIRR for Rekha’s investment is 21.84%. Notice that this table includes every cash flow. The Rs. 20,000 are investment amounts, which is why they are entered with a minus sign. The final cash flow is when Rekha redeems her investment on 1st January 2024, which is why Rs. 7,00,000 is positive in the table.
This example is quite straightforward, as Rekha didn’t make any redemptions and made all her SIPs on time. If she wanted to redeem some units partially or invest a lump sum at some point, she could simply add the date of the transaction and the corresponding amount to the list of cash flows. The XIRR function would then account for these extra cash flows and their respective timings, and adjust the overall annualised return accordingly.
XIRR Formula and Explanation
The XIRR formula in the Excel function is quite simple: =XIRR(values, dates, guess). Here,
- ‘Values’ is the range of cells on the spreadsheet that represents cash flows. Investments should be entered with a negative sign and redemptions should be positive.
- ‘Dates’ refers to the range of cells containing the dates on which investment or redemption was made. Make sure the dates you enter have a valid format because invalid formats break the function and result in the #VALUE! error.
- ‘Guess’ is just an initial rate of return that Excel assumes before calculating the exact XIRR, which is 10%. This is an optional field so you can ignore it.
Conclusion
XIRR stands for Extended Internal Rate of Return, which is a way to measure the annualised return of investments that have multiple irregular cash flows. The XIRR in mutual fund investments considers the timing and amount of every single transaction in the period, which makes it very useful for estimating the accurate returns of an irregular investment.
