XIRR (extended internal rate of return)

Key Takeaways

• XIRR calculates the return on the total investment made with increments and is paid throughout the period under consideration. 

• Dates of SIP investment, SIP amount, Date of redemption, and Redemption amount are required to calculate XIRR

What is 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.

How to use XIRR in mutual funds?

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.

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.

How is XIRR calculated?

XIRR can be easily calculated using few steps on Microsoft Excel,

The formula would go as “= (values, dates, [guess])”

Where ‘values’ represents a series of cash flows, the date represents a series of dates, and ‘guess’ is the random assumption for IRR.

To calculate XIRR, one must have the following values:

• Dates of SIP investment

• SIP amount

• Date of redemption

• Redemption (maturity) amount

Example of XIRR

Step 1: Construct a table of two columns, one with the date of investment/redemptions and another with the actual cash flow.

Notice how the dates are spread apart unevenly with no specific defined intervals.

Step 2: Use the XIRR function in Excel. First excel will ask the user to define the values and then define the dates. The specific columns can be selected, the heading should not be selected. XIRR function on Excel is a calculator made easy with a few defined values.

Step 3: Convert the resultant cell into a percentage to record the XIRR in percentage terms

The returns on an investment fund’s SIPs, made with installments paid at different dates, will be calculated using XIRR because the principal is spread across a period of time and is not paid in a lump sum.

Similarly, XIRR can be applied to get the returns on stock we may keep reinvesting in by buying incremental amounts over time or on any other investment we top up from time to time.

What is the difference between XIRR, IRR, and CAGR?

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 take an example to understand this better.

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.

What is the difference between XIRR and IRR?

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.