What is XIRR in Mutual Funds

Introduction

Although past returns do not guarantee future performance, past returns are one of the most critical metrics reviewed by mutual fund investors before investing. SIPs have become a popular medium for mutual fund investments to make investing easier. Calculating the return on your investment is relatively simple for lump sum investments. However, the standard return calculation method of CAGR (Compound Annual Growth Rate), generally used to calculate mutual fund returns on lump sum investments, is not very accurate when used for SIP investments. 

That’s where you need to use the XIRR metric. XIRR full form is Extended Internal Rate of Return. The XIRR metric is necessary for calculating SIP returns because SIPs involve making a series of investments in mutual fund schemes. Using the CAGR metric would disregard the timing of each investment and only consider the first and last values, leading to an inaccurate representation of the returns to investors.

So, how does XIRR improve on the drawbacks of CAGR for calculating SIP mutual fund returns? How do you calculate the XIRR of your mutual fund investments? Let’s answer all these questions, starting with what XIRR is.

What is XIRR?

SIP investments involve making several investments at different times. Using the XIRR formula, investors can calculate their rate of return on each investment. Once that’s done, the XIRR calculation can determine the overall average annual rate of return on all investments.

Using the XIRR formula, investors can also determine their returns from a SWP (Systematic Withdrawal Plan). The XIRR formula is used to determine the accurate rate of return for SIPs. The same formula could be used in any long-term investments in mutual funds. This would include any investment account where there have been multiple investments or even withdrawals during the tenure of the investment.

How XIRR work in mutual funds?

Let’s understand what is XIRR in mutual funds and how it is calculated with an example:

You have started an SIP of ₹10,000 per month, intending to cash out after five years. After five years, your invested amount is ₹6,00,000. The total value of your investment is ₹8,84,000 after five years. What do you assume is the rate of return on your investment?

Considering the CAGR calculations, it would involve starting by deducting the total investment amount from your total investment returns to calculate the rate of return. But is that accurate?

The ₹10,000 you invested in the first month of your SIP has remained invested for the entirety of five years. The ₹10,000 invested in the 59th month of the SIP has remained invested for only one month. That explains why the CAGR of the first month’s investment amount will be higher than the 59th month’s because it has remained invested longer.

Now, you can sit and calculate the CAGR of each individual instalment and calculate the performance of your mutual fund investments from there, but it is needlessly complicated and difficult to analyse. The easier way is to calculate the XIRR of the investment, which takes all the individual CAGRs of the separate investments and combines them into a common CAGR, which is shown as the XIRR of your investments.

Let’s calculate the actual XIRR and CAGR of the example discussed above. It will help you see how different the return percentages between the two metrics can be.

The CAGR of the above-mentioned investment scenario is 8.06%. This may seem like decent returns, given we do not know what type of mutual fund the investment was made in. However, let’s see what the XIRR of this same investment scenario is.

The XIRR of the same investment scenario is 15.48%, a difference of over 7% from the CAGR calculations. This is mainly because XIRR considers the impact that timing and frequency of cash flow have on the returns of any investment. So, the actual rate of return on the investment scenario discussed is 15.48%.

Why is XIRR or Extended Internal Rate of Return important?

Now that you know how the calculation is done for XIRR in mutual funds, let’s understand why using this metric is essential. This is not just for SIP return calculations but for any long-term investment return calculations in general. Ideally, investors would invest a lump sum and hold it for the entire duration of their investment period without any withdrawals or additional investments. That way, the CAGR metric would provide an accurate return rate on the investment.

However, in reality, investors often invest more during the tenure of their investments or even make partial withdrawals from the investment amount during the tenure in irregular periods. For SIPs, investors have the option of investing more than their SIP amount as a lump sum into the fund at any time during the SIP tenure. Inversely, they can also skip SIP payments for a month or a few months at any time during the tenure.

These investments and withdrawals create cash inflows and outflows in your investment account. All these factors collectively need to be considered when calculating the rate of return on your investment. These nuances can’t be factored in when calculating the rate of return using the CAGR method, and as such, the XIRR metric is necessary to figure out the actual return rates on your investment.

Step-by-Step Process to Calculate in Excel

Microsoft Excel and other spreadsheet applications allow you to calculate the XIRR of any investment. Before you learn how to calculate XIRR in Excel, let’s look at the data you’ll need to calculate XIRR.

To calculate XIRR, you need to know two things about your investment: the transaction amounts and the transaction dates. This includes any withdrawals as well during the tenure. Once you have all the information, this is the Excel formula you’ll need to use. 

=XIRR(Values,Dates[guess])

You need to set up your table accordingly so that all the transaction amounts are in one column and all the transaction dates are in another. Make sure any investment amount in the transaction amount column has a ‘-’ (minus) sign ahead of it, and any withdrawal amounts don’t.

The values in the formula correspond to the transaction amounts, and the dates in the formula correspond to transaction dates. The guess part is to be left unchanged in the formula. Here is a step-wise guide to calculate XIRR in Excel.

Step 1: Enter all the investments made in a column in sequence.

Step 2: Enter the dates of each corresponding investment in another column.

Step 3: Once all the investments and dates are entered, mention the final investment value and its corresponding date in their respective columns.

Step 4: Make sure you add a ‘-’ (minus) sign ahead of any investment transaction in the transaction amount column.

Step 5: In any other row, insert the XIRR formula and drag the transaction amounts and transaction dates separated by a comma.

Step 6: Hit enter, and you’re done. The XIRR return rate of your investment will be displayed.

Bonus Step: If you have a decimal output, add “*100” at the end of the formula to get the accurate XIRR return rate percentage.

What are the benefits of calculating XIRR in a mutual fund?

As an investor, having a thorough understanding of your investments and acquired returns over time is crucial. It helps determine the performance of your individual investments and areas where you can improve your investment style. These calculations also help you plan your upcoming investments and ensure they’re more efficient and able to attract better returns. Studying the returns from your mutual fund investments is the root of all these benefits, and XIRR is an excellent and accurate method of calculating your mutual fund returns