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.)