Allow the user to sort the XIRR() function by dates
This development would resolve the issue about the XIRR calculation being incorrect in most cases.
Today, the XIRR() function has 3 main limitations:
1. The values array must contain at least one positive value and one negative value. 2. All Dates must be valid interpretations of dates that correspond to values. 3. Values and dates must be in chronological order.
The points 1 & 2 are logical and can be corrected in the script if there is any issue. The problem comes from the point 3, as it is not possible for now to order the data directly in the script. Therefore, the XIRR() function returns, most of the time, incorrect results.
To work correctly, we should add in the definition of the XIRR() function, a new parameter being a column to determine the order of the function processing. Let's say that my table is composed of 2 columns (Paiement & DateOfPaiement), the function would then look like XIRR(Paiement, DateOfPaiement, DateOfPaiement).