How Do I Calculate Stock Value Using the Gordon Growth Model in Excel?

Steven Nickolas is a freelance writer and has 10+ years of experience working as a consultant to retail and institutional investors.

Chip Stapleton is a Series 7 and Series 66 license holder, CFA Level 1 exam holder, and currently holds a Life, Accident, and Health License in Indiana. He has 8 years experience in finance, from financial planning and wealth management to corporate finance and FP&A.

Skylar Clarine is a fact-checker and expert in personal finance with a range of experience including veterinary technology and film studies.

How Do I Calculate Stock Value Using the Gordon Growth Model in Excel?

The Gordon growth model (GGM), or the dividend discount model (DDM), is a model used to calculate the intrinsic value of a stock based on the present value of future dividends that grow at a constant rate.

The model assumes a company exists forever and pays dividends that increase at a constant rate. It has advantages as well as disadvantages.

To estimate the value of a stock, the model takes the infinite series of dividends per share and discounts them back into the present using the required rate of return. The result is a simple formula, which is based on the mathematical properties of an infinite series of numbers growing at a constant rate.

Key Takeaways

• The Gordon growth model calculates a stock's intrinsic value.
• The model bases the intrinsic value of stocks on the present value of future dividends that grow at a constant rate.
• Doing the calculation in Excel is simple, as you enter only five numbers into Excel cells.
• The Gordon growth model is also known as the dividend discount model (DDM).

Understanding the Gordon Growth Model

The intrinsic value of a stock can be found using the formula (which is based on mathematical properties of an infinite series of numbers growing at a constant rate):

Intrinsic value of stock = D₁ / (k - g)

D₁ is the dividend per share one year from now, k is the investor's required rate of return, and g is the expected dividend growth rate.

How to Calculate Intrinsic Value Using Excel

Using the Gordon growth model to find intrinsic value is fairly simple to calculate in Microsoft Excel.

To get started, set up the following in an Excel spreadsheet:

1. Enter "stock price" into cell A2
2. Next, enter "current dividend" into cell A3.
3. Then, enter the "expected dividend in one year" into cell A4.
4. In cell A5, enter "constant growth rate."
5. Enter "Required Rate of Return" in cell A6.

For example, suppose you are looking at stock ABC and want to figure out the intrinsic value of it. Assume you know the growth rate in dividends and also know the value of the current dividend.

The current dividend is $0.60 per share, the constant growth rate is 6%, and your required rate of return is 22%.

To determine the intrinsic value, plug the values from the example above into Excel as follows:

1. Enter $0.60 into cell B3.
2. Enter 6% into cell B5.
3. Enter 22% into cell B6.
4. Now, you need to find the expected dividend in one year. In cell B4, enter "=B3*(1+B5)," which gives you 0.64 for the expected dividend, one year from the present day.
5. Finally, you can now find the value of the intrinsic price of the stock. In cell B2, enter "=B4/(B6-B5)."

The current intrinsic value of the stock ABC in this example is $3.98 per share.

1. CFA Institute. "Discounted Dividend Valuation."

The offers that appear in this table are from partnerships from which Investopedia receives compensation. This compensation may impact how and where listings appear. Investopedia does not include all offers available in the marketplace.

The target payout ratio is a goal companies set for the amount of earnings they intend to pay out as dividends. The ratio is important to the company and shareholders.

When stockholders have the right to vote on matters of corporate policy making, they are said to own voting shares.

A dividend aristocrat is a company that not only pays a dividend consistently but continuously increases the size of its payouts to shareholders. Get here the 2024 dividend aristocrats list.

An aged fail is a transaction between two broker-dealers that has not been settled within 30 days of the trade date.

A due bill is a promissory note that states that a stock seller must deliver an upcoming dividend payment to the stock's buyer.

The technology, media, and telecommunications (TMT) sector is an industry grouping that includes companies focused on new technologies.

• About Us
• Terms of Service
• Dictionary
• Editorial Policy
• Advertise
• News
• Privacy Policy
• Contact Us
• Careers
• EU Privacy

We Care About Your Privacy

We and our 100 partners store and/or access information on a device, such as unique IDs in cookies to process personal data. You may accept or manage your choices by clicking below, including your right to object where legitimate interest is used, or at any time in the privacy policy page. These choices will be signaled to our partners and will not affect browsing data.

We and our partners process data to provide:

Store and/or access information on a device. Use limited data to select advertising. Create profiles for personalised advertising. Use profiles to select personalised advertising. Create profiles to personalise content. Use profiles to select personalised content. Measure advertising performance. Measure content performance. Understand audiences through statistics or combinations of data from different sources. Develop and improve services. Use limited data to select content. List of Partners (vendors)