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.

## ๐ Download Excel File

The sample worksheet that was used during the discussion is available to download for free right here.

## 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:

**G _{1}**= The first yearโs annual dividend growth rate

**G _{i}**= Rate of dividend growth in the ith year

**n** = Phase count

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

Here,

**D _{n}**= Dividend at last year

**D _{0}**= 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**

- Now press
**Enter**and**copy down the formula along the column**.

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

- Press
**Enter**and**copy down the formula along the column**.

- 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,"")`

- After that,
**copy down the formula along the column**for the rest of the cells.

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

## ๐ Takeaways from This Article

`๐`

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

**Conclusion**

Firstly, I hope you were able to use the techniques I demonstrated in this **How to Calculate Dividend Growth Rate in Excel** lesson. As you can see, there are a lot of options on how to do this. However, decide deliberately on the approach that best addresses your circumstance. Most importantly, I advise repeating the steps if you become confused in any of the steps if you get stuck. However, practice on your own after taking a look at the Excel file in the practice workbook Iโve provided above because practice makes a man perfect. Above all, I earnestly hope you can use it. Please share your experience with us in the comment box about how your experience was throughout the article and what we need to improve or add to make things better for you folks. However, if you have any problem regarding Excel, spit it in the comment box. The **Excelden** crew is always available to answer your questions. In conclusion, keep educating yourself by reading. For more Excel-related articles, please visit our website **Excelden.com**.