Tuesday, March 24, 2015

Dividend Yield Theory Spreadsheet

Hello all, this is Div4Son.


In my Getting Started Part I post, I mentioned the Dividend Yield Theory that is described in "Dividends Still Don't Lie" book by Kelley Wright.

There is a good description here in the 'Tao of Wealth' blog. 

"According to the dividend yield theory a stock or a stock market index is overvalued when the yield is low and the price is high and undervalued vice versa. The actual yield at which a stock becomes overvalued or undervalued depends on its own history of dividend yield over many years."


My strategy is buy and hold Dividend Growth Stocks as long as possible, so I will use only part of the theory. More on that later.


As mentioned in my my earlier blog, the main drawback of the Dividend Yield Theory is that it is based on historical information. Moreover, the "Yield Profile" - i.e. High and Lows yield value lines can change over time. 

The method from the book is to look back at the highs and lows prices of a stock and get the associated 'averaged' out yields. There is a degree of subjectivity with this approach. 

In any case, it is sometimes not so easy to look over a prices of a stock and the corresponding yields - unless you have a tool (FastGraph etc).

Therefore, I created a Google Sheets spreadsheet for you to play with. 


NOTE: Corrected dividend links 5/19/2015 

Bear in mind that this spreadsheet uses third party information (Yahoo Finance, etc), which may or may not be correct. Also, with dividends and dividend raises, you need to make manual adjustments in some cases.

Instructions
  1. You should change only the yellow highlighted cells. First thing to do is to enter the high and low yields. You can do this by checking the "price yield" tab.
  2. Using BAX as an example, I will use 3.2 as the high yield and 2.0 for the low yield. Clearly, before the 2008 (the great recession), we have a different yield profile.
  3. After entering this in the first tab, check the "Yield Value" tab to see if the high and low yields follow the channel. Again, I am choosing post 2008. Repeat 2&3 until you are satisfied with the high and low yields of the stock, Note: This is the subjective part I am talking about.
  4. Check the first tab again for the High, Mid, Low prices based on the yield information you entered. Notice also how the dividends are rising every year. This is also a good thing!
Example with BAX

Current Year:2015
High Price:103.08
Mid Price:83.75
Low Price:64.43

Based on Dividend Yield Theory, BAX (today at 68.53) is considered very close to "Undervalue". 

According to the book, you buy at high yields and sell at low yields.

For me, I am not too interested in selling. Also, I am interested in fair value also, so I modified the approach slightly to use the Mid Price point for some companies. See the green line on the chart.

Note: For some companies, you need to manually adjust the dividends column (Column BE) due to one time dividend payments/splits etc.

Lastly, I've included the Relative Strength chart (Relative Strength Tab) - which we will talk about in another day.




--

Again, there is a degree of subjectivity with the Dividend Theory Approach. 
Also, the price of a stock can go much higher or lower than the yield bounds. 

In other words, you should use other valuation methods that fit your investment style. For me, I use this method in conjunction with average yield, Dividend Drill Method from the "Dividend Playbook" as well as morningstar & S&P valuations.

Have fun with the spreadsheet!

Div4Son

Disclosure: Long BAX




No comments:

Post a Comment