Dividend Discount Model
Capital Markets Financial Model (Free Excel Download)
Estimate equity value from multi-stage dividend growth, payout ratios, cost of equity, terminal growth, and sensitivity analysis for income-focused investments.
professionals from Deloitte
Used by professionals from
About this model
The dividend discount model (DDM) values a mature, dividend-paying stock by projecting future dividend payments and discounting them back to present value. It is the preferred valuation method for companies with stable dividend policies (utilities, REITs, dividend aristocrats), where cash returns to shareholders are predictable and less subject to reinvestment risk than retained earnings. The model projects dividends through explicit forecast years (typically 5–10 years) using an assumed growth rate, then applies a terminal growth rate (usually 2–3%, aligned with long-run GDP growth) to estimate dividends in perpetuity.
The workbook begins with historical payout ratios and analyzes dividend sustainability: if a company pays 80% of earnings as dividends and earnings growth is positive, the payout ratio will shrink and the dividend is safe. The discount rate (required return) is derived from the capital asset pricing model (CAPM), capturing the investor's cost of equity. Sensitivity analysis shows how the fair value per share changes across a range of growth rates and required returns, allowing you to see whether the stock is cheap, fair, or expensive at the current market price.
This model is standard for income-focused investors, pension funds, and dividend-growth strategies. It works for any dividend-paying stock with at least 5–10 years of stable history and predictable payout policy.
What every model includes
Live formulas, no hardcoded values
Outputs are driven by live formulas, so the workbook updates from its assumptions instead of relying on hardcoded results.
All assumptions in one tab
Inputs are clearly marked in the Assumptions tab and separated from calculations, making it clear what to change and what to leave intact.
Statements always balancing
For integrated-statement models, the balance sheet, cash flow, and supporting schedules tie through properly.
Distinct schedules for clarity
Debt, working capital, taxes, and cash flow can get messy quickly. We group calculations in clear schedules, not across disconnected tabs.
No hidden macros or external links
There are no unexplained external workbook links or macros to undermine auditability or portability.
Changes flow through the model
Update a key driver and see the impact carry through the forecast, financing, and return outputs. We never use hardcoded numbers in formulas.
What's inside the Dividend Discount Model
- Multi-stage (2 or 3 stage) dividend growth forecast
- Cost of equity build-up using CAPM inputs
- Sustainable growth rate calculation from ROE and retention ratio
- Terminal value using the Gordon Growth method
- Sensitivity analysis for discount rates and terminal growth
- Historical dividend data and payout ratio analysis
- Terminal dividend yield calculation
- Discount rate (required return) derivation
Dividend Discount Model: How This Equity Valuation Template Works
This dividend discount model template estimates a company's intrinsic share value by projecting dividends over five years and extending them with a Gordon Growth terminal stage. It includes driver assumptions, cost of equity build-up, a dividend schedule, an H-model cross-check, a sensitivity grid, and eight validation checks to support income-focused equity analysis.
Operating drivers and scenario layer
The model uses a driver-based income statement: revenue is split across Products, Services, and Licensing, each with a base and five per-year growth rates.
- COGS follows a linear glide from a starting to a target percentage, and operating expenses combine fixed growth patterns with revenue-linked marketing and R&D.
- A four-row debt rollforward feeds interest expense, while a share rollforward drives both DPS and EPS on average shares.
- A scenario selector flexes four key drivers: a uniform revenue growth delta, terminal growth, equity beta, and a payout delta that shifts cost of equity, the dividend path, and terminal value without altering the underlying formulas.
Calculation flow from dividends to value
Net income from the income statement links to the dividend schedule, where total dividends are calculated as net income times the payout ratio, floored at zero. DPS is derived using average shares from the buyback rollforward.
- The valuation then discounts each year's DPS at the CAPM cost of equity and adds the present value of the terminal value, computed as the Year 5 DPS times one plus terminal growth, divided by the cost of equity minus terminal growth.
- An H-model provides a cross-check using trailing DPS and peak short-term growth, with a visible variance row to highlight differences between the two approaches.
Outputs and diagnostics
The headline output is the implied share price, broken into the present value of explicit dividends and the terminal value contribution. Additional outputs include the terminal value as a percentage of total value, the implied steady-state payout, and an H-model variance.
- A market context section compares the implied price to a current share price input to generate upside or downside and a buy, hold, or sell recommendation. Implied multiples like forward P/E, Gordon-justified P/E, and implied P/B provide valuation context.
- A 7x7 sensitivity grid sweeps cost of equity and terminal growth to show how value responds to discount rate and growth assumptions.
Practical use and validation checks
Eight validation checks monitor payout ratios, dividend non-negativity, the Gordon constraint, terminal value percentage, EBITDA margin, EPS positivity, dividend coverage, and whether assumed terminal growth exceeds sustainable growth.
- These checks help users avoid common pitfalls such as setting terminal growth above the cost of equity or pushing payout beyond feasible limits.
- The model is scoped to equity valuation only and does not include a full balance sheet or cash flow statement.
- Public templates are provided as values-only previews; the underlying model captures the full calculation logic but does not automatically recalculate in the free download.
Formatted to IB standards
Named theme colors repaint the whole workbook in one click, on top of an investment-banking structure with clear input, output, and cross-sheet reference styling - brand-ready, institutional-grade, and fully auditable.
Created by ex-finance professionals
Hey, I’m Alex and I created Finamodel.
Over my years in the finance industry I kept building the same models over and over again. Same structure, same assumptions, different logo. So I started building frameworks to turn them into clean, reusable templates.
Every model here is one I’d actually use for a client, and I personally vet each one before it goes up.
I’m not an expert in every industry, but I’ve built enough models to know what belongs in one. And when something is completely foreign to me, I reach out to my network for experts to work on our models with us.
Having a template library on hand cuts a first build from hours to minutes.
Need help finding your model? You’ll find me in the Finamodel app!
Frequently asked
What is a dividend discount model?+
It is a valuation model that estimates the intrinsic value of a stock by discounting expected future dividends back to present value.
When should I use a DDM instead of a DCF?+
DDM is most effective for mature companies where dividends are closely aligned with free cash flow to equity and there is an established payout history.
What should a DDM include?+
It should include dividend growth assumptions, cost of equity, sustainable growth rate logic, terminal value, and sensitivity analysis.
How does the model handle growth transitions?+
The model uses multi-stage logic to transition from a high-growth phase to a stable maturity stage, reflecting how payout capabilities shift over time.
Can I test different payout ratios?+
Yes. The model shows how changing the payout ratio affects immediate dividends, the sustainable growth rate, and total intrinsic value.
Have more financial modelling questions? Contact us
Related templates
DCF Model
Discounted cash flow valuation model with explicit forecast, terminal value, and WACC.
Comparable Companies Analysis
Pull trading multiples from peer companies and calculate implied valuation ranges.
Equity Portfolio Analysis
Portfolio-level analytics for equity holdings with performance attribution, risk, and rebalancing.
Mortgage Portfolio Model
Mortgage loan portfolio analysis with prepayment speeds, default rates, and cash flow projections.
