Friday, 6 September 2013

Keeping Track of Dividends

As an income investor who depends on dividends to pay the bills and buy the food, it is important for me to have a good idea what income I can expect from each investment and when it will be due for payment.

Many of my investment trusts pay dividends quarterly which is very useful. With shares and fixed income, the income is usually paid half yearly.

If you have only two or three holdings, it may be simple to keep track of receipts in a notebook. However with several shares and/or investment trusts, I find it much easier to keep track using a sreadsheet on my computer.

When I make a new purchase, the first thing to record on my income spreadsheet will be the dates for expected payments, the amount expected and some way to indicate that the payment has been received.

I use a simple Microsoft Works S/S package that was installed on my old computer when purchased. The modern day version would be Microsoft Excel. Here’s how it  looks using the sipp portfolio from this recent portfolio review  -

click to enlarge


As information comes in from annual reports and Investegate news, the spreadsheet will be updated. For example, I recently received this announcement regarding the next quarterly dividend from City of London Trust to be paid in November. As each announcement comes in, it will be updated and the entry marked in bold font. Similarly, when a payment is received in my brokers account it is marked in bold.

Spreadsheet Formulas

Formulas are a quick way of automatically adding up lengthy columns of figures or multiplying a constant number (e.g. shares held) by a changing number (e.g. dividend amount). I use a few simple formulas in my spreadsheet -

Column G ‘Amount Received’ is a formula to multiply the number of shares held in column D x the current dividend per share in column F. The formula for the first line - Murray Income - is =D4*F4/100

This formula can then be simply copied and pasted for each line.

The next column is the full year dividend as a percentage of initial cost. The formula is =SUM(E4+F4)*D4/C4

The total dividends received for the year =SUM(G4:G41)

In a future article, I will cover how to keep track of capital.

As ever, slow & steady steps….

6 comments:

  1. Very helpful post. Best I start to create an Excel Spreadsheet

    Is there any reason why you do not use the facility to
    automatically re-invest the dividends into the same
    Investment Trusts once they are paid out.

    thanks

    Matt

    ReplyDelete
  2. Hello Matt,

    Good to hear you found this helpful.

    As my sipp has been converted to income drawdown, most of the income is withdrawn.

    Of course, if you do not need the dividend income, it is a good idea to re-invest to turbo-charge your returns - some brokers offer to do this automatically for a small percentage but I don't think my broker, Sippdeal offer dividend reinvestment so accumulated dividends would need to be added to any new money and invested manually when it was economical to do so.

    Good luck with creating the spreadsheet!

    ReplyDelete
  3. Hi John

    Nice idea to show the Previous dividend so that you can assess whether companies are cutting, fudging by not growing the dividend at least in line with inflation or actually rewarding the shareholder for their investment.

    I capture my dividends initially sequentialy by date, then use a few formulas including hlookup/vlookup to populate the total dividend received by month. I use this method as I'm more interested if the total dividend per month is growing.

    To assess at the company level it currently requires a bit of "eyeballing". If I simply add the previous dividend to my dated sequence life will become a lot easier. Thanks.

    Cheers
    RIT

    ReplyDelete
    Replies
    1. Hello RIT,

      Thanks for stopping by and pleased you have seen a method to improve your own dividend recording. I'm probably stating the obvious but where payments are made quarterly, the column E amount will be the total of the previous three quarterly payments combined.

      Delete
  4. Interesting to see the helpful practical advice you give....not that I will be following it!

    ReplyDelete
    Replies
    1. Hi Janet,

      Thanks for taking the time to leave a comment and good to hear you think the article is helpful... probably more for others than yourself though.

      Investing and spreadsheets can be a lethal combination in the wrong hands!

      Delete