‘Excel’ing . Understanding the XIRR function in Excel

A few weeks ago , we saw how the IRR (Internal Rate of Return) function in Microsoft Excel can be quite handy in calculating mutual fund SIP returns. As long as the time interval between the cash flows (monthly, annually, etc) is the same, IRR can be used to calculate returns even if the cash flow amounts (say, SIP investments) are different. (https://bit.ly/3bWDSk4)

But what if the frequency of the cash flows (for instance, regular investments) itself is different? Say, you make the year 1 investment in March, the year 2 investment in June, the year 3 investment in February and so on, and realise the sale proceeds in year 6 in September. IRR cannot handle this and will give erroneous results, but XIRR can do the job. The XIRR function can be found using the command fx and under the category ‘Financial’.

XIRR, short for Extended Internal Rate of Return, can help calculate the return when the time interval between cash flows varies. Similar to IRR, the XIRR formula will give results whether the investment amounts are the same or different. But unlike the IRR that gives the periodical return and may have to be annualised, the XIRR automatically calculates the annualised return. In essence, XIRR gives the annualised IRR for a schedule of cash flows that is not necessarily periodic.

Consider the case below (Screenshot 1), where you make an investment on different dates and months over five years, and withdraw the proceeds in the sixth year. The frequency of the cash flows is irregular. Using the XIRR function, we can find the annualised return – 6.4 per cent.

Screenshot 1 Irregular time intervals, different cash outflows

Fields and conditions

The XIRR function has three arguments or fields. The first one, ‘Values’ is a series of cash flows that corresponds to a schedule of payments in dates. ‘Investments’ (payments) must be preceded by a minus sign as they are cash outflows; Maturity Values (receipts) do not need a sign. The next field, ‘Dates’ is the schedule of payment dates that corresponds to the cash flow payments. The last field, ‘Guess’, is a number that you think is close to the result of XIRR; this is an optional field most times and need not be filled always. If ‘Guess’ is left blank, it is assumed by Excel to be 0.1 (10 per cent).

There are some conditions for the XIRR function to give proper results. One, there must be at least one positive value and one negative value to calculate the XIRR – that is, there must be at least one payment (investment value) and one receipt (maturity value). The dates have to be valid and in a correct date format. Also, the values and dates must not contain a different number of values.

Here’s how the XIRR function works. First, list out in an excel sheet the series of dates and cash flows. Match the cash flow values and dates correctly; else, the results will be different.

For instance, in our example, against the date of March 1, 2015, the cash outflow of ₹10,000 is mentioned (with a negative sign) and against September 1, 2020, the cash inflow of ₹80,000 is mentioned.

Next, in the XIRR function, in the ‘Values’ field, select the array of cells that contain the cash flows for which you want to calculate the XIRR; in our example, it is B1:B6. In the ‘Dates’ field, select the array of cells that contain the dates corresponding to the cash flows; in our example, it is A1:A6. You can leave the ‘Guess’ field empty or enter a number that you think is close to the result.

That’s it. The formula result is displayed as 6.4% . This is an annualised return and there is no need for further adjustment.

Here’s another example (Screenshot 2) where we use the same values as above, but change dates to keep the same time intervals between cash flows. The XIRR formula gives the result as 7.1 per cent . Note that the IRR formula can also solve this, since the frequency of the cash flows is the same.

Screenshot 2 Regular time intervals, different cash outflows

In the third example (Screenshot 3), the time interval between the cash flows is irregular but the cash outflows (investments) are the same. The XIRR function gives the result as 14.4%. The IRR function would give an erroneous result in this case, since the frequency of the cash flows is different.

Screenshot 3 Irregular time intervals, same cash outflows

Given its versatile use, the XIRR function can also be employed to calculate returns on SIP investments and the yield-to-maturity (YTM) on bond investments.

The XIRR function may not give proper results in cases where the cash flows are for a period less than a year. That’s because the XIRR calculates the annualised return.