[EDIT:ERRATA] In general, the order does not matter,
except: the first row must be the earliest date and value.
But first, let's get the correct XIRR formula with the ascending order.
Then we can talk about modifying it, if you insist on entering the data in descending date order.
The following image demonstrates the XIRR formula for each date.
Refer to the attached Excel file for details.
As proof of concept, the growth curve for the last date is demonstrated in column H.
Attachment 802814
Enter the following formula into F23, then copy into F24:F30:
=XIRR(IF(ROW($E$22:E23)=ROW(E23), -E23+B23+C23, $B$22:B23 + $C$22:C23), $A$22:A23)
That must be
array-entered (press
ctrl+shift+Enter instead just Enter) in my version of Excel. YMMV.
Note that the IRRs are very different from what you posted.
If you are certain that your posted IRRs are expected, please
explain their derivation.
And please
attach an example Excel file that demonstrates all calculation, per the instructions in the bright yellow banner at the top of the webpage.
Bookmarks