Calculate Dividend Growth Rate in Excel [2+ Formulas]

Knowing a company’s dividend growth rate in previous years can be very beneficial for an investor. So that, the investor can look into the performance of that company in the past, did they make a profit or loss? This article will help you with 2+ formulas to calculate the dividend growth rate in Excel.

Dividend Growth Rate in Excel.xlsx

Table of Contents

What is Dividend Growth Rate (DGR)?

I am going to explain the terms Dividend and Dividend Growth Rate separately.

When you want to invest your money in a company, you buy a small share of the company. You become a shareholder of that business company.

After a period of time, the company will make a good amount of profit. And when they make a profit, they will reward all the shareholders with a percentage of the profit for investing in their company.

This percentage of the profit given to the shareholders is called a Dividend.

The Dividend Growth Rate means the percentage form of the growth rate of a company’s or corporation’s dividend over a span of time.

Formulas to Calculate Dividend Growth Rate

There are two ways to calculate the Dividend Growth Rate:

1. Using the Arithmetic Mean Formula
2. Using the Compound Growth Formula

1. The formula of Dividend Growth Rate Using the Arithmetic Mean

D = (G1+G2+G3+...+Gn)/n

In the above formula,

D = Dividend Growth Rate.

Gn = Dividend Growth in the nth year.
n = Number of total years.

2. The Formula of Dividend Growth Rate Using the Compound Growth

D = ((Dn/D0)^(1/n))-1

In the above formula,

Dn = Dividend in the last year of the period
D0 = Dividend in the first year of the period
n = Number of total years

Method #1: Dividend Growth Rate Using Arithmetic Mean

To show you how use can calculate the DGR using the arithmetic mean, I am using a simple dataset of a company with 3 columns Year, Dividend per share (DPS), and the yearly growth rate.

I have inserted two spare cells for the Number of Years (cell C12) and Dividend Growth Rate (cell C13).

Usage Guide

Step_1: Write this formula in cell C13:

=SUM(C3:C10)/C12

Formula Explanation

SUM(C3:C10) calculates the summation of the values from cell C3 to cell C10.

C12 is the total period of time from the year 2014 to 2022, 8 years.

Step_2: Press ENTER to apply the formula.

So we can see that the DGR is 0.16. But usually, DGR is expressed in percentage.

To convert the result from decimal to a percentage,

Step_3: Select cell C13.

Step_4: Click on the Home tab.

Step_5: From the Number group, select the ‘%’ option.

Final Result >

Now the result is in percentage form.

Method #2: Dividend Growth Rate Using Compound Growth

This method is also trouble-free like the previous one.

To show you how to apply it, I am using the previous dataset.

Usage Guide

Step_1: Type the formula in cell C13:

=(B10/B2)^(1/C12)-1

Formula Explanation

B10 is the final dividend per share (DPS).
B2 is the starting dividend (DPS).
C12 is the number of total periods, 8 years.

Step_2: Press ENTER.

Step_3: Convert the decimal result into a percentage, and select the ‘%’ command from the Number group.

Final Result >

Both methods will give you the same result. You can memorize one of the two methods.

🔖 Reminder: Notice that both formulas require the difference between the final year of the dividend and the starting year of the dividend. So it’s better if you keep the number of years in a different cell of your dataset before using the formula, just like I did.

Calculate Sustainable Growth Rate

A Sustainable Growth Rate (SGR) refers to the maximum increase in the sales value a company can achieve on its own without taking any external financial support or debt.

The Formula of Sustainable Growth Rate(SGR):

SGR = ROE x (1 - Dividend Payout Ratio)

You can calculate the Return of Equity (ROE) by dividing the net income of a company by the average equity of the shareholders.

Let me give you a simple example of SGR. I have a dataset where the Return of Equity and Dividend Payout Ratio is available.

Usage Guide

Step_1: Write this formula in cell C7:

=C4*(1-C5)

Formula Explanation

C4 is the ROE and C5 is the Dividend Payout Ratio here.

Step_2: Press ENTER.

Step_3: Select the ‘%’ command from the Number group to convert the decimal result into a percentage.

Final Result >

Here you have the resulting SGR. The formula is really easy to remember.

Conclusion

I think now you have a clear idea about the Dividend Growth Rate (DGR) and how to calculate it in Excel. I hope the formula of Sustainable Growth Rate (SGR) also helped you in some way. Feel free to comment below if you have further confusion regarding this topic. Have a good day!

Related Articles