**The Dividend Discount Model** **Formula **determines the stock price based on the probable dividends one will pay. It helps to evaluate a company based on the concept that the stock price is worth the discounted sum of all its future dividend payments. In this article, we will discuss the procedure for using the **Dividend Discount Model Formula **in **Excel**.

## Download Practice Workbook

You can download the practice workbook from here.

## What Is Dividend Discount Model Formula?

Dividend discount model (**DDM**) formula is a theory that states the stock price is worth all the future cash flows expected to be generated by the firm discounted by a risk-adjustment rate. We can use the dividends to measure the returned cash flow to the shareholders. The generic formula for this model is:

**Intrinsic Value = Sum of Present Value of Dividends + Present Value of Stock Sale Price**

In a more advanced formula, the growth rate is also taken into account. Based on that fact, the formula can be derived for **zero growth**, **constant growth** or **variable growth**.

## Step-by-Step Procedures to Use Dividend Discount Model Formula in Excel

In this section, we will discuss the generic dividend discount model formula. In the dataset, we have introduced the required terms and some initial values to determine the **Total Intrinsic Value **using **DDM **formula. Follow the stepwise procedures given below to learn the application of **DDM **formula.

### STEP 1: Calculate Present Discounted Value of Dividends

- Firstly, calculate
**Present Discounted Value of Dividends**by applying the formula:

**PDVD = Divided Payment/(1+Return Rate)^Year Number**

- Write the following formula in
**Cell D6**for determining**PDVD**for**Year 2**.

`=D5/(1+F5)^2`

- Also, use a bit-adjusted formula in
**Cell C5**to get the**PDVD**for**Year 1**. Just use**1**as power in the formula given above.

`=C5/(1+F5)^1`

**Read More: ****How to Calculate Discount in Excel (2 Easy Methods)**

### STEP 2: Find Present Discounted Value of Stock

- As the stock price is fixed, now we calculate the
**Present Value**of the stock by applying**DDMÂ**formula. - For that, write the following formula in
**Cell D8**.

`=D7/(1+F5)^2`

**Read More: ****How to Calculate Discount Rate for NPV in Excel (3 Useful Methods)**

### STEP 3: Apply Dividend Discount Model to Get Intrinsic Value of Stock

- Afterward, calculate the
**Intrinsic Value**for**Year 1**which is the same value as**PDVD**for**Year 1**. - You can simply copy the value from
**Cell C6**to get this value.

- After that, just add the present values of dividends and stocks for
**Year 2**to get its**Intrinsic Value**. - So, write the following formula in
**Cell D9**.

`=D6+D9`

**Read More: ****How to Calculate Multiple Discounts in Excel (4 Easy Methods)**

### STEP 4: Determine Total Intrinsic Value Using Dividend Discount Model Formula

- Later on, Add the
**Intrinsic Value**for**Year 1**and**Year 2**to get the**Total Intrinsic Value**. - For that purpose, you can apply the following formula in
**Cell C10**.

`=C9+D9`

**Read More: ****How to Calculate Discount Factor in Excel (6 Common Ways)**

### Final Output

- As a result, we have calculated the
**Total Intrinsic Value**using**DDMÂ**formula.

## How to Use Different Dividend Discount Models in Excel

Letâ€™s discuss various types of **DDM **model formula for different growth rate for a stock. We will discuss examples of **zero growth rate**, **constant growth rate** and **variable growth rate**.

### 1. Use Zero Growth Dividend Discount Model to Find Stock Value

When we take into account zero growth rate, the formula is quite simple. Just divide the dividend by the return rate (risk-adjustment rate).

- Just insert the following formula in
**Cell C6**to get the**Intrinsic**Value.

`=C4/C5`

**Read More: ****How to Use IF Function in Excel for Discounts (4 Suitable Examples)**

### 2. Apply Constant Growth Dividend Discount Model to Get Stockâ€™s Intrinsic Price

Now letâ€™s discuss **DDM **formula for stock with a constant growth rate. The generic formula for this case is:

**Intrinsic Value = Dividend*(1+Growth Rate)/(Return Rate-Growth Rate)**

- Simply, write the following formula in
**Cell C7**to calculate the**Intrinsic Value.**

`=C4*(1+C5)/(C6-C5)`

**Read More: ****How to Calculate Discount Rate in Excel (3 Quick Methods)**

### 3. Utilize Variable Growth Dividend Discount Model to Determine Stock Value

As the last case, we will discuss stock with variable growth rate. Suppose the growth rate for a dividend is **18% **for the first **3 **years and will be fixed to **12% **later on.

- Firstly, calculate the dividend for the next year (
**Year 1**) adjusting with the growth rate. Just write the following formula in**Cell D5**.

`=C5*(1+D6)`

- In a similar manner, determine the dividends for the rest of the years.
- You can use the
**Fill Handle**to copy the formula in the adjacent cells.

- Then, calculate the
**Terminal Value**using the following formula in**Cell F7**.

`=G5/(G6-C12)`

**Note: **We divided the dividend value of **Year 4 **by the difference of growth rate and return rate.

- Afterward, determine the
**Present Value of Cash Flows**using**DDM**Write the following formula in**Cell E8**to get the value for**Year 2**.

`=E5/(1+C12)^2`

- Calculate the
**PV of Cash Flows**for the rest of the years. Just change the power number in the formula according to the year number.

- Later on, calculate the
**PV of Terminal Value**using the following formula in**Cell F9**.

`=F7/(1+C12)^3`

**Note: **We have used **3 **as power in the formula as the growth rate is fixed after **3 **years.

- Finally, add the
**PV of Cash Flows**and**PV of Terminal Value**to get the**Intrinsic**Value.

`=SUM(D8:F8,F9)`

## Conclusion

The dividend discount model formula is highly efficient to calculate the stock price with discounted adjustment-rate. Hope, you will be able to use **Dividend Discount Model Formula in Excel **after reading this article. If you have any queries or suggestions, please let us know in the comment section. Visit our **ExcelDemy Website **for more articles regarding **Excel**.