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.
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.
🔗 Calculate Percentage of a Number in Excel [4 Cases Explained]
Formulas to Calculate Dividend Growth Rate
There are two ways to calculate the Dividend Growth Rate:
-
- Using the Arithmetic Mean Formula
- 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
🔗 How to Calculate Annual Growth Rate in Excel (5 Different Cases)
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
🔗 2 Ways to Calculate Bacterial Growth Rate in Excel
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).
Now Follow The 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 the percentage form.
🔗 3 Ways to Forecast Growth Rate in Excel
🔗 2 Ways to Calculate Revenue Growth Rate in Excel
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.
Now Follow The 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.
🔗 3 Scenarios of Exponential Growth Formula in Excel
🔗 2+ Ways to Calculate Monthly Growth Rate in Excel
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.
Now Follow The 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 resulted 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!
![Calculate Percentage of a Number in Excel [4 Cases Explained]](https://excelgraduate.com/wp-content/uploads/2022/05/How-to-Calculate-Percentage-of-a-Number-in-Excel-6-3.png)
