When I was setting up the historical high and low yields for my spreadsheet I sort of eye-balled the historical yields I had to get the historical ones. This is not very professional. I do not know how Mike Higgs' determined his values. So, I talked to a friend of mine who is a consulting actuary. Actuaries are people who deal with math a lot.
John suggested I use the "PERCENTILE.EXC" formula of excel. (The explain of this excel function is that it will look through a given set of numbers and find the exact number that breaks the data set into your chosen percentiles.)
Dividend yield and stock prices go in the opposite directions. So to find the historical high dividend yields I looked for dividend yields on the spreadsheet line with dividend yield on "low stock prices" and for historical low yields I looked for dividend yields on "high stock prices".
The functions format is "=percentile.exc(arrary,k)". For the high dividend yield formula, I used "k=0.8" and for the low dividend yield formula, I used "k=0.2") In these formulas, I am not looking for the highest or the lowest yield, but ones higher or lower than normal.
There are still problems. One is with old income trust companies which mostly have lowered dividend rates with the change to corporations. The old dividend yields will probably never occur again.
The other problem is that I have variable amounts of years of data on different stocks. For most new stocks I track it is sometimes hard to get more than 10 years of data. Also, some companies have only been around for a short period of time. For companies I have tracked for a long time, like Bombardier Inc., I have data back to 1991 in by spreadsheet on this company. (I have elsewhere data going back even further.)
I have updated my portfolio spreadsheet. The applicable sections are shown here and I have updated these fields for the stocks that I hold.
On my other blog I am today writing about CCL Industries Inc. (TSX-CCL.B, OTC-CCDBF)...continue...
This blog is meant for educational purposes only, and is not to provide investment advice. Before making any investment decision, you should always do your own research or consult an investment professional. See my site for an index to these blog entries and for stocks followed. Follow me on Twitter.