The dividend discount model is used to determine the appropriate price of a stock by considering the present value of the future dividends that the stock is expected to pay. Imagine that you are going to make a stock investment. Considering that you want to keep that stock for a long time, you would want to do the valuation based on the dividend prices. The Dividend Discount Model can help you with that. In this article, we will learn how to prepare the dividend discount model in Excel.
➤ Use the following formula to calculate the stock value using a basic dividend discount model:
=B1/(B2-B3)
➤ Replace B1 with the dividend value, B2 with the discount rate, and B3 with the growth rate.
There are three variants of the dividend discount model used to calculate the stock value. In this article, we will learn all of the variants with step-by-step processes. Let’s begin.
The Zero-Growth Model
This is the simplest method of the dividend discount model calculation. We assume that the dividend amount will not be changed throughout the lifetime of the stock, and calculate the stock price using limited info. We have the dividend price here, along with the required discount rate. Here is how to calculate the stock price:
➤ Insert the following formula in the B3 cell:
=B1/B2
The Gordon Growth Model
In most cases, the dividend price won’t stay the same for all of the future years. The gordon growth model uses a growth rate to calculate using a specific growth rate. In the following table, we have the dividend price, the discount rate, and the growth rate. Follow the steps below to calculate the stock price:
➤ Insert the following formula in the B4 cell:
=B1/(B2-B3)
The Multi-Stage Models
Realistically, an investment makes a high profit at the beginning, then it gets reduced, and finally it settles for a somewhat consistent growth rate. For this kind of investment, we can use the multi-stage model of dividend discounts. There are some variations of the multi-stage model. Most companies use the two-stage or three-stage variants of the multi-stage model. We will learn the three-stage variant, and you will learn how to modify it for two-stage or, if you want, more than three stages.
We have the dividend price, growth rate for years 1-3, 4-5, and 6+ in the table. In this stock, the dividend growth rate changes twice, and we get three growth rates, hence the three-stage naming. The growth rate is 25% for the first three years. Then it becomes 10% for the next two years. Finally, the rate settles at 5% for perpetuity. There is also the rate of return required by the investment. We will calculate the stock price using this information.
➤ Create a table for preparing the multi-stage model. The table will include the columns for the year, growth rate, dividend, PV factor, and the present value of the dividend.
➤ Insert the growth rate according to the source table.
➤ In the F2 cell, insert the following formula:
=B1+(B1*E2)
➤ In the G2 cell, write the following formula, and autofill till G6:
=(1+$B$5)^-D2
➤ Insert the following formula in the H2 cell, and autofill till H7:
=F2*G2
We are multiplying the dividend price by the PV factor to get the present value of the dividend.
➤ In the F2 cell, insert the following formula, and autofill till F5:
=F2+(F2*E3)
Note:
This time, the dividend price is acquired from the previous dividend price instead of D0. Then, we autofill the rest of the rows so that the new rows use the dividend prices from the previous cells.
➤ Now, we have to calculate the terminal value. This time, the dividend formula is like the following for the F7 cell:
=(F6+(F6*B4))/(B5-B4)
After calculating the price of the dividend like the previous cells, we divide it by the difference between the rate of return (B5) and the perpetual growth rate (B4) to calculate the terminal value.
➤ Calculate the stock value using the following formula:
=SUM(H2:H7)
Note:
In the two-stage dividend model, the growth rate will not change in years 4 and 5. Instead, we will calculate the terminal value from there. Other than that, all the steps are the same as the current method.
Frequently Asked Questions
When to use DDM vs DCF?
The dividend discount model is based on dividends, while the discounted cash flow model is based on future cash flows. An investment might produce different amounts of cash flows in the future. But, all of the cash flow from a business is not given to the shareholders. A dividend is the amount that an individual shareholder receives. DDM is more appropriate for people who purchase shares, and DCF is used by the project handlers.
Is DDM better than other valuation models?
Not necessarily. It depends on the type of investment you are making and the model that makes sense for your industry. Before making an investment decision, try to use multiple models to understand the investment’s worth.
Is DDM suitable for all companies?
The key issue with the DDM method is that it only works for dividend-paying companies. Suppose the company you are investing in is not a public limited company, or it provides returns in some other form. In that case, you cannot use the dividend discount model because you won’t have any dividends.
What are the risks of investing in DDM?
The DDM method is often inaccurate in real-world scenarios. Companies decide every year how much the dividend amount might be, so it changes. Companies also have no way of knowing how much profit they are going to make in the future. The market dynamics are complex, and the share prices change depending on a lot of variables. Considering all these, the DDM method is nothing but an oversimplification of share prices.
When to use the dividend discount model?
The dividend discount model makes sense in two cases. Primary stocks almost always provide decent dividends, so this model can be used there. If the company is stable enough and has been in the stock market for a long time, we have enough data to estimate the future dividends. In that case, this model can be used as well.
Wrapping Up
In this article, we have learned how to prepare the dividend discount model in Excel. Download the Excel file provided with this article to see the formulas in action. Comment down which method you like the most and intend to use for your financial calculations. We will see you next time in another tutorial.
