What is XIRR in mutual fund? Check how to calculate it and key difference between CAGR and XIRR

The extended internal rate of return, or XIRR, is a financial metric to calculate returns on your mutual fund investments, especially when you invest through the systematic investment plan (SIP) route.

The SIP facility allows investors to invest small amounts of money periodically instead of a lump sum amount. The frequency of such investments can be weekly, monthly or quarterly.

XIRR calculation considers all these SIP transactions, along with the time at which they were executed, to give you one consolidated rate of return on your entire investment.

Why is XIRR used for SIPs?

Calculating returns for SIPs is tricky as multiple investments occur at different purchase prices, and that too at regular/ irregular intervals of time. In addition, there can be inflows in the form of dividends or redemptions during the course of your investment journey.

In such a scenario, XIRR is the only financial metric that would tell you how much money you are actually making on your mutual fund at any given point in time.

XIRR considers each installment in an SIP a new investment, along with the total time for which it was invested.

For example, if you invested in a 10-year monthly SIP, your first installment was invested for 10 years, second for 9 years, 11 months, and so on. So, each SIP amount is compounded for a different period.

XIRR calculates the compounded annual growth rate (CAGR) of each SIP and then adds all these together to give one overall compounded rate.

Difference between CAGR and XIRR

Compounded annual growth rate (CAGR) is one of the most widely used measures for calculating mutual fund returns in India.

The CAGR of a mutual fund scheme is the average annual return that the fund has generated over a period of time. It is the rate at which your investment grows yearly during the investment period, assuming that the profits are reinvested at the end of each year.

CAGR is the rate of return that you usually see when you check the past performance of a mutual fund scheme. However, it can effectively calculate returns only if you invested a lump sum amount in any mutual fund scheme at point A and redeemed it later at Point B. It cannot help you estimate returns if there were a series of transactions between Point A and Point B.

XIRR helps in measuring SIP returns more accurately as it takes periodic investments into account.

How is XIRR portfolio return calculated?

You can calculate the XIRR of your mutual fund investments using the XIRR function in the Microsoft Excel software. Here’s a detailed guide to help you through the process:

• Enter all your transactions in one column (SIP instalments, redemptions or any other cash inflow or outflow), with the transaction date in the corresponding column.
• Every investment value should be written as a negative number, whereas inflows like dividends or redemptions should be written as a positive figure.
• In the last row, you need to mention the current value of your portfolio (as a positive figure) along with the current date.
• Now use the XIRR function in any other free cell. For that, first write =XIRR in that call. Then, put a bracket and select all cells with transaction values written in them. Add a comma, and then select all the cells with dates written in them. Close the bracket.
• Then, click enter to get the XIRR on all your transactions.

Assume you start a SIP of ₹10,000 on January 02, 2024, and continue to invest the same amount for the next six months on the same date. And as of July 10, 2024, your maturity amount is ₹62,500. In this case, you can calculate the XIRR of your SIPs as shown below:

In the above table, the cash flows happen at irregular intervals. Here, you can use the XIRR function to calculate the return for these cash flows. Do not forget to include the ‘minus’ sign whenever you invest money.