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 NewtonRaphson 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.
Subscribe to:
Post Comments (Atom)
Purging Broadcom Drivers for Broadcom Wifi Card on Ubuntu sudo aptget purge bcmwlkernelsource Reference: https://askubuntu.com/qu...

This little excursion in numerical analysis was prompted by the rather flaky IRR Excel function available in Microsofts' Excel API. We w...

This solution resolved my SQLite connection string configuration. The code makes use of the SQLiteConnectionStringBuilder class. The databas...

After starting out on the XBAP route and quickly hitting the sandbox limit it was time to actually move to an application paradigm that wou...
5 comments:
Thank you for posting this. This was really interesting. I was looking all over the place for code that computes the IRR. Unfortunately, Visual Studio's own function can only compute for 20 tries.
Is it okay if you give me a copy of the code? I'd like to try this out.
Thanks for an interesting post. But as far as I can tell, there seems to be a mistake in the code, in the calculations of the derivative.
sumOfDerivative += _cashFlows[i]*(i)/Math.Pow((1 + estimatedReturnRate), i);
should read
sumOfDerivative += _cashFlows[i]*(i)/Math.Pow((1 + estimatedReturnRate), i+1);
(the divisor is power i+1), since (if I remember derivation correctly :) ), the derivative is
f'(r) = sum__{i=1}^{n} iC_i/(r+1)^(i+1).
The practical difference is extremely small, but nice to have correct anyway.
Thanks so much for sharing this.
Thanks for your post. I found an error with the following series of cash flows:
350105,
0,
0,
576.0649156,
4070.543812,
4070.543812,
4070.543812,
4070.543812,
4070.543812,
4070.543812,
4070.543812,
4970.832112,
5991.884743,
5991.884743,
5991.884743,
5991.884743,
5991.884743,
5991.884743,
5991.884743,
5991.884743,
5991.884743,
5991.884743,
5991.884743,
6718.467639,
7750.046587,
7750.046587,
7750.046587,
7750.046587,
7750.046587,
7750.046587,
7750.046587,
7750.046587,
7750.046587,
7750.046587,
7750.046587,
9818.998968,
10892.68318,
10892.68318,
10892.68318,
10892.68318,
10892.68318,
10892.68318,
10892.68318,
10892.68318,
10892.68318,
10892.68318,
10892.68318,
11739.97321,
0,
0,
1000000
Due to the second cash flow being 0, the InitialGuess property is 1 which then fails the IsValidIterationBounds test.
Once I initially commented I clicked the Notify me when new comments are added checkbox and now each time a comment is added I get 4 emails with the same comment. Is there any way you can remove me from that service? Thanks! real money casino
Post a Comment