IRR() speed and accuracy issues

[jkndrkn]

We are working on a web-based interface for comparing mortgage offerings. I need to be able to compute the APR given an initial loan value, monthly payments, and the number of loan payments.

We have evaluated a number of options for computing APR and while the finance gem's IRR function does appear to be a contender, it has a number of issues that prevent us from adopting it:

1. Speed. The gem is somewhat slow, often completing in 10+ seconds. It needs to be able to compute results very quickly (in the +/- 200ms range) to maintain a responsive user interface.
2. Accuracy. It is remarkably accurate relative to Google spreadsheet's RATE() function. However, it is possible for some test cases to be wildly inaccurate.

Please consult this spreadsheet of test values: https://docs.google.com/spreadsheet/ccc?key=0AlIlnFeJb27JdHFzc0RQa0t5RDBNajRoZ2lYemdTakE#gid=0

And note this particular bug:

[24] pry(main)> t = Time.now; puts [-236282.5, *[1165.81]*360].irr.to_f * 12; puts Time.now - t
-23.801691983216614
70.137133

[wkranec]

Hi John,

Thanks for the feedback, and for your interest in using the finance gem for your project.

Any implementation of IRR involves numerically solving for the root of a polynomial, and the current implementation achieves this with a Newton's Method solver from ruby's built-in BigDecimal library. This is likely the source of the speed issue, as I believe it is implemented in ruby.

The best alternative I can think of right now would be to rewrite these methods using the gsl gem. I actually think I tried this when first writing the function, but I got the BigDecimal version working first, and didn't really want to add another dependency for the gem.

With regard to accuracy, I'll blame the solver again (although I don't have any evidence to support this). I tested the function on a few examples that I had at hand, but as people have found "edge" cases, my guess has been that the solver is converging to another solution outside the desired range.

So in summary, I guess I'm saying that my bias would be to rewrite #irr and #xirr using the gsl library, and see if that fixes the issues. Within my current time constraints, I can probably try this sometime over the next few weeks. I'll add your examples as test cases and see if we can get accurate results everywhere. My guess is that this will also improve performance, but I don't know if we'll get to the range you are looking for.

Of course, if you really want performance, I'd suggest using numpy. I know it's python rather than ruby, but it's fast, accurate, and really has no analogue in the ruby ecosystem right now.

[directionless]

I ran into some accuracy issues. Maybe it's the fault of the solver, but it's pretty bad.

https://gist.github.com/directionless/6656208

[tubedude]

@directionless , I think your gist is wrong. You are showing the irr at each payment and what I think you want is the below.

flow = []
flow << -26000

20.times do
  flow << 4000
end
20.times do
  puts flow.irr.to_f
end

and the result is:

0.14327648969026274
0.14327648969026274
0.14327648969026274
0.14327648969026274
0.14327648969026274
0.14327648969026274
0.14327648969026274
0.14327648969026274
0.14327648969026274
0.14327648969026274
0.14327648969026274
0.14327648969026274
0.14327648969026274
0.14327648969026274
0.14327648969026274
0.14327648969026274
0.14327648969026274
0.14327648969026274
0.14327648969026274
0.14327648969026274
 => 20

[directionless]

@tubedude It's been most of a year since I wrote that, so the details are a bit fuzzy (sorry). If I remember correctly, it's a silly example to show the IRR bug, it's meant to calculate the IRR after each payment.

If I understand it correctly, I should see an IRR go from bad to better, the more payments I have. 20 payments is better than 19 is better than 10, etc. But calculating it at each step shows weird fluctuations between between steps 7 and 11.

[tubedude]

You are right!. But I was able to match the Excel result by adding a guess of 0.1. The numbers matched up to the 11th decimal. When I rounded by 12, the runs 8 and 10 were a bit off.

flow = []

flow << -26000

20.times do
  flow << 4000
  puts flow.irr(0.1).to_f
end

-0.8461538461538454
-0.5233728905610319
-0.30557541608096167
-0.1694192913321116
-0.08153134104840246
-0.02240080635339067
0.01887782195106145
0.04859805366633852
0.07055058069605369
0.08711375560586361
0.09983492129221049
0.1097530879159395
0.11758533353892073
0.12383850888864659
0.1288784241200623
0.13297398101041266
0.13632607196439528
0.13908695276575578
0.1413735051419361
0.14327648969026321

[jkndrkn]

Hey, thanks for responding. One of our devs wrote a finance gem from scratch that matches Excel very accurately. Check it out here if you are curious: https://github.com/lmp/croovy_finance

[tubedude]

@jkndrkn Thanks! I'll take a look at it.

[gustiando]

@jkndrkn great catch! Currently having the same performance issue with this gem, which is resulting in timeouts in our production app.

Was anyone able to be more successful with another Gem or a fork that could significantly improve the performance when calculating irr? thanks! cc @wkranec