Download Gordon Growth Model In Excel (With Marketxls™ Template)

Gordon Growth Model is a popular valuation model that analysts use to calculate the intrinsic value of a stock based on the expected dividends in the future. In this article we will learn about what Gordon Growth Model is and how we can build the Gordon Growth Model in Excel.

Consider a company that is in a stable business and is expecting to grow at a constant growth rate over the next few years. Also, the company pays all its free cash flow as dividends to its shareholders. We can calculate the intrinsic value of such a company as the present value of its expected dividends, assuming that the company will continue to grow at the constant growth rate and will live for ever.

V = D1/(k-g)

Where:

• D1 is the expected dividend in the next period. if we have the current year’s dividend(D0), we can calculate D1 as D0(1+g)
• k is cost of equity or the required rate of return by shareholders
• g is the dividend growth rate

The model is most effective when used for valuing large stable companies in maturing markets that have a predictable dividend growth rate.

Gordon Growth Model Assumptions

The model has a few underlying assumptions:

1. The firm must have a stable business model and is not expecting to change it’s operations significantly over the next many years
2. The firm’s free cash flow to equity (FCFE) and therefore, dividends will grow at a constant growth rate. Dividends refer to money paid to shareholders out of a company’s earnings. Dividends are usually equal to a percentage of earnings per share.
3. The firm has a stable financial leverage in order to keep the cost of equity constant. Any change in financial leverage affects the cost of equity capital.
4. The firm pays out all the free cash flow to equity as dividends. This is not always true because we can’t trust managers to always payout the FCFE to shareholders. For this reason, the most common implementations of the Gordon Growth Model only consider dividends for stock valuation.

Gordon Growth Model – Example

For the purpose of explaining the model, we will take the stock of Duke Energy (NYSE: DUK). This fits our assumptions about the firm’s stable approach to business. Below are the stock’s data and assumptions:

1. The stock has a beta of 0.12. With a risk-free rate of 3.5% and a market risk premium of 5.5%, the cost of equity is 4.1% (refer excel sheet for details).
2. The dividend paid in year 2016 is 3.36.
3. We assume a constant dividend growth rate of 1%.

We these details, the Gordon Growth Model, calculates the stock’s value to be 108.16 which is higher than the current market price of 85.95. So, if are assumptions are accurate, then according the GGM, the stock is undervalued and investors should buy it.

Gordon Growth Model in Excel

We have developed Gordon Growth Model in Excel template that you can use to value any stock using this model. The excel template also showcases the Duke Energy example as shown above. The excel template makes use of the MarketXLS hf_ functions to fetch all market data. This includes data such as beta, current year dividends, the stock’s current market price, etc.