Array formula for Internal Rate of Return for Individual Portfolios Using XIRR

[Jim2001]

Hi,

I have a set of data with portfolio ID, cashflow amount and cashflow date. I am looking for an array formula that will calculate the IRR using the XIRR formula for each unique portfolio ID.

Example of the data set (the real dataset has 16000 lines:

Portfolio ID	Cashflow	Cashflow Date
10003487-001	-19749.34	1/07/2020
10003487-001	19442.33	30/06/2021
10005198-001	-124527.56	1/07/2020
10005198-001	132226.3	30/06/2021
10006333-001	-99252.82	1/07/2020
10006333-001	113157.21	30/06/2021
10012418-001	-407347.17	1/07/2020
10012418-001	15000	28/01/2021
10012418-001	7000	11/06/2021
10012418-001	466808.63	30/06/2021

The output required (the real output has 5000 Portfolio IDs):

Portfolio ID	IRR
10003487-001	-2%
10005198-001	6%
10006333-001	14%
10012418-001	20.37%

Thanks for your help

Jim

 

[bosco_yip]

Maybe,

In F2, formula copied down :

=XIRR(OFFSET(B$1,MATCH(E2,A$2:A$11,0),,COUNTIF(A$2:A$11,E2)),OFFSET(C$1,MATCH(E2,A$2:A$11,0),,COUNTIF(A$2:A$11,E2)))

 

[Jim2001]

Outstanding! Thanks so much

 

[Peter Bartholomew]

It would be interesting to know the performance of these array method on the moderately large datasets you have.
[Note: The LET and FILTER functions are specific to Excel 365]

 

[Jim2001]

Hi Peter,

Thanks for your reply. I do have 365 but evidently I am not so good at using power pivots and couldn't get this work. Can you share a good link where I could learn a bit more. Also, can you upload your sheet. I am working in a bigger workbook and have split this data across a number of sheets.

Regarding the performance (based on the first formula) it was fine. I only have 16000 rows so not a real big data set. I am only working with cashflows to and from the portfolio and it would be great if I could do the same calc on cashflows within the portfolios (and including fees, brokerage, dividends, tax and trades) but that would be millions of rows and I will need a corporate solution.

Thanks
Jim

 

[Peter Bartholomew]

Hi Jim, (@Jim2001)

The first thing to realise is that the proposed solution is not VBA or one of the Power tools; it is a standard (if somewhat unusual) Excel worksheet formula! The LET function has only recently been released to wider usage in 365. My formulas all use Tables/Structured References and Defined Names rather than direct cell references which makes the solutions more readable but less familiar.

What LET does is use an alternating pattern of parameters to assign parts of the formula (entered within the even parameters) to a name/variable (the preceding odd parameter). This allows subsequent formulas to use the variable name rather than creating a deeply nested formula. I separate the pairs of parameters by inserting Alt/Enter (line feed) after the formula fragments, to make the overall formula more readable. The final parameter contains the formula or previously-calculated variable to be returned as the result.

The LET function allows the development of intensely complicated formulas without relying upon the brilliance of the traditional Excel guru.
For example, see another current XIRR query: XIRR with non-contiguous entries for Flows (ranges) and Valuation (single period) based on a cutoff date. | Chandoo.org Excel Forums - Become Awesome in Excel