How do I create an XIRR function based on unique names and between certain dates?

Question

I'm trying to create an XIRR function that will calculate the return based on an unique investment names and corresponding cash flows between certain dates. For example, I want to calculate the IRR for Investment A between 01/16/2018 to 5/20/2018 with the following cash flows:

Investments Dates Amounts A 01/15/18 ($55) B 01/18/18 ($20) B 01/19/18 $9 B 04/06/18 $6 A 04/08/18 $24 A 05/20/18 $40 B 05/21/18 $7 A 05/23/18 ($5)

Start 01/16/18
End 05/20/18

The challenge I am having is skipping past the Investment B cash flows and only calculating Investment A. This is a simplified version of what I’m trying to do. The full version has about 50 investment names spread over about 1000 rows. Any suggestions would help. I’ve tried using fncs INDEX, MATCH, OFFSET, COUNTIF.

The spreadsheet is attached for reference.

I appreciate the help ahead of time!

Answer 1

The following array formula returns the XIRR based on the investment date in E2, the start date in F2, and the end date in G2. Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER.

=XIRR(INDEX(C:C,N(IF(1,MODE.MULT(IF(A2:A9=E2,IF(B2:B9>=F2,IF(B2:B9<=G2,ROW(C2:C9)*{1,1}))))))),INDEX(B:B,N(IF(1,MODE.MULT(IF(A2:A9=E2,IF(B2:B9>=F2,IF(B2:B9<=G2,ROW(C2:C9)*{1,1}))))))))

For earlier versions of Excel, try the following formula instead. Note that this formula also needs to be confirmed with CONTROL+SHIFT+ENTER.

=XIRR(N(OFFSET(C2:C9,SMALL(IF(A2:A9=E2,IF(B2:B9>=F2,IF(B2:B9<=G2,ROW(C2:C9)-ROW(C2)))),ROW(INDIRECT("1:"&COUNTIFS(A2:A9,E2,B2:B9,">="&F2,B2:B9,"<="&G2)))),0,1)),N(OFFSET(B2:B9,SMALL(IF(A2:A9=E2,IF(B2:B9>=F2,IF(B2:B9<=G2,ROW(B2:B9)-ROW(B2)))),ROW(INDIRECT("1:"&COUNTIFS(A2:A9,E2,B2:B9,">="&F2,B2:B9,"<="&G2)))),0,1)))