XIRR: How to calculate your returns

In one of our earlier articles, we had discussed, ‘What is IRR & how to calculate it?’ As mentioned in the article, IRR or internal rate of return is the interest rate received for an investment—consisting of money invested at the beginning of the investment period and cash flows received at ‘regular’ periods such as half-yearly or annually.

But what happens if you make an investment in the beginning of the investment period and you receive cash flows that are ‘not’ necessarily periodic such as dividends offered on mutual fund units. In this case how would you calculate the return on your investment? Using IRR to calculate the internal rate of return for a series of inconsistent cash flows is not the right method.

Let’s try to understand the concept of XIRR to find out the internal rate of return for a series of cash flows that is not periodic.

Demystifying XIRR

XIRR is a more powerful function in Excel for calculating internal rate of return or annualized yield for a schedule of cash flows occurring at irregular intervals. To compute XIRR on an Excel sheet is easy. Assume we have a set of cash flows like those in the table below:

In the above table, the cash flows are occurring at irregular intervals. Here, you can use XIRR function to compute the IRR for these cash flows. In an Excel sheet, first enter the original amount invested. The amount invested should be represented by a ‘minus’ sign. In each cell enter the cash flows which received each period. Remember to include the ‘minus’ sign whenever you invest money. Now find out XIRR by mentioning =XIRR(values,dates,guess)

Values refer to a series of cash flows that corresponds to a schedule of payments in dates. The first payment refers to the investment made at the beginning of the investment period and must be a negative value. All succeeding payments are discounted based on a 365-day year. The series of values must contain at least one positive and one negative value.

Dates stand for the date when the first investment was made and when the cash flows were received. Each date should correspond to its respective investment made or cash flow received as shown in the above table. Dates should be entered in DD-MM-YY (date-month-year) format. Problems can occur if dates are not entered in the right format. If any number in dates is not a valid date, XIRR returns the #VALUE! error value.

One of the disadvantages of the IRR function is that it assumes a periodic cash flow and most financial instruments these days are generally giving irregular cash flows. This is where the XIRR comes into play. XIRR allows for uneven cash flow intervals by taking into account the dates of which a cash flow occurs.