XIRR Calculation error

Hello,

I am trying to calculate XIRR but having 2 issues, will appreciate the help.

1) if I have money added and withdrawn are in 2 separates column.
2) if my dates are descending order.
The image below shows dates in ascending order. and formula shows what i am trying to use but result is #NUM

Originally Posted by curiouscat408

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

Hello,

Thank you for helping me out,
The IRR calculated were not correct, I had error of looking into wrong cell.
I do need to make the data in descending date order.
Thanks

There might be features in Excel 2013 that make this easier.

But in my version, I think the easiest implementation requires a number of "helper" cells.

See the image below. See the "descend" worksheet in the attached Excel file for details.

xirr multicol descend.jpg

Formulas:
F22: =INDEX($L$22:$L$30, ROWS($L$22:$L$30)-ROWS($A$22:A22)+1)
H22: =INDEX(A$22:A$30, ROWS(A$22:A$30)-ROWS(A$22:A22)+1)
L23: =XIRR(IF(ROW($K$22:K23)=ROW(K23), -K23+I23+J23, $I$22:I23 + $J$22:J23), $H$22:H23)
Copy F22 into F23:F29
Insert column before column K, copy H22 into H22:L30, then delete column K and format I22:K30 as Number
Copy L23 into L24:L30

Again, the formula in L23 must be array-entered (press ctrl+shift+Enter instead of just Enter) in my version of Excel. Perhaps not in your version.

Once again Thankyou,
I am updating to office 2019 / 365. If you can tell me which feature, you were referring to for Excel 2013. I can explore also.

Thanks

Originally Posted by rsd007

If you can tell me which feature, you were referring to for Excel 2013. I can explore also.

I know nothing about any new features in Excel 2013, Excel 2019 or Office 365.

And note: I said there "might be", not there "are", new features that might help.