Extended Internal Rate of Return (XIRR)

The extended internal rate of return or the more commonly used, XIRR, is the rate which calculates the returns on the total investment made with increments, paid throughout the period under consideration.

XIRR is an annualized form of return. Annualized return indicates what investment would return over a time period if the annual return is compounded.

XIRR comes to an investor’s aid when investments are made into mutual funds at randomly spaced intervals. Moreover, redemptions are also processed at irregular time intervals. The time periods will differ for each cash flow. Each particular investment will offer a different rate of return at a given date of measurement.

XIRR= Weighted average CAGR of all instalments

IV= Investment Value (IV)

FV= Final Value (FV)

N= Investment intervals (n)

We understand it can be complicated to manually calculate XIRR for any investment. What’s more important is understanding its significance and need, especially when you are investing in mutual funds via SIP.

Therefore, XIRR can be used to calculate an investor’s mutual fund returns when investments and redemptions are spread over a period of time.

For example, Mr. A decided to invest Rs 50,000 in a thematic fund with the pharma sector as the theme on the news of the coronavirus pandemic. Mr. A redeems the invested amount on the news of economic recovery to look for better returns. Mr. A invests that Rs 50,000 in the consumer durables sector on the news of vaccine efficacy. On the release of auto sales data, Mr. A invested another Rs 50,000 in the auto sector with a short-term view to reap benefits of festive sales rally. Now, the investment and redemption time periods for each investment will be unevenly spaced. XIRR can be used to calculate the overall return on the invested corpus.

An investor may choose different options such as SIP, SWP, and lumpsum for different mutual funds. XIRR can be applied to such investment strategies.

XIRR, IRR, and CAGR can be quite confusing at times. CAGR and XIRR are both used to calculate returns on investment. XIRR can be referred to as an aggregate of multiple CAGRs. When there are multiple investments made in a fund, XIRR can be used to calculate the overall return.

There is a fine line of difference between CAGR and XIRR. CAGR is used to assess the performance of a mutual fund on a standalone basis. XIRR is computed to assess the performance of your investment in the mutual fund.

Let’s assume that one invests Rs 2000 per month for a year in a fund, which increases to Rs 48,000 in 4 years. How to calculate the overall return on this investment made over 12 months? If using CAGR, you will have to measure the CAGR for 48 months on the first installment, for 47 months on the second installment, for 46 months on the third installment, and so on. Instead of doing that, one can use the XIRR function of excel that takes into account all these CAGRs to give the overall CAGR.

XIRR and IRR functions of excel differ on the basis that the IRR function assumes that each period between a series of cash flows is of the same length. Rate of return on monthly, quarterly, or annual cash flows is generally measured using IRR. XIRR on the other hand offers to assign dates to the cash flows and doesn’t require each cash flow to be made after the same interval.

To summarise, if cash flows are made at regular intervals, IRR is preferred and if cash flows are not made at regular intervals then XIRR is preferred to measure the overall return.