https://bugs.documentfoundation.org/show_bug.cgi?id=40460
--- Comment #10 from Winfried Donkers <[email protected]> ---
With the attached document IRR returns an error.
However, when you change the call to IRR to include an estimate and you give an
estimate of 0.02 or less (including negative values), you get a result
(0,3775673823%
, probably the same as Excel/Gnumeric).
When you look at the OpenFormula Standard, you will find that depending on the
implementation of the IRR function a error as result is acceptable.
That needs some explanation:
IRR uses iterative algorithms to get a result, i.e. it starts with an estimate
(either given in the call to IRR, or a default first estimate as written in the
source code) and calculates backwards with that estimate to see how far wrong
the estimate is. Depending on the error found, the algorithm adjusts its
estimate and recalculates the error. This process repeats itself until the
error is so small that is is acceptable. But -and this is what happens here- it
is possible that for some combinations of data and estimate the error does not
decrease and that the recalculations continue indefinitely -or until the
algorithm has a maximum number of calculations within which an acceptable
result must be found. If that maximum is exceeded, an error is returned.
The fact that Gnumeric and Excel do return a value and not an error with the
attached document means that they either use a different algorithm (there are
many possible algorithms for this type of calculation) and/or use a different
starting estimate.
It is impossible to guarantee that a valid result will be found with IRR, but
possibly it could try again with another starting estimate if the first
estimate does not lead to a result. Even then there will be cases where an
error is returned by IRR.
I will do some tests and either take this bug to myself to improve its
behaviour, or set the status to 'RESOLVED/WONTFIX', as that is the actual
situation.
--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Libreoffice-bugs mailing list
[email protected]
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
