Calculate Dividend Growth Rate in Excel [2+ Formulas]

Knowing a company’s dividend growth rate in previous years can be very beneficial for an investor. So that, the investor can look into the performance of that company in the past, did they make a profit or loss? This article will help you with 2+ formulas to calculate the dividend growth rate in Excel.

What is Dividend Growth Rate (DGR)?

I am going to explain the terms Dividend and Dividend Growth Rate separately.

When you want to invest your money in a company, you buy a small share of the company. You become a shareholder of that business company.

After a period of time, the company will make a good amount of profit. And when they make a profit, they will reward all the shareholders with a percentage of the profit for investing in their company.

This percentage of the profit given to the shareholders is called a Dividend.

The Dividend Growth Rate means the percentage form of the growth rate of a company’s or corporation’s dividend over a span of time.

🔗 Calculate Percentage of a Number in Excel [4 Cases Explained]

Formulas to Calculate Dividend Growth Rate

There are two ways to calculate the Dividend Growth Rate:

    1. Using the Arithmetic Mean Formula
    2. Using the Compound Growth Formula

1. The formula of Dividend Growth Rate Using the Arithmetic Mean

D = (G1+G2+G3+...+Gn)/n

In the above formula,

  • D = Dividend Growth Rate
  • Gn = Dividend Growth in the  nth year
  • n = Number of total years

🔗 How to Calculate Annual Growth Rate in Excel (5 Different Cases)

2. The Formula of Dividend Growth Rate Using the Compound Growth

D = ((Dn/D0)^(1/n))-1

In the above formula,

  • Dn = Dividend in the last year of the period
  • D0 = Dividend in the first year of the period
  • n = Number of total years

Formula to Calculate Dividend Growth Rate in Excel (Arithmetic mean and Compound Growth)

🔗 2 Ways to Calculate Bacterial Growth Rate in Excel

Method #1: Dividend Growth Rate Using Arithmetic Mean

To show you how use can calculate the DGR using the arithmetic mean, I am using a simple dataset of a company with 3 columns Year, Dividend per share (DPS), and the yearly growth rate.

I have inserted two spare cells for the Number of Years (cell C12) and Dividend Growth Rate (cell C13).

Now Follow The Guide

Step_1: Write this formula in cell C13:

=SUM(C3:C10)/C12

Dividend Growth Rate Using Arithmetic Mean in Excel


Formula Explanation

SUM(C3:C10) calculates the summation of the values from cell C3 to cell C10.

C12 is the total period of time from the year 2014 to 2022, 8 years.


Step_2: Press ENTER to apply the formula.

So we can see that the DGR is 0.16. But usually, DGR is expressed in percentage.

Result of Using Dividend Growth Rate Using Arithmetic Mean in Excel

To convert the result from decimal to a percentage,

Step_3: Select cell C13.

Step_4: Click on the Home tab.

Step_5: From the Number group, select the ‘%’ option.

Final Result

Now the result is in the percentage form.

🔗 3 Ways to Forecast Growth Rate in Excel

🔗 2 Ways to Calculate Revenue Growth Rate in Excel

Method #2: Dividend Growth Rate Using Compound Growth

This method is also trouble-free like the previous one.

To show you how to apply it, I am using the previous dataset.

Now Follow The Guide

Step_1: Type the formula in cell C13:

=(B10/B2)^(1/C12)-1

Dividend Growth Rate Using Compound Growth in Excel


Formula Explanation

  • B10 is the final dividend per share (DPS).
  • B2 is the starting dividend (DPS).
  • C12 is the number of total periods, 8 years.

Step_2: Press ENTER.

Step_3: Convert the decimal result into a percentage, and select the ‘%’ command from the Number group.

Final Result

Both methods will give you the same result. You can memorize one of the two methods.

Result of Using Dividend Growth Rate Using Compound Growth in Excel

🔖 Reminder

Notice that both formulas require the difference between the final year of the dividend and the starting year of the dividend. So it’s better if you keep the number of years in a different cell of your dataset before using the formula, just like I did.

🔗 3 Scenarios of Exponential Growth Formula in Excel

🔗 2+ Ways to Calculate Monthly Growth Rate in Excel

Calculate Sustainable Growth Rate

A Sustainable Growth Rate (SGR) refers to the maximum increase in the sales value a company can achieve on its own without taking any external financial support or debt.

The Formula of Sustainable Growth Rate(SGR):

SGR = ROE x (1 - Dividend Payout Ratio)

You can calculate the Return of Equity (ROE) by dividing the net income of a company by the average equity of the shareholders.

Let me give you a simple example of SGR. I have a dataset where the Return of Equity and Dividend Payout Ratio is available.

Now Follow The Guide

Step_1: Write this formula in cell C7:

=C4*(1-C5)

Calculate Sustainable Growth Rate in Excel


Formula Explanation

C4 is the ROE and C5 is the Dividend Payout Ratio here.


Step_2: Press ENTER.

Step_3: Select the ‘%’ command from the Number group to convert the decimal result into a percentage.

Final Result

Here you have the resulted SGR. The formula is really easy to remember.

Result of Calculating Sustainable Growth Rate in Excel

Conclusion

I think now you have a clear idea about the Dividend Growth Rate (DGR) and how to calculate it in Excel. I hope the formula of Sustainable Growth Rate (SGR) also helped you in some way. Feel free to comment below if you have further confusion regarding this topic. Have a good day!

(Visited 127 times, 1 visits today)

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *