What is XIRR in Mutual Funds | Meaning, Importance, How to calculate

Updated on:  

08 min read

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 using 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.

What is the difference between XIRR and CAGR?


Particulars

CAGR

XIRR

Description

It shows the annual growth of mutual fund investments over some time





It shows the average rate earned by every cash flow that you invest during a period





Multiple Cash Flows

Not Considered

Considered

Return





Measures Absolute Return

Only Annualised Return

Use





Useful for measuring the return from lumpsum investment in mutual funds





Measures mutual fund returns through SIP and SWP


inline CTA
Invest in Direct Mutual Funds
Save taxes upto Rs 46,800, 0% commission