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

Tuckbox

New Member
Joined
Aug 19, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOP
1
2DIVIDEND INFORMATION
3PUBLIC STORAGE (XNYS:PSA)Date Share Price Share ReturnReinvested DividendTotal Return Share PriceEx-Dividend DateDividends
419 08 2016224.03 0.000224.0314 06 20212.00
522 08 2016226.781.23%0.000226.7815 03 20212.00
6Input23 08 2016226.51-0.12%0.000226.5114 12 20202.00
7Period24 08 2016223.93-1.14%0.000223.9314 09 20202.00
8Years525 08 2016225.740.81%0.000225.7412 06 20202.00
9Months026 08 2016224.00-0.77%0.000224.0013 03 20202.00
10Days029 08 2016225.740.78%0.000225.7412 12 20192.00
1130 08 2016223.77-0.87%0.000223.7711 09 20192.00
12Interval31 08 2016223.940.08%0.000223.9411 06 20192.00
13Years001 09 2016226.110.97%0.000226.1112 03 20192.00
14Months002 09 2016226.790.30%0.000226.7911 12 20182.00
15Days106 09 2016224.37-1.07%0.000224.3711 09 20182.00
1607 09 2016225.930.70%0.000225.9312 06 20182.00
17STKHIS08 09 2016223.00-1.30%0.000223.0013 03 20182.00
18Daily009 09 2016218.58-1.98%0.000218.5812 12 20172.00
1912 09 2016219.000.19%1.800220.8012 09 20172.00
20Output13 09 2016214.75-1.94%1.765216.5212 06 20172.00
21End Period19 08 202114 09 2016215.100.16%1.768216.8713 03 20172.00
22Start Period19 08 201615 09 2016214.80-0.14%1.765216.5712 12 20162.00
2316 09 2016214.960.07%1.767216.7312 09 20161.80
2419 09 2016216.070.52%1.776217.8513 06 20161.80
2520 09 2016216.840.36%1.782218.6214 03 20161.70
2621 09 2016217.530.32%1.788219.3211 12 20151.70
2722 09 2016221.801.96%1.823223.6211 09 20151.70
2823 09 2016222.120.14%1.826223.9511 06 20151.70
2926 09 2016223.950.82%1.841225.7912 03 20151.40
3027 09 2016222.94-0.45%1.832224.7711 12 20141.40
3128 09 2016225.831.30%1.856227.6911 09 20141.40
3229 09 2016224.99-0.37%1.849226.8411 06 20141.40
3330 09 2016223.14-0.82%1.834224.9712 03 20141.40
3403 10 2016216.56-2.95%1.780218.3411 12 20131.40
3504 10 2016212.66-1.80%1.748214.4111 09 20131.25
3605 10 2016208.89-1.77%1.717210.6110 06 20131.25
3706 10 2016211.241.12%1.736212.9811 03 20131.25
3807 10 2016212.050.38%1.743213.7910 12 20121.10
3910 10 2016211.59-0.22%1.739213.3310 09 20121.10
4011 10 2016209.72-0.88%1.724211.4411 06 20121.10
4112 10 2016212.771.45%1.749214.5212 03 20121.10
4213 10 2016214.230.69%1.761215.9912 12 20110.95
4314 10 2016214.20-0.01%1.761215.9612 09 20110.95
4417 10 2016215.110.42%1.768216.8813 06 20110.95
4518 10 2016215.830.33%1.774217.6011 03 20110.80
4619 10 2016215.19-0.30%1.769216.9613 12 20100.80
4720 10 2016214.12-0.50%1.760215.8813 09 20100.80
4821 10 2016211.05-1.43%1.735212.7811 06 20100.80
4924 10 2016211.500.21%1.738213.2411 03 20100.65
5025 10 2016212.350.40%1.745214.1011 12 20090.55
5126 10 2016213.280.44%1.753215.0311 09 20090.55
5227 10 2016202.25-5.17%1.662203.9111 06 20090.55
5328 10 2016207.752.72%1.708209.4612 03 20090.55
5431 10 2016213.722.87%1.757215.4811 12 20081.15
5501 11 2016205.72-3.74%1.691207.4111 09 20080.55
5602 11 2016205.26-0.22%1.687206.9511 06 20080.55
5703 11 2016203.76-0.73%1.675205.4312 03 20080.55
5804 11 2016205.440.82%1.689207.1311 12 20070.50
5907 11 2016211.733.06%1.740213.4710 09 20070.50
6008 11 2016216.382.20%1.778218.1613 06 20070.50
6109 11 2016217.460.50%1.787219.2513 03 20070.50
6210 11 2016214.74-1.25%1.765216.5013 12 20060.50
6311 11 2016214.18-0.26%1.760215.9413 09 20060.50
6414 11 2016216.451.06%1.779218.2331 05 20060.50
6515 11 2016212.12-2.00%1.743213.8613 03 20060.50
6616 11 2016208.21-1.84%1.711209.9213 12 20050.50
6717 11 2016204.61-1.73%1.682206.2913 09 20050.50
6818 11 2016203.97-0.31%1.676205.6513 06 20050.45
6921 11 2016201.93-1.00%1.660203.5911 03 20050.45
7022 11 2016205.101.57%1.686206.7913 12 20040.45
7123 11 2016204.76-0.17%1.683206.4413 09 20040.45
7225 11 2016205.730.47%1.691207.4214 06 20040.45
7328 11 2016209.491.83%1.722211.2111 03 20040.45
7429 11 2016209.610.06%1.723211.3311 12 20030.45
7530 11 2016209.30-0.15%1.720211.0211 09 20030.45
7601 12 2016207.86-0.69%1.708209.5712 06 20030.45
7702 12 2016209.480.78%1.722211.2012 03 20030.45
7805 12 2016212.451.42%1.746214.2011 12 20020.45
7906 12 2016214.711.06%1.765216.4711 09 20020.45
8007 12 2016221.012.93%1.817222.8312 06 20020.45
8108 12 2016221.060.02%1.817222.8813 03 20020.45
8209 12 2016219.13-0.87%1.801220.9312 12 20010.45
8312 12 2016217.67-0.67%3.789221.4617 09 20010.80
8413 12 2016218.380.33%3.801222.1813 06 20010.22
8514 12 2016215.20-1.46%3.746218.9513 03 20010.22
8615 12 2016216.290.51%3.765220.0613 12 20000.22
8716 12 2016218.731.13%3.808222.5413 09 20000.82
8819 12 2016220.370.75%3.836224.2113 06 20000.22
8920 12 2016217.84-1.15%3.792221.6313 03 20000.22
9021 12 2016215.13-1.24%3.745218.8713 12 19990.22
9122 12 2016217.260.99%3.782221.0410 11 19990.62
9223 12 2016218.720.67%3.807222.5313 09 19990.22
9327 12 2016220.140.65%3.832223.9711 06 19990.22
9428 12 2016218.28-0.84%3.800222.0811 03 19990.22
9529 12 2016220.400.97%3.837224.2407 12 19980.22
9630 12 2016223.501.41%3.891227.3909 09 19980.22
9703 01 2017223.520.01%3.891227.4111 06 19980.22
9804 01 2017225.160.73%3.919229.0812 03 19980.22
9905 01 2017227.561.07%3.961231.5211 12 19970.22
10006 01 2017228.350.35%3.975232.3211 09 19970.22
10109 01 2017226.79-0.68%3.948230.7412 06 19970.22
10210 01 2017225.30-0.66%3.922229.2212 03 19970.22
10311 01 2017219.76-2.46%3.825223.5911 12 19960.22
10412 01 2017217.30-1.12%3.783221.08
10513 01 2017214.12-1.46%3.727217.85
10617 01 2017217.691.67%3.789221.48
10718 01 2017215.60-0.96%3.753219.35
Sheet1
Cell Formulas
RangeFormula
F4:G1261F4=STOCKHISTORY(B3,C22,C21,C18,0,0,1)
H4:H1261H4=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:I107I4=IFERROR(I3*(H4+1),0)+IFERROR(XLOOKUP(F4,Dividends__YAHOO[Ex-Dividend Date],Dividends__YAHOO[Dividends]),0)
J4:J107J4=I4+G4
C18C18=IFS(B18="Daily",0,B18="Weekly",1,B18="Monthly",2)
C21C21=TODAY()
C22C22=DATE(YEAR(C21)-C8,MONTH(C21)-C9,DAY(C21)-C10)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F4:H3000Celldoes not contain a blank value textNO
Cells with Data Validation
CellAllowCriteria
B18ListDaily,Weekly,Monthly


Also asked here Using a dynamic formula to calculate value of reinvested dividends over time periods
 
Last edited by a moderator:

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,990
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Forum!

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

ABCDE
1DividendShare
2DateAmountDatePrice
330 Jun 2020$3.0029 Jun 2020$99.00
431 Dec 2020$5.0030 Jun 2020$100.00
530 Jun 2021$7.0030 Sep 2020$100.00
631 Dec 2020$110.00
7Current Value$16.3231 Mar 2021$112.00
830 Jun 2021$120.00
931 Jul 2021$115.00
10
11CURRENT$122.00
Sheet2
Cell Formulas
RangeFormula
B7B7=SUM(B3:B5*CurrentPrice/VLOOKUP(A3:A5,SharePrice,2,1))
Named Ranges
NameRefers ToCells
CurrentPrice=Sheet2!$E$11B7
SharePrice=Sheet2!$D$3:$E$9B7
 

Tuckbox

New Member
Joined
Aug 19, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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?):

ABCDE
1DividendShare
2DateAmountDatePrice
330 Jun 2020$3.0029 Jun 2020$99.00
431 Dec 2020$5.0030 Jun 2020$100.00
530 Jun 2021$7.0030 Sep 2020$100.00
631 Dec 2020$110.00
7Current Value$16.3231 Mar 2021$112.00
830 Jun 2021$120.00
931 Jul 2021$115.00
10
11CURRENT$122.00
Sheet2
Cell Formulas
RangeFormula
B7B7=SUM(B3:B5*CurrentPrice/VLOOKUP(A3:A5,SharePrice,2,1))
Named Ranges
NameRefers ToCells
CurrentPrice=Sheet2!$E$11B7
SharePrice=Sheet2!$D$3:$E$9B7
 

Forum statistics

Threads
1,175,527
Messages
5,897,942
Members
434,688
Latest member
vi28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top