Social


25

How To Track Your Dividend Income

Plot of y=x 2 made in Microsoft Excel. All pro...

Image via Wikipedia

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.

Dividend Tracker, Monthly Dividends

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.

Dividend Tracking, Dividends, Monthly Dividends

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.

Monthly Dividend Report

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.

Summary

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.

Enhanced by Zemanta
Sign Up for my FREE Money Tips Newsletter!

25 Responses to "How To Track Your Dividend Income"

  1. SophieW says:

    Just to be nosey… what are on lines 6 thru 18? ;)

    I’m like you, I usually end up making my own spreadsheets because I can never find the one program that does exactly what I want it to!

    Thanks for your take on things, I may have to make some minor adjustments on mine :)

    • The Passive Income Earner says:

      Hi Sophie,
      Line 6 through 18 are simply 2010 data. I really started tracking my dividends this way last year. Since 2010 is completed, I simply hide it to not have to scroll down. It should not change either if you don’t do any math based on your heading information.

      I have a Total heading for all time too. This can be useful to see when your dividend earnings have passed your capital invested. It’s years down the road though.

      Thanks for the comments. If you have any other questions, let me know.

  2. Very cool!
    The one thing I need to figure out is how to tell what my dividend income has been in questrade! I just switched our RRSP from mutual funds to ETFs last November and implemented the SM with dividend paying stocks but I don’t know how to tell what our dividend income has been …

  3. Palazzo says:

    Great post. I also use Google Docs spreadsheets to capture my stock performance. I will now start using it to capture my dividend performance.
    The question I have for you, is what broker do you utilize that allows you to purchase parts of shares, with your dividends? I use quest trade and they only allow full share purchases.

  4. great setup 101, I also track my dividends on a spreadsheet. But so far I don’t have to follow partial shares/dividends as I currently don’t drip. It’ll come…

  5. IncomePirate says:

    One thing I noticed where you track your dividend payments under your dividend income tab…under your 2011 data only the month of January is labeled as 2011. The rest of the months are labeled as 2010 still. Thought I would point that out. Love the website, love dividends and love tracking dividends. Keep up the great work! ~Doc

  6. gibor says:

    I use similar Excel spreadsheet to track my investments,… have some troubles with US stocks/ETF as conversion going up back abd forth

    • The Passive Income Earner says:

      Hi Gibor,

      Thanks for sharing. You have a good point about currency conversion. I’ll admit that I am not bothering with it at the moment. I really couldn’t use my spreadsheet for accounting and tax filing. I don’t think I’ll make the correction either as I would need exact date to retrieve currency exchange rate and that becomes quite a bit of work.

  7. Ian Easson says:

    I not sure I understand the reason given for the layout: having time run vertically. Most people (myself included) find it more natural for it to be displayed horizontally. You said there was limited expansion capabilities horizontally. Is that because of a limitation in Google’s spreadsheet, which by some accounts is less capable than Excel?

    For my dividend spreadsheet in Excel, I have time going horizontally, and have room for 1350 or so years worth of 12 months each. That’s enough expansion for me!

    As for businesses in my spreadsheet, they just expand downwards.

    I have the whole dividend tracker in what’s called an Excel “table”, so it is trivially easy to add additional rows at the bottom or in the middle, or additional months to the right with one or two clicks.

    The use of a table has other advantages. I can sort it by any field with one or two clicks (e.g., to sort this months’ dividends from highest to lowest, or sort by stock symbol). It also allows me to have automatic sums or other calculations in a special summary row at the bottom of the table.

    • The Passive Income Earner says:

      Hi Ian, thanks for the comment. It’s not a limitation, just a visualization preference. I started the same way you said and I needed to scroll too much. So I swapped it around. It’s just something to consider before you set it up. As you can see, I hide rows for past year and I could have gone in reverse order to add my data but it can be hard to read. So I have found that hiding rows work well to avoid scrolling.

      It’s just a decision that you need to make base on the information that is important to you and not a limitation of the spreadsheet.

      I agree that having investments horizontally is more natural and it’s actually how my other sheets are when looking at performance. In this case only, it’s about monthly income, so I made my monthly income the horizontal rows. This sheet actually references all my other spreadsheet data.

      Excel will work really well for this too. Much more powerful than Google Spreadsheet. I use Google Spreadsheet because I can easily access my file from anywhere :)

  8. Ian Easson says:

    Ah, I see.

    The scrolling problem you mentioned is easily solved, though.

    First, to collapse years, use the “Outline” capability (it’s in Excel; don’t know about Google). You can use it to collapse and expand (without using hiding or unhiding) any set of numbers, horizontally or vertically arranged. It will even do subtotals automatically, e.g., annual sums).

    Also, use the freeze panes capability to make sure your common rows or columns are always in view while you scroll.

    As for accessing and editing your file from anywhere on the web, I guess you didn’t know you can do that with Excel as well (and Word, Powerpoint, etc.).

    • The Passive Income Earner says:

      Thanks for sharing. I did not know Microsoft had brought it over to the cloud :) I had only herd of the whispers. I wonder if the Mac version works too … I do have it at home. I’ll give it a try as I find Google Spreadsheet limited in functionality.

  9. This is a great post. I follow a very similar tracking system using excel for my dividend payments. It definitely helps to stay organized and monitor performance. Thanks again!

  10. Ian Easson says:

    One more hint for those tracking dividends. The problem? Different currencies.

    For me, here is how I solve it. Remember, in my case, time flows horizontally.

    For each company, I have one additional column, which tells me the currency the dividends are paid in. In my case, there are two possible values: “CDN” and “US”.

    Then, below the table of monthly dividend payments, I have a set of 7 rows, labelled:

    US Gross Subtotal (US$)
    Less: US Witholding @ 15% (US$)
    Equals: Net US subtotal (US$)
    Times: US Dollar exchange rate
    Equals: Net US Subtotal (C$)
    Plus: Canadian subtotal (C$)
    Equals: Monthly dividends (C$)

    To make this happen, you need to be able to be able to perform a CONDITIONAL sum of each monthly column. There are two possible conditions: The currency is “CDN” or it is “US”. To implement this conditional sum, use the SUMIFS function in Excel (look it up in help to see how to do it). I use this twice of course – once for the “US Gross Subtotal (US$)” calculation and once for the “Plus: Canadian subtotal (C$)” calculation.

    Works like a charm.

    • The Passive Income Earner says:

      Thanks for sharing! Much appreciate Ian. Handling different currencies is not something I handle at the moment. Totally by currency is definitely a plus.

  11. Ian Easson says:

    If you want to learn more about storing, viewing, and editing MS Office documents, visit:
    http://www.docs.com (for Facebook), or
    http://office.microsoft.com/en-us/web-apps (for Office Live)

    In the next few months, there will be a third portal for Office apps: Office 365.

    Hope these pointers help.

  12. Moneycone says:

    Excellent HOWTO post PIE! I too use google docs with a similar setup – I wish there was a good affordable tool for this.

  13. Hello just wanted to give you a quick heads up. The text in your post seem to be running off the screen in Firefox. I’m not sure if this is a formatting issue or something to do with browser compatibility but I figured I’d post to let you know. The layout look great though! Hope you get the problem fixed soon. Cheers

  14. I visit your site quite often. It is very good and rich with great information. I also try to earn from passive income.nice post

  15. RobinTyler says:

    When you start selling and buying and selling and buying and so forth … the ROI calculation starts to get blurry. The initial investment changes when you buy a new investment. The initial investment is the amount you invested which may well include profits (or loss) from another investments.

  16. Janice says:

    This Microsoft link illustrates some differences between Excel Web App & Google Spreadsheets,with the obvious bias, but interesting non-the-less: http://www.whymicrosoft.com/en-us/compare-demo/pages/google-spreadsheets-vs-excel-web-app.aspx

Leave a Reply

Submit Comment
*

Copyright © 2010 - 2014 The Passive Income Earner. All Rights Reserved.
Powered by HostGator on Theme Junkie.