top of page

Plot no. 129/1 Industrial Area Phase 1, Chandigarh


  • Kritika Chauhan

XIRR: A versatile indicator of your financial returns

What is XIRR in mutual funds?

While investing in equity mutual funds through Systematic investment plans (SIP), it becomes imperative for us to track and measure the returns over our investment duration. Normally, a good measure for quantifying returns is the compounded annual growth rate (C.A.G.R) which is calculated by the formula: (Final investment/Initial Investment) ^ (1/n)-1 where n denotes the investment duration in years. But in the case of equity mutual funds, the calculation becomes a tad complicated. There are multiple cash inflows and outflows in SIPs which warrants the use of an extended internal rate of return (XIRR) for the calculation of investment returns. XIRR is the best metric for ascertaining whether your financial goals have been met or not. Annual average returns of every SIP are taken into consideration and adjusted accordingly to compute the average annual return rate for all investments.

Significance of XIRR in the long run:

XIRR can be used for every SIP instalment or liquidation and would give an overall current value of the investment. It is analogous to a single rate of return which is obtained after adjusting the average return on each instalment. Here lies the beauty of XIRR. You can use it to compute the aggregate return whenever you are investing or liquidating units in mutual funds.

An example would further make things simpler. Suppose, you have invested Rs.5000 in SIP for 12 years and your total investment yields Rs.10 lakhs at the end of the tenure. The first month’s investment return will be different from the 15th month or the 37th month. Therefore, calculating C.A.G.R. for every SIP instalment will become counterintuitive. Then again, you would increase your SIP in some months or liquidate certain units which add the next layer of complexity in the calculation of financial returns. XIRR aggregates the C.A.G.Rs of each instalment and adjusts it to a common C.A.G.R. XIRR gives an accurate picture of the financial returns and whether your strategy should encapsulate units of diversified equity mutual funds that will ensure you meet your targets.

Replicating the magic of XIRR in Excel:
  • The SIP transaction dates are to be mentioned in one column. Dates must be accurately entered with the necessary formatting.

  • The SIP amounts with suitable notation for cash inflow and outflow are to be mentioned in the adjacent column. The value of cash flows must mandatorily have one negative and positive value.

  • The last row of the individual headers needs to be filled appropriately with value fields in place.

  • Apply the XIRR formula with proper syntax in place. Follow the syntax =XIRR (values, dates, [guess]).

  • Guess is a field which you may choose to enter and is assigned a default of 0.1 if nothing is entered. The rate of return is arrived at by an extensive iterative process. XIRR runs through the calculation until the result is accurate within 0.000001 per cent thereby denoting an extremely low margin of error.

Some other interesting facts about XIRR:

We have already looked into the fact that for multiple cash flows, XIRR becomes the single most important metric to quantify financial returns. It considers financial spread, and has no limitations such as the SIP should start on the same date of every month which ideally is not the case as due to holidays, it may not follow the same date every month and also the fact that months may have different days. The ratio inherently takes into account many such limitations which is not possible in the case of metrics such as C.A.G.R and normal IRR which assumes a particular rate to discount future cash flows to present value.

XIRR can be used for both debt and equity mutual funds along with Systematic Withdrawal plans and dividends.

A good XIRR: A good XIRR is one particular metric every investor should research when they are picking particular mutual funds they want to invest in. It gives an underlying idea about the portfolio allocation and quality of stocks that comprise an equity mutual fund. The XIRR over the last 5 years gives an overall idea about the long-term performance of the fund while a more short-term approach would be looking at immediate 1-2 yrs. Volatility in annual XIRR by considering different scenarios is ideally what should be looked into by investors looking to maximize their financial return over a portfolio of equity mutual funds. A typical debt mutual fund has a lower XIRR than an equity mutual fund. A good XIRR for an equity mutual fund may be in the range of 11-14% while for debt mutual funds would be around 7-9%. An investor whose investment is spread out over multiple mutual funds should always have an eye for details. A typical trend of XIRR over the years would help us to understand wealth creators and accordingly shift more units toward such high performers.

1 view0 comments


bottom of page