[Bug 64085] New: IRR function doesn't work properly for certain values

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

[Bug 64085] New: IRR function doesn't work properly for certain values

Bugzilla from bugzilla@apache.org
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: [hidden email]
          Reporter: [hidden email]
  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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 64085] IRR function doesn't work properly for certain values

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=64085

--- Comment #1 from Bishop Sarkar <[hidden email]> ---
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 64085] IRR function doesn't work properly for certain values

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=64085

--- Comment #2 from Andreas Beeker <[hidden email]> ---
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 64085] IRR function doesn't work properly for certain values

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=64085

--- Comment #3 from Bishop Sarkar <[hidden email]> ---
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: [hidden email]
For additional commands, e-mail: [hidden email]