What is XIRR, and how to calculate it?

What is XIRR, and how to calculate it?

Back

When you invest in mutual fund schemes, stocks, real estate, or other investment instruments, you assess the returns by calculating the increase or decrease in your principal investment.

Suppose you invest Rs. 1 lakh in a mutual fund scheme and earn returns worth Rs. 50,000. As your returns are 50% of your investment, you feel elated. But what is the duration of these earnings? Will it be worth the joy if it took you ten years to earn the 50% returns?

Thus, the investment duration is an essential factor in determining a reasonable rate of return. There are two ways to calculate mutual fund returns – Compound Annual Growth Rate (CAGR) and Extended Internal Rate of Return. (XIRR)

CAGR is a way to calculate the annual growth rate for your investment in a mutual fund. But CAGR doesn’t let you estimate the return for several cash flows. XIRR is the formula you need to determine the cash flows returns in a mutual fund. If you are on a systematic investment plan (SIP), XIRR helps you calculate the returns from your mutual funds.

Understanding XIRR

When you invest a significant amount, you redeem the entire investment in one go. In this process, there are only two transactions. CAGR helps you measure the return on this investment.

But, some investors use SIP for investing and opt for a systematic withdrawal plan when it comes to redemption. There are multiple cash flows, which CAGR cannot calculate accurately.

XIRR takes into account multiple cash inflows and outflows. The XIRR formula calculates the annual average return of every installment. Then, it adjusts this to arrive at the overall average annual return for all your investments.

Importance of XIRR in mutual funds

XIRR gives a much more accurate calculation of returns. It provides the added flexibility to assign particular dates to individual cash flows. Interestingly, the formula for XIRR is available in excel sheets to include uneven cash-flow intervals in your calculation.

How to calculate XIRR using Excel?

The formula for XIRR is derived by modifying the Internal Rate of Return (IRR) formula. It considers irregular periods. For calculation, you will need to record SIP transactions and their corresponding dates. You can gather this data from the statements you receive from your fund house.

It is easy to use XIRR in Excel for calculating the return from mutual funds for multiple cash flows. Make sure you feed in details about all mutual fund transactions. These include SIP, SWP, redemptions, additional purchases, and the dates for each of these transactions.

  • Your invested amounts will have a negative sign. It applies to your SIP and additional purchases.
  • Your redemption amounts will have a positive sign.

Suppose you invest Rs. 2500 in a mutual fund scheme through SIP every month. Here’s what you do to calculate the returns from the mutual fund using XIRR:

  • Enter all dates of transactions in Column A.
  • Fill out the SIP amount in the next column (-2500).
  • If there is any redemption, enter the amount in the cells of Column B corresponding to the redemption dates. Make sure the sign for redemption amount is positive.
  • In a new cell, enter the XIRR formula = XIRR (values, dates, [guess]). For this example, it will be XIRR (B2:15, A2:15)*100.

XIRR Vs. CAGR

Both XIRR and CAGR help calculate returns from your investments in mutual fund schemes. XIRR is a way to calculate returns from SIP investments, while CAGR helps calculate returns from lump-sum investments. An investor must know how to calculate returns so that there is no dependency on others.

  • The returns using XIRR and CAGR will be the same for a lump-sum investment made only once a year.
  • The returns using XIRR and CAGR will be the same in the case of instruments having the same annual return throughout the investment period.
  • XIRR and CAGR will give different returns for multiple cash inflows and outflows.
  • XIRR and CAGR have different calculations for investments with varying returns, like mutual fund returns.

Last words

XIRR is the right metric to assess your investment returns in real life. It is different from CAGR in many ways. While CAGR helps you select mutual funds, XIRR is vital to calculate the returns you earned from your investments. IRR enables you to find out returns from cash flows that are equally spaced in time. But, the chances that your investments are evenly spaced are low.

XIRR aids in the calculation of returns when you are making a series of investments over time along with transactions like switches, dividends, withdrawals, etc. When compared to CAGR and IRR, it is a better way to calculate returns from your mutual fund. 

Thank you for reading this post, don't forget to subscribe!