How to 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 while, 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 is the percentage form of the growth rate of a company’s or corporation’s dividend over some 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

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).

Apply the formula below to use arithmetic mean in Excel:

=SUM(C3:C10)/C12

In this formula, SUM(C3:C10) calculates the summation of the values from cell C3 to cell C10. Then, C12 is the total period from the year 2014 to 2022, 8 years.

Steps to calculate dividend growth rate using arithmetic mean in Excel:

Click on an empty cell.
Write this formula: =SUM(C3:C10)/C12

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,
Select cell C13.
Click on the Home tab.

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

Now, the result is in percentage form.

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.

Use the formula below:

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

Here,

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

Now, go through the steps below to calculate the dividend growth rate using compound growth:

Select a blank cell.
Type the formula in cell C13: =(B10/B2)^(1/C12)-1

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

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)

In this guide, I will use the formula below:

=C4*(1-C5)

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

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.

Here are the steps to calculate the sustainable growth rate in Excel:

Select cell C7.

Write this formula: =C4*(1-C5)
Press ENTER.
Select the ‘%’ command from the Number group to convert the decimal result into a percentage.

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!

Frequently Asked Questions

What is the growth formula in Excel?

In Excel, the growth formula is expressed as: =GROWTH(known_y’s, known_x’s, [new_x], [const])

This formula calculates the predicted exponential growth based on existing data points, making it useful for forecasting trends in financial, scientific, or business scenarios.

How do you calculate dividends in Excel?

To calculate dividends in Excel, use the formula: =Dividend per Share * Number of Shares

Substitute the actual dividend per share and the number of shares to determine the total dividend amount.

How do I calculate growth rate per month in Excel?

To calculate the growth rate per month in Excel, use the formula: =((Ending Value/Beginning Value)^(1/Number of Months)) – 1

Substituting the actual values for “Ending Value” and “Beginning Value” and adjusting the number of months accordingly will give you the growth rate per month.

Related Articles

2+ Ways to Calculate Monthly Growth Rate in Excel

Rate this post