Keeping Track of Dividends

I was recently asked in an email from Art if I could share the layout of my spreadsheet that I use to keep track of my dividend investments. Well Art, I'd be more than happy to share this with my readers because without you all, there would be no Loonie Bin.

By keeping track of my investments with a spreadsheet, I save a lot of time by using formulas to calculate averages and yields that would otherwise take forever. It may take a while to setup the layout and formulas in the beginning, but once it's complete you'll have fully functional way of keeping track of your dividends which can be updated with ease. Below is an example of my personal spreadsheet layout:

Under the first column I keep track of the stock ticker symbol. Once you have been trading for a while and reading up on each company, the stock symbols become second nature and are easy to recognize.


Next there's the initial purchase price. I keep it simple by only using the amount the stock purchase cost me. Some people like to include the trading commission for how much the entire transaction cost them but I personally feel that trading fees are just another cost in life; much like ticket fees to performances or gratuities in restaurants.

In the next column I keep track of the number of shares I currently own of each stock. I personally like to buy in lots of 100 to make it easier to keep track of everything. Also if you buy 50 shares here and 20 shares there, you spend a lot of extra money on commissions each time you hit the buy or sell button.

Next I keep track of the current dividend that each stock pays per year. The data under this column can be changed at any time without effecting the formulas of other columns. This allows me to increase, or God forbid, decrease any dividend amount at any given time.

The yield column uses a formula of dividend/share price. By using the formula, the yield will be updated automatically if either the purchase price or the dividend changes. Beneath the column I use a formula to calculate what my average yield is on my dividend investments.

My favourite column is the expected yearly dividend. I choose to colour it green because it's the expected money that gets paid to me each and every year and is the tangible profit from dividend investing. I use a simple formula of multiplying the number of shares by the dividend to calculate my expected yearly dividend payout. Beneath the column I keep a sum of all my dividends to easily calculate my entire yearly expected dividend income.

Last but not least, there is the total price of the initial investment. It is easily calculated by multiplying the share purchase price by the number of shares. I keep it near the end so that I can visually see the difference between what my investment pays me and what it ended up costing me initially. Then beneath the column I add up the total amount my dividend investments cost.

So there you have it; simple yet very effective. Life is complicated enough, so why make something as simple as dividend investing more complicated than it has to be? Stay tuned as I will be introducing a simple spreadsheet next week that helps me calculate my monthly dividend income for easy reference. If you need help with setting up formulas, please don't hesitate to shoot me an email and I will try to help you the best I can. I look forward to your comments and critiques.

Enjoy the rest of your Sunday, folks!