• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

bosco_yip

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

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

76433
 

Tusharrm

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

Thanks Bosco for you prompt response but i am getting "#name?" error... (fyi...i am using MS office 2013..)

76434
 

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:

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"),)))

76445
 
Last edited:

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

will wait for your response..

Regards,
Tushar M.
 

Attachments

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"),)))

76455
 

Excel Wizard

Active Member
Please try

=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

Tusharrm

New Member
Please try

=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
Please try

=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)
 
Top