How to Calculate Dividend Growth Rate in Excel (3 Methods)

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.

How to Calculate Dividend Growth Rate in Excel Dataset


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.

Measuring Arithmetic Average Growth Rate

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.

Measuring Arithmetic Average Growth Rate

Related Content: How to Calculate Revenue Growth Rate in Excel (3 Methods)


Similar Readings:


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

How to Calculate Dividend Growth Rate in Excel Using Formula

If you press Enter after inserting the above formula, you’ll get the following output.

Computing Compound Dividend Growth Rate Manually Using Formula


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.

Applying the DATE Function

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)

Daily Growth Rate Applying the LOGEST function

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.

Compound Dividend Growth Rate in Excel Applying the LOGEST Function

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.


Related Articles

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo