XIRR while ignoring one investment?

[skitheast13]

Hello,

I am attempting to calculate the IRR of a portfolio of investments while excluding just one of the investments.
I successfully used the 365 filter function to calculate the IRR of a particular investment in the portfolio - now, how can I exclude that same investment while calculating the whole portfolio IRR?

To note, each of the dates/cash flows have an investment name attached.

IRR Calc.xlsx
	A	B	C	D	E	F
1	Investment Name	Date	Cash Flow		Investment Name	IRR
2	Gravity	5/25/2022	(402,435.52)		Gravity	108.20%
3	Gravity	5/25/2022	(21,858,724.39)
4	Gravity	8/10/2022	292,481.52		Whole Portfolio	38.65%
5	Gravity	8/10/2022	1,318.10		Whole Portfolio EXCLUDING Gravity	?
6	Gravity	9/30/2022	28,464,149.00
7	Shipment	12/31/2021	(345,389.31)
8	Shipment	12/31/2021	(10,067,597.81)
9	Shipment	6/22/2022	(2,378,468.50)
10	Shipment	6/22/2022	(528,526.58)
11	Shipment	8/2/2022	(513,480.21)
12	Shipment	9/30/2022	15,928,116.62
13	Jewel	8/10/2021	(2,301,517.36)
14	Jewel	5/24/2022	(13,712,117.63)
15	Jewel	9/30/2022	17,496,746.00
16	Galmour	7/5/2022	(44,101.00)
17	Galmour	7/5/2022	(48,142.00)
18	Galmour	7/5/2022	(30,181,828.00)
19	Galmour	9/14/2022	296,308.00
20	Galmour	9/30/2022	33,085,800.00
21	Finland	3/22/2022	(6,487,672.48)
22	Finland	3/22/2022	(50,350.80)
23	Finland	3/22/2022	(109,519.79)
24	Finland	3/22/2022	(19,183.22)
25	Finland	3/22/2022	(41,726.10)
26	Finland	3/22/2022	(2,471,747.21)
27	Finland	5/5/2022	(104,055.30)
28	Finland	5/5/2022	(39,666.41)
29	Finland	9/20/2022	(37,233.04)
30	Finland	9/20/2022	(22,538.42)
31	Finland	9/20/2022	(14,195.21)
32	Finland	9/20/2022	(8,592.83)
33	Finland	9/30/2022	9,004,982.71
34
Sheet1

Cell Formulas
Range	Formula
F2	F2	=XIRR(FILTER($C$1:$C$33,$A$1:$A$33=E2),FILTER($B$1:$B$33,$A$1:$A$33=E2))
F4	F4	=XIRR(C2:C33,B2:B33)

 

[DRSteele]

Welcome to the Forum.

If you wish to exclude only one criteria like "Gravity" you can just use FILTER with a 'does not equal' operator. Like:

=XIRR(FILTER(C2:C33,A2:A33<>E2),FILTER(B2:B33,A2:A33<>E2))

 

[skitheast13]

Welcome to the Forum.

If you wish to exclude only one criteria like "Gravity" you can just use FILTER with a 'does not equal' operator. Like:

=XIRR(FILTER(C2:C33,A2:A33<>E2),FILTER(B2:B33,A2:A33<>E2))

This was the perfect solution - thank you for your help!

 

[DRSteele]

Super! You're welcome.