Md. Abdul Kader
While doing stock valuation, you need to find the dividend growth rate for accomplishing a better decision. Interestingly, you can compute the growth rate quickly using Excel. In this article, I’ll show you how to calculate dividend growth rate in Excel using the formulas and functions quickly with the basics of the term.
Download Practice Workbook
Basics of Dividend Growth Rate
What is Dividend Growth Rate
Fundamentally, a dividend is the amount of share from the profit of a company to the other stockholders. And the dividend growth rate is mainly the annual rate of increasing the dividend in percentage.
Formula of Measuring Dividend Growth Rate
The formula for both arithmetic average and compound annual dividend growth is as follows.
i. The formula of Arithmetic Average Dividend Growth Rate
Average Annual Growth Rate = (G1+G2+G3+...+Gi)/n
Here,
G1= yearly dividend growth rate in the first year
Gi= dividend growth rate at ith year
n = number of periods
ii. The formula of Compound Dividend Growth Rate
Compound Dividend Growth Rate = (Dn/D0)1/n – 1
Here,
Dn = Dividend at last year
D0 = Dividend at first year
n = Number of periods
Application of Dividend Growth Rate
- Provides instructive guidelines to investors while engaging with any company.
- The healthy dividend growth rate of a company discloses profit stability whereas a diminishing growth rate reveals the problems with the profit.
- Worthwhile while using the dividend discount model
- Helpful in predicting the future dividend growth rate of a company
Methods to Calculate Dividend Growth Rate in Excel
In our today’s dataset, yearly Dividend Per Share (DPS) for the top 10 companies in the U.S. are given from 2013 to 2022. Therefore, we have to calculate the dividend growth rate in Excel.
1. Measuring Arithmetic Average Dividend Growth Rate
There are mainly two steps while measuring the arithmetic average growth rate. Firstly, we need to compute the yearly growth rate. Secondly, the average growth rate will be calculated over a period of time.
Step 1:
For determining the yearly growth rate, just use the following formula in the E8 cell.
=(D8/D7-1)*100
Here, D8 is the dividend in 2014, and D7 is the dividend in 2013.
Next, use the Fill Handle Tool to copy the formula for the below cells.
Step 2:
Right now, we have to find the average dividend growth rate using the calculated yearly growth rate over 10 years (from 2013 to 2022).
To calculate that, simply insert the following formula.
=SUM(E8:E16)/D18
Here, E8 is the yearly growth rate in 2014, E16 is the yearly growth rate in 2022, and D18 is the number of periods i.e. 10.
In the above formula, the SUM function returns the total of the yearly growth rates. Later, the total will be divided by the number of periods.
Related Content: How to Calculate Revenue Growth Rate in Excel (3 Methods)
Similar Readings:
- Percentage Formula in Excel (6 Examples)
- Calculate Average Percentage in Excel [Free Template+Calculator]
- Percentage Difference between Two Percentages Excel (2 Easy Ways)
- How to Add Percentage to Price with Excel Formula (2 Ways)
- Calculate Percentage Using Absolute Cell Reference in Excel (4 Methods)
2. Computing Compound Dividend Growth Rate (CAGR)
Unlike the arithmetic average growth rate, we may calculate the compound dividend growth rate in two ways. The first one is using the formula discussed in the formula section. And the other one is utilizing an Excel function.
2.1. Computing Compound Dividend Growth Rate Manually Using Formula
Truly speaking, it is a simple method as you can find the growth rate within a single step.
Just use the following formula.
=((D16/D7)^(1/C18)-1)*100
Here, D16 is the dividend in the last year (2022), D7 is the dividend in the first year (2013), and C18 is the number of periods (10).
If you press Enter after inserting the above formula, you’ll get the following output.
2.2. Compound Dividend Growth Rate Applying the LOGEST Function
More importantly, we may compute the compound dividend growth rate using the LOGEST function. The function finds the value of an exponential curve in regression analysis. For example, if you have dependent and independent variables, it’ll calculate the value of the exponential curve.
Anyway, we can calculate the growth rate using the function in 3 steps.
Step 1:
While using the function, your year value should be in date format. Also, you can do this using the DATE function in the following way.
=DATE(2022,1,1)
Here, 2022 is the year argument, 1 is the month argument, and 1 is the day argument.
Step 2:
Now, we need to calculate daily dividend growth data and the formula that can be used is the following.
=LOGEST(D7:D16,C7:C16)
Here, D7:D16 is the cell range for dividend (dependent variable-known_ys argument), C7:C16 is the cell range representing the date (independent variable-known_xs argument)
Step 3:
Finally, we have to calculate the annual dividend growth rate. As we found the daily growth rate in the previous step. So, we need to multiply 365 like the following way to get the rate for a year.
=((G10^365)-1)*100
Here, G10 is the found daily dividend growth rate.
Related Content: How to Calculate Monthly Growth Rate in Excel (2 Methods)
Things to Remember
- As the LOGEST is an array formula, don’t forget to press Ctrl + Shift + Enter if you’re not using Microsoft 365.
- Be careful that the dividend growth rate is generally calculated in percentage form.
Conclusion
In short, you can easily calculate the dividend growth rate in Excel using the formula and the LOGEST function. I strongly believe that this article will articulate calculation methods. However, if you have any queries or suggestions, please let me know in the comments section below.
