# 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”

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.

You can run these reports for a single company, or for a hundred companies.

## 10 thoughts on “Calculate Dividend Growth Rate in Excel”

1. Steve R says:

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?

1. Crashing:

I just downloaded and allowed editing. Using your stock symbols, the program crashes every time I open and attempt refresh.

Thanks,
Dave Kellum

2. Gabri says:

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?

3. EVAN POTTER says:

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?

4. Brent says:

can you pull data on Canadian companies somehow?

1. Only the stocks that Yahoo Finance for which Yahoo Finance allows API access. That may or may not include Canadian companies – you’d have to try

5. Dan Teeters says: