Consolidated XIRR based on multiple conditions (Excel 2019)

[ViBee]

Dear Members,

I am using Excel 2019.

I am creating a portfolio tracker and my data is in a Table form (Table1) which contains following fields

Column A (Security)
Column B (Asset Class)
Column C (Sub Type)
Column D (Outflow Date)
Column E (Outflow Amt)
Column F (Inflow Date)
Column G (Inflow Amt)
Column H (Time Invested in Years)
Column I (XIRR)

Columns A to H are inputs and Column I is a calculated field.

Is there a way to calculate consolidated XIRR for each unique combination of entries in Column A (Security), Column B (Asset Class) & Column C (Sub Type) in column O? Sample file is attached for ready reference.

Please note that solution is requested for Excel 2019.

Regards.

 

[bosco_yip]

Try,

With reference to your XIRR calculation in Column I formula, then

In O2, formula copied down :

=IFERROR((SUM(OFFSET(INDEX(G$1:G$21,MATCH(1,INDEX((A$1:A$21=L2)*(B$1:B$21=M2)*(C$1:C$21=N2),0),0)),,,COUNTIFS(A$1:A$21,L2,B$1:B$21,M2,C$1:C$21,N2)))/SUM(OFFSET(INDEX(E$1:E$21,MATCH(1,INDEX((A$1:A$21=L2)*(B$1:B$21=M2)*(C$1:C$21=N2),0),0)),,,COUNTIFS(A$1:A$21,L2,B$1:B$21,M2,C$1:C$21,N2))))^(1/SUM(OFFSET(INDEX(H$1:H$21,MATCH(1,INDEX((A$1:A$21=L2)*(B$1:B$21=M2)*(C$1:C$21=N2),0),0)),,,COUNTIFS(A$1:A$21,L2,B$1:B$21,M2,C$1:C$21,N2))))-1,"")

 

[ViBee]

Grateful for your help.

Cross posted here: https://www.excelforum.com/excel-fo...idated-xirr-based-on-multiple-conditions.html

OP was advised to post a link back to EF here, but has not yet done so. This is FYI.

Dear AliGW, I appreciate your hurry but even hurry takes time, I would have appreciated if you could give me atleast 5 minutes to figure out how to linkback after I conveyed my intention to do it. Not everyone is as smart as you to figure out things as quickly.

 

[ViBee]

Dear Members,

I am using Excel 2019.

I am creating a portfolio tracker and my data is in a Table form (Table1) which contains following fields

Column A (Security)
Column B (Asset Class)
Column C (Sub Type)
Column D (Outflow Date)
Column E (Outflow Amt)
Column F (Inflow Date)
Column G (Inflow Amt)
Column H (Time Invested in Years)
Column I (XIRR)

Columns A to H are inputs and Column I is a calculated field.

Is there a way to calculate consolidated XIRR for each unique combination of entries in Column A (Security), Column B (Asset Class) & Column C (Sub Type) in column O? Sample file is attached for ready reference.

Please note that solution is requested for Excel 2019.

Regards.

Cross posted at

Consolidated XIRR based on multiple conditions

Dear Members, I am using Excel 2019. I am creating a portfolio tracker and my data is in a Table form (Table1) which contains following fields Column A (Security) Column B (Asset Class) Column C (Sub Type) Column D (Outflow Date)

www.excelforum.com

due to my laxity to go through the forum rules. I apologize for the lapse and assure of strict compliance in future.

 

[AliGW]

Grateful for your help.

Dear AliGW, I appreciate your hurry but even hurry takes time, I would have appreciated if you could give me atleast 5 minutes to figure out how to linkback after I conveyed my intention to do it. Not everyone is as smart as you to figure out things as quickly.

I've removed my post now you have added the link yourself. Glad you are grateful.

 

[ViBee]

Try,

With reference to your XIRR calculation in Column I formula, then

In O2, formula copied down :

=IFERROR((SUM(OFFSET(INDEX(G$1:G$21,MATCH(1,INDEX((A$1:A$21=L2)*(B$1:B$21=M2)*(C$1:C$21=N2),0),0)),,,COUNTIFS(A$1:A$21,L2,B$1:B$21,M2,C$1:C$21,N2)))/SUM(OFFSET(INDEX(E$1:E$21,MATCH(1,INDEX((A$1:A$21=L2)*(B$1:B$21=M2)*

Try,

With reference to your XIRR calculation in Column I formula, then

In O2, formula copied down :

=IFERROR((SUM(OFFSET(INDEX(G$1:G$21,MATCH(1,INDEX((A$1:A$21=L2)*(B$1:B$21=M2)*(C$1:C$21=N2),0),0)),,,COUNTIFS(A$1:A$21,L2,B$1:B$21,M2,C$1:C$21,N2)))/SUM(OFFSET(INDEX(E$1:E$21,MATCH(1,INDEX((A$1:A$21=L2)*(B$1:B$21=M2)*(C$1:C$21=N2),0),0)),,,COUNTIFS(A$1:A$21,L2,B$1:B$21,M2,C$1:C$21,N2))))^(1/SUM(OFFSET(INDEX(H$1:H$21,MATCH(1,INDEX((A$1:A$21=L2)*(B$1:B$21=M2)*(C$1:C$21=N2),0),0)),,,COUNTIFS(A$1:A$21,L2,B$1:B$21,M2,C$1:C$21,N2))))-1,"")

View attachment 80432

Many thanks for providing just the solution I was looking for. Grateful to you.
(C$1:C$21=N2),0),0)),,,COUNTIFS(A$1:A$21,L2,B$1:B$21,M2,C$1:C$21,N2))))^(1/SUM(OFFSET(INDEX(H$1:H$21,MATCH(1,INDEX((A$1:A$21=L2)*(B$1:B$21=M2)*(C$1:C$21=N2),0),0)),,,COUNTIFS(A$1:A$21,L2,B$1:B$21,M2,C$1:C$21,N2))))-1,"")

View attachment 80432