Template for Dividend Growth Annualized Returns

I have created a Google Sheets template that calculates the long term annual return of a stock (or portfolio if you wish) based on the expected average dividend growth and the initial dividend yield. I scoured the internet to find such a template and could not. There are calculators all over, but none gave me the information presented in this manner.

It is fairly common knowledge that the average return of the S&P over time is around 7%. If we can expect to get a 7% annualized return, why do we invest in Dividend Growth Stocks? The template I created shows why. The main output is the green line at the bottom "Annual Return Rate" shows the Yield on cost of your stock or portfolio. After 20 years, the yield is a very nice 11.5%.

There are some assumptions for this sheet. The first and most important is that the initial dividend rate that you enter remains the same or close to it every year that you invest more into the stock or portfolio. The next is that the annual growth rate is always climbing and stays steady around the entered value. If a stock has wild up and down years, the end result probably wont look as clean as this sheet. Finally, the annual return rate in green will decrease if you do not invest more every year. This is the power of compounding, but it is also possible to just put 0 in the fields from years 2-20.

Lets take a look at a screenshot of the template:

Inputs:

Annual Growth Rate = This is the average annual dividend growth rate expected over the 20 year period.

Initial Dividend Rate = The current dividend yield of the stock or portfolio

Yr Contrb = Enter in the amount you plan to invest annually into the stock or portfolio


Outputs:

Annual Return Rate = This is the current dividend yield of the combined purchases for each year. This is essentially the Yield On Cost of the stock or portfolio.

Future Dividend Rate = This shows the calculated dividend yield based on the Annual Growth Rate entered for each year since the beginning.







I created a list of random stocks that have relatively high average dividend growth numbers. This shows you that if you put a lower initial dividend rate, but a higher annual growth rate, you will get better results than a high initial yield but poor growth.

An example is if you have an initial dividend rate of just 3% and a growth rate of 15%, you get a 15.4% return after 20 years. Compare that to the screenshot above where you have an initial dividend rate of 4% and a growth rate of just 10%. This yields a return of just 11.5% after 20 years.

Ticker	10 Yr Avg Div Growth
KO	10%
PM	15%
IBM	16%
PEP	10%
JNJ	12%
ADM	17%
AMAT	21%
BBL	18%
CAT	12%
EMR	8%
ABT	7%
OKE	16%
KMI	19%

 

This is just one tool of many to help come up with a long term plan for your portfolio and set some goals. My main goal was to see if its possible to be the 7% annual return of the S&P and if so, how long it would take. Now I have a good hypothesis of what that may be based on my settings.

What do you think of the template? Do you see any errors or bad assumptions? Please let me know so we can discuss.


Link to Template is here:
https://docs.google.com/spreadsheets/d/1E3x8uSPBuvDZUh2nDhueJFDseaiUU6F_5s8t-lAk1Q8/edit?usp=sharing




Disclaimer: I am not a licensed investment professional. I am not liable for any losses suffered by any parties. Any information on this site is my opinion only and should not be used for investments of any kind.