This little excursion in numerical analysis was prompted by the rather flaky IRR Excel function available in Microsofts' Excel API. We were building a bond quotation engine and part of the actuarial calculations required the determination of the internal rate of return for a given list of cashflows.
I am an ardent advocate of software reuse, but when faced with APIs that are not extensible, the options are: Google for an alternative implementation; build a custom component or defenestration.
I explored the first option, but the solutions available did not integrate with the clients' target platform. A webservices facade was not an option either - think technical debt and the "my Java is better than your Java" type arguments. I am saving the more drastic option for an apt occasion.
The IRR function is essentially a polynomial function in r whose solution is any value of r that makes the function equal to zero. A more detailed description is available
here.
Formally IRR can be expressed as:
An expanded form of this is expressed as:
There are several approaches to solving polynomials for zero roots and in the majority of cases, the limiting factors tend to be speed of convergence, accuracy of approach and the number of compute cycles incurred.
The business needs to get the answer today and it must be reliably and accurately produced on the hardware available.
The Newton-Raphson method of root finding is used. Other approaches e.g. Bisection where prototyped, but proved less robust in UAT.
The roots of a polynomial function can be approximated by the following equation:
The initial guess is derived by linearising the IRR function. Linearisation simply means getting rid of the higher order terms. The linearised version of the function thus becomes:
The source code for the IRR calculator implementation is
here.
