# 4 Examples to Calculate Dividend Growth Rate in Excel

In this lesson, Iโll demonstrate 2 methods to calculate dividend growth rate in Excel. Moreover, these techniques will enable you to solve the problem that led you here. However, you will master several crucial Excel features and functions during this article, which will come in extremely handy for any assignment using Excel.

## What is Dividend Growth Rate?

The percentage increase rate of a companyโs profits over a specific time frame is known as the Dividend Growth Rate or DGR. The DGR or Dividend Growth Rate is mainly evaluated on a yearly basis. However, this can also be estimated on a monthly or quarterly basis, if needed.

## Formulas for Dividend Growth Rate

There are two formulas or two different ways to determine Dividend Growth rate which are Arithmetic Average Dividend Growth Rate and Compound Dividend Growth Rate.

The formula for Arithmetic Average Dividend Growth Rate is given below here:

G1= The first yearโs annual dividend growth rate

Gi= Rate of dividend growth in the ith year

n = Phase count

The formula for Compound Dividend Growth Rate is given below here:

Here,

Dn = Dividend at last year

D0 = Dividend at first year

n = Phase count

## Learn to Calculate Dividend Growth Rate in Excel with These 4 Examples

Here in this article, we will learn how to calculate dividend growth rate in Excel using different approaches. To be exact, Iโve provided a total of 4 methods, 2 for each type down below.

Example 1

### 1. Calculating Dividend Growth Rate for Arithmetic Model

Here we will be learning to calculate the Dividend Growth Rate by simple arithmetic formulas. We will be calculating the individual growth rate for every year and then calculate the average growth rate for the total time being.

Example 1.1

#### 1.1. Using Conventional Formula

Here we are using Basic formula. Follow the steps below.

โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ

• At first, we will be determining growth rate for each year by the formula. Copy following formula in cell D7 as we need at least two values for the formula.

=(C7/C6-1)*100

• After that, we are going to need the Phase count n. So we are going to use the COUNTA function to count cells.

=COUNTA(C6:C15)

• Now we are going to determine the average annual dividend growth rate with the following formula.

=(D7+D8+D9+D10+D11+D12+D13+D14+D15)/D17

• Finally, the average annual dividend growth rate is determined.

Example 1.2

#### 1.2. Utilizing SUM Function

In this part we are using the SUM function. Follow the steps below.

โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ

• In this section, we are going to use the SUM function to get the Average Annual Dividend Growth Rate.
• Follow the previous method 1.1 until the final formula for average annual dividend growth rate, use this formula instead.

=SUM(D7:D15)/D17

• Finally, the result is like this.

Example 2

### 2. Calculating Dividend Growth Rate for Compound Model

We can determine the dividend growth rate for a compound model in two different ways. One is by arithmetic formula, and another way is by using LOGEST function. The LOGEST function, in regression analysis produces an exponential curve that suits the data and provides an array of numbers that define the graph. The function must be provided as an array equation since it delivers an array of values

Example 2.1

#### 2.1. Using Conventional Formula

Here we are using Basic formula. Follow the steps below.

โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ

• Firstly, we need the Phase count, so copy the following formula.

=COUNTA(C6:C15)

• After that, press Enter and copy the formula for determining Compound Dividend Growth Rate.

=((C15/C6)^(1/C17)-1)*100

• Finally, press Enter to get the annual Compound Dividend Growth Rate by formula.

Example 2.2

#### 2.2. Applying LOGEST Function

In this part, we are using the LOGEST function. Follow the steps below.

โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ

• The period value must be in date format when using the LOGESTย function. You may therefore perform this with the DATE function, as seen below.

=DATE(B6,1,1)

• After that, we are going to calculate the daily dividend growth rate. We are using the following formula.

=LOGEST(D6:D15,C6:C15)

• Press Enter for the results.

• To determine the Annual Growth Rate copy the following formula.

=((G7^365)-1)*100

• Press Enter to get the result.

## Dividend Growth Rate Calculator in Excel

To create a calculator, we can apply the same formulas. However, applying the formulas for blank cells will give us errors. So to avoid those unnecessary error warnings on the spreadsheet, we are going to useย the IFERROR function here. We will use theย COUNTAย andย SUMย functions as usual.

Hereโs how to create a custom calculator for calculating Dividend Growth Rate by only taking the value of Dividend per share.

โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ

• First, put the following formula in the 2nd cell of the Yearly Growth Rate.

`=IFERROR((C7/C6-1)*100,"")`

• Then, copy the following formula in the Number of Periods cell.

=COUNTA(C6:C15)

• Finally copy the following formula in the Average Annual Dividend Growth Rate.

`=IFERROR(SUM(D7:D15)/D17,"")`

• Thus we created the Calculator, You just need to insert the value of Dividend Per Share and the calculator will automatically deliver you the Average Annual Dividend Growth Rate value.

• For instance, we are randomly providing some Dividend Per Share data to get the Average Annual Dividend Growth Rate.

## ๐ Important Notes

`๐๏ธ`ย  Use Ctrl+Shift+Enter if you are not using Microsoft 365.

`๐๏ธ`ย  While writing formulas carefully, see the suggestions and donโt forget the commas and parentheses.

`๐๏ธ`ย  Be careful while defining criteria to be accurate as the datasets.

`๐`ย  The article demonstrated how to calculate dividend growth rate in Excel.

`๐`ย  In the first section, we showed how to determine dividends with simple arithmetic formula in Excel.

`๐`ย  Then, we demonstrated how to calculate compound dividends in Excel.

`๐`ย  Finally, weโve created a Dividend Calculator for you so you can just input data and get the result.