Calculate Dividend Growth Rate in Excel

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”

A spreadsheet that downloads historical dividend data and calculates dividend growth rates

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)

Formula that gives the dividen growth rate in terms of the present year's dividend payout and the previous years dividend payout

  • calculates the arithmetic average annual dividend

average dividend growth rate

  • and also calculates the compound annual growth rate of the final year’s dividend DN with respect to the first year’s dividend D1.

An equation describing the compound annual growth rate of two dividend payments in two different years

Here’s a typical report for Exxon Mobil (ticker: XOM). We analyze the dividends paid between 2000 and 2014

Dividend report for Exxon Mobil

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.

A chart of dividend growth rates for Exxon Mobil between 2001 to 2014You can run these reports for a single company, or for a hundred companies.

Download Excel Spreadsheet to Analyze Historical Dividend Payments

It's only fair to share...Share on FacebookShare on Google+Tweet about this on Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *