Bug 64137

Summary:	IRR NaN When it should give back a value
Product:	POI	Reporter:	Fawaz Alhenaki <fawaz.alhenaki>
Component:	SS Common	Assignee:	POI Developers List <dev>
Status:	RESOLVED FIXED
Severity:	normal
Priority:	P2
Version:	unspecified
Target Milestone:	---
Hardware:	All
OS:	All
Attachments:	Reproduction file

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