This Excel spreadsheet downloads historical dividend data and calculates annual dividend growth rates for a set of user-specified stock tickers.
You can analyze a single company, or a hundred companies – you just need their ticker symbols.
Companies that have stable, long-term dividend growth rates form the core of many value-oriented investment portfolios. You can use this spreadsheet to filter for companies that fit this profile
Additionally, the dividend discount method of stock valuation requires estimates of long-term dividend growth rates.
Watch me use this spreadsheet in this video.
Just follow these steps to use this tool
- Enter a set of tickers (you could enter one or over fifty)
- Enter a start year and an end year (data will be downloaded from 1st January of the start year to 31st December of the end year)
- Optionally, check “Write to CSV”, “Collate Data” or “Dividend Growth Rate”
- Click “Get Bulk Dividends”
The spreadsheet then downloads historical dividend data from Yahoo Finance. Since most companies pay dividends quarterly, you’ll find that you usually get four payments per year
If “Dividend Growth Rate” is checked, then the VBA performs a few extra operations. The code
- adds up the quarterly data to give yearly data (this is what most investors are interested in)
- calculates the annual dividend growth rate using this formula (where Dn is dividend in year n, and Dn-1 is the dividend in year n-1)
- calculates the arithmetic average annual dividend
- and also calculates the compound annual growth rate of the final year’s dividend DN with respect to the first year’s dividend D1.
Here’s a typical report for Exxon Mobil (ticker: XOM). We analyze the dividends paid between 2000 and 2014
Between 2000-2014, the average growth rate was 0.084 (or 8.4 %). The CAGR between the first and last annual dividends was 0.076 (7.8%).
You can now use Excel’s functionality to analyze and visualize the information in these reports. For example, you could chart the growth rates.