XIRR in Mutual Funds

When investing in mutual funds, especially through SIPs, it’s crucial to track your returns accurately. Many investors look at absolute returns or CAGR to measure performance, but these methods don’t account for multiple investments made at different times. That’s where XIRR comes in. In this guide, we’ll break down what XIRR is, why it’s important, how it’s calculated, and how it differs from other return metrics like CAGR and absolute returns. Let’s dive in!

What is XIRR?

XIRR also known as Extended Internal Rate of Return is used to determine the return of an investment that includes multiple inflows at irregular intervals. Unlike traditional return calculating methods, XIRR helps you account for the time, redemption and lump sum investments, if any, during your investment journey. It is considered an important metric when calculating the returns of your SIP investments over a period of time.

Why Is XIRR Important For Mutual Fund Investors?

Mutual fund investors often set up a SIP that requires investments in different intervals. XIRR shows you the true picture of returns accounting for the time, any top-ups or withdrawals during this period. Let’s understand this with an example. 

Say, you set up a SIP of ₹5000 in a mutual fund in December 2023. You had to withdraw ₹25000 in August 2024, but you managed to invest ₹10,000 in a lump sum in September 2024. 

By 1st Jan 2025, you managed to invest a total of ₹55,000 after all redemptions and lump sum investments and your return in January 2025 was ₹60,000. 

This is what your inflows and outflows will look like during the period. The negative indicates money flowing out of your bank account and the positive indicates inflow to your bank account.

Now, at first glance, one might assume the absolute returns are 9.09% (60000-55000/55000)*100. However, this does not tell the full story. Since this method does not consider when you invested the money or how long each investment was at work. For example:

• If you had invested ₹55,000 all at once in Dec 2023, your 9.09% return would have been accurate.
• But in a SIP, you invest gradually. Some money was invested for 13 months, some for 1 month.
• Additionally, if you withdrew some amount in between, the actual return on your remaining investment changes.

This is why absolute returns and CAGR can be misleading for SIPs. This is where XIRR comes in. XIRR adjusts for the time value of money and shows how much your investments truly earned on an annualized basis.

For the above investments, XIRR comes to 15.84% (calculation explained below), which means your SIP investments effectively grew by 15.84%  annually. Unlike absolute returns, XIRR gives a more realistic measure of how your money actually grew over time.

How Is XIRR Calculated?

While XIRR of any mutual fund is easily available on the broking app you are using to invest in mutual funds or the funds fact sheet and you do not really need to learn how to calculate, let’s look at how we arrive at the number for the purpose of understanding the concept. The mathematical formula for calculating XIRR is:

XIRR Formula: (NPV(Cash Flows, r) / Initial Investment) * 100

Where NPV is the net present value, r is the rate of return, and cash flows are the series of investments and withdrawals over time.

However, this calculation can easily be done on an Excel sheet. Excel has a built-in XIRR formula, which means, all you have to do is enter the fields and calculate the XIRR of your investments. 

How To Calculate XIRR on Excel or Google Sheets?

Here is how you can calculate XIRR on an Excel sheet:

1. To begin with create two columns, On column A list down the date of events, when your installments will be going out, redemptions if any, or lump sum investments.

2. In column B, list down your inflows and outflows. Keep your SIP investments in negative signalling outflow, redemptions and final return in positive indicating inflow.

3. Towards the end of the outflow also note the current value of your investments. This is the value that you have after all your installments. 

4. Continuing the above example, this is what your sheet would look like. Now, enter the XIRR formula which is ‘XIRR(cashflow amount, cashflow dates)'. So type = XIRR(B2:B18,A2:A18).

The XIRR for your investments from December 2023 to January 2025 would come to 15.84%. 

What Is a Good XIRR in Mutual Funds?

A "good" XIRR in mutual funds depends on market conditions, the fund's performance, and the investor's goals. Generally, an XIRR higher than the average market return or beating inflation is considered good. For example, if the average market return is 8% and your XIRR is 12%, it indicates strong performance.

Difference between CAGR, Absolute Returns & XIRR

Let’s look at the return calculator from the lens of CAGR and Absolute Returns, this will help you understand what sets XIRR truly apart. 

To draw a simple differentiation between the three return calculation methods, here’s a breakdown:

• Absolute Returns – Best for investments held for less than a year.
• CAGR (Compounded Annual Growth Rate) – Used for lump sum investments held for more than a year.
• XIRR (Extended Internal Rate of Return) – Used when investments happen at different intervals, such as in an SIP.

Let’s understand how this works.

Absolute returns are helpful when calculating returns for investments that have grown within a year. For example:

Assume, you invest ₹100,000 in a mutual fund at the beginning of Feb 2023. By December 2023, your mutual fund investment will grow to 1,20,000. What will be your return here? It’s simple. (1,20,000 - 1,00,000)/100,000*100 = 20%.

Absolute returns give you an absolute number by which your investment has grown. Now, say you decided to hold the above investment a little longer. By February 2025 your investment moved to ₹1,35000. What will be your return here? If you calculate Absolute Returns your return would be 35%. 

Here’s the problem with this approach:

You don’t know whether you earned 35% returns in 2023, 2024 or 2025. This is why in scenarios where you hold your investment for more than a year, an appropriate metric would be calculating at what rate your investment grew every year. 

Which is where CAGR comes in. CAGR or Compounded Annual Growth Rate tells you the rate at which your investment has grown every year.

To calculate the CAGR for the above example, the formula is:

CAGR = (Ending Value / Beginning Value)^(1 / Number of Years) –1.

CAGR = (135000/100000)^(1/3)-1 = 10.52%

This tells you that your investment grew at a rate of 10.52% every year.

Now, let’s assume instead of investing ₹1,00,000 at once, you decided to invest ₹5,000 every month from January 2023 to January 2025. By January 2025, your total investments added up to ₹1,20,000, and your final value was ₹1,35,000. Here’s where things change.

CAGR assumes one-time investment growth, but in this case, you invested at different times. The first ₹5,000 had more time to grow than the last ₹5,000. Using XIRR helps factor in multiple investment intervals and gives you the true annualized return of your SIP.

Calculating XIRR using the method explained using Google Sheets. , which is why the XIRR in the above example would be 7.86%. This means your SIP investments grow at a rate of 7.86%.

Learn more about the difference between XIRR and CAGR.

Conclusion

XIRR is an essential metric for evaluating the performance of mutual fund investments with irregular cash flows. It provides a realistic annualized return, helping investors make informed decisions. Whether you're investing in mutual funds or NPS, understanding and calculating XIRR can significantly enhance your investment strategy.