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

[Torcan]

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

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:


InvestmentsDatesAmounts

A01/15/18($55)

B01/18/18($20)

B01/19/18$9

B04/06/18$6

A04/08/18$24

A05/20/18$40

B05/21/18$7

A05/23/18($5)


Start01/16/18

End05/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!

 

[bosco_yip]

Cross-Posting

If you do cross-post, please put link of the other forum in your post #.1

So, other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

Regards
Bosco

 

[AlanSidman]

suggest you read this: https://www.excelguru.ca/content.php?184