Weighted average date for stock portfolio

[gpalmans]

Hi all,

been trying for hour and hours. Probably read half of the Internet by now. Still no luck getting this to work. Would appreciate if anyone has some insights or maybe even a solution.

I keep a spreadsheet to track my investment portofolio and I'm trying to calculate the annualized return (%) for active positions I hold.
Each position can have multiple transactions: buy, dividents,sell, re-buy,... and all the data will be in non-contiguous lines.
I keep a seperate tab with the summary of each position. This is where I'd also like to show the annualized return.

The challenge I'm having, is to calculate the position's offset date to be used in the annualized return calculation.
It seems to work just fine for most of my stock/positions, but for some it comes up with a rediculous date (sometimes centuries into the future).

Hard to explain, and a lot of data/formulas involved. I made a small sample sheet to show what I'm trying to achieve, which can be found here (<-- click)

I tried to just use regular 'average' to calculate the date, but the results are not exaclty correct I believe, and this is supposed to be an exact science .

Who can help me out here? You get to be my hero for the day!

 

[gpalmans]

Not sure if the link to the acutal spreadsheet I put in my original post is allowed, so adding info below:

'Overview' tab:

Stock.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Stock Name	Last Price	Shares	Cost	Dividends Collected	Mkt Value	Date 1	YoY (%)	Date 2	YoY (%)
2	Google	$ 1 395,77	2	$ 963,66	$ -	$ 2 791,54	12/07/2015	23%	4/10/2015	24%
3	Cisco	$ 35,69	174	$ 5 520,14	$ 615,85	$ 6 210,06	5/05/2431		30/12/2017	8%
4	Robeco BP Global	$ 244,60	11,59	$ 2 841,23	$ -	$ 2 834,91	22/04/2124		9/06/2018	0%
5	United Health	$ 260,88	15	$ 828,74	$ 155,49	$ 3 913,20	19/11/2013	26%	22/11/2013	27%
Overview

Cell Formulas
Range	Formula
E2:E5	E2	=SUMIFS(Transactions!$I$2:$I$55,Transactions!$B$2:$B$55,"Div",Transactions!$C$2:$C$55,Overview!$A2)
F2:F5	F2	=C2*B2
G2:G5	G2	=IF(A2="","",ROUND(SUMPRODUCT(--(Transactions!$B$2:$B$55="Buy"),--(Transactions!$C$2:$C$55=Overview!A2),Transactions!$A$2:$A$55,Transactions!$I$2:$I$55)/Overview!D2,0))
H2:H5	H2	=IF(A2="","",IF(TODAY()-G2>365,(((F2+E2)/D2)^(365.25/(TODAY()-G2)))-1,""))
I2:I5	I2	=ROUND(AVERAGEIFS(Transactions!$A$2:$A$55,Transactions!$B$2:$B$55,"Buy",Transactions!$C$2:$C$55,Overview!A2),0)
J2:J5	J2	=IF(A2="","",IF(TODAY()-I2>365,(((F2+E2)/D2)^(365.25/(TODAY()-I2)))-1,""))
C2:C5	C2	=SUMIFS(Transactions!$D$2:$D$55,Transactions!$B$2:$B$55,"Buy",Transactions!$C$2:$C$55,Overview!A2)-SUMIFS(Transactions!$D$2:$D$55,Transactions!$B$2:$B$55,"Sell",Transactions!$C$2:$C$55,Overview!A2)

'Transactions' tab:

Stock.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	Type	Stock Name	Units	Price	Fees	Previous Units	Cum. Units	Value	Previous Cost	Cost of Transaction	Cum. Cost
2	22/11/2013	Buy	United Health	15	$ 54,26	$ 14,78	0	15	$ 828,68	$ -	-	$ 828,68
3	17/12/2013	Div	United Health	15	$ 0,20	$ 1,09	15	15	$ 1,91	$ 828,68	-	$ 828,68
4	25/03/2014	Div	United Health	15	$ 0,20	$ 1,09	15	15	$ 1,91	$ 828,68	-	$ 828,68
5	25/06/2014	Div	United Health	15	$ 0,28	$ 1,52	15	15	$ 2,68	$ 828,68	-	$ 828,68
6	23/09/2014	Div	United Health	15	$ 0,29	$ 1,58	15	15	$ 2,77	$ 828,68	-	$ 828,68
7	16/12/2014	Div	United Health	15	$ 0,30	$ 1,63	15	15	$ 2,87	$ 828,68	-	$ 828,68
8	24/03/2015	Div	United Health	15	$ 0,35	$ 1,90	15	15	$ 3,35	$ 828,68	-	$ 828,68
9	24/06/2015	Div	United Health	15	$ 0,44	$ 2,39	15	15	$ 4,21	$ 828,68	-	$ 828,68
10	22/09/2015	Div	United Health	15	$ 0,45	$ 2,45	15	15	$ 4,30	$ 828,68	-	$ 828,68
11	4/10/2015	Buy	Google	2	$ 472,84	$ 16,08	0	2	$ 961,76	$ -	-	$ 961,76
12	15/12/2015	Div	United Health	15	$ 0,47	$ 2,56	15	15	$ 4,49	$ 828,68	-	$ 828,68
13	22/03/2016	Div	United Health	15	$ 0,45	$ 2,45	15	15	$ 4,30	$ 828,68	-	$ 828,68
14	7/04/2016	Buy	Robeco BP Global	12	$ 215,75	$ 9,95	0	12	$ 2 510,49	$ -	-	$ 2 510,49
15	28/06/2016	Div	United Health	15	$ 0,55	$ 2,99	15	15	$ 5,26	$ 828,68	-	$ 828,68
16	20/09/2016	Div	United Health	15	$ 0,56	$ 3,05	15	15	$ 5,36	$ 828,68	-	$ 828,68
17	13/12/2016	Div	United Health	15	$ 0,59	$ 3,21	15	15	$ 5,64	$ 828,68	-	$ 828,68
18	30/12/2016	Buy	Cisco	175	$ 23,53	$ -	0	175	$ 4 117,75	$ -	-	$ 4 117,75
19	25/01/2017	Div	Cisco	175	$ 0,24	$ 6,30	175	175	$ 35,70	$ 4 117,65	-	$ 4 117,65
20	27/02/2017	Sell	Cisco	100	$ 32,26	$ 45,00	175	75	$ 3 181,00	$ 4 117,65	$ 2 352,94	$ 1 764,71
21	21/03/2017	Div	United Health	15	$ 0,58	$ 3,15	15	15	$ 5,55	$ 828,68	-	$ 828,68
22	26/04/2017	Div	Cisco	75	$ 0,27	$ 3,04	75	75	$ 17,21	$ 1 764,71	-	$ 1 764,71
23	15/05/2017	Sell	Cisco	75	$ 30,06	$ 45,00	75	0	$ 2 209,50	$ 1 764,71	$ 1 764,71	$ -
24	27/06/2017	Div	United Health	15	$ 0,56	$ 3,05	15	15	$ 5,36	$ 828,68	-	$ 828,68
25	30/06/2017	Buy	Cisco	205	$ 21,53	$ -	0	205	$ 4 413,65	$ -	-	$ 4 413,65
26	26/07/2017	Div	Cisco	205	$ 0,25	$ 7,69	205	205	$ 43,56	$ 4 414,50	-	$ 4 414,50
27	19/09/2017	Div	United Health	15	$ 0,62	$ 3,37	15	15	$ 5,93	$ 828,68	-	$ 828,68
28	25/10/2017	Div	Cisco	205	$ 0,25	$ 7,69	205	205	$ 43,56	$ 4 414,50	-	$ 4 414,50
29	12/12/2017	Div	United Health	15	$ 0,63	$ 3,43	15	15	$ 6,02	$ 828,68	-	$ 828,68
30	29/12/2017	Buy	Cisco	257	$ 20,38	$ -	205	462	$ 5 237,66	$ 4 414,50	-	$ 9 652,16
31	24/01/2018	Div	Cisco	462	$ 0,23	$ 15,94	462	462	$ 90,32	$ 9 652,93	-	$ 9 652,93
32	7/02/2018	Sell	Cisco	462	$ 32,71	$ 40,00	462	0	$ 15 072,02	$ 9 652,93	$ 9 652,93	$ -
33	20/03/2018	Div	United Health	15	$ 0,61	$ 3,32	15	15	$ 5,83	$ 828,68	-	$ 828,68
34	26/06/2018	Div	United Health	15	$ 0,77	$ 4,19	15	15	$ 7,36	$ 828,68	-	$ 828,68
35	29/06/2018	Buy	Cisco	265	$ 20,95	$ -	0	265	$ 5 551,75	$ -	-	$ 5 551,75
36	20/07/2018	Sell	Cisco	265	$ 35,45	$ 42,00	265	0	$ 9 352,25	$ 5 551,73	$ 5 551,73	$ -
37	25/07/2018	Div	Cisco	265	$ 0,28	$ 11,13	0	0	$ 63,07	$ -	-	$ -
38	18/09/2018	Div	United Health	15	$ 0,77	$ 4,19	15	15	$ 7,36	$ 828,68	-	$ 828,68
39	13/12/2018	Div	United Health	15	$ 0,79	$ 4,30	15	15	$ 7,55	$ 828,68	-	$ 828,68
40	31/12/2018	Buy	Cisco	174	$ 31,72	$ -	0	174	$ 5 519,28	$ -	-	$ 5 519,28
41	23/01/2019	Div	Cisco	174	$ 0,29	$ 7,57	174	174	$ 42,89	$ 5 520,14	-	$ 5 520,14
42	19/03/2019	Div	United Health	15	$ 0,79	$ 4,30	15	15	$ 7,55	$ 828,68	-	$ 828,68
43	24/04/2019	Div	Cisco	174	$ 0,31	$ 8,09	174	174	$ 45,85	$ 5 520,14	-	$ 5 520,14
44	25/06/2019	Div	United Health	15	$ 0,95	$ 5,17	15	15	$ 9,08	$ 828,68	-	$ 828,68
45	24/07/2019	Div	Cisco	174	$ 0,31	$ 8,09	174	174	$ 45,85	$ 5 520,14	-	$ 5 520,14
46	24/09/2019	Div	United Health	15	$ 0,98	$ 5,33	15	15	$ 9,37	$ 828,68	-	$ 828,68
47	23/10/2019	Div	Cisco	174	$ 0,31	$ 8,09	174	174	$ 45,85	$ 5 520,14	-	$ 5 520,14
48	17/12/2019	Div	United Health	15	$ 0,97	$ 5,27	15	15	$ 9,28	$ 828,68	-	$ 828,68
49	22/01/2020	Div	Cisco	174	$ 0,32	$ 8,35	174	174	$ 47,33	$ 5 520,14	-	$ 5 520,14
50	24/03/2020	Div	United Health	15	$ 1,00	$ 5,44	15	15	$ 9,56	$ 828,68	-	$ 828,68
51	22/04/2020	Div	Cisco	174	$ 0,33	$ 8,61	174	174	$ 48,81	$ 5 520,14	-	$ 5 520,14
52	30/06/2020	Div	United Health	15	$ 1,11	$ 6,04	15	15	$ 10,61	$ 828,68	-	$ 828,68
53	22/07/2020	Div	Cisco	174	$ 0,31	$ 8,09	174	174	$ 45,85	$ 5 520,14	-	$ 5 520,14
54	8/08/2020	Sell	Robeco BP Global	12	$ 245,20	$ 37,50	12	0	$ 2 804,37	$ 2 510,49	$ 2 510,49	$ -
55	10/08/2020	Buy	Robeco BP Global	12	$ 247,10	$ -	0	12	$ 2 863,89	$ -	-	$ 2 863,89
Transactions

Cell Formulas
Range	Formula
K2:K55	K2	=IF(C2="","",IF(B2="Sell",IF(G2=0,0,D2/G2*J2),"-"))
L2:L55	L2	=IF(C2="","",IF(B2="Buy",I2+J2,IF(B2="Div",J2,IF(B2="Sell",IF(J2<=0,0,J2-K2)))))
F52,F50,F48,F46,F44,F42,F38:F39,F33:F34,F29,F27,F24,F21,F15:F17,F12:F13,F3:F10	F3	=(D3*E3)*0.3625
F11	F11	=(D11*E11)*0.017
F19,F53,F51,F49,F47,F45,F43,F41,F37,F31,F28,F26,F22	F19	=D19*E19*0.15
I2:I55	I2	=IF(C2="","",IF(B2="Buy",D2*E2+F2,IF(B2="Sell",D2*E2-F2,D2*E2-F2)))

 

[gpalmans]

No one?