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