• 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

  • XIFF Formula.xlsx
    9.8 KB · Views: 14
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
 
=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
 
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:
=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........
 
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:
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

  • XIFF Formula.xlsx
    15.9 KB · Views: 8
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
 
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

  • XIFF Formula.xlsx
    17.1 KB · Views: 10
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"...
 
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...
 
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)
 
Back
Top