XIRR Sort Order Weirdness and a fix?

[wsanders1]

For years I have been using a large Calc sheet (about 3000 rows) that has seemingly been able to calculate a correct two-column range XIRR even though the rows of the range are not sorted in chronological order. I just changed the range of the XIRR parameters, and the result was no longer correct.

It turned out that in the new range for XIRR, the first row had blank entries. When I altered the range so that the first row had valid date and number values, the calculation became correct again.

Bug or feature? When I cut and paste the values of the original data, and try XIRR on the pasted values, I get an Err:502, but many of the pasted values are blank or strings, and the blank dates get value-pasted as 12/30/99.

However, when I clean up blank or zero data and re-sort it (leaving only 1355 rows) I get the same number I get with the original, unsorted full-of-junk data (resumably correct) Maybe I have just been lucky using XIRR with out of order dates?

(I am not concerned that a blank date may be interpreted as 12/30/99 since the corresponding value is always blank or zero in my sheet.)

[Ash733]

I feel this might be difficult to resolve without an example. But have a read through the help for the XIRR function, a few things that seem to be occurring in your spreadsheet in particularly are the blank dates (which should result in a Err.502); and they need not be in chronological order.

https://wiki.documentfoundation.org/Documentation/Calc_Functions/XIRR

[wsanders1]

I’m disinclined to file a bug given that XIRR sees to be able to compute the result with blank date, unless the first entry’s date is blank. This post should serve as a helpful hint for a workaround in case anyone else encounters this.

I think that XIRR is interpreting the blank dates as 12/30/99, and I am just luckyI have a special case that all the values associated with the blank dates are zero or blank themselves. I would expect a row with a blank date and a non-zero value to be interpreted as bad data and result in a 502, as per the documentation. (Documentation/Calc Functions/XIRR - The Document Foundation Wiki

The documentation also states “The first entry in Dates is taken as the start of the cash flows, with subsequent entries discounted to that date”. but that doesn’t seem to be strictly true, as my unsorted data contains dates earlier than the first entry, and I got the same results whether the dates were sorted or not,

[Ash733]

@wsanders1

The documentation also states “The first entry in Dates is taken as the start of the cash flows, with subsequent entries discounted to that date”. but that doesn’t seem to be strictly true, as my unsorted data contains dates earlier than the first entry, and I got the same results whether the dates were sorted or not,

It is true if you consider dates are on a continuum and so whether the dates are sorted or not the first date is the earliest date.