Bug 64085 - IRR function doesn't work properly for certain values
Summary: IRR function doesn't work properly for certain values
Alias: None
Product: POI
Classification: Unclassified
Component: POI Overall (show other bugs)
Version: unspecified
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2020-01-17 19:21 UTC by Bishop Sarkar
Modified: 2021-12-09 11:46 UTC (History)
0 users

irr excel file(sheet 1 reports bad values whereas sheet 2 works fine) (12.48 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-01-17 19:21 UTC, Bishop Sarkar
irr sheet 1 which reports wrong values (35.93 KB, image/png)
2020-01-17 19:22 UTC, Bishop Sarkar

Note You need to log in before you can comment on or make changes to this bug.
Description Bishop Sarkar 2020-01-17 19:21:04 UTC
Created attachment 36969 [details]
irr excel file(sheet 1 reports bad values whereas sheet 2 works fine)

We use the POI IRR to resolve values in a spreadsheet interface in web. Some of them work some of them don't. I have attached an excel file as well. Sheet 2 works fine sheet 1 doesn't report the correct values when I use the poi api. I have attached the screenshots as well.
Comment 1 Bishop Sarkar 2020-01-17 19:22:42 UTC
Created attachment 36970 [details]
irr sheet 1 which reports wrong values
Comment 2 Andreas Beeker 2020-01-20 22:57:11 UTC
I've played around with the data and read through the wiki pages.

I think the multiple in-/outflow values lead to multiple roots and depending on your initial guess, the IRR function or the Newton–Raphson method converges to one of the roots.

I've checked it in Libre Office and google sheets and they come to same conclusion as POI does. Another point: If you change the initial guess, e.g. to -1.3, the value of B6:B7 converges to a similar value as B2:B5.

If you look through MSDN, you'll find a few posts about unpredictable results:




In KB 214105 it's mentioned, that Excel uses Newton–Raphson, but from the links above you see that the implementation is probably tweaked.

So basically, as long as we don't know how Excels internal IRR implementation is supposed to work, we can only blindly guess.

If you want to go that extra mile, you might get a response on the Open Specification forum:
Comment 3 Bishop Sarkar 2020-01-22 19:04:51 UTC
Thanks for the response. This seems more like an excel bug than of POI