Filter and sort within XIRR?

[dxg169]

Hi all - I have a table of raw cashflow data which I'm hoping to do an XIRR formula on based on an inputted deal. However the raw cashflow data is not always sorted so that the first cashflow is negative. This causes issues with an XIRR formula

Example data in table below.

I was hoping to use this formula:
=XIRR(
FILTER(C:C,B:B=[deal X]), #Filter to obtain array of cashflows for [deal X]
FILTER(A:A,B:B=[deal X]), #Filter to obtain array of dates for [deal X]
0.1) #Guess for XIRR

However I get an error on Deal C as the cashflows are not in order. The +120 is before the -110.

I thought I could use the sort formula on the cashflows, but can't think how to ensure that the dates correctly align with those sorted cashflows.
For example I couldn't sort on both columns as for deal A the +10 would be 2nd in the sort, but the 01/08/2022 date corresponding for that cashflow would be 3rd in the sort.

Any help greatly appreciated.

Date	Deal	Cashflow
01/12/2019​	Deal A	-100​
01/12/2019​	Deal B	-120​
01/12/2021​	Deal C	120​
01/06/2022​	Deal A	150​
01/12/2020​	Deal B	130​
01/08/2022​	Deal A	10​
01/06/2019​	Deal C	-110​

 

[Domenic]

Try...

dxg.xlsm
	A	B	C	D	E	F
1	Date	Deal	Cashflow		Results
2	12/01/2019	Deal A	-100		Deal A	0.205782
3	12/01/2019	Deal B	-120		Deal B	0.083096
4	12/01/2021	Deal C	120		Deal C	0.035358
5	06/01/2022	Deal A	150
6	12/01/2020	Deal B	130
7	08/01/2022	Deal A	10
8	06/01/2019	Deal C	-110
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=LET(rng,SORT(FILTER($A$2:$C$8,($B$2:$B$8=E2)),1,1,FALSE),XIRR(INDEX(rng,0,3),INDEX(rng,0,1),0.1))

Note that the formula is first entered in F2, and then copied down.

Hope this helps!

 

[dxg169]

Thank you very much - this worked great.