The Dividend Discount Model in Excel

The dividend discount model values a share as the present value of every dividend it will ever pay. The simplest form, the Gordon growth model, assumes dividends grow at a constant rate forever and discounts them at the cost of equity. It works best for mature, stable payers like utilities and large banks, where dividends are predictable and the growth rate is sensible.

The formula and its parts

The Gordon growth model is Value = D1 / (r - g), where D1 is next year's dividend, r is the cost of equity, and g is the constant growth rate. D1 equals this year's dividend grown one period: D1 = D0 * (1 + g).

The discount rate r is the cost of equity, because dividends are cash flows to equity holders. Estimate it with CAPM: r = Rf + beta*(Rm-Rf). The model only holds when r > g; if growth meets or exceeds the discount rate, the denominator collapses and the value is meaningless.

When growth is not constant, use a two-stage model: project explicit dividends through a high-growth phase, then apply Gordon growth to a terminal year and discount everything back.

Worked example

Last year's dividend D0 was 2.00, the cost of equity r is 9 percent, and the long-run growth rate g is 4 percent. First grow the dividend one year, then apply the formula.

1. Next year's dividend: =2.00*(1+0.04) returns 2.08.
2. Spread: =0.09-0.04 returns 0.05.
3. Value per share: =2.08/0.05 returns 41.60.
4. Sensitivity: cut g to 3 percent and =2.06/0.06 returns 34.33, showing how sensitive the model is to the growth input.

=2.00*(1+0.04)/(0.09-0.04) returns 41.60.

Laying it out in a model

Keep the model auditable by separating the three drivers into their own input cells and computing D1 and value in their own steps.

• Put D0, r, and g in three labeled input cells, never inside the value formula.
• Compute D1 in its own cell so the grown dividend is visible.
• Reference a CAPM block for r rather than typing a rate, so the discount rate is traceable.
• Add a small data table flexing r and g to show the valuation range.

Pitfalls

The most common error is setting g too close to r. A growth rate of 8 percent against a 9 percent cost of equity gives a 1 percent denominator and an absurd value; the long-run growth rate should not exceed the economy's nominal growth.

Applying the model to a company that pays no dividend, or whose payout is erratic, produces a number with no meaning. Use the two-stage version for firms still ramping their payout, and reserve the single-stage model for mature, steady payers. Confusing D0 with D1 and forgetting to grow the dividend one period understates value by exactly one growth factor.