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.

how to calculate dividend growth rate in 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:

Average Annual Growth Rate formula

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:

Compound Dividend Growth Rate Formula

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

applying yearly growth rate formula

applying yearly growth rate formula in the rest of 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)

Counting phase to calculate dividend growth rate in excel

  • 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

Final formula for calculating dividend growth rate in excel

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

Final output

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

calculating dividend growth rate in excel using SUM function

  • Finally, the result is like this.

Calculating dividend growth rate in excel

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)

counting phase to calculate dividend growth rate using COUNTA function

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

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

Applying compound Dividend Growth rate formula

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

Compound Dividend Growth Rate results

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)

Turning year into date using DATE function

Turning Year into Date with DATE function 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)

calculating daily dividend growth rate in excel using LOGEST function

  • Press Enter for the results.

Results for daily dividend growth rate in Excel

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

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

Applying formula to calculate annual dividend growth rate from daily dividend growth rate in excel

  • Press Enter to get the result.

Outcome using LOGEST function


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

Creating a calculator for Dividend growth rate

Copying formula down along the column

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

=COUNTA(C6:C15)

Using the COUNTA function to count phase

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

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

Inserting the dividend growth rate formula to create a calculator

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

Instructional Catalogue for Dividend Calculator

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

Calculating dividend growth rate using custom calculator example


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

(Visited 59 times, 1 visits today)
Nashid

Nashid

Hello there. I am Nashid, an Engineer. I Love music, traveling, sports and gaming. I have recently grown interest in DATA SCIENCE and wish to build a career over it. Excel is one of the greatest modern digital creation of human. Life simply gets easier. My Articles are meant to lead you to that easier life with efficiency. If you need any kind of help regarding my articles even anything else try contacting me on social platforms of which I've provided. Keep learning. Wish me luck and Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo