In my previous article " 50/50 (BDC, MREIT) Portfolio Q3 2016 Update, Yield At 11% With Income At 70% Of My Employment Earnings" I alluded to developing a method of extracting historical dividend data and creating tabulated tables of the results. The dividend data is from Yahoo Financial services and this one application can save a tremendous amount of research time. It is built using Excel 2010 and downloads directly into Excel for your own mathematical experimentation. It must be noted the collected results are only as good as the source and verification of data is recommended.
The POT (Portfolio Online Tracker) was developed for my own High-Yield stocks and increasing dividend cash flow before retirement. As with all my Excel applications they are free and I share them with SA members.
Since I'm close to retirement and have been building my own income generator over the past four years I needed something other than my brokerage account to help determine my projected income stream. POT can be used to get 15 minute delayed quotes and give you a sense of how your stocks and portfolio balance are doing during the day.
I have also incorporated special indicators when doing a quote update on your stocks to record the SP500 VIX level and SP500 correction/bear-market price limits. There are quite a few other bar charts included to help the investor track their investments.
Two other reference articles that introduced POT;
- 50/50 Portfolio Update, And Introducing A Brand-New Stock-Tracking Tool
- 50/50 (BDC And MREIT) Update, New Stock-Tracking Tool Update POT2.0
The new dividend spread sheet will automatically collect every dividend payment and Ex-dividend date for each occurrence. It can display yearly dividends from 2 to 25 years (user selectable) of past historic dividends for every stock in your portfolio.
The New Year is fast approaching and I needed a method to project total dividend income. One of the tables generated will contain not only the projected dividends, but also determine the payment cycle; monthly, quarterly or semi-annually. This automates the user parameter entry when creating a POT portfolio; stock symbol and shares owned. This is one of the major updates that makes using the software simple and locally stored on the user's PC. I find entering my personal information online is uncomfortable and the accumulation calculations are specific to my needs.
DGI stocks chosen…
I chose 10 DGI stocks to illustrate 25 years of historical dividends, and generate tables to show how well the dividends performed. No total return or capital gains are included for this evaluation. The years for this study extend through two recessions and the user can visually determine how well the dividends held up during this 25 year period very quickly.
Coca-Cola Company (The)
Procter & Gamble Company (The)
Johnson & Johnson
Honeywell International Inc.
International Business Machines
Exxon Mobil Corporation
Boeing Company (The)
Verizon Communications Inc.
Dividend Historical Data; Table-1
This is the latest addition to POT and the last piece of the puzzle I needed to complete my own portfolio tracker. It was a pain to always manually determine the pay cycle and dividends for the stocks in my portfolio. As my portfolio got larger the time spent trying to keep track of the dividends during Due-Diligence was horrendous.
This is the basic monthly dividend collection for each stock in the user's portfolio including stocks on your watch list. This example is a collection of dividends over a 25 year period (table truncated for this article). The historical dividend years can be set from 2 to 25 years.
For each dividend a comment (red mark) is placed in the cell to indicate the Ex-Dividend date. The comment can be activated by placing your cursor over the cell.
Dividend Historical Data; Table-2
What I noticed from Table-1 are the dividends reported are the Ex-dividend dates and usually not paid until the following month.
With this information I designed the total yearly dividends from December of the previous year to November of the accumulated year. Example the total dividends for 1991 is from December 1, 1990 to November 30, 1991. Most of the time the December dividend is actually paid in January of the new year.
The green cells mean the dividend has stayed the same or went up from the previous year. With this one visual aid, you can instantly determine how your stocks performed in the past. Notice the last column is a trend-cell that gives a visual representation of dividend growth performance. This was all created with one click of a button, not digging into online research and adding up dividends for stocks of interest. Repetitive work reduced to a single push of a button saving huge amounts of time. Laziness is the mother of invention in my world; both at work and at home! I wish I can convince my better-half with this argument ;-(
Dividend Historical Data; Table-3, Table-4
I have included header cell comments; notice the red markers. They will give you more information about the column data.
Table-3; column MK (2015) in this example is the actual total dividends for the 2015 year. Column ML (P2016) are the projected dividends from the beginning of the year based on the last known dividends paid in 2015.
At the beginning of each year I run an analysis of future projected dividends during the first week of January. I then can estimate what income I'll receive during the year depending on each stocks pay-cycle. The last column MM (-- Trend --) is a quick visual result of the dividend change from the actual total dividends from the previous year.
Table-4; column MP (P2016) is the projected dividends from the first week of the 2016 year. Column MQ (C2016) is the current dividends as of the last quote update; October 13, 2016. As time goes by the dividend will change depending on announcements during the current year. The column MR (-- Trend --) tells the story of dividend changes with a quick visual evaluation. All stocks selected have raised their dividends since last year except for HON because Honeywell is having some issues with being acquired and earnings.
New Chart added for POT3.0
This chart calculates the price of a stock in reference to its 52 week high and low values as a percent. As noted in the title the zero percent is the mid-point of the 52 week high and low (average value). The 100 percent is the 52 week high and -100 percent is the 52 week low. This is sometimes a good indicator of when to purchase an investment if price is below the mid-point of their 52 week range.
Having a simple way to evaluate historical dividends for the income investor is just another tool to help perform due-diligence. For me since I'm a high-yield investor the projected dividends are essential for my paycheck replacement in retirement.
There will be future updates to POT and if you want to be informed of any changes you might want to click the follow button at the top of this article. I understand most of you do not prescribe to High Yield investing, but the POT application applies to all income seekers.
POT is more than a simple quote tracker, I have also built an application to project future returns of stocks for yield, growth, withdraw, retirement start year and inflation applied to distributions. Its already installed in this update and displays the results in a text window. I will highlight this feature in a future article. Good luck in your quest of increasing dividends with sound investments! Joe HYI ;-)
The down load link is a ZIP file located here…
Please note the stocks included in the 50/50 portfolio are not recommendations. They were personally selected by the author and contain a great deal of investment risk. The stocks in the portfolio are Business Development Companies [BDCs] and mortgage Real Estate Investment Trusts [mREITs]. Both investment vehicles are Regulated Investment Companies [RICs] and are required to distribute at least 90 percent of taxable income as dividends to investors. This is a live active IRA portfolio that I believe will withstand the markets' bull and bear movements based on my own research. The progress will be updated and tracked for feasibility of this investment method over the years. The article titled 50/50 Portfolio (BDCs And mREITs) Baseline 2014 details how the portfolio was constructed.