Please Note: Blog posts are not selected, edited or screened by Seeking Alpha editors.

My Favorite Spreadsheet Tool

Summary

A spreadsheet tool I put together to track dividend growth.

Why it's helpful.

Introduction

More than once I've mentioned that I put together a simple spreadsheet to track my dividend income growth over time. Whenever I say this I wonder if I'm explaining it correctly or if people just sort of gloss over it. I'm going to demonstrate it - the old "show, don't tell" principle. In the future, rather than take 50 words to try to explain what I'm talking about, I'll link to this post.

The Tool

I put this together because I spent some time in "I think" country. Once I really converted to DGI/Income investing I'd say to myself, "I think I'm on track." But I didn't know. Technically I still don't - none of the 35 stocks I own has ever provided me with a guarantee of their future dividends and I expect at some point dividend growth rates for many companies will decrease. But by running the numbers I have a much clearer picture.

By taking the number of shares I own of a stock and its annual dividend I project how much income I will receive for the next 12 months. Then by using the 5-year dividend growth rate I project how much I will receive - if that DGR remains at that level - for each of the next 5 years. I make no assumptions about my ability to purchase additional shares with the dividend; for the purposes of this spreadsheet I may as well be withdrawing them. By adding numbers I can come up with projections for portfolio income and calculate the annual portfolio dividend growth rate.

My dividend amount reflects the last dividend paid. I do not change the figures when a company announces but when it pays, mainly because I don't always take note of announcements but I sure notice increased (or decreased) payments.

What I'm going to do is take the first two rows from the spreadsheet and display it here. There is one change. I'm going to list 100 shares of Apple because A) following the math will be a little simpler and b) I don't want to tell strangers on the internet how much my investments are worth even though I'm sure you're all fine people.

I am going to add a third row. That one will list the Excel formula used for the cell directly above it in case someone wants to try duplicating it. The row and column numbers/letters are the same as on the spreadsheet. Rows 1 and 2 are for a text box header.

A B C D E F G H I J K L M N O P Q
3 Symbol Description Quantity Dividend Income 5 yr DGR Yr 1 incr Yr 1 tot Yr 2 incr Yr 2 tot Yr 3 incr Yr 3 tot Yr 4 incr Yr 4 tot Yr 5 incr Yr 5 tot Last Updated
4 AAPL APPLE INC COM USD0.00001 100 $3.08 $308.00 0.1038 $31.97 $339.97 $35.29 $375.26 $38.95 $414.21 $43.00 $457.21 $47.46 $504.66 12/23/2019
Formula =C4*D4 =E4*F4 =E4+G4 =H4*F4 =H4+I4 =J4*F4 =J4+K4 =L4*F4 =L4+M4 =N4*F4 =N4+O4

This was simple to build. Each stock I own has a row and when I talk about my portfolio dividend growth rate (9.05% at this moment) it's just a matter of adding up the numbers (autosum function) and doing a quick calculation of how much the projected dividend will increase over the previous year - the year 1/Column H Excel formula for this is: =(H42-E42)/E42. You can't quite do a "control D" for the spreadsheet - Excel wizards probably could but I had to manually change things a little after doing so. But it was still pretty quick.

If a company raises its dividend, or I buy shares of a dividend-payer, or one of my Roth companies drips shares, those numbers change, almost always for the better. And since I keep dividends in my accounts to buy more dividend-payers with, I outperform the listed dividend figures, another bonus. I don't try to guess how many shares and subsequent income I can buy with the dividend income. 

Technically my column headers are wrong. Column E is actually one year ahead (dividends over the next 12 months), Column G is year 2. But by the time I realized I was tracking 6 years, not 5 it was built so I went with it. I update the DGR whenever a company raises its dividend or every three months which is what column Q is for. 

I see folks talking about Google sheets and tools like that. I'm good with my own simple spreadsheets. And I like seeing the impact taking place when I make an entry rather than having something auto-update. If I can do this, so can 95% of the people on SA.

Purpose

This is my "grounding mechanism." It's what I look at from time to time to remind myself that I am on track. And starting this year, on the last day of each month I'm recording the total from the dividend column for each year as another reminder that I'm making progress. I initially did this to track potential future growth and it does this but the largest benefit has been peace of mind. Not that I'm overly worried. Things have been going pretty well - but without this tool I wouldn't know that.

Happy investing everyone!

Disclosure: I am/we are long AAPL.

Additional disclosure: I am not a professional investor and do not offer investing advice. I have a college degree in Animal Science and used to train horses for a living. Would you really want to tell a loved one you invested based on something an ex-horse trainer/animal scientist wrote?

I didn't think so. Please perform your own due diligence when making investing decisions.