One function in Microsoft Excel that calculates the IRR is the IRR function. One of the disadvantages of the IRR function is that it assumes an annual cashflow and most financial model these days are generally monthly or quarterly. This is where the XIRR comes into play. XIRR allows for uneven cashflow intervals by taking into account the dates of which a cashflow occurs.
However, a recent discussion online raised the issue that XIRR is calculated based on the assumption that there are 365 days a year (i.e. (d1 – d2) / 365), a reasonable assumption in most cases. However, it causes issues when there is a leap year or when assessing the IRR of financial instruments that are calculated using different measurements of durations, such as the 30/360 day count or actual/360 day count.
How then should IRR be calculated?
For a truly dynamic and robust method of calculating IRR, one has to understand how IRRs are being calculated in the first place.
The IRR (and XIRR) function in Excel uses what is called as an iterative technique to arrive at an answer. An iterative technique works as follows:
- Start with a guesstimate
- Based on that guesstimate, work out the next, more accurate estimate
- Rinse and repeat step 2 until the resulting estimate is reasonably accurate
This method is the commonly used method among IRR calculations methodologies, and that includes the IRR and XIRR function.
The iterative process mentioned above is the general outline of how the process works. One of the biggest issues with the iterative process is how quickly does a particular iterative process takes to arrive at an accurate answer. Mathematicians have come up with a multitude of iterative techniques to quickly arrive at an answer. Some examples are, but not limited to, the bisection method, the secant method, Newton’s method (a.k.a. Newton-Raphson method), Inverse Quadratic method and Brent’s method. The method that we will be focusing on to calculate IRR in this article is the Newton-Raphson method. Other methods are shown in the accompanying excel spreadsheet.
For the purposes of this article, we will attempt to achieve the same result as per the IRR or XIRR function in Excel. This would mean achieving an estimate within 0.00001% accuracy.
The Newton-Raphson method in detail (technical & boring)
Methodology
The Newton-Raphson method requires a mathematical function f(x) to be solved such that f(x) = 0 and with an initial estimate of e0. A new estimate can then be found using the algorithm:
e1 = e0 – f(e0) / f’(e0) where f’(x) is the first order differentiation of f(x) with respect to x.
In simpler terms, f’(e0) is the slope of the graph at e0. A more accurate answer can then be found by reiterating the algorithm above to get e2, e3, e4… until desired accuracy.
To translate the mathematical jargon above into something related to the calculation of IRR, we need to understand that IRR is a discount rate such that the NPV is 0. In other words; NPV( IRR ) = 0 (look familiar?)
Replacing f(x) with NPV(r) where r is the discount rate, we can see that the IRR can be calculated by solving NPV(r) such that NPV(r) = 0. I.e., the iterative algorithm to find the IRR will then be: r1 = r0 – NPV(r0) / NPV’(r0)
In the interest of transparency, the accompanying model will use an estimate of the slope by using NPV calculation from first principles, rather than using the first derivative of NPV with respect to the discount rate. An estimate of the slope can be found by taking the slope of two sufficiently close points on the NPV curve. In the example, we used +0.025% as the second point.
In English please
In simpler terms, you get the curve of the NPV vs. discount rate, pick an estimate discount rate, and draw the tangent line. The new estimate will then be where the tangent line crosses the horizontal axis. (Refer chart below)
Newton Iteration
Image source: Wikipedia, by Ralf Pfeifer
Results
The example model contains the calculations using the Newton-Raphson Method, the bisection method, and the secant method. Details of the other two methods can be found on Wikipedia (a great source for information, regardless of what one thinks of its reliability). Using the initial estimates of 10%, for the Newton-Raphson method, 10% and 50% for the Bisection method, and 9.5% and 10.5% for the Secant method, the results are as follows.
The table above shows the number of iterations each method requires to achieving 0.00001% accuracy. Even though the results from the table above are not directly comparable, the Newton-Raphson method is still a preferred method since it is relatively easy to implement, quick to achieve desired results (in the case of IRR), and only requires one initial estimate.
Conclusion
Couple this method with NPV calculations from first principles; one can easily manipulate any assumptions made in the IRR (or XIRR) function, e.g. the duration between dates.
Find out more
Bing is a firm supporter of Wikipedia. A lot of these information can be found on Wikipedia.
