Mehedi Hasan Shimul
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.
