Created attachment 126068 [details] test case =MIRR(G1:G4;51;0,1) returns error- correct, G1:G4 are only positive values =MIRR(F1:F4;51;0,1) returns -1 - incorrect (excel2010 return -1 too), F1:F4 are only negative values Info from Eike: Looks like a bug, but it may just be due to the algorithm used. Actually for only negative values there's no reason for #DIV/0 error, but maybe another error should be generated. ODF definition: 6.12.27 MIRR Summary: Returns the modified internal rate of return (IRR) of a series of periodic investments Syntax: MIRR( Array Values ; Number Investment ; Number ReinvestRate ) Returns: Percentage Constraints: Values shall contain at least one positive value and at least one negative value.
Also, MIRR should support use of arrays, e.g. =MIRR({-10;23;35;45};0.065;0.1).
Created attachment 126125 [details] test document for fixes. Document verified with Excel 2016. Formula in cell A14 predictably returns a different result in Excel and Calc. That is an Excel bug.
For completeness, Gnumeric also returns an error in A14.
Winfried Donkers committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=3d4a68aa04d13de374641792d3d4981783045dbd tdf#100767 make MIRR compliant with ODFF1.2 It will be available in 5.3.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.