You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2020/01/17 19:21:04 UTC
[Bug 64085] New: IRR function doesn't work properly for certain
values
https://bz.apache.org/bugzilla/show_bug.cgi?id=64085
Bug ID: 64085
Summary: IRR function doesn't work properly for certain values
Product: POI
Version: unspecified
Hardware: PC
OS: All
Status: NEW
Severity: normal
Priority: P2
Component: POI Overall
Assignee: dev@poi.apache.org
Reporter: Bishop.Sarkar@ibm.com
Target Milestone: ---
Created attachment 36969
--> https://bz.apache.org/bugzilla/attachment.cgi?id=36969&action=edit
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.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 64085] IRR function doesn't work properly for certain values
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=64085
jamessmithjs <ja...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |NEEDINFO
--- Comment #4 from jamessmithjs <ja...@gmail.com> ---
https://maps.google.ch/url?sa=t&url=https%3A%2F%2Fwww.remindzapp.com
https://images.google.be/url?sa=t&url=https%3A%2F%2Fwww.remindzapp.com
https://maps.google.be/url?sa=t&url=https%3A%2F%2Fwww.remindzapp.com
https://maps.google.cz/url?sa=t&url=https%3A%2F%2Fwww.remindzapp.com
https://www.google.co.th/url?sa=t&url=https%3A%2F%2Fwww.remindzapp.com
https://images.google.com.ua/url?sa=t&url=https%3A%2F%2Fwww.remindzapp.com
https://images.google.com.tr/url?sa=t&url=https%3A%2F%2Fwww.remindzapp.com
https://www.google.com.mx/url?sa=t&url=https%3A%2F%2Fwww.remindzapp.com
https://www.google.dk/url?sa=t&url=https%3A%2F%2Fwww.remindzapp.com
https://images.google.hu/url?sa=t&url=https%3A%2F%2Fwww.remindzapp.com
https://www.google.fi/url?sa=t&url=https%3A%2F%2Fwww.remindzapp.com
https://images.google.co.nz/url?sa=t&url=https%3A%2F%2Fwww.remindzapp.com
https://www.google.co.nz/url?sa=t&url=https%3A%2F%2Fwww.remindzapp.com
https://maps.google.co.nz/url?sa=t&url=https%3A%2F%2Fwww.remindzapp.com
https://www.google.com.vn/url?sa=t&url=https%3A%2F%2Fwww.remindzapp.com
https://www.google.pt/url?sa=t&url=https%3A%2F%2Fwww.remindzapp.com
https://images.google.ro/url?sa=t&url=https%3A%2F%2Fwww.remindzapp.com
https://www.google.com.my/url?sa=t&url=https%3A%2F%2Fwww.remindzapp.com
https://www.google.co.za/url?sa=t&url=https%3A%2F%2Fwww.remindzapp.com
https://www.google.com.sg/url?sa=t&url=https%3A%2F%2Fwww.remindzapp.com
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 64085] IRR function doesn't work properly for certain values
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=64085
--- Comment #2 from Andreas Beeker <ki...@apache.org> ---
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
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 64085] IRR function doesn't work properly for certain values
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=64085
PJ Fanning <fa...@yahoo.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Resolution|--- |FIXED
Status|NEEDINFO |RESOLVED
--- Comment #4 from PJ Fanning <fa...@yahoo.com> ---
https://github.com/apache/poi/pull/1216 added - reopen if that doesn't help
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 64085] IRR function doesn't work properly for certain values
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=64085
--- Comment #1 from Bishop Sarkar <Bi...@ibm.com> ---
Created attachment 36970
--> https://bz.apache.org/bugzilla/attachment.cgi?id=36970&action=edit
irr sheet 1 which reports wrong values
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 64085] IRR function doesn't work properly for certain values
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=64085
--- Comment #3 from Bishop Sarkar <Bi...@ibm.com> ---
Thanks for the response. This seems more like an excel bug than of POI
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org