# XIRR Formula for Different Text and Date Criteria

#### Tusharrm

##### New Member
Hello,

I want to calculate XIRR but challenge is that i have four text criteria as well as i have capital invested date, dividend date, capital return date and Market value date and values for all four are in different columns.

I have use XIRR(INDEX(indirect))) which is gives me correct answerer but if all date are in same column then only it works fine ..

I want formula in yellow highlighted cell, theoretical answerer I have written just beside yellow highlighted cell - Pl go through the excel sheet attached.

Any expert pl help ASAP.......

#### Attachments

• 9.8 KB Views: 10

#### bosco_yip

##### Excel Ninja
• Tusharrm

#### bosco_yip

##### Excel Ninja
1] TEXTJOIN function only available in Excel 2019 and Office 365.

2] If your Excel version is Excel 2013, you need download a User Define Function (UDF) as below :

Function textjoint(delimiter As Variant, ignore_empty As Variant, ParamArray textn() As Variant) As String
Dim txt, txtrng
Dim ignor As Boolean
Dim textb4 As String
On Error GoTo Exitf
If IsMissing(delimiter) Then
delimiter = ""
End If
If IsMissing(ignore_empty) Then
ignore_empty = True
End If
textb4 = ""
If ignore_empty = False Or ignore_empty = 0 Then
ignor = False
Else
ignor = True
End If
For Each txtrng In textn
If IsObject(txtrng) Or IsArray(txtrng) Then
For Each txt In txtrng
If Len(txt) = 0 Then
If Not ignor Then
textb4 = textb4 & txt & delimiter
End If
Else
textb4 = textb4 & txt & delimiter
End If
Next txt
Else
If Len(txtrng) > 0 Then
If Not ignor Then
textb4 = textb4 & txtrng & delimiter
End If
End If
End If
Next txtrng
If Len(textb4) > 0 Then
textb4 = Left(textb4, Len(textb4) - Len(delimiter))
End If
Exitf:
textjoint = textb4
End Function

3] And, the CSE formula revised a bit, in changing TEXTJOIN to TEXTJOINT, as in :

=XIRR(FILTERXML("<a><b>"&TEXTJOINT("</b><b>",,IF((A5:A12=A16)*(B5:B12=B16)*(C5:C12=C16)*(D5:D12=D16),I5:L12,""))&"</b></a>","//b"),FILTERXML("<a><b>"&TEXTJOINT("</b><b>",,IF((A5:A12=A16)*(B5:B12=B16)*(C5:C12=C16)*(D5:D12=D16),E5:H12,""))&"</b></a>","//b"))

Last edited:
• • deciog and Tusharrm

#### Tusharrm

##### New Member
=XIRR(FILTERXML("<a><b>"&TEXTJOINT("</b><b>",,IF((A5:A12=A16)*(B5:B12=B16)*(C5:C12=C16)*(D5:D12=D16),I5:L12,""))&"</b></a>","//b"),FILTERXML("<a><b>"&TEXTJOINT("</b><b>",,IF((A5:A12=A16)*(B5:B12=B16)*(C5:C12=C16)*(D5:D12=D16),E5:H12,""))&"</b></a>","//b"))
Thank to you Man... it works.... BRAVO....

Any other way without UFD........

#### bosco_yip

##### Excel Ninja
Thank to you Man... it works.... BRAVO....

Any other way without UFD........
Then, try this longer formula for all old Excel versions.

In E16, enter array (Ctrl+Shift+Enter) formula :

=XIRR(N(INDIRECT(TEXT(AGGREGATE(15,6,(ROW(\$5:\$12)/1%+COLUMN(I:L))/(A5:A12=A16)/(B5:B12=B16)/(C5:C12=C16)/(D5:D12=D16)/(I5:L12<>""),ROW(INDIRECT("1:"&SUM((A5:A12=A16)*(B5:B12=B16)*(C5:C12=C16)*(D5:D12=D16)*(I5:L12<>""))))),"r0c00"),)),N(INDIRECT(TEXT(AGGREGATE(15,6,(ROW(\$5:\$12)/1%+COLUMN(E:H))/(A5:A12=A16)/(B5:B12=B16)/(C5:C12=C16)/(D5:D12=D16)/(E5:H12<>""),ROW(INDIRECT("1:"&SUM((A5:A12=A16)*(B5:B12=B16)*(C5:C12=C16)*(D5:D12=D16)*(I5:L12<>""))))),"r0c00"),))) Last edited:
• Tusharrm

#### Tusharrm

##### New Member
Then, try this longer formula for all old Excel versions.

In E16, enter array (Ctrl+Shift+Enter) formula :

=XIRR(N(INDIRECT(TEXT(AGGREGATE(15,6,(ROW(\$5:\$12)/1%+COLUMN(I:L))/(A5:A12=A16)/(B5:B12=B16)/(C5:C12=C16)/(D5:D12=D16)/(I5:L12<>""),ROW(INDIRECT("1:"&SUM((A5:A12=A16)*(B5:B12=B16)*(C5:C12=C16)*(D5:D12=D16)*(I5:L12<>""))))),"r0c00"),)),N(INDIRECT(TEXT(AGGREGATE(15,6,(ROW(\$5:\$12)/1%+COLUMN(E:H))/(A5:A12=A16)/(B5:B12=B16)/(C5:C12=C16)/(D5:D12=D16)/(E5:H12<>""),ROW(INDIRECT("1:"&SUM((A5:A12=A16)*(B5:B12=B16)*(C5:C12=C16)*(D5:D12=D16)*(I5:L12<>""))))),"r0c00"),)))

View attachment 76445
Dear Bosco,
You are champ man...i like to use without UDF....

but I got new issue, my summary data is in different sheet and data is in different sheet, i tried to "cut" & "pest" the formula details in other sheet and also add few data in it, then it give answer as "#NUM!".... excel sheet attached check, new data sheet and summary... i also have dynamic data set which will add at end of every month in sheet called "NEW DATA" pl also try to incorporate it if possible and i know u can do it... (without UDF)..

Regards,
Tushar M.

#### Attachments

• 15.9 KB Views: 5

#### bosco_yip

##### Excel Ninja
You missed adding the "sheet name" in front of Text function, the formula modification as in :

=XIRR(N(INDIRECT("'New Data'!"&TEXT(AGGREGATE(15,6,(ROW('New Data'!\$5:\$14)/1%+COLUMN('New Data'!I:L))/('New Data'!\$A\$5:\$A\$13=B5)/('New Data'!\$B\$5:\$B\$13=C5)/('New Data'!\$C\$5:\$C\$13=D5)/('New Data'!\$D\$5:\$D\$13=E5)/('New Data'!\$I\$5:\$L\$13<>""),ROW(INDIRECT("1:"&SUM(('New Data'!\$A\$5:\$A\$13=B5)*('New Data'!\$B\$5:\$B\$13=C5)*('New Data'!\$C\$5:\$C\$13=D5)*('New Data'!\$D\$5:\$D\$13=E5)*('New Data'!\$I\$5:\$L\$13<>""))))),"r0c00"),)),N(INDIRECT("'New Data'!"&TEXT(AGGREGATE(15,6,(ROW('New Data'!\$5:\$14)/1%+COLUMN('New Data'!E:H))/('New Data'!\$A\$5:\$A\$13=B5)/('New Data'!\$B\$5:\$B\$13=C5)/('New Data'!\$C\$5:\$C\$13=D5)/('New Data'!\$D\$5:\$D\$13=E5)/('New Data'!\$E\$5:\$H\$13<>""),ROW(INDIRECT("1:"&SUM(('New Data'!\$A\$5:\$A\$13=B5)*('New Data'!\$B\$5:\$B\$13=C5)*('New Data'!\$C\$5:\$C\$13=D5)*('New Data'!\$D\$5:\$D\$13=E5)*('New Data'!\$I\$5:\$L\$13<>""))))),"r0c00"),))) • • herofox and Tusharrm

#### Excel Wizard

##### Active Member

=XIRR((MMULT(--(INDEX('New Data'!\$A\$5:\$A\$14,MATCH(4,MMULT(--('New Data'!\$A\$5:\$D\$14=B5:E5),{1;1;1;1}),)):'New Data'!\$D\$14=B5:E5),{1;1;1;1})=4)*INDEX('New Data'!\$I\$5:\$I\$14,MATCH(4,MMULT(--('New Data'!\$A\$5:\$D\$14=B5:E5),{1;1;1;1}),)):'New Data'!\$L\$14,(MMULT(--(INDEX('New Data'!\$A\$5:\$A\$14,MATCH(4,MMULT(--('New Data'!\$A\$5:\$D\$14=B5:E5),{1;1;1;1}),)):'New Data'!\$D\$14=B5:E5),{1;1;1;1})=4)*INDEX('New Data'!\$E\$5:\$E\$14,MATCH(4,MMULT(--('New Data'!\$A\$5:\$D\$14=B5:E5),{1;1;1;1}),)):'New Data'!\$H\$14)

#### Attachments

• 17.1 KB Views: 8
• Tusharrm and herofox

#### Tusharrm

##### New Member

=XIRR((MMULT(--(INDEX('New Data'!\$A\$5:\$A\$14,MATCH(4,MMULT(--('New Data'!\$A\$5:\$D\$14=B5:E5),{1;1;1;1}),)):'New Data'!\$D\$14=B5:E5),{1;1;1;1})=4)*INDEX('New Data'!\$I\$5:\$I\$14,MATCH(4,MMULT(--('New Data'!\$A\$5:\$D\$14=B5:E5),{1;1;1;1}),)):'New Data'!\$L\$14,(MMULT(--(INDEX('New Data'!\$A\$5:\$A\$14,MATCH(4,MMULT(--('New Data'!\$A\$5:\$D\$14=B5:E5),{1;1;1;1}),)):'New Data'!\$D\$14=B5:E5),{1;1;1;1})=4)*INDEX('New Data'!\$E\$5:\$E\$14,MATCH(4,MMULT(--('New Data'!\$A\$5:\$D\$14=B5:E5),{1;1;1;1}),)):'New Data'!\$H\$14)

Yes "excel wizard" thanks man perfectly work around.... bravo...

what if i want to calculate xirr for only 2 selection criteria rather then 4 (which is in above) .... what will be formula change can you plz suggest... two criteria will be in coloum B and C only in "NEW data"...

#### Tusharrm

##### New Member

=XIRR((MMULT(--(INDEX('New Data'!\$A\$5:\$A\$14,MATCH(4,MMULT(--('New Data'!\$A\$5:\$D\$14=B5:E5),{1;1;1;1}),)):'New Data'!\$D\$14=B5:E5),{1;1;1;1})=4)*INDEX('New Data'!\$I\$5:\$I\$14,MATCH(4,MMULT(--('New Data'!\$A\$5:\$D\$14=B5:E5),{1;1;1;1}),)):'New Data'!\$L\$14,(MMULT(--(INDEX('New Data'!\$A\$5:\$A\$14,MATCH(4,MMULT(--('New Data'!\$A\$5:\$D\$14=B5:E5),{1;1;1;1}),)):'New Data'!\$D\$14=B5:E5),{1;1;1;1})=4)*INDEX('New Data'!\$E\$5:\$E\$14,MATCH(4,MMULT(--('New Data'!\$A\$5:\$D\$14=B5:E5),{1;1;1;1}),)):'New Data'!\$H\$14)
hi excel wizard can you help plz...

#### Excel Wizard

##### Active Member
Try

=XIRR(
(MMULT(--(INDEX('New Data'!\$B\$5:\$B\$14,MATCH(2,MMULT(--('New Data'!\$B\$5:\$C\$14=C5:D5),{1;1}),)):'New Data'!\$C\$14=C5:D5),{1;1})=2)*INDEX('New Data'!\$I\$5:\$I\$14,MATCH(2,MMULT(--('New Data'!\$B\$5:\$C\$14=C5:D5),{1;1}),)):'New Data'!\$L\$14,
(MMULT(--(INDEX('New Data'!\$B\$5:\$B\$14,MATCH(2,MMULT(--('New Data'!\$B\$5:\$C\$14=C5:D5),{1;1}),)):'New Data'!\$C\$14=C5:D5),{1;1})=2)*INDEX('New Data'!\$E\$5:\$E\$14,MATCH(2,MMULT(--('New Data'!\$B\$5:\$C\$14=C5:D5),{1;1}),)):'New Data'!\$H\$14)

#### Tusharrm

##### New Member
Thanks Bosco and Excel Wizard... worked perfectly.... you both are champ....