Bug 64085 - IRR function doesn't work properly for certain values
Summary: IRR function doesn't work properly for certain values
Status: NEW
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
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2020-01-17 19:21 UTC by Bishop Sarkar
Modified: 2020-01-22 19:04 UTC (History)
0 users



Attachments
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
Details
irr sheet 1 which reports wrong values (35.93 KB, image/png)
2020-01-17 19:22 UTC, Bishop Sarkar
Details

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:

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other-mso_2007/irr-responds-unpredictably-to-the-guess-in-a-two/94aa0911-03e7-4dde-9577-2834e571ef75

https://answers.microsoft.com/en-us/msoffice/forum/all/irr-calculation/6b01c74e-7f22-49be-ae7b-a4423afd5553

https://social.msdn.microsoft.com/Forums/en-US/393f390d-2f5b-49be-bb9d-db44f8309b31/excel-irr-xirr-source-code-or-library?forum=exceldev

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:
https://social.msdn.microsoft.com/Forums/en-US/93cbb7c6-dba1-44c7-8a71-304037adb92e/open-specifications-general-faq?forum=os_binaryfile
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