Calculating Internal Rates of Return
Dec
15
Written by:
Charles Flock
12/15/2008 10:19 AM
Insight into the XIRR and XNPV functions as implemented in Microsoft Excel and in WestClinTech's XLeratorDB/financial.
We have posted a new version of XLeratorDB/financial which you can now download. The new version includes changes to XIRR and XIRR_q which improves the calculations where cumulative cash flows are less than zero. We have also made changes to make XIRR, XIRR_q, XNPV and XNPV_q to eliminate dependencies on the order of data. Many thanks to Munish Kumar for his suggestions and input on this.
How XIRR works
XIRR is an iterative process that attempts to find values for ‘rate’ which will return zero or a number sufficiently close to zero in the XNPV calculation. We had originally implemented this using a technique known as the secant method.
It turns out that while the secant method was sufficiently robust and efficient for data sets which had positive cumulative cash flows, it was not quite so good where the data sets contained negative cumulative cash flows. Thus, we changed methodologies and implemented a method which combines 2 other iterative techniques, the Newton –Raphson method and the bisection method. We then ran the new XIRR and XIRR_q across our test system and duplicated the results obtained from EXCEL. Our test system has approximately 100,000 randomly generated test cases with over 2.7 million cash flows. For each one of these test cases we separately calculated the XIRR value using worksheetfunction.XIRR and stored it on the data base.
We also changed XIRR and XIRR_q to not be dependent on the order of data. In the previous version, internal rate of return was calculated from the first date in the resultant table, so that if the results were not properly ordered the calculation would not be what you would otherwise expect. It was easy enough to order the results by using an ORDER BY, but we decided that it made life easier to not make that a requirement.
We were also able to verify our results by using the results of the XIRR and XIRR_q functions, calling XNPV with the same cash flows and verifying that the result was near zero (within 8 decimal places).
What we found in EXCEL
The first thing that we found was that order mattered in EXCEL, too. EXCEL will return different results depending on the order of the dates. For predictable and consistent results in EXCEL, you need to make sure that your cash flows are in date order.
The second thing requires a bit more explanation. In creating test data, we discovered that when the cumulative cash flows are around zero and there are changes in sign, we would occasionally return a negative IRR and EXCEL would return a positive IRR. This turned out to be very tricky to confirm as the XNPV function will not accept negative rates (which is odd, since XIRR will calculate them). The wct.XNPV function confirmed that the negative rate would return an XNPV of zero. I also manually calculated it and included it in this spreadsheet which you are free to download. The spreadsheet also contains examples of the first problem.
Nobody should really be surprised by this situation. Basically XIRR is trying to find the solution to a polynomial which, by definition, has more than one solution. Normally I would think of the square root of 64 as being 8, but it’s also -8. The cubed root is 4, but the fourth root is 2 and -2. In the case of XIRR, solutions which are less than or equal to minus 1 can be discarded, but there are clearly situations where a positive and negative XIRR will compute an XNPV of zero. Which of these results are ‘correct’? It’s not for me to say.