From time to time, I am asked how I track my dividend income. My current setup is quite good that I can predict all my future income not taking into account dividend changes by the companies. Over the past year, I fine tuned my process to simplify the data entry I needed to do and I’ll share my process with you.
One item to note is that my payment correspond to the ex-dividend date. Waiting to see when the payment happened in term of share in my account tends to be a bit erratic between the companies and I prefer the ex-dividend knowing the payment will follow. As such, my monthly dividend income accurately represent how much I am getting on the ex-dividend and not in my pocket. I could not rely on it to make payments for example.
Step 1 – Choose a Spreadsheet
My choice of software is Google Spreadsheet. You should be comfortable with any spreadsheet since it only requires simple math.
The table below represents the heading for all my holdings:
- Initial row has the account and stock represented. You can see that I specify the account it belongs to since I could always the same stock invested under multiple accounts. As it stands, I happen to have BMO under Computershare and my regular trading account.
- The ‘Month’ refers to the month of the year as you will see later.
- The ‘Total’ referes to the total for the month as you will see later.
- The ‘Ticker’ represents the stock ticker. It’s a bit redundant with the heading but I need it for Google Finance data look up for other sheets.
- The ‘Shares’ represents the number of shares I currently own
- The ‘Dividend’ represents the current dividend the company pays to its shareholders
- The ‘Next Payment’ is simply the result of Shares x Dividend. I basically can see my next dividend payment based on my share updates.
Step 2 – Decide On The Layout
I have deliberately put the companies on the horizontal to easily allow it to extend. Since I never delete investments I no longer hold, I simply move them to the right and keep them there to retain the dividends. A red cell highlights an investment I no longer hold. As you can see, past dividends are still present. It’s also important to not tie the ‘Shares’ to the dividends below the frozen pane since that number can change downward if you sell. You can see on sell Y20 that I had a dividends from BNS but I don’t hold shares in it. Note that my BNS holdings are in my RRSP. That investment was executed to transfer money between a US and a CDN account (to avoid exchange rate fees) and I happen to earn a dividend payment in the process.
Step 3 – Add Up Your Monthly Dividends
As you can see in the picture above, you just need to add up the dividends for each of the investment. I simply use the SUM function to add up all the cells next to the B column for each of the month. I also track the yearly total as an extra row to easily to how much I earned in dividends.
Once you have your data calculated, you can easily graph it and analyze it. If it’s important to have an evenly dividend income, than it can show you the gap you need to correct.
Step 4 – Maintaining The Data
What’s left is to keep up with the dividends paid by your investments and to manage the number of shares you have. Unfortunately, a Google Function doesn’t resolve either of these. Luckily, I don’t trade that often and companies don’t increase their dividends more than a couple times a year at best. If I know I will earn 2 shares per month for my dividends, I will adjust the monthly dividend to account for the extra 2 shares.
For example, my Just Energy (JE) earns 2 shares per month from the dividends. My monthly dividend is a function that look like this : “=R22+2*.10333″. That way, my dividends are tracked with little effort and I can also extrapolate future income.
I have not found an easy way to track all my finances and investments in only one program. I use Quicken for my finance but when it comes to my investments, I like to run my numbers differently and I endup using a spreadsheet for many of my scenarios. It’s the difference between accounting and forecasting.
As you can see, you can use the spreadsheet to identify which months are better than others for income. If your intention is to average your months, I recommend using iLoveDividends.com or build your own list to track when dividends are paid. It could be an extra filter you have for future investments.