Bug 100767 - MIRR function not compliant with ODFF1.2
Summary: MIRR function not compliant with ODFF1.2
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Winfried Donkers
Whiteboard: target:5.3.0
Depends on:
Reported: 2016-07-05 04:14 UTC by raal
Modified: 2016-10-15 13:03 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:

test case (124.33 KB, application/vnd.oasis.opendocument.spreadsheet-flat-xml)
2016-07-05 04:14 UTC, raal
test document for fixes. (12.28 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-07-08 10:10 UTC, Winfried Donkers

Note You need to log in before you can comment on or make changes to this bug.
Description raal 2016-07-05 04:14:35 UTC
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.
Comment 1 Winfried Donkers 2016-07-08 07:59:24 UTC
Also, MIRR should support use of arrays, e.g. =MIRR({-10;23;35;45};0.065;0.1).
Comment 2 Winfried Donkers 2016-07-08 10:10:48 UTC
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.
Comment 3 Eike Rathke 2016-07-12 10:54:49 UTC
For completeness, Gnumeric also returns an error in A14.
Comment 4 Commit Notification 2016-07-12 10:57:17 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "master":


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:

Affected users are encouraged to test the fix and report feedback.