XIRR with non-contiguous entries for Flows (ranges) and Valuation (single period) based on cutoff date.

[plshelpexcel]

Hello All:

I am trying to find a way to calculate the XIRR for many investments over a vast Data Input structure that already exists and which is constantly being updated with new Inputs for the respective investments.

The challenge is to find a standard formula that produces XIRR calculations for such Data Input structure, which contains entries for Cashflows (ranges) and Valuations (a single and final value) that are mostly not contiguous. Furthermore, the Data Input structure also includes Cashflow entries for dates that go beyond the Valuation entries. There has to be a cutoff date for the XIRR calculations and such dates are equal to the single Valuation inputs.

Please refer to the attached example, which makes the problem easier to understand.

Each Investment has an individual column for Inputs that include Cashflow transactions (we would look at them over a range) and different period end Valuations (we would only consider one of these for each investment) The dates for the Investment Inputs are all entered into a single column that is shared by all Investments.

The Valuation value Inputs are found in the end of each Investment column after the Cashflows, and often are not in chronological order.

As stated before there is also a challenge because Cashflow entries beyond the last Valuation date often times exist in the Investment Input columns. This has to be accounted for when calculating the XIRR, as the calculation should ignore all entries that are more recent than the Valuation date Input.

Thank you very, very, very, very much for help and tips!!!!

XIRR.xlsx
	B	C	D	E	F	G
2			Investment 1	Investment 2	Investment 3
3	XIRR at cutoff:		Desired XIRR:	Desired XIRR:	Desired XIRR:
4	10/31/2020		41.57%	15.50%	17.20%
5	12/31/2020		29.77%	9.65%	10.61%
6	9/30/2020		37.61%	9.42%	10.73%
7	2/28/2021		31.60%	14.58%	24.36%
8
9	Valuation date:		2/28/2021			This Valuation date, or cutoff date, should serve for 1 and 2 below:
10	XIRR		0.00%	-96.14%	0.00%	These current XIRR formulas do not work because they capture only the Cashflows. I cannot find a way to incorporate the single needed Valuation Input entry for each Investment into these XIRR formulas.
11
12	Cashflows					1. Cashflows. The last Cashflow Input has to match the Valuation date. I need to capture a range of Flows while considering that data Input structure below includes Cashflows beyond the desired XIRR calculation date.
13	Date		2/28/2021	2/28/2021	2/28/2021
14	Start ID		1	5	6
15	End ID		16	16	16
16
17	Valuation					2. Valuation date. Only one Valuation Input should be included in the XIRR calculation. The data Input structure includes several Valuation Inputs for different periods. Only one Valuation Input * controlled by cell D9 * should be used in the formula.
18	Date		2/28/2021	2/28/2021	2/28/2021
19	Start ID		1	5	6
20	End ID		20	20	20
21
22
23			Investment 1	Investment 2	Investment 3	Data Input Structure: new entries are constantly added under each investment column that include Cash In and Cash Out inputs and also the Valuations inputs for multiple periods
24	Date	Entry Type	$	$	$
25	1/1/2020	Cash In	(100.00)
26	1/8/2020	Cash In	(5.00)
27	1/14/2020	Cash In	(5.00)
28	1/22/2020	Cash In	(100.00)
29	2/7/2020	Cash In		(300.00)
30	2/14/2020	Cash In			(10.00)
31	2/20/2020	Cash In			(75.00)
32	2/28/2020	Cash In		(5.00)
33	3/17/2020	Cash In	(50.00)
34	3/24/2020	Cash Out	20.00
35	3/30/2020	Cash Out		70.00
36	4/7/2020	Cash Out			5.00
37	4/23/2020	Cash Out			5.00
38	1/5/2021	Cash Out		10.00
39	1/6/2021	Cash Out	10.00
40	2/7/2021	Cash Out			10.00
41	10/31/2020	Valuation	315.19	262.66	84.05
42	12/31/2020	Valuation	307.50	256.25	82.00
43	9/30/2020	Valuation	300.00	250.00	80.00
44	2/28/2021	Valuation	315.35	262.79	84.09
45
46						B47:F136 CONTAIN XIRR CALCULATIONS WITH MANUAL TREATMENT OF DATA TO ACHIEVE REAL RESULTS
47	1/1/2020	Cash In	(100.00)
48	1/8/2020	Cash In	(5.00)
49	1/14/2020	Cash In	(5.00)
50	1/22/2020	Cash In	(100.00)
51	3/17/2020	Cash In	(50.00)
52	3/24/2020	Cash Out	20.00
53	10/31/2020	Valuation	315.19
54		XIRR	41.57%
55
56	1/1/2020	Cash In	(100.00)
57	1/8/2020	Cash In	(5.00)
58	1/14/2020	Cash In	(5.00)
59	1/22/2020	Cash In	(100.00)
60	3/17/2020	Cash In	(50.00)
61	3/24/2020	Cash Out	20.00
62	12/31/2020	Valuation	307.50
63		XIRR	29.77%
64
65	1/1/2020	Cash In	(100.00)
66	1/8/2020	Cash In	(5.00)
67	1/14/2020	Cash In	(5.00)
68	1/22/2020	Cash In	(100.00)
69	3/17/2020	Cash In	(50.00)
70	3/24/2020	Cash Out	20.00
71	9/30/2020	Valuation	300.00
72		XIRR	37.61%
73
74	1/1/2020	Cash In	(100.00)
75	1/8/2020	Cash In	(5.00)
76	1/14/2020	Cash In	(5.00)
77	1/22/2020	Cash In	(100.00)
78	3/17/2020	Cash In	(50.00)
79	3/24/2020	Cash Out	20.00
80	1/6/2021	Cash Out	10.00
81	2/28/2021	Valuation	315.35
82		XIRR	31.60%
83
84	2/7/2020	Cash In		(300.00)
85	2/28/2020	Cash In		(5.00)
86	3/30/2020	Cash Out		70.00
87	10/31/2020	Valuation		262.66
88		XIRR		15.50%
89
90	2/7/2020	Cash In		(300.00)
91	2/28/2020	Cash In		(5.00)
92	3/30/2020	Cash Out		70.00
93	12/31/2020	Valuation		256.25
94		XIRR		9.65%
95
96	2/7/2020	Cash In		(300.00)
97	2/28/2020	Cash In		(5.00)
98	3/30/2020	Cash Out		70.00
99	9/30/2020	Valuation		250.00
100		XIRR		9.42%
101
102	2/7/2020	Cash In		(300.00)
103	2/28/2020	Cash In		(5.00)
104	3/30/2020	Cash Out		70.00
105	1/5/2021	Cash Out		10.00
106	2/28/2021	Valuation		262.79
107		XIRR		14.58%
108
109	2/14/2020	Cash In			(10.00)
110	2/20/2020	Cash In			(75.00)
111	4/7/2020	Cash Out			5.00
112	4/23/2020	Cash Out			5.00
113	10/31/2020	Valuation			84.05
114		XIRR			17.20%
115
116	2/14/2020	Cash In			(10.00)
117	2/20/2020	Cash In			(75.00)
118	4/7/2020	Cash Out			5.00
119	4/23/2020	Cash Out			5.00
120	12/31/2020	Valuation			82.00
121		XIRR			10.61%
122
123	2/14/2020	Cash In			(10.00)
124	2/20/2020	Cash In			(75.00)
125	4/7/2020	Cash Out			5.00
126	4/23/2020	Cash Out			5.00
127	9/30/2020	Valuation			80.00
128		XIRR			10.73%
129
130	2/14/2020	Cash In			(10.00)
131	2/20/2020	Cash In			(75.00)
132	4/7/2020	Cash Out			5.00
133	4/23/2020	Cash Out			5.00
134	2/7/2021	Cash Out			10.00
135	2/28/2021	Valuation			84.09
136		XIRR			24.36%
Mr Excel ready

Cell Formulas
Range	Formula
D10:F10	D10	=+XIRR(INDEX(D25:D44,D14):INDEX(D25:D44,D15),INDEX($B$25:$B$44,D14):INDEX($B$25:$B$44,D15))
D13	D13	=D9
E13	E13	=D9
F13	F13	=D9
D14:F14	D14	=MATCH(TRUE,INDEX(D25:D44<>0,0),0)
D15:F15	D15	=MATCH(1,($C$25:$C$44<>$B$17)*($B$25:$B$44<=D$13),1)
D18,B74:D79,B65:D70,B56:D61	D18	=D9
E18	E18	=D9
F18	F18	=D9
D19:F19	D19	=MATCH(TRUE,INDEX(D25:D44<>0,0),0)
D20:F20	D20	=+MATCH(1,($C$25:$C$44=$B$17)*($B$25:$B$44=D$18),0)
B47:D50	B47	=B25
B51:D52	B51	=B33
B53:D53	B53	=B41
D54,D72,D63	D54	=XIRR(D47:D53,B47:B53)
B62:D62	B62	=B42
B71:D71	B71	=B43
B80:D80	B80	=B39
B81:D81	B81	=B44
D82	D82	=XIRR(D74:D81,B74:B81)
B84:C84,E84	B84	=B29
B85:C85,E85	B85	=B32
B86:C86,E93,B93:C93,E86	B86	=B35
B87:C87,E87	B87	=B41
E88,E100,E94	E88	=XIRR(E84:E87,B84:B87)
B90:C92,E102:E104,B102:C104,E96:E98,B96:C98,E90:E92	B90	=B84
B99:C99,E99	B99	=B43
B105:C105,E105	B105	=B38
B106:C106,E106	B106	=B44
E107	E107	=XIRR(E102:E106,B102:B106)
B109:C110,F109:F110	B109	=B30
B111:C112,F111:F112	B111	=B36
B113:C113,F113	B113	=B41
F114,F128,F121	F114	=XIRR(F109:F113,B109:B113)
B116:C119,F130:F133,B130:C133,F123:F126,B123:C126,F116:F119	B116	=B109
B120:C120,F120	B120	=B42
B127:C127,F127	B127	=B43
B134:C134,F134	B134	=B40
B135:C135,F135	B135	=B44
F136	F136	=XIRR(F130:F135,B130:B135)

 

[TheVillageIdiot]

The main problem you will have is an initial zero in the Dates column. That messes up the XIRR function (trailing zeroes are fine).

I took your first series and loaded the data in A1:C21, with the first row a header row.

In L2, I placed the valuation reference date option -- just got it out of the way of the data. It can be a dropdown/picklist. You choose the valuation date here and the data calculations should reference it.

In D2, I used the following formula and copied down to D21
IF(A2>$L$2,0,IF(A2=$L$2,C2,IF(B2="Valuation",0,C2)))

In L3, I entered the XIRR formula:
=XIRR(INDIRECT("D"&MATCH(TRUE,C2:C21<>0,0)+1&":D"&MATCH(9.9E+307,A:A)),INDIRECT("A"&MATCH(TRUE,C2:C21<>0,0)+1&":A"&MATCH(9.9E+307,A:A)))

This matched your results in my limited testing.

Key Idea:
You have to find the first non-zero data point and start from there.
=MATCH(TRUE,C2:C21<>0,0) will find it. Add 1 because you are starting in row 2.

The last data point can be found with MATCH(9.9E+307,A:A) (or MATCH(9.9E+307,C:C)). Note the missing optional third parameter. We don't want to find an exact match, just the last row.

This can be made fully dynamic, but this worked for me. You can play around with a guess for datasets with a lot of sign changes in the cashflows.

 

[plshelpexcel]

The main problem you will have is an initial zero in the Dates column. That messes up the XIRR function (trailing zeroes are fine).

I took your first series and loaded the data in A1:C21, with the first row a header row.

In L2, I placed the valuation reference date option -- just got it out of the way of the data. It can be a dropdown/picklist. You choose the valuation date here and the data calculations should reference it.

In D2, I used the following formula and copied down to D21
IF(A2>$L$2,0,IF(A2=$L$2,C2,IF(B2="Valuation",0,C2)))

In L3, I entered the XIRR formula:
=XIRR(INDIRECT("D"&MATCH(TRUE,C2:C21<>0,0)+1&":D"&MATCH(9.9E+307,A:A)),INDIRECT("A"&MATCH(TRUE,C2:C21<>0,0)+1&":A"&MATCH(9.9E+307,A:A)))

This matched your results in my limited testing.

Key Idea:
You have to find the first non-zero data point and start from there.
=MATCH(TRUE,C2:C21<>0,0) will find it. Add 1 because you are starting in row 2.

The last data point can be found with MATCH(9.9E+307,A:A) (or MATCH(9.9E+307,C:C)). Note the missing optional third parameter. We don't want to find an exact match, just the last row.

This can be made fully dynamic, but this worked for me. You can play around with a guess for datasets with a lot of sign changes in the cashflows.

Thank you for the proposed solution!