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.
Created attachment 36970 [details] irr sheet 1 which reports wrong values
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
Thanks for the response. This seems more like an excel bug than of POI