How Do I Calculate Stock Value Using the Gordon Growꦕth Model in Excel?

The Gordon growth model (GGM), or the 澳洲幸运5官方开奖结果体彩网: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 grꦇow at a constant rate.

The model assumes a company exists forever and pays dividends that increase at a constant rate. It has 澳洲幸运5官方开奖结果体彩网:♏advantages as well as disadvantages.

To estimate the 澳洲幸运5官方开奖结果体彩网: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.

Understanding the Gordon Growth Model

The 澳洲幸运5官方开奖结果体彩网:intrinsic value of a stock can be foun✱d 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 澳洲幸运5官方开奖结果体彩网:dividend per share one year from now, k is the investor's required rate of return, and g is the expected 澳洲幸运5官方开奖结果体彩网:dividend growth rate.

How to Calculate Intrinsic Value Using Excel

Using the Gordon growth model to find 澳洲幸运5官方开奖结果体彩网:intrinsic value is fairly simple to calculate in Mi🍌c๊rosoft Excel.

To get starteওd, 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 y🌊ou 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 澳洲幸运5官方开奖结果体彩网:growth rate is 6%, and your required ꧑rate of return is 22%.𓃲 

To determine the intrinsic value, plug the values from the example above i💃nto Excel as fol🉐lows:

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 s𒀰tock ABC in this example is $3.98 per share.