Using a dynamic formula to calculate value of reinvested dividends over changing time periods

[Tuckbox]

Hi Everyone,

I am trying to calculate the value of reinvested dividends on a daily basis over changing time periods. I can't seem to find an ideal formula that works for me without the dynamic array values requiring the value above it to calculate itself.

All I want to do is create a dynamic array that will identify if a dividend is paid on a date which will then be "reinvested' back into the company and grow at the same rate that the share price moves. All the dividends must be reinvested at the dates they are paid out (in my case go ex-div) and accumulate on top of the dividends that have been reinvested and grown at prior dates.

And if I extend the time period the calculations will change to accommodate a longer or shorter period which I can plot on a chart.

In the mini-sheet, dynamic arrays for the share price and price returns are dynamic (grey) but the dividend reinvestment array is static (white). The dividend information (Amount and date) are in the table on the right.

Also the start date is assumed to be when an investor buys the share. So any dividends paid out before the start period are irrelevant as they would not have received those amounts.

I'm not too sure if this is possible but I seriously would appreciate the help. I've been stuck on it for a while.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2													DIVIDEND INFORMATION
3		PUBLIC STORAGE (XNYS:PSA)				Date	Share Price	Share Return	Reinvested Dividend	Total Return Share Price			Ex-Dividend Date	Dividends
4						19 08 2016	224.03		0.000	224.03			14 06 2021	2.00
5						22 08 2016	226.78	1.23%	0.000	226.78			15 03 2021	2.00
6		Input				23 08 2016	226.51	-0.12%	0.000	226.51			14 12 2020	2.00
7		Period				24 08 2016	223.93	-1.14%	0.000	223.93			14 09 2020	2.00
8		Years	5			25 08 2016	225.74	0.81%	0.000	225.74			12 06 2020	2.00
9		Months	0			26 08 2016	224.00	-0.77%	0.000	224.00			13 03 2020	2.00
10		Days	0			29 08 2016	225.74	0.78%	0.000	225.74			12 12 2019	2.00
11						30 08 2016	223.77	-0.87%	0.000	223.77			11 09 2019	2.00
12		Interval				31 08 2016	223.94	0.08%	0.000	223.94			11 06 2019	2.00
13		Years	0			01 09 2016	226.11	0.97%	0.000	226.11			12 03 2019	2.00
14		Months	0			02 09 2016	226.79	0.30%	0.000	226.79			11 12 2018	2.00
15		Days	1			06 09 2016	224.37	-1.07%	0.000	224.37			11 09 2018	2.00
16						07 09 2016	225.93	0.70%	0.000	225.93			12 06 2018	2.00
17		STKHIS				08 09 2016	223.00	-1.30%	0.000	223.00			13 03 2018	2.00
18		Daily	0			09 09 2016	218.58	-1.98%	0.000	218.58			12 12 2017	2.00
19						12 09 2016	219.00	0.19%	1.800	220.80			12 09 2017	2.00
20		Output				13 09 2016	214.75	-1.94%	1.765	216.52			12 06 2017	2.00
21		End Period	19 08 2021			14 09 2016	215.10	0.16%	1.768	216.87			13 03 2017	2.00
22		Start Period	19 08 2016			15 09 2016	214.80	-0.14%	1.765	216.57			12 12 2016	2.00
23						16 09 2016	214.96	0.07%	1.767	216.73			12 09 2016	1.80
24						19 09 2016	216.07	0.52%	1.776	217.85			13 06 2016	1.80
25						20 09 2016	216.84	0.36%	1.782	218.62			14 03 2016	1.70
26						21 09 2016	217.53	0.32%	1.788	219.32			11 12 2015	1.70
27						22 09 2016	221.80	1.96%	1.823	223.62			11 09 2015	1.70
28						23 09 2016	222.12	0.14%	1.826	223.95			11 06 2015	1.70
29						26 09 2016	223.95	0.82%	1.841	225.79			12 03 2015	1.40
30						27 09 2016	222.94	-0.45%	1.832	224.77			11 12 2014	1.40
31						28 09 2016	225.83	1.30%	1.856	227.69			11 09 2014	1.40
32						29 09 2016	224.99	-0.37%	1.849	226.84			11 06 2014	1.40
33						30 09 2016	223.14	-0.82%	1.834	224.97			12 03 2014	1.40
34						03 10 2016	216.56	-2.95%	1.780	218.34			11 12 2013	1.40
35						04 10 2016	212.66	-1.80%	1.748	214.41			11 09 2013	1.25
36						05 10 2016	208.89	-1.77%	1.717	210.61			10 06 2013	1.25
37						06 10 2016	211.24	1.12%	1.736	212.98			11 03 2013	1.25
38						07 10 2016	212.05	0.38%	1.743	213.79			10 12 2012	1.10
39						10 10 2016	211.59	-0.22%	1.739	213.33			10 09 2012	1.10
40						11 10 2016	209.72	-0.88%	1.724	211.44			11 06 2012	1.10
41						12 10 2016	212.77	1.45%	1.749	214.52			12 03 2012	1.10
42						13 10 2016	214.23	0.69%	1.761	215.99			12 12 2011	0.95
43						14 10 2016	214.20	-0.01%	1.761	215.96			12 09 2011	0.95
44						17 10 2016	215.11	0.42%	1.768	216.88			13 06 2011	0.95
45						18 10 2016	215.83	0.33%	1.774	217.60			11 03 2011	0.80
46						19 10 2016	215.19	-0.30%	1.769	216.96			13 12 2010	0.80
47						20 10 2016	214.12	-0.50%	1.760	215.88			13 09 2010	0.80
48						21 10 2016	211.05	-1.43%	1.735	212.78			11 06 2010	0.80
49						24 10 2016	211.50	0.21%	1.738	213.24			11 03 2010	0.65
50						25 10 2016	212.35	0.40%	1.745	214.10			11 12 2009	0.55
51						26 10 2016	213.28	0.44%	1.753	215.03			11 09 2009	0.55
52						27 10 2016	202.25	-5.17%	1.662	203.91			11 06 2009	0.55
53						28 10 2016	207.75	2.72%	1.708	209.46			12 03 2009	0.55
54						31 10 2016	213.72	2.87%	1.757	215.48			11 12 2008	1.15
55						01 11 2016	205.72	-3.74%	1.691	207.41			11 09 2008	0.55
56						02 11 2016	205.26	-0.22%	1.687	206.95			11 06 2008	0.55
57						03 11 2016	203.76	-0.73%	1.675	205.43			12 03 2008	0.55
58						04 11 2016	205.44	0.82%	1.689	207.13			11 12 2007	0.50
59						07 11 2016	211.73	3.06%	1.740	213.47			10 09 2007	0.50
60						08 11 2016	216.38	2.20%	1.778	218.16			13 06 2007	0.50
61						09 11 2016	217.46	0.50%	1.787	219.25			13 03 2007	0.50
62						10 11 2016	214.74	-1.25%	1.765	216.50			13 12 2006	0.50
63						11 11 2016	214.18	-0.26%	1.760	215.94			13 09 2006	0.50
64						14 11 2016	216.45	1.06%	1.779	218.23			31 05 2006	0.50
65						15 11 2016	212.12	-2.00%	1.743	213.86			13 03 2006	0.50
66						16 11 2016	208.21	-1.84%	1.711	209.92			13 12 2005	0.50
67						17 11 2016	204.61	-1.73%	1.682	206.29			13 09 2005	0.50
68						18 11 2016	203.97	-0.31%	1.676	205.65			13 06 2005	0.45
69						21 11 2016	201.93	-1.00%	1.660	203.59			11 03 2005	0.45
70						22 11 2016	205.10	1.57%	1.686	206.79			13 12 2004	0.45
71						23 11 2016	204.76	-0.17%	1.683	206.44			13 09 2004	0.45
72						25 11 2016	205.73	0.47%	1.691	207.42			14 06 2004	0.45
73						28 11 2016	209.49	1.83%	1.722	211.21			11 03 2004	0.45
74						29 11 2016	209.61	0.06%	1.723	211.33			11 12 2003	0.45
75						30 11 2016	209.30	-0.15%	1.720	211.02			11 09 2003	0.45
76						01 12 2016	207.86	-0.69%	1.708	209.57			12 06 2003	0.45
77						02 12 2016	209.48	0.78%	1.722	211.20			12 03 2003	0.45
78						05 12 2016	212.45	1.42%	1.746	214.20			11 12 2002	0.45
79						06 12 2016	214.71	1.06%	1.765	216.47			11 09 2002	0.45
80						07 12 2016	221.01	2.93%	1.817	222.83			12 06 2002	0.45
81						08 12 2016	221.06	0.02%	1.817	222.88			13 03 2002	0.45
82						09 12 2016	219.13	-0.87%	1.801	220.93			12 12 2001	0.45
83						12 12 2016	217.67	-0.67%	3.789	221.46			17 09 2001	0.80
84						13 12 2016	218.38	0.33%	3.801	222.18			13 06 2001	0.22
85						14 12 2016	215.20	-1.46%	3.746	218.95			13 03 2001	0.22
86						15 12 2016	216.29	0.51%	3.765	220.06			13 12 2000	0.22
87						16 12 2016	218.73	1.13%	3.808	222.54			13 09 2000	0.82
88						19 12 2016	220.37	0.75%	3.836	224.21			13 06 2000	0.22
89						20 12 2016	217.84	-1.15%	3.792	221.63			13 03 2000	0.22
90						21 12 2016	215.13	-1.24%	3.745	218.87			13 12 1999	0.22
91						22 12 2016	217.26	0.99%	3.782	221.04			10 11 1999	0.62
92						23 12 2016	218.72	0.67%	3.807	222.53			13 09 1999	0.22
93						27 12 2016	220.14	0.65%	3.832	223.97			11 06 1999	0.22
94						28 12 2016	218.28	-0.84%	3.800	222.08			11 03 1999	0.22
95						29 12 2016	220.40	0.97%	3.837	224.24			07 12 1998	0.22
96						30 12 2016	223.50	1.41%	3.891	227.39			09 09 1998	0.22
97						03 01 2017	223.52	0.01%	3.891	227.41			11 06 1998	0.22
98						04 01 2017	225.16	0.73%	3.919	229.08			12 03 1998	0.22
99						05 01 2017	227.56	1.07%	3.961	231.52			11 12 1997	0.22
100						06 01 2017	228.35	0.35%	3.975	232.32			11 09 1997	0.22
101						09 01 2017	226.79	-0.68%	3.948	230.74			12 06 1997	0.22
102						10 01 2017	225.30	-0.66%	3.922	229.22			12 03 1997	0.22
103						11 01 2017	219.76	-2.46%	3.825	223.59			11 12 1996	0.22
104						12 01 2017	217.30	-1.12%	3.783	221.08
105						13 01 2017	214.12	-1.46%	3.727	217.85
106						17 01 2017	217.69	1.67%	3.789	221.48
107						18 01 2017	215.60	-0.96%	3.753	219.35
Sheet1

Cell Formulas
Range	Formula
F4:G1261	F4	=STOCKHISTORY(B3,C22,C21,C18,0,0,1)
H4:H1261	H4	=IFERROR((INDEX($F$4#,,2)/VLOOKUP(DATE(YEAR(INDEX($F$4#,,1))-$C$13,MONTH(INDEX($F$4#,,1))-$C$14,DAY(INDEX($F$4#,,1))-$C$15),$F$4#,2,TRUE))-1,"")
I4:I107	I4	=IFERROR(I3*(H4+1),0)+IFERROR(XLOOKUP(F4,Dividends__YAHOO[Ex-Dividend Date],Dividends__YAHOO[Dividends]),0)
J4:J107	J4	=I4+G4
C18	C18	=IFS(B18="Daily",0,B18="Weekly",1,B18="Monthly",2)
C21	C21	=TODAY()
C22	C22	=DATE(YEAR(C21)-C8,MONTH(C21)-C9,DAY(C21)-C10)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F4:H3000	Cell	does not contain a blank value	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B18	List	Daily,Weekly,Monthly

Also asked here Using a dynamic formula to calculate value of reinvested dividends over time periods

 

[StephenCrump]

Welcome to the Forum!

Perhaps along these lines (assuming the reinvestment program allows purchase of fractional shares?):

	A	B	C	D	E
1	Dividend				Share
2	Date	Amount		Date	Price
3	30 Jun 2020	$3.00		29 Jun 2020	$99.00
4	31 Dec 2020	$5.00		30 Jun 2020	$100.00
5	30 Jun 2021	$7.00		30 Sep 2020	$100.00
6				31 Dec 2020	$110.00
7	Current Value	$16.32		31 Mar 2021	$112.00
8				30 Jun 2021	$120.00
9				31 Jul 2021	$115.00
10
11				CURRENT	$122.00
Sheet2

Cell Formulas
Range	Formula
B7	B7	=SUM(B3:B5*CurrentPrice/VLOOKUP(A3:A5,SharePrice,2,1))

Named Ranges
Name	Refers To	Cells
CurrentPrice	=Sheet2!$E$11	B7
SharePrice	=Sheet2!$D$3:$E$9	B7

 

[Tuckbox]

Hi Stephen

Thank you so much for the response! So you can assume that the reinvestment program takes into consideration fractional shares. With regards to my issue I use the stock history function to pull through share price data over a period of time which is displayed as a dynamic array and I am trying to get the dividend reinvestment amount to trail next to the share price at the dates given. So with your example would it be possible to add a third column on the right of "Share Price" and track the value of the reinvestment dividend dynamically so if I added on an additional date it would automatically add the value of the new reinvestment amount or even if I changed the period from 29 Jun 2020 - 31 July 2021 to 29 Jun 2017 - 31 July 2018 it would automatically change the share price and add the appropriate dividends paid in that period.

I almost want what I did in Column I (Dividend Reinvestment) on my mini sheet but in a dynamic/spill array. I hope that I am making sense and really appreciate your help.

Welcome to the Forum!

Perhaps along these lines (assuming the reinvestment program allows purchase of fractional shares?):

	A	B	C	D	E
1	Dividend				Share
2	Date	Amount		Date	Price
3	30 Jun 2020	$3.00		29 Jun 2020	$99.00
4	31 Dec 2020	$5.00		30 Jun 2020	$100.00
5	30 Jun 2021	$7.00		30 Sep 2020	$100.00
6				31 Dec 2020	$110.00
7	Current Value	$16.32		31 Mar 2021	$112.00
8				30 Jun 2021	$120.00
9				31 Jul 2021	$115.00
10
11				CURRENT	$122.00
Sheet2

Cell Formulas
Range	Formula
B7	B7	=SUM(B3:B5*CurrentPrice/VLOOKUP(A3:A5,SharePrice,2,1))

Named Ranges
Name	Refers To	Cells
CurrentPrice	=Sheet2!$E$11	B7
SharePrice	=Sheet2!$D$3:$E$9	B7