What Is XIRR In Mutual Funds And How To Calculate It?

If you’ve been investing in mutual funds, you may go back and redeem these investments when you get closer to your financial goals. Before you do so, you must compute your returns based on your initial investment amount and the maturity value.

However, computing these returns may seem difficult, especially in the case of multiple cash inflows and outflows, like in SIPs (Systematic Investment Plans). This is where the XIRR formula in mutual funds can help. XIRR stands for extended internal rate of return.

In mutual funds, XIRR is a method to compute the returns from your mutual fund investments made at irregular intervals. Using the XIRR calculator formula for every SIP investment or liquidation, if any, would help you derive the return from your overall investment.

Let’s assume that you started investing in a monthly SIP of ₹5,000 in an equity mutual fund and continued this for 10 years. At the end of the 10 years, the total value of your investment is ₹12.33 Lakh. However, the return on your initial SIP instalment of ₹5,000 will differ from the returns generated on the other instalments over the years. This is because the initial instalment stayed invested in the mutual fund for the longest period (10 years).

Since every SIP instalment remains invested in the fund for a different tenure, the corresponding CAGR (compound annual growth rate) for each instalment will differ. So, to simplify the calculation of returns, you can combine the CAGRs of all the SIP investments in the fund into a common CAGR, which is called XIRR.

You can use the XIRR formula in Microsoft Excel to calculate the XIRR in mutual funds. The XIRR formula in excel is = XIRR (value, dates, guess)

Open an Excel sheet and enter all your mutual fund transactions in one column. Mark all outflows like investments and purchases as negative and all inflows like redemptions as positive.

1. In the next column, enter all the corresponding transaction dates.

2. In the last row, enter the current value of your holdings and the current date.

3. Now, use the XIRR function "=XIRR (values, dates, guess)." 

4. In the XIRR formula, guess is an optional parameter. If you do not enter any value, excel will use the 0.1 value.

Let’s understand this with an example.

Monthly SIP Investment Amount = ₹1,000

SIP Investment Tenure = 1/1/2022 to 1/1/2023

Redemption Date = 1/2/2023

Maturity Amount = ₹14,500

Now, assume that we have a set of cash flows as mentioned below:

Here, all transaction dates are entered on the left and all SIP investment amounts are entered on the right with a negative sign as it is an outflow of cash. The redemption amount is mentioned towards the end with its date.

To calculate the XIRR, enter "=XIRR (B1:B14, A1:A14) *100" in the box below the redemption amount and press the enter button. Once done, the XIRR value will be displayed, which is 20.11%.

XIRR is a great way to calculate the returns on your investment with multiple transactions. Using the XIRR formula, you can compute your returns easily when you wish to redeem your mutual funds.