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

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


8 thoughts on “Calculate Dividend Growth Rate in Excel”

  1. Samir,
    This is great, it saved me so much work and research how to do it in the first place.
    This is a great site.
    Thank you so much

    A question:
    do you have a plan to consolidate all the data automatically on a single page?

    Reply
  2. Really great job and useful.
    I would like to add some features to the VB module.
    Is it possible to have the psw to access the code please?

    Thanks in advance

    Reply
  3. Great Workbook!!

    There is some other functionality I would love to add to it.
    How do I go about obtaining the VBA password to do this?

    Reply
  4. I downloaded this spreadsheet and it worked great, I loved it.
    But it now fails to find any of the Stocks in the search list.

    What happened?

    Reply

Leave a Comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.