What is XIRR in Mutual Funds: Meaning, Example, Formula, How to Calculate XIRR

You may invest in mutual funds for regular income, capital appreciation or both depending on the mutual fund type. You could measure the return from mutual funds using CAGR (Compound Annual Growth Rate) and XIRR (Extended Internal Rate of Return).

You will find CAGR showing you the rate at which your mutual fund investment grows per year over the investment period. However, you cannot use CAGR to calculate the return for multiple cash flows.

You may consider using XIRR to calculate mutual fund returns for multiple cash flows. For example, you may use XIRR to determine the return from mutual fund investments through the systematic investment plan (SIP).

What is XIRR?

You can put a lump-sum amount in a mutual fund. You have a one-time redemption where you sell the entire investment after some time. Investing through a lump sum involves two cash flows. You have a cash outflow which is your investment, and a cash inflow (redemption). 

You may consider using CAGR to calculate the return if you invest a lump-sum amount in a mutual fund scheme. However, you may invest in mutual fund schemes through the SIP or redeem units through the systematic withdrawal plan (SWP).

It involves multiple cash flows, and you cannot use CAGR to determine returns from the mutual fund. You can use XIRR to determine returns from mutual funds through SIP (multiple-cash flows through SIP, Top-ups) and SWP (multiple-cash flows through SWP, dividends, partial redemption and so on).

How to calculate XIRR in Excel?

You can use the XIRR formula in excel sheets to calculate return from mutual funds for multiple cash flows. You must enter all mutual fund transactions such as SIP, SWP, additional purchases or redemptions, and the corresponding dates.

The XIRR formula is the modification of IRR (Internal Rate of Return) and factors irregular periods. You will have to enter SIP transactions, and the corresponding dates from mutual fund statements in the excel sheet. You then apply the XIRR formula to calculate SIP returns.

For example, you invest Rs 3,000 per month in a mutual fund scheme through SIP. You may calculate mutual fund returns through SIP using XIRR Formula in excel, as shown below.

• You must enter all amounts you have invested with a ‘negative’ sign. It includes your SIP instalments and additional repurchases. 
• You must enter all redemption amounts with a ‘positive sign.

Procedure to calculate XIRR using excel

• You must open an excel sheet and enter SIP transaction dates in Column A.
• You then enter the SIP instalment as a negative figure in Column B.
• Enter the redemption amount against the redemption date in Column B.
• You have XIRR (values, dates, [guess]). Use the formula =XIRR (B5:B15, A5:A17) * 100 and hit the enter button.

XIRR vs CAGR: What is the difference between XIRR and CAGR?