XIRR with Non-Contiguous Entries (Single Column of Cash Flows, Multiple Entities)

[jjobrien03]

I have a stream of cash flows with with two different Categories by which I would like to generate IRRs. I know you can create TRUE/FALSE ranges to be referenced by formulas and I think this is probably the way I want to do this? Basically I want to calculate separate IRRs for apple/banana/pear and Unrealized/Realized using one column of cash flows similar to the below. Can someone help me with this?

Ideally this would be without helper columns and/or array formulas

XIRR Offset.xlsx
	E	F	G	H
2	Category	XIRR
3	Apple	?
4	Pear	?
5	Banana	?
6	Realized	?
7	Unrealized	?
8
9	Date	Category1	Category2	$
10	1/1/2022	apple	Realized	-1,000
11	4/1/2022	apple	Realized	200
12	7/1/2022	banana	Unrealized	-2,500
13	10/1/2022	pear	Unrealized	-500
14	1/1/2023	banana	Unrealized	1,000
15	4/1/2023	pear	Unrealized	500
16	7/1/2023	banana	Unrealized	1,000
17	10/1/2023	apple	Realized	300
18	1/1/2024	apple	Realized	900
19	4/1/2024	pear	Unrealized	150
20	7/1/2024	banana	Unrealized	1,000
Sheet1

 

[JoeMo]

What version of Excel are you using? Below seems to be working in MS 365:

Book1
	E	F	G	H
2	Category	XIRR
3	Apple	22.5%
4	Pear	45.6%
5	Banana	17.4%
6	Realized	22.5%
7	Unrealized	20.3%
8
9	Date	Category1	Category2	$
10	1/1/2022	apple	Realized	-1000
11	4/1/2022	apple	Realized	200
12	7/1/2022	banana	Unrealized	-2500
13	10/1/2022	pear	Unrealized	-500
14	1/1/2023	banana	Unrealized	1000
15	4/1/2023	pear	Unrealized	500
16	7/1/2023	banana	Unrealized	1000
17	10/1/2023	apple	Realized	300
18	1/1/2024	apple	Realized	900
19	4/1/2024	pear	Unrealized	150
20	7/1/2024	banana	Unrealized	1000
Sheet4

Cell Formulas
Range	Formula
F3:F5	F3	=XIRR(INDEX(FILTER($E$10:$H$20,$F$10:$F$20=$E3),0,4),INDEX(FILTER($E$10:$H$20,$F$10:$F$20=$E3),0,1))
F6:F7	F6	=XIRR(INDEX(FILTER($E$10:$H$20,$G$10:$G$20=$E6),0,4),INDEX(FILTER($E$10:$H$20,$G$10:$G$20=$E6),0,1))

 

[jjobrien03]

What version of Excel are you using? Below seems to be working in MS 365:

Book1
	E	F	G	H
2	Category	XIRR
3	Apple	22.5%
4	Pear	45.6%
5	Banana	17.4%
6	Realized	22.5%
7	Unrealized	20.3%
8
9	Date	Category1	Category2	$
10	1/1/2022	apple	Realized	-1000
11	4/1/2022	apple	Realized	200
12	7/1/2022	banana	Unrealized	-2500
13	10/1/2022	pear	Unrealized	-500
14	1/1/2023	banana	Unrealized	1000
15	4/1/2023	pear	Unrealized	500
16	7/1/2023	banana	Unrealized	1000
17	10/1/2023	apple	Realized	300
18	1/1/2024	apple	Realized	900
19	4/1/2024	pear	Unrealized	150
20	7/1/2024	banana	Unrealized	1000
Sheet4

Cell Formulas
Range	Formula
F3:F5	F3	=XIRR(INDEX(FILTER($E$10:$H$20,$F$10:$F$20=$E3),0,4),INDEX(FILTER($E$10:$H$20,$F$10:$F$20=$E3),0,1))
F6:F7	F6	=XIRR(INDEX(FILTER($E$10:$H$20,$G$10:$G$20=$E6),0,4),INDEX(FILTER($E$10:$H$20,$G$10:$G$20=$E6),0,1))

It does - thank you!

 

[JoeMo]

It does - thank you!

You are welcome - thanks for the reply.