Bug 64137 - IRR NaN When it should give back a value
Summary: IRR NaN When it should give back a value
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: unspecified
Hardware: All All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2020-02-11 21:30 UTC by Fawaz Alhenaki
Modified: 2021-10-10 13:46 UTC (History)
0 users

Reproduction file (983 bytes, text/plain)
2020-02-11 21:30 UTC, Fawaz Alhenaki

Note You need to log in before you can comment on or make changes to this bug.
Description Fawaz Alhenaki 2020-02-11 21:30:15 UTC
Created attachment 37012 [details]
Reproduction file

I have created a sample of when the issue occurs. IRR in apache documentation says that covers "20 tries" as shown here https://poi.apache.org/apidocs/dev/org/apache/poi/ss/formula/functions/Irr.html 
which is identical to Microsoft Excel https://support.office.com/en-us/article/irr-function-64925eaa-9988-495b-b290-3ad0c163c1bc
This indicate that the result is the same between the two; however, in the case of apache poi, it seems that the result is returned as NaN (while excel returns the actual result).
Comment 1 PJ Fanning 2021-10-10 13:22:29 UTC
I had to increase the iteration count and then POI calculates -0.009463562705856032 for the attached test case
Comment 2 PJ Fanning 2021-10-10 13:27:57 UTC
r1894110 - it seems like the way that Excel calculates its iteration count is very different from how the POI code does because the attached use case requires a much higher iteration count than 20 (POI iteration count)
Comment 3 PJ Fanning 2021-10-10 13:46:32 UTC
https://bz.apache.org/bugzilla/show_bug.cgi?id=64085 is also open and has a discussion about how Excel's IRR has some unusual implementation