Home MarketXLS
Dashboard MarketXLS
Screener MarketXLS
Stock Ranks MarketXLS
Spreadsheet Builder MarketXLS
Logout MarketXLS
Guru functionsValuation Models

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

Written by admin
Mon Jun 26 2017
Gordon Growth Model in Excel
See how MarketXLS helps you take advantage in the markets.
Download Option Templates →
Gordon Growth Model in Excel
 

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.

Interested in building, analyzing and managing Portfolios in Excel?
Download our Free Portfolio Template
Download Option Templates
Stock screens
Public saved screens

Top 100 Gainers Today

Top 100 losers Today

Call: 1-877-778-8358
Ankur Mohan MarketXLS
I am so happy you are here. My name is Ankur and I am the founder/CEO of MarketXLS. Over the past four years, I have helped more than 2500 customers to implement their own investment research strategies and monitoring systems in Excel.
Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONSGet started today
Search for a stock

Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS

Get started today

Top MarketXLS Rank stocks

Dick's Sporting Goods Inc logo

Dick's Sporting Goods Inc

109.82
 
USD
 
5.18
 
(4.95%)
86
Rank
Optionable: Yes
Market Cap: 8,757 M
Industry: Specialty Retail
52 week range    
63.45   
   142.78
Dillard's Inc. logo

Dillard's Inc.

285.06
 
USD
 
12.30
 
(4.51%)
80
Rank
Optionable: Yes
Market Cap: 4,506 M
Industry: Department Stores
52 week range    
184.12   
   416.71
EOG Resources Inc. logo

EOG Resources Inc.

119.93
 
USD
 
8.20
 
(7.34%)
76
Rank
Optionable: Yes
Market Cap: 61,798 M
Industry: Oil & Gas E&P
52 week range    
80.67   
   147.99
STORE Capital Corporation logo

STORE Capital Corporation

31.36
 
USD
 
0.03
 
(0.10%)
75
Rank
Optionable: Yes
Market Cap: 8,941 M
Industry: REIT - Diversified
52 week range    
24.48   
   36.13
National Research Corporation logo

National Research Corporation

40.64
 
USD
 
0.84
 
(2.11%)
75
Rank
Optionable: No
Market Cap: 983 M
Industry: Diagnostics & Research
52 week range    
30.93   
   52.36
Chesapeake Energy Corporation logo

Chesapeake Energy Corporation

98.93
 
USD
 
4.72
 
(5.01%)
75
Rank
Optionable: Yes
Market Cap: 11,105 M
Industry: Oil & Gas E&P
52 week range    
56.75   
   105.93
NACCO Industries Inc. logo

NACCO Industries Inc.

48.70
 
USD
 
1.67
 
(3.55%)
74
Rank
Optionable: No
Market Cap: 346 M
Industry: Coal
52 week range    
28.32   
   63.19
Marathon Oil Corporation logo

Marathon Oil Corporation

24.97
 
USD
 
2.39
 
(10.58%)
74
Rank
Optionable: Yes
Market Cap: 14,494 M
Industry: Oil & Gas E&P
52 week range    
14.29   
   33.24
Destination XL Group Inc. logo

Destination XL Group Inc.

5.75
 
USD
 
0.33
 
(6.09%)
74
Rank
Optionable: No
Market Cap: 340 M
Industry: Apparel Stores
52 week range    
3.27   
   9.00
CF Industries Holdings Inc. logo

CF Industries Holdings Inc.

100.30
 
USD
 
4.05
 
(4.21%)
74
Rank
Optionable: Yes
Market Cap: 19,103 M
Industry: Agricultural Inputs
52 week range    
55.29   
   119.60

More Features