Conditional XIRR with multiple columns for dates / cash flows

[rotvik]

I need a XIRR function that would only compute the XIRR for specific ISINs.
This is a picture of a sample file with unnecessary info deleted:



[TABLE="width: 806"]
<tbody>[TR]
[TD]ISIN[/TD]
[TD][/TD]
[TD][/TD]
[TD]DATE OF INVESTMENT[/TD]
[TD]DATE OF SALE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]BUY VOLUME[/TD]
[TD]SELL VOLUME[/TD]
[/TR]
[TR]
[TD]US7475251036[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18.11. 2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2421[/TD]
[TD="align: right"]-385,86[/TD]
[/TR]
[TR]
[TD]US3546131018[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3.12. 2015[/TD]
[TD="align: right"]4.11. 2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1470,92[/TD]
[TD="align: right"]-1021,68[/TD]
[/TR]
[TR]
[TD]US6826801036[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7.12. 2015[/TD]
[TD="align: right"]4.5. 2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1514,9[/TD]
[TD="align: right"]-2736,58[/TD]
[/TR]
[TR]
[TD]US3546131018[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11.12. 2015[/TD]
[TD="align: right"]4.11. 2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1487,97[/TD]
[TD="align: right"]-1582,69[/TD]
[/TR]
[TR]
[TD]US0378331005[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7.1. 2016[/TD]
[TD="align: right"]31.7. 2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1475,3[/TD]
[TD="align: right"]-3370,39[/TD]
[/TR]
</tbody>[/TABLE]

For example a single line function:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">=XIRR(I33:J33;D31:E31)
</code>What I want would be the same function that can do it for non-contiguous ranges based on the ISIN condition. I.e. do XIRR which takes into account all dates in columns D:E and cash flows in columns I:J that are for the same ISIN.

 

[joeu2004]

Caveat: I use comma to separate parameters. You would use semicolons.

If we did it manually, we might array-enter (press ctrl+shift+enter instead of just Enter) the following formula:

=XIRR(IF($A$3:$A$6=A3, $I$3:$J$6,0), IF($A$3:$A$6=A3, $D$3:$E$6, 0))

Importantly, note that we start with row 3, which has the first cash flow that we are interested in (that is, for "US3546131018" in A3).

We would like to start with row 2, but Excel XIRR does not allow the first value/date pair to be zero.

So more generally, we must replace references to row 3 with references to the first row that we are interested in. That is what the following does (again, array-entered):

=XIRR(IF(INDEX($A$2:$A$6,MATCH(A3,$A$2:$A$6,0)):$A$6=A3, INDEX($I$2:$I$6,MATCH(A3,$A$2:$A$6,0)):$J$6, 0),
IF(INDEX($A$2:$A$6,MATCH(A3,$A$2:$A$6,0)):$A$6=A3, INDEX($D$2:$D$6,MATCH(A3,$A$2:$A$6,0)):$E$6, 0))

Obviously, it would be better to calculate MATCH(A3,$A$2:$A$6,0) one time in a helper cell.

 

[rotvik]

Thank you very much! Worked like magic.