Dividend Discount Model in Excel: DDM Valuation Guide (2026)

A dividend discount model in Excel can value a mature, dividend-paying stock in under five minutes — but the same five-minute build will mis-price a bank by 40% if you ignore the relationship between ROE, payout ratio, and sustainable growth. Most online DDM templates skip this entirely. This guide walks through a working single-stage, two-stage, and three-stage dividend discount model in Excel, including the formulas, the sensitivity tables that turn a point estimate into a defensible range, and the assumptions that quietly break the model.

DDM is the canonical valuation method for equities whose cash returns to shareholders are dominated by dividends: regional banks, utilities, insurance companies, consumer staples, and REITs. For these companies, free cash flow to equity is noisy or hard to define cleanly, while dividends are observable, board-approved, and reasonably stable. If you already know DCF cold but have never built a DDM, this is the post for you.

What Is the Dividend Discount Model?

The dividend discount model (DDM) values a stock as the present value of all future dividends, discounted at the cost of equity. The simplest form — the Gordon Growth Model — collapses an infinite dividend stream into a single closed-form equation: P = D1 / (r − g), where D1 is next year's dividend, r is cost of equity, and g is the perpetual growth rate.

DDM differs from DCF in two important ways. First, it discounts dividends (cash actually paid to shareholders) rather than free cash flow (cash the firm could pay). Second, it uses cost of equity directly, so the output is equity value per share — no enterprise-to-equity bridge required. That makes DDM cleaner for financial-services companies, where net debt is meaningless and EV/EBITDA is undefined.

ℹ️ Note: DDM only works for companies that pay dividends and whose dividends roughly track their economic earnings. For Berkshire Hathaway, Google, or Tesla, DDM is the wrong tool — use DCF or comparables instead.

When Should You Use DDM Instead of DCF?

A DDM is appropriate when three conditions hold: (1) the company has a stable, multi-year dividend history; (2) management has a stated payout policy you trust; and (3) dividends approximately equal sustainable free cash flow to equity. Banks and utilities almost always meet these tests. High-growth tech companies almost never do.

How Do You Build a Dividend Discount Model in Excel?

Building a DDM in Excel takes four steps: (1) set up an input block with cost of equity, current dividend, and growth assumptions; (2) project dividends across an explicit forecast horizon; (3) calculate a terminal value using Gordon Growth; (4) discount everything to present and divide by diluted shares. The output is intrinsic equity value per share, which you compare to the market price.

Below is a complete walkthrough using a fictional regional bank, "Meridian Bancorp," as the example. Current price $42, current annual dividend $2.10, cost of equity 9.0%, dividend growth 7% for five years fading to 3% terminal growth.

Step 1: Build the Input Block

Open a blank workbook and create an "Assumptions" sheet. Use a single column for inputs so they are easy to flex later.

Cell	Label	Value	Notes
B3	Current dividend (D0)	2.10	Last 12-month dividend per share
B4	Cost of equity (r)	9.0%	CAPM: Rf + β × ERP
B5	Stage 1 growth (g1)	7.0%	High-growth phase
B6	Stage 1 years	5	Length of explicit forecast
B7	Terminal growth (g∞)	3.0%	≤ long-run nominal GDP
B8	Diluted shares (M)	250	From most recent 10-Q
B9	Current price	42.00	For comparison to fair value

Always color-code input cells (e.g., blue font on yellow fill) and lock formulas. This is the first habit a model auditor checks for.

💡 Pro Tip: Compute cost of equity in its own cell using CAPM rather than hard-coding it. Use =RiskFree + Beta * EquityRiskPremium so you can flex assumptions cleanly. For a U.S. regional bank in mid-2026, 4.2% Rf + 1.1 β × 4.5% ERP ≈ 9.15%.

Step 2: Project Stage 1 Dividends

In row 12, create a year header (Year 1 through Year 5). In row 13, calculate dividends:

B13:  =B3*(1+B5)            // Year 1 = D0 × (1+g1)
C13:  =B13*(1+$B$5)         // Year 2 onward = prior × (1+g1)
D13:  =C13*(1+$B$5)
E13:  =D13*(1+$B$5)
F13:  =E13*(1+$B$5)

Alternative one-cell form using the exponent operator:

=$B$3*(1+$B$5)^COLUMN()      // grows D0 by g1 raised to the year number

For Meridian Bancorp the Year 1–5 dividends become $2.25, $2.40, $2.57, $2.75, and $2.94.

Step 3: Calculate the Terminal Value

At the end of Year 5, capitalize the Year 6 dividend at the Gordon Growth rate:

G13:  =F13*(1+B7)/(B4-B7)    // TV = D6 / (r − g∞)

D6 = $2.94 × 1.03 = $3.03. Terminal value = $3.03 / (9% − 3%) = $50.50.

⚠️ Warning: Gordon Growth is mathematically undefined when r ≤ g and produces nonsense (or a negative) when terminal growth exceeds cost of equity. Build a guard cell: =IF(B4<=B7, "ERROR: r must exceed g", "OK"). This single cell has saved more analysts from embarrassing valuations than any other.

Step 4: Discount to Present and Solve for Intrinsic Value

Row 14 holds discount factors: =1/(1+$B$4)^COLUMN(A1) filled across Years 1–5 and applied to the terminal value as well (terminal value is end-of-Year-5, so it uses the Year 5 discount factor).

Row 15 multiplies dividends × discount factor. Sum row 15 to get total present value:

B17:  =SUMPRODUCT(B13:F13, B14:F14) + G13*F14

For Meridian: PV of dividends ≈ $10.39, PV of terminal value ≈ $32.82, total intrinsic equity value per share ≈ $43.21 — slightly above the $42 market price, suggesting fair to mildly undervalued.

graph TD
    A[Inputs: D0, r, g1, g∞, N] --> B[Project Stage 1 Dividends]
    B --> C[Year N+1 Dividend × 1+g∞]
    C --> D[Terminal Value = D_N+1 / r−g∞]
    B --> E[Discount Each Dividend by 1+r^t]
    D --> F[Discount TV by 1+r^N]
    E --> G[Sum PVs]
    F --> G
    G --> H[Intrinsic Value Per Share]
    H --> I[Compare to Market Price]

DDM data flow: dividend discount model in Excel from input block to intrinsic value per share.

What Are the Three Main DDM Variants?

The three standard DDM variants — single-stage Gordon Growth, two-stage, and three-stage (the H-model is a close cousin) — differ only in how they model the growth path. Pick the one that matches the company's lifecycle stage. Using single-stage on a fast-growing dividend payer overstates value; using three-stage on a utility adds noise without insight.

Variant	Growth Pattern	Best For	Excel Complexity
Single-stage (Gordon Growth)	Constant g forever	Utilities, mature staples	Trivial: =D1/(r-g)
Two-stage	High g for N years, then terminal g	Regional banks, mature tech with growing dividend	~10 rows
Three-stage	High g, declining transition, terminal g	Insurance companies in expansion	~20 rows
H-model	Linear glide from high g to terminal g	Faster build than three-stage	One closed-form equation

The Gordon Growth Model (Single-Stage)

The single-stage version is the easiest to build but the easiest to misuse:

=B3*(1+B7)/(B4-B7)    // P = D0 × (1+g) / (r − g)

It assumes one constant growth rate forever. That assumption only fits truly mature companies (think Procter & Gamble, Duke Energy). For everything else, you need at least two stages.

The Two-Stage DDM

Use two-stage when a company is still growing dividends faster than the economy but will eventually mature. The Excel build is exactly what we walked through above: explicit dividend projection for Years 1–N, then Gordon Growth on Year N+1.

Example: A regional bank growing dividends 8% as it scales loan book and improves ROE, then fading to 3% as it saturates its geography. Two-stage DDM captures this far more accurately than a single 5% blended rate.

The Three-Stage DDM

Three-stage adds a transition phase where growth declines linearly from g1 to g∞. In Excel, build a growth-rate row that interpolates between stages:

// Years 1-5: high growth g1
// Years 6-10: linear decline from g1 to g∞
// Year 11+: terminal growth g∞

Year 6 growth:  =B5 - (B5-B7)/5*1
Year 7 growth:  =B5 - (B5-B7)/5*2
... etc

Apply each year's growth rate to the prior year's dividend, then capitalize Year 11 dividend at terminal growth for the terminal value. Three-stage is most useful for life insurers and growth-phase financials where margin expansion drives a multi-year dividend ramp.

How Do You Build a Sensitivity Table for DDM?

A DDM output without a sensitivity table is analytically incomplete. The two variables that swing valuation the most are cost of equity (r) and terminal growth (g∞) — flex both in a two-variable data table to produce a price-per-share matrix.

Set up the table as follows. Put cost of equity values across the top row (e.g., 7.5% to 11.0% in 0.5% steps) and terminal growth values down the left column (e.g., 1.5% to 4.5% in 0.5% steps). In the top-left corner cell, reference your intrinsic value output cell.

Then highlight the entire table range and run Data → What-If Analysis → Data Table. Set the Row input cell to your cost of equity cell ($B$4) and the Column input cell to your terminal growth cell ($B$7). Excel populates the matrix.

Row input cell:    $B$4   (cost of equity, varied across top)
Column input cell: $B$7   (terminal growth, varied down left)

The result is an instant range of fair values. For Meridian Bancorp at 9.0% r and 3.0% g∞ the model says $43.21. Flex r to 10% with g∞ at 2.5% and you get $35.40. That spread — roughly $35 to $52 — is the honest answer to "what is this stock worth," not the single $43.21 point estimate.

💡 Pro Tip: Add conditional formatting (Home → Conditional Formatting → Color Scales) to the sensitivity table. Green-to-red shading makes the asymmetry between optimistic and pessimistic assumptions immediately visible to anyone reviewing the model.

What Are the Most Common DDM Mistakes?

The most common DDM mistakes are: (1) setting terminal growth above long-run nominal GDP; (2) using a payout ratio that is inconsistent with the assumed growth and ROE; (3) applying DDM to non-dividend-paying or special-dividend companies; (4) forgetting to discount the terminal value; and (5) using current rather than next-period dividend as D1. Each of these can swing intrinsic value by double-digit percentages.

Mistake 1: Terminal Growth Above Nominal GDP

If terminal growth exceeds long-run nominal GDP growth (roughly 4–5% for the U.S.), the company eventually becomes larger than the economy — which is impossible. Cap terminal growth at 2.5–3.5% for U.S. companies. Use the 10-year Treasury yield as a reasonable upper bound.

Mistake 2: Inconsistent Sustainable Growth

The sustainable growth rate must equal ROE × retention ratio. If you assume 8% dividend growth, 12% ROE, and an 80% payout ratio, you have a contradiction: 12% × (1 − 80%) = 2.4% sustainable growth, not 8%. Build a check cell:

=IF(ABS(g_assumed - (ROE * (1-payout_ratio))) > 0.005,
    "INCONSISTENT: growth ≠ ROE × retention",
    "OK")

This is the single biggest analytical sin in DDM modeling. CFA Level II exam questions hammer it relentlessly for a reason.

Mistake 3: Wrong D1

The Gordon Growth Model uses next year's dividend, not the trailing dividend. If trailing-twelve-month DPS is $2.10 and you expect 7% growth, D1 = $2.10 × 1.07 = $2.25, not $2.10. A surprisingly large number of online templates get this wrong.

Mistake 4: Negative Denominator

If your cost of equity is 8% and terminal growth is 9%, the Gordon formula returns a negative number. The denominator (r − g) must be positive and meaningful — typically at least 200–300 basis points of separation. Any spread below that produces an unstable, hyper-sensitive valuation.

⚠️ Warning: As (r − g) shrinks, DDM value explodes hyperbolically. Going from a 4-point spread to a 2-point spread roughly doubles the terminal value. This is a feature of the math, not the business — always show the sensitivity to flag the fragility.

How Does DDM Compare to DCF and Multiples?

DDM, DCF, and trading multiples each value the same equity but discount different cash flows or use different anchors. For a regional bank, DDM and DCF (specifically dividend-equivalent FCFE) should land within 10%. Multiples (P/E, P/TBV) anchor to the market and reveal whether the entire sector is mispriced.

graph LR
    A[Equity Value] --> B[DDM: Discount dividends at cost of equity]
    A --> C[DCF: Discount FCFF at WACC, subtract debt]
    A --> D[Multiples: Apply P/E or P/TBV from comps]
    B --> E[Best for banks, utilities, REITs]
    C --> F[Best for non-financial corporates]
    D --> G[Cross-check for all]

Choosing among DDM, DCF, and multiples for equity valuation in Excel.

In a typical equity research report, you triangulate. DDM gives the fundamentals-based fair value. Comparable company analysis gives the market-implied value. Precedent transactions give the takeover-implied value. The intersection — or the divergence — is the actual investment thesis. Our precedent transactions analysis in Excel and comparable company analysis posts cover the cross-check methodology in depth.

How Do You Validate a DDM Model in Excel?

Validate a DDM by running four checks: (1) implied growth — back-solve the growth rate that reconciles the market price to your model; (2) reverse engineer — solve for the cost of equity implied by the current price; (3) check the sustainable growth equation; (4) verify the terminal value as a percentage of total intrinsic value (typically 60–80% — much higher means the model is essentially a terminal value bet).

The implied growth back-solve uses Goal Seek. With your intrinsic value formula in B17 and the current market price in B9, set Goal Seek → Set cell B17 → To value B9 → By changing B7 (terminal growth). The growth rate Excel returns is what the market is implicitly pricing in. If that number is wildly higher than what management or the industry can plausibly deliver, the stock is overvalued.

ℹ️ Note: Terminal value typically represents 60–80% of intrinsic value in a five-year two-stage DDM. If it exceeds 90%, you are effectively running a one-stage model — extend your explicit horizon or rethink the inputs.

DDM for Financial Services: A Special Case

Banks and insurers are the textbook DDM use case because they cannot meaningfully use FCFF. Net debt is undefined for a bank (deposits are funding, not debt in the usual sense), and capital expenditures don't have the same meaning. Instead, dividends are constrained by regulatory capital — specifically Common Equity Tier 1 (CET1) for banks under Basel III.

When modeling a bank's DDM, layer in a capital constraint check. Calculate the dividend that brings the bank to its target CET1 ratio after factoring in retained earnings, risk-weighted asset growth, and any buybacks. The dividend that flows into your DDM should be the lesser of (a) the dividend management has signaled, and (b) the dividend the capital stack supports.

For a tactical walkthrough of building three-statement banking models that feed into DDM, our three-statement financial model guide covers the linkage logic that ties net income to dividends and retained earnings.

Frequently Asked Questions

What is the formula for the dividend discount model in Excel?

The single-stage formula is =D1/(r-g), where D1 is next year's expected dividend, r is the cost of equity, and g is the perpetual dividend growth rate. For multi-stage, project dividends explicitly for the high-growth years using =D0*(1+g1)^t, calculate a Gordon Growth terminal value at year N, then discount everything at 1/(1+r)^t.

Why can't I use DDM for companies that don't pay dividends?

DDM values the dividend stream — with no dividends, there is no stream to discount. Companies like Google or Berkshire Hathaway return value via buybacks and reinvestment rather than dividends, so DCF (which uses free cash flow) is the correct tool. A FCFE-based model can be thought of as a "potential dividend" DDM if you trust that excess cash flow would be returned to shareholders eventually.

What growth rate should I use for terminal value in DDM?

Cap terminal growth at long-run nominal GDP — typically 2.5–3.5% for the U.S. Higher rates imply the company will eventually exceed the size of the entire economy, which is mathematically impossible over an infinite horizon. The 10-year Treasury yield is another useful upper bound, as it represents long-run risk-free expectations.

How do I calculate cost of equity for a DDM?

Use CAPM: Cost of equity = Risk-free rate + β × Equity risk premium. As of mid-2026, that is roughly 4.2% + β × 4.5%. Beta comes from regression against a broad market index (most analysts use the 5-year monthly beta from Bloomberg or a comparable source). For private companies or thinly traded stocks, use the industry average beta.

What is the difference between DDM and DCF?

DDM discounts dividends paid to shareholders at the cost of equity and produces equity value per share directly. DCF discounts free cash flow to the firm (FCFF) at WACC and produces enterprise value, from which you subtract net debt to get equity value. DDM is cleaner for financial services (where net debt is meaningless); DCF is preferred for non-financial corporates.

Final Takeaways

A working dividend discount model in Excel is a four-step build: input block, dividend projection, terminal value, discounting. The hard part is not the formulas — it is making sure terminal growth, payout ratio, and ROE are mutually consistent and that the sensitivity table honestly conveys the range of plausible values.

If you build DDMs frequently, you are doing the same input → projection → discount → sensitivity workflow every time. This is exactly the kind of structured financial modeling work where an AI Excel copilot like VeloraAI can write the formulas, set up the sensitivity tables, and flag inconsistent growth assumptions in seconds rather than minutes. Your job becomes evaluating the assumptions, not typing the formulas.

Next step: take the simplest dividend-paying name on your coverage list, build a two-stage DDM, and run the reverse-engineering Goal Seek to see what growth the market is pricing in. That number — not the consensus price target — is the single most actionable output a DDM produces.