XIRR: How to calculate your returns

XIRR is a more powerful function in Excel for calculating annualized yield for a schedule of cash flows occurring at irregular periods

Jun 07, 2018 06:06 IST India Infoline News Service

In a previous article, we had discussed ‘IRR’ and how to calculate it. As mentioned in the article, IRR, or internal rate of return, helps estimate the amount earned as interest on an investment. This consists money invested at the beginning of an investment period and subsequent interest inflows received at ‘regular’ periods such as half-yearly or annually.

But what happens if you make an investment but the interest inflows are ‘not’ necessarily periodic, such as dividends on mutual fund units. One of the disadvantages of the IRR function is that it assumes periodic returns, whereas most financial instruments these days generally give out interests at irregular intervals.

How would you calculate the return on such investments? For starters, using IRR to calculate profitability here would not be the right method. This is where XIRR, a powerful Excel function that is used to calculate the annualized yield for a range of irregular cash flows, comes in. XIRR allows for uneven cash flow intervals by taking into account the dates on which a cash flow occurs.
Let us understand the concept of XIRR to find out the IRR for a series of sporadic cash inflows.

Demystifying XIRR 

To compute XIRR on an Excel sheet is easy. Assume we have a set of cash flows like the ones in the table below:

How to calculate XIRR?
Date Cash flows (in Rs)
01 January, 2016 -50,000
10 January, 2016 500
01 June, 2016 500
25 October, 2016 500
27 October, 2016 500
01 March, 2017 500
15 June, 2017 51,000
XIRR 4.89%

In the above table, the interest inflows are irregular. Hence, you can use the XIRR function to compute the IRR on these cash flows.
 
In an Excel sheet, first enter the original amount invested. The amount invested should be represented by a ‘minus’ sign. In the following cells, enter the returns received during each period. “Remember to include the ‘minus’ sign whenever you invest money.”
 
Now, find XIRR using the function: “=XIRR(values,dates,guess)”

Values refer to a series of cash flows that correspond 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.
 
Date stands for the day the first investment was made and when the returns were received. Each date should correspond to its respective investment made or income received as shown in the above table. Dates should be entered in a ‘DD-MM-YY (date-month-year)’ format as errors can occur if the format is not followed. If any number in the dates is invalid, or the format of dates is inconsistent, XIRR will reflect the “#VALUE!” error.

Related Story