Issue 27597

Created attachment 14409 [details]
Demonstrates Problem with XIRR Function in 1.1.1

XIRR returns an error if there is a date present in the bottom row of the date 
range (see cell D68 in attached file). XIRR returns the wrong value when there 
is NOT a date present (see cell A68 in attached file). The value in cell B70 of 
the attachment should be +2.3% as checked against a competing spreadsheet 
program.

In addition, XIRR should be calculated on the basis of 356.25 days in a year 
because there are 1461 days in a four-year period due to leap year, not 1460.

Hi Eike,

as discussed this one is for you.

Frank

This may be related to issue 27002, and also falls under the category of
functions to be investigated => adding blocking dependency to issue 18704.
Adding needhelp keyword for time reasons.

No, this is unrelated.
- XIRR is calculated based on 365 days a year, by convention. This is compatible
with other spreadsheets and won't be changed.
- The empty cell is evaluated as value 0 (date 1899-12-30). -0.25% actually is a
valid solution then (in 1980-05-13 values, the sum of the other values is then
218397.48, as is the value of 267741.73 on 1899-12-30). Sure, these unusual
cases could be rejected, but it's not a "wrong value".
- The error code in E70 is caused by the default "guess" value of 10%, which
breaks the iteration. If you specify anything near the result (like 1%), the
formula works. This is the only real problem here. Eike, there is also internal
#86392# for that.

A major commercial spreadsheet application was able to return a usable value for
XIRR from this same data with the default guess of 10%. Perhaps OOo needs more
iterations or a looser error value to get a data set such as this to converge?
From the user's point of view it can be difficult to estimate a suitable "guess"
value when all they see is the Err:502 message and conclude that the function
isn't working properly.

taking over