The Dividend Investing Resource Center

Letting Dividends Do Their Work

Dividend investing is a means of building wealth over a long period of time with reduced risk. Many brokers offer fee-free purchases and reinvestments and the ability to reinvest fractional shares. Dividend investing is for the long term buy-and-hold type investor who wants to sleep at night while their investments steadily build.

About
Information about the Dividend Investing Resource Center.
Articles
A series of articles for those learning about the world of dividend investing.
Blog
The Prudent Investor is the associated blog about dividend investing.
Books
Books that have been found to be useful for dividend investors.
Community
A Message Board for people to meet and discuss DRiP-related topics.
Getting Started
A guide to getting you started with dividend investing.
Info / Tools / Forms
Information, spreadsheets and databases specifically for the dividend investor.
Research
Links to the best information available to the dividend investor.

Articles from The Prudent Investor

A Dividend Spreadsheet for Google Sheets

by George L Smyth

There are many options for tracking purchases. Some options, while powerful, may be intimidating to the new or small investor. This dividend spreadsheet is small and simple but has the flexibility to become whatever you wish over time.

     

Everyone needs to be able to keep track of their portfolio and there are numerous means available to us for doing so.  One direction is to go with something very complete, like Stock Rover, which is a great piece of software that offers an incredible range of information.  The downside is that its completeness is accompanied by a rather steep learning curve.  The other end of the spectrum is to start with something simple and easy to use, though it may not have the capabilities other means of tracking offer.

Many years ago I created an Excel spreadsheet that used Yahoo to get current information and it worked quite well.  Yahoo then ended this ability so I found another source and rewrote the formulas.  That source, too, then went away and I had the impression that I might get involved in a never-ending task of rewriting the spreadsheet to backfill vanishing resources.  I changed it to rely on the user to manually update the information.  This was not convenient but it was something.

Google offers the ability to automatically update a spreadsheet with the current price information, so I have again rewritten the spreadsheet to work with Google Sheets.  It does mean that one needs to have a Google account (free, of course), but hopefully, the information Google offers will continue well into the future.

Dividend Spreadsheet for Google Sheets

 

My idea is to offer a starting point and let the user take it where they feel it needs to go – or simply use it as it comes.  You can offer enhancements to what I have done and modify it for your design.  I need something simple so that at a moment’s glance I can see the state of my portfolio, but we all have our own needs.

Below are instructions on how to alter the example spreadsheet for your personal use.  While the spreadsheet does have a .xlsx extension and will open in Excel, it will not work properly within this software.  It is designed for Google Sheets and only works on that platform.

For those who are familiar with Excel much of the following explanation is superfluous, but I wanted to offer a fairly full description to those less familiar with the software.

How to Import the Spreadsheet into Google Sheets

After downloading the spreadsheet (Dividend Spreadsheet for Google Sheets) upload the file to Google Drive.  Double-click Dividend Spreadsheet.xlsx to open it.

example image

Although this example spreadsheet opens it will not work properly, as it first needs to be saved as a Google Sheet so that it can tap into the platform’s capabilities.  To open the spreadsheet for use click “Open with Google Sheets”, then save the file by going to File and select "Save as Google Sheets".  Saving in this manner only needs to be done once, as all subsequent changes will be saved automatically.  The program is now ready to be used.  In Drive the "Dividend Spreadsheet.xls" file may be removed, as it is no longer be needed.

How to Use the Dividend Spreadsheet for Google Sheets

The spreadsheet comes with an example stock from my portfolio (Aflac), as well as some sample purchases and dividend reinvestments. 

example image

To add a stock to the spreadsheet we need to copy the formulas from the "(new)" sheet into a blank sheet that will hold our transactions for this added stock.  We will then update the "Summary" sheet to connect to this new sheet, which will populate information from the newly created sheet. 

The sheet “(new)” is the blank sheet with the formulas.  To copy "(new)" to a new sheet select "(new)", click the down arrow next to the name and select "Duplicate".  This will result in a new sheet titled "Copy of (new)".

example image

Rename the newly created sheet by clicking the down arrow to the right of the newly created sheet, select Rename, enter the new ticker and press Enter (WTRG, the ticker for Essential Utilities, is used in this example).  The tab can be moved by click-holding and moving to the desired location.

example image

We are now able to begin entering information into the WTRG sheet.  Let's say that on 1/10/2020 you bought 3 shares at $30 each.  In the WTRG sheet enter the date of the transaction into A3, the total cost of the shares into B3, the price of each share into D3 and the number of shares into E3.

example image

On 2/3/2020 we received a dividend of $1.65 with the share cost of $31, resulting in 0.055 shares.  Enter the date of the dividend, dividend amount, cost per share, and the number of shares into cells A4 and C4-E4.  The number of shares could have been calculated instead of having to enter them, but there could be fees associated that would alter this simple calculation, so it is easiest to simply enter all of the information.

example image

As can be seen, the initial errors in row 2 have been resolved.  Also, columns F and G show the shares that have been obtained either through outright purchase or dividend.  This is just "interesting to know" information that will be reflected on the Summary sheet so that one can see what percentage of their shares come through dividend reinvestment.

The value in cell C2 is the most recent dividend and will show on the Summary sheet.  The Summary sheet will total these values for all securities so if they are all quarterly dividends then one will know how much they are receiving each quarter.

Return to the "Summary" sheet.  Enter the new ticker into cell A4 and company name into B4.

example image

It is now time to link the new sheet to the Summary sheet.  This is done by entering formulas into the appropriate cells.  We need to do this for cells C4, F4, J4 and K4.

The value within the single quotes in the below formulas is the ticker used to identify the new sheet.  In our example that ticker is WTRG.
C4 Enter ='WTRG'!E2 and press Enter (including the equals sign)
F4 Enter ='WTRG'!B2 and press Enter
J4 Enter ='WTRG'!C2 and press Enter
K4 Enter ='WTRG'!F2/C4 and press Enter

example image

Sometimes the formatting is not maintained, though the above is probably my fault (note cell J4).  This may be the result of transferring the sheet in and out of Google Sheets while testing for this article.  Correcting the issue is easy if it happens to you.  Simply click the column heading “J”, which will select the entire column, then select Format – Number – Currency.

example image

The spreadsheet now has the basic information needed to track your dividend portfolio.  There are many enhancements you may wish to add to the spreadsheet.

Google offers a listing of financial formulas that can be included.  For instance, you may wish to add the beta of a company to the spreadsheet.  On the above-noted link it offers:
"beta" - The beta value.
to be used within the formula:
GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])

To add beta to the spreadsheet enter “Beta” as the heading in cell L1, then in cell L3 enter:
=if(C3="","",googlefinance($A3, "beta"))
and press Enter.

Breaking this apart, googlefinance($A3, "beta") is the operative part of the formula.  $A3 points to the value found in cell A3, which is AFL, so it translates to:
googlefinance(‘AFL’, "beta")

=if(C3="","", (completed by the right paren at the end) simply looks at cell C3 to see if anything is there – if not then nothing will be displayed in the cell.

example image

Finishing Up

For those new to working with spreadsheets, by hovering over the nub on the lower right of the selected cell then click-holding, the formula can be copied to lower cells by dragging down (or up, or to the side, depending upon the direction in which it is to be copied).  Google has quite a few other options that may be of interest, but unfortunately for me, the ones of most interest me are only available for mutual funds, which is another reason I use Stock Rover or another resource when researching stocks.

If you do not already have something then this is a start.  Fortunately, it is easy enough to use that over time you can customize it to fit your exact needs.

Dividend Champions, Achievers, Kings and Aristocrats – A Comparison against the Indexes

by George L Smyth

There are numerous lists that track the length of time companies have raised their dividend.  Dividend Champions, Dividend Achievers, Dividend Kings and Dividend Aristocrats have similar names, but they have important characteristics that differentiate themselves from one another.

When speaking with some people about investing, I realize that while they know that it is important for their future, they generally understand pretty close to nothing about the subject.  Initially I question whether or not they are actually ready to invest.   If so, then instead of suggesting that their starting point be to go through a long period of (what will probably be extremely boring to them) learning, I recommend a simple low expense ratio S&P 500 index fund as a starting point.  After all, even Warren Buffett has expressed doubt that he can continue to do as well as this group of companies.

If we are going to invest then we can either invest in this strong index or try to find a better alternative. The latter is apparently not a simple task, after all, since most mutual fund managers fail at this why would we think we could do better?  The simple reason is that we have a long term horizon and dividends on our side.  Now the key is to find the right companies.

I have written about Dividend Champions, so if you wish a refresher then you can follow the link.  Dividend Champions are companies that have increased their dividend every year for at least the past 25 years.  This is a major accomplishment and absolutely commands notice of the investor.  As of this writing there are 138 companies in the list.

A list of companies that have been able to accomplish this feat is maintained at The Dividend Investing Resource Center   This list was originally created by Dave Fish and is now updated at the beginning of every month by Justin Law.  Included within the spreadsheet are Dividend Challengers and Dividend Contenders, companies that have respectively increased their dividend for at least 5 or 10 years.  As of this writing there are 463 and 265 members in these lists.

Of course, these are not the only lists.  There are lists for pretty much everything.  When it comes to companies that offer dividend growth to their investors, those lists are Dividend Achievers, Dividend Aristocrats and Dividend Kings.  At first blush one might think that these are synonymous with Dividend Champions and indeed they are similar, but the specifics vary and can be significant.

Dividend Achievers

Dividend Achievers refers to companies that have increased their dividend every year over the past ten years.  Moody’s Investor Service created the list in 1979 and in 1998 it was acquired by Mergent Inc. and rebranded.  In addition to this requirement a company must be listed on the NYSE or Nasdaq, and meet minimum trading volume requirements (if you want to get into the methodology of selection you can find it here).  The 2019 list currently has 358 entries.

Invesco’s Dividend Achievers ETF tracks fairly closely to the Russell 3000 Value Index.  Although I prefer a direct comparison to the S&P 500 this index is fair in the respect that it includes stocks from the broader Russell index that offer lower price-to-book ratios and lower expected growth values, which is indicative of Dividend Achievers.  Over the past ten years the list outperformed the comparison by returning 12.5% as compared to the index’s 11.7%.

Although the comparison is fair, it must be noted that from a total returns standpoint the Russell 3000 Value Index pales in comparison to the S&P 500.  This is not to disparage the index, it includes companies with broad growth and value characteristics, and if that fits one’s investing strategy then this would be an appropriate direction.  That said, below is a total returns comparison.

Benchmark Average Annual Total Returns as of 01/31/2020
1 Year 3 Year 5 Year 10 Year
Russell 3000 Value Index 14.1% 8.2% 8.6% 11.8%
S&P 500 Index 21.7% 14.5% 12.4% 14.0%

Dividend Aristocrats

Dividend Aristocrats are companies that have increased their dividend each year for at least 25 years, are members of the S&P 500 and have a market capitalization of at least $3 billion.  This final requirement is inconsequential as the smallest company in the S&P 500, Mattel, has a market cap of $5 billion.  The list was created by Standard and Poors in 2005 and at the time of this writing has 64 members.

With a longer dividend growth streak Dividend Aristocrats compare much better than Dividend Achievers when benchmarked against the S&P 500.  The Motley Fool ran a comparison from 1991 through 2016 and found that a hypothetical $10,000 investment in the S&P 500 index at the beginning of 1991 would have yielded almost $117,000 but would have returned over $191,000 with Dividend Aristocrats.  With a nod to the Aristocrat’s lack of volatility, there is also a smoother line during the dot-com bubble of the early 2000s and recession of 2008 that helped that total return.

Wikipedia notes that over the past ten years (ending 31 December 2019) Dividend Aristocrats not only matched the Nasdaq but did so with a significantly lower beta, 0.87 as opposed to 1.16.  This means that final performance was achieved without the 33% greater volatility of the Nasdaq’s technology-laden group.

Dividend Champions

As noted and linked above, Dividend Champions have a 25 year streak of dividend growth.  The question is how this list compares when placed against the standard S&P 500 benchmark.

The Part-time Investor at SeekingAlpha offered multiple back tests to answer this question.  In each test an imaginary $10,000 was purchased on 25 December 2007 and the returns were calculated through 31 March 2019.  The reason for more than one test revolved around the question over what to do if during the test’s time period a stock was removed from the Champions list. 

I will not go through the individual mechanics of the backtesting (which is thoroughly discussed in the article) but will report that while the annual return for the S&P 500 was 8.3%, the returns for the Champions were 10.2%.  In the test where the original stocks were held throughout the entire testing period regardless of potential eventual exclusion – not just a case of buy and hold but a case of buy and forget – the returns were 8.5%, still beating the S&P 500.

Dividend Kings

Dividend Kings is a more exclusive list.  To belong a company must have extended their consecutive streak to at least 50 years.  Only 28 companies are in this list.

Along with Dividend Aristocrats this group also beats the S&P 500 in past benchmark tests.  Dividend Growth Investor showed a positive comparison with the S&P 500 between the years of 2008 and 2017, where a $100 investment would have returned $265.06 if invested in the Dividend Kings list as opposed to $225.84 in the S&P 500.  An interesting takeaway with this test is that the S&P 500 actually outpaced Dividend Kings 60% of the time, but when the market fell in 2008 by 37% the Dividend Kings only lost 21%.  This is actually not unexpected since the companies in these lists generally tend to have a smaller beta than the S&P 500.

Simply Safe Dividends performed a more extensive comparison to the S&P 500 over the years of 1991 through 2017.  As expected Dividend Kings offered a superior annual return of 13.8% over the S&P 500’s 10.2%.  The study outlines the importance of lower volatility by noting that the Kings significantly outperformed the S&P 500 in the four years that the index declined in value.  This is a noteworthy fact for those who are concerned with retaining the value of their investment.

The Takeaway

While Dividend Achievers, Dividend Champions, Dividend Aristocrats and Dividend Kings have similar names they are different lists with different rules for inclusion.  Dividend Achievers is the easiest list to join, with a dividend growth streak of only ten years.  While solid, these companies do not match up well against the S&P 500, though they may be of value if employing a different strategy.

Dividend Champions and Dividend Aristocrats both require at least 25 years of consecutive dividend growth and both fare well in a comparison against the S&P 500.  Dividend Kings, requiring a full 50 consecutive years of dividend growth, also handily beats the index.

There is a clear message here, which I have said before and expect to say again in the future – there is great value in a long term approach in general and dividend stocks in specific.  Companies that increase their dividend over a long period of time generally do better than the S&P 500 index, an index that one of the greatest investors of all time is finding it difficult to beat.  In addition to this, and a factor in their success, this is done with less volatility.

There is something to be said about mitigating volatility.  In each of these tests the lower beta of the group assisted in retaining its value and eventually offered a superior total return.  This is a fact lost by those who only think in terms of short time spans.

The only issue remaining would be to try to determine which list is best – Dividend Champions, Dividend Aristocrats or Dividend Kings.  In my mind this is like having the ability to draft one of the three greatest pitchers in Baseball.  Truthfully, anyone would be overjoyed to have any one of the three on their team, so comparisons would simply result in nitpicking that in the long run would probably make no difference.

Personally, I prefer Dividend Champions if only because it is not only reliably updated every month, but the amount of information in the spreadsheet (and PDF) is a treasure trove when researching companies.  By simply sorting the columns and removing rows of companies that do not meet one’s needs, the spreadsheet can be used as a tracking and research tool.

At the end of the day, whether they be Champions, Aristocrats or Kings, these lists are invaluable when beginning research for the possible purchase of dividend companies.



This website is maintained by George L Smyth