A:

The Gordon Growth Model, 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. 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 mathematical properties of an infinite series of numbers growing at a constant rate.

## How to Use 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 expected dividend per share, k is the investor's rate of return required 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 the required rate of return into cell B6 and "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 percent.

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 in this example is \$3.98 per share.

RELATED FAQS
1. ### What is the formula for calculating the capital asset pricing model (CAPM) in Excel?

Find out more about the capital asset pricing model (CAPM) and the formula for calculating it in Microsoft Excel. Read Answer >>

3. ### Intrinsic Value vs Current Market Value

Discover the differences between intrinsic and market values, what makes the former difficult to determine, and how investor ... Read Answer >>
4. ### How do you calculate a payout ratio using Excel?

Learn what the payout ratio is, what dividends per share and earnings per share are, and how the payout ratio is calculated ... Read Answer >>
5. ### How do I calculate fixed asset depreciation using Excel?

Learn what depreciation is and how to use Excel to calculate depreciation for fixed assets with the straight line and sum ... Read Answer >>
6. ### How can I create a yield curve in Excel?

Yield curves indicate where future interest rates are headed and you can actually make one in excel. Find out more about ... Read Answer >>
Related Articles
1. Investing

### What Is The Intrinsic Value Of A Stock?

Intrinsic value reduces the subjective perception of a stock's value by analyzing its fundamentals.
2. Investing

### What's the Gordon Growth Model?

The Gordon growth model is used to calculate the intrinsic value of a stock today, based on the stock’s expected future dividends. It is widely used by investors and analysts to compare the predicted ...
3. Investing

### Microsoft Is Paying Dividends. Is Its Share Price Undervalued Or Overvalued Based On DDM? (MSFT)

How can you use the dividend discount model to estimate the value the common stock of Microsoft?

### Improve your investing with Excel

Find out how to use Excel, a useful tool for assisting with investment organizations and evaluations.
5. Investing

### Why Dividends Matter To Investors

There is much evidence as to why dividends matter for investors, profitability in the form of a dividend check can help investors sleep easily. Learn more.
6. Investing

### Valuation Of A Preferred Stock

Determining the value of a preferred stock is important for your portfolio. Learn how it's done.
RELATED TERMS
1. ### Gordon Growth Model

The Gordon Growth Model is used to determine the intrinsic value ...
2. ### Intrinsic Value

Intrinsic value is the perceived or calculated value of a company, ...
3. ### Work Cell

A work cell is an arrangement of resources in a manufacturing ...
4. ### Stated Annual Interest Rate

A stated annual interest rate is the return on an investment ...
5. ### Return on Market Value of Equity - ROME

Return on market value of equity (ROME) is a measure used to ...
6. ### Manufacturing Cells

Manufacturing cells are sets of machines that are grouped by ...