Friday, October 9, 2015

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:


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


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:

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.


  1. Adam,
    Very nice. I like experiments! I find them very useful for understanding why we invest - especially in DGI companies. I have a couple of suggestions:
    1) Use your results to generate a plot.
    2) Add an option for reinvestments.
    Thanks for sharing! Keep up the good work.

    1. I figured you would like this D4s! :)

      Thanks for the suggestions. I initially had re investments as part of the table but it started getting confusing. It's a good suggestion though. Same with the graph. Ill see what I can do and make a future post about. Thanks for stopping by as always!

  2. Hi Adam,

    Cool spreadsheet, we will always have some variables to deal with. KO is a good example of a very consistent growth rate and some of the stocks will vary more as you are well aware, the important thing is you chart illustrates the benefits of dividend growth. As Div4Son mentioned, you might be able to build upon your idea, it's a great start.

    1. Thanks Chimp! I always like to see data behind anything I do so it was cool to see how different numbers affect outcome. Thanks for stopping by and commenting!