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.
To compute XIRR on an Excel sheet is easy. Assume we have a set of cash flows like the ones in the table below:
|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|
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.