DCF Model Builder

DCF Model Builder

Builds an institutional-quality DCF as a live Excel workbook: four sheets, three scenarios, three sensitivity grids, and a valuation a portfolio manager can argue with. A model that computes correctly and says nothing about the stock is half the job, so the construction rules and the judgement rules below are one document.

Evidence labels, source tiers, staleness, the readiness posture and the intake limits: .agents/skills/research-conventions/SKILL.md, read before the first figure enters the workbook.

• recalc.py reports errors, the case selector does not move the model, or the valuation looks wrong: read .agents/skills/dcf-model/TROUBLESHOOTING.md.
• The revenue build needs the drivers of a particular sector, or the company is a bank, an insurer, a miner or a REIT: read .agents/skills/dcf-model/references/sector-drivers.md.

Tools

• fundamentals MCP: get_financial_statements, get_financial_ratios, get_growth_metrics, get_historical_valuation; diluted shares come from here
• macro MCP: get_treasury_rates, get_market_risk_premium
• get_company_overview: share price, analyst consensus, earnings surprises, revenue segmentation
• yf_fundamentals MCP: compare_valuations, whose per-symbol record carries the beta field
• yf_analysis MCP: get_growth_estimates for consensus growth, one record per period (0q, +1q, 0y, +1y, +5y, -5y)
• Web search and fetch, and user-provided data, as supplements

Step 0: Before you build

The question budget, the shape of each question and what to do when no answer comes back are in .agents/skills/research-conventions/references/intake.md.

The forks that usually matter here: whether the user has an existing model to extend rather than a new build, the horizon, and whether the base case should follow guidance, consensus, or your own view.

Three anchors are required, not optional. A DCF without them produces a number nobody can act on:

A required input that cannot be sourced is written into the cell as required-and-absent with a comment saying what was searched, never silently defaulted, and the readiness posture is re-read from the table in .agents/skills/research-conventions/SKILL.md against that input state. Say it in the delivery message too, not only in the workbook.

Done when the three anchors are in hand with as-of dates, or the missing one is labelled and the posture is set, and any question asked has either an answer or a disclosed default.

Build Workflow

Execution pattern: build the DCF as a saved Python script (for example <task_name>/build_dcf.py) rather than inline ExecuteCode. Model building is iterative: you will debug formulas, tweak assumptions and rerun, and a saved script lets you Edit one section and rerun cheaply. Read references/workbook-patterns.md before writing the build script; it carries the row layouts and formula patterns. For all formatting, number formats and colour standards, follow .agents/skills/xlsx/SKILL.md.

Formulas, not hardcoded values. Every projection, margin, discount factor, present value and sensitivity cell is a live Excel formula. A number computed in Python and written into the cell is a defect even when the value is right today. With openpyxl, ws["F29"] = "=E29*(1+B$10)" is correct and ws["F29"] = 12500.0 is not. The only typed numbers a DCF should hold are historical actuals, the assumption drivers in the scenario blocks, current market data (share price, diluted shares, debt, cash), and a solved value under the one exception .agents/skills/xlsx/SKILL.md allows (the reverse DCF driver, Step 10). If you catch yourself computing a value in Python and writing the result, stop and write the formula instead. The model has to move when the user changes an assumption, and a hardcode breaks every downstream tie-out silently, because the check still evaluates and still reads OK.

Comment as you build. Every blue input carries its provenance comment as the value is created, with the real source in it, before you move to the next section. The format and the examples are in references/workbook-patterns.md.

Present each stage as you finish it.

A PTC turn is not chat-interactive at every step, so this is not a blocking question: present the block, say what you are about to build next, and carry on unless the user objects. The point is that a wrong margin assumption surfaces while it is cheap to fix, not after 75 sensitivity formulas have been wired to it.

1. Inputs - revenue, margins, diluted shares, net debt, beta, risk-free rate, each with its source
2. Projections - the projected top line and margin build, with the implied growth rates
3. FCF schedule - NOPAT through unlevered free cash flow for every projection year
4. WACC - the CAPM inputs, the capital structure weights, and the resulting rate
5. Equity bridge - enterprise value, net debt, equity value, implied price, implied return against spot
6. Sensitivities - the three grids

Do not build the model end to end and present it complete.

Step 1: Data Retrieval and Validation

• Financial statements: fundamentals MCP get_financial_statements(symbol, 'all', 'annual', 5); the diluted share count is weightedAverageShsOutDil on the income-statement records
• Ratios and metrics: get_financial_ratios(symbol); growth rates: get_growth_metrics(symbol)
• Historical valuation: get_historical_valuation(symbol) for the DCF fair value against price and the enterprise-value history; the exit-multiple back-check takes its comparison from evToEBITDA in get_financial_ratios
• Risk-free rate: macro MCP get_treasury_rates(), the 10Y
• Equity risk premium: macro MCP get_market_risk_premium(); the record carries no observation date, so the as-of is the retrieval date and the basis comment says so
• Consensus and price: get_company_overview
• Beta: yf_fundamentals MCP compare_valuations([symbol]), the beta field on the symbol's record; where the basis comment needs a window and frequency the model controls, regress the name's returns against an index over bars from yf_price MCP get_stock_history(ticker, period="5y", interval="1month") and record that basis instead
• Web search and fetch: current price, debt and cash when a tool does not carry them

Validate before building: net debt against net cash, diluted shares against recent buybacks or issuance, historical margins against the business model, growth against sector norms, tax rate in a defensible range.

Done when every input the model needs exists with a source and an as-of, and the validation list above has been walked.

Step 2: Historical Analysis (3-5 years)

Document revenue growth and its CAGR, margin progression (gross, EBIT, FCF), capital intensity (D&A and capex as a percent of revenue), working-capital efficiency (NWC change against revenue growth), and return metrics (ROIC, ROE).

Historical Metrics (LTM):
Revenue: $X million | Revenue growth: X% CAGR | Gross margin: X%
EBIT margin: X% | D&A % of revenue: X% | CapEx % of revenue: X% | FCF margin: X%

Done when each forecast driver you are about to set has a historical range beside it.

Step 3: Build Revenue Projections

Start from the latest actual, apply a growth rate per year, and show both the dollar amount and the calculated growth percent. Revenue(N) = Revenue(N-1) * (1 + growth), Growth(N) = Revenue(N)/Revenue(N-1) - 1.

Shape the path rather than typing a flat number: near-term growth reflects visibility, the middle years moderate toward the industry rate, and the final year approaches terminal growth. Where the sector has real drivers, build revenue over them instead of over a percentage: .agents/skills/dcf-model/references/sector-drivers.md.

Three scenarios, each a described world rather than three numbers:

Bear: conservative growth, margin compression or none, higher WACC, lower terminal growth, higher capex
Base: guidance or consensus growth, moderate operating leverage, market-implied WACC, GDP-aligned terminal growth
Bull: high-end growth, meaningful margin expansion, lower WACC, higher terminal growth, lighter capex

Done when every projection cell is a formula over the selected-case block, and the implied growth row prints beside the revenue row.

Step 4: Operating Expense Modeling

Model operating leverage rather than a fixed margin: percentages decline as revenue scales, and each of S&M, R&D and G&A keeps its own line. Where .agents/skills/dcf-model/references/sector-drivers.md names a driver for the sector, that driver governs the line instead: software S&M runs against new bookings, biopharma R&D is a commitment rather than a percentage. The percentage-of-revenue rule below is the default for every line the sector reference does not claim.

• A percentage-driven opex line is based on revenue, not gross profit. Operating expenses scale with the top line
• EBIT = Gross Profit - Total OpEx
• State the margin path as an argument: current gross margin to target gross margin, current EBIT margin to target, each with the reason (scale, mix, pricing, a named efficiency programme)

Done when the EBIT margin path has a stated reason per period, every line the sector reference claims runs off its named driver, and no opex row references gross profit.

Step 5: Free Cash Flow Calculation

EBIT
(-) Taxes (EBIT x tax rate)
= NOPAT
(+) D&A (non-cash, % of revenue)
(-) CapEx (% of revenue)
(-) Change in NWC
= Unlevered Free Cash Flow

State which free cash flow definition the model runs on and hold it: an unlevered stream discounts at WACC to enterprise value, a levered stream discounts at cost of equity to equity value. Mixing them is the most expensive error in this file. Audit for the three contaminations: interest expense inside an unlevered stream, the tax shield counted both in the cash flow and in the WACC, and non-operating income left in EBIT.

• Working capital: computed on the change in revenue, not the level. Negative is a source of cash, positive a use
• Maintenance against growth capex: name the split, because the terminal year should carry maintenance rather than the growth ramp

Done when the FCF row is a formula over NOPAT, D&A, capex and the NWC change, and the cash flow definition is written on the sheet.

Step 6: Cost of Capital (WACC)

Cost of Equity = Risk-Free Rate + Beta x Equity Risk Premium
After-Tax Cost of Debt = Pre-Tax Cost of Debt x (1 - Tax Rate)
Market Value Equity = Price x Diluted Shares
Net Debt = Total Debt - Cash
Enterprise Value = Market Cap + Net Debt
WACC = Cost of Equity x Equity Weight + After-Tax Cost of Debt x Debt Weight

Pre-tax cost of debt comes from the credit rating, the yield on the company's own bonds, or interest expense over average total debt, in that order of preference.

Basis discipline. Every WACC component is a defensible choice, and the provenance comment on its blue input is where the defence lives. Each comment records:

Market values, not book, for the weights. A comment reading "9.2%" is not provenance; a comment reading which tenor, which window and which date is what lets a reviewer disagree with a number rather than merely distrust it.

Special cases: the weights use gross interest-bearing debt at market value, or a stated target structure, never net debt; cash enters the equity bridge, not the discount rate. A net-cash company still weights its gross debt, and a company with no debt has a debt weight of zero, so WACC is the cost of equity.

Done when every WACC input carries a basis comment in the form above and the WACC cell is a formula over them.

Step 7: Discount Rate Application

Mid-year convention: periods run 0.5, 1.5, 2.5 and so on, and Discount Factor = 1 / (1 + WACC)^Period. PV of FCF = Unlevered FCF x Discount Factor.

Horizon: five years is standard, seven to ten for a company still converging on a defensible margin, three for a mature business. The explicit period should run until the drivers are stable, because everything after it is the terminal value.

Done when the discount-factor row is a formula over the period row and the WACC cell, and every period is present.

Step 8: Terminal Value

Perpetuity growth (preferred):

Terminal FCF = Final Year FCF x (1 + g)
Terminal Value = Terminal FCF / (WACC - g)
PV of Terminal Value = Terminal Value / (1 + WACC)^Final Period

g below the risk-free rate and below long-term nominal GDP, and always below WACC or the value is infinite. Conservative is 2.0 to 2.5 percent, moderate 2.5 to 3.5, and anything above that is a claim that the company outgrows the economy forever, which needs a sentence defending it.

Exit multiple (alternative): Terminal Value = Final Year EBITDA x Exit Multiple, with the multiple taken from where the subject and its peers actually trade.

The implied-exit-multiple back-check is mandatory, whichever method built the terminal value. Divide the terminal value by the terminal-year EBITDA and compare the result against the subject's own trading history from evToEBITDA in the key_metrics list of get_financial_ratios, and against the peer set. A perpetuity growth rate that implies an exit multiple far from where the stock has ever traded is the tell that the terminal assumptions are wrong, and it is a finding about the model rather than a footnote. Run it the other way too when the exit-multiple method was used: solve for the perpetuity growth rate that multiple implies, and check it is a rate a company could actually sustain.

Terminal value share of enterprise value is a structural finding about horizon adequacy, not a formatting note. Around half to two thirds is normal. Above 80 percent, the model is a claim about the terminal year wearing a forecast, and the answer is a longer explicit period, not a different growth rate. Below about 40 percent, check the terminal assumptions are not too conservative to be credible.

Done when both the implied exit multiple and the terminal share of EV are live cells on the sheet with Checks rows against them.

Step 9: Enterprise to Equity Value Bridge

Valuation Component,Amount ($M)
PV Explicit FCFs,X.X
PV Terminal Value,Y.Y
Enterprise Value,Z.Z
(-) Net Debt,A.A
Equity Value,B.B
Diluted Shares (M),C.C
Implied Price per Share,$XX.XX
Current Share Price (as of DATE),$YY.YY
Implied Return,+XX%

Anchor to spot. The current price and the implied return sit next to the fair value in the output block, both as-of stamped. Without them a fair value below spot reads as an unexplained number rather than as the sell case it is, and a reader cannot tell a 4 percent gap from a 40 percent one without arithmetic you should have done.

Net debt is total debt less cash: positive subtracts from EV, negative (net cash) adds. Use diluted shares. Where they exist and matter, bridge the other claims too: minority interests, unfunded pension, and operating leases when they are not already in debt.

Done when the bridge is a formula chain from enterprise value to implied price, and the implied-return cell references the spot cell rather than a typed number.

Step 10: Scenarios and Sensitivities

Pick the grid from the decision, not from habit. WACC against terminal growth answers "how much of this is the discount rate", which is often not the question:

Three grids stacked at the bottom of the DCF sheet is the default; swap one for the question actually being asked when it differs.

Grid construction. Use odd dimensions, 5x5 as standard and 7x7 where the range matters, so the grid has a true centre cell. Build each axis as [base - 2*step, base - step, base, base + step, base + 2*step], which puts the model's own assumption in the middle row header and the middle column header, where a reader can see which cell is the actual forecast. The centre cell therefore has to equal the model's headline output. That is the check that the grid is wired correctly, and the build script asserts it: after recalc.py, reopen the workbook with data_only=True, read the centre of every grid against the output that grid varies, and fail the build if they differ. .agents/skills/xlsx/SKILL.md requires this assertion; do not skip it. Highlight the centre cell (bold, BDD7EE fill) so the base case is visually anchored. Every data cell in all three grids, 75 in total, holds a full DCF recalculation formula written by an openpyxl loop, so the grids work the moment the user opens the file.

Weak sensitivity designs. Each of these produces a grid that looks like analysis and carries none:

• Dependent axes. Beta against WACC, or revenue growth against revenue CAGR: one axis moves the other, so the corners are worlds that cannot exist
• Off-centre base. The grid is built around something other than the model's own assumption, so the centre rule above cannot hold and the reader cannot see which cell is the actual forecast
• A range too narrow to inform. Every cell rounds to the same story; widen the step until the corners are genuinely different cases
• Sensitising a non-driver. The grid moves the output by a rounding error while the real driver sits fixed. If the corner cells differ by less than a few percent, sensitise something else
• A haircut bear case. A percentage cut to the base target is not a scenario, because nothing in the business had to happen for it

The bear case is mechanical. It is driven by a stated change to a driver, with the arithmetic shown: demand falls to this level, or the gross margin resets to that one, or the exit multiple derates to where the stock traded in the last downturn. The case then names what breaks, through which line item, to what number. Under the bear case, check that liquidity, covenants and maturities still work rather than only reporting a lower target: a company that cannot fund the bear case has a different downside from one that can.

Each case records where it came from: the model, a source, the user, your own judgement, or purely illustrative, with an as-of. A bull case taken from management's own targets is a different object from one you built.

Probabilities complete or nothing is weighted. A probability-weighted fair value is published only when the three cases satisfy the completeness rule in .agents/skills/research-conventions/references/judgment.md; otherwise the cases ship unweighted and labelled an illustrative skew, and no weighted fair value appears in the model or the message.