discounted cash flow excel: build a two-stage DCF model that updates itself

discounted cash flow excel searches almost always come from the same place: you understand the concept, you have seen the formula, and you have hit the wall where the model needs fourteen numbers per company and every one of them has to be typed in by hand from a filing. Then the quarter closes and every number is stale. The maths in a DCF is the easy part. Keeping the inputs alive is the part that kills the model.

This guide builds a two-stage discounted cash flow model in Excel where every input is a live formula, walks through the four decisions that actually determine the output, and shows the two places where a DCF quietly stops being useful. At the bottom there is a complete workbook to download in both a static version and a live-formula version.

What a DCF model needs, and where each number comes from

Everything below is a real MarketXLS function. The point of the table is that the entire input side of a discounted cash flow model can be sourced with ten formulas.

Ten formulas, one ticker cell, and the input side of the model refreshes on every open. Change the ticker and the whole workbook re-points.

What a discounted cash flow model actually claims

A DCF says one thing: a business is worth the cash it can hand to its capital providers from now until it stops existing, with future cash discounted because a dollar in year eight is worth less than a dollar today.

In practice that becomes three lines:

Enterprise Value = SUM( FCF(t) / (1 + WACC)^t ) for t = 1 to 10
                   + [ FCF(10) x (1 + g) / (WACC - g) ] / (1 + WACC)^10

Equity Value     = Enterprise Value - Total Debt + Total Cash

Fair Value/Share = Equity Value / Shares Outstanding

Everything else in a valuation workbook is a way of pressure-testing the four assumptions those lines hide: the starting cash flow, the growth path, the discount rate, and the terminal growth rate.

Step 1: get the starting cash flow right

Free cash flow is operating cash flow less capital expenditure. It is the cash left after the business has paid to keep itself running and to grow.

The temptation is to start from net income because it is the number everyone quotes. Resist it. Net income runs through depreciation schedules, impairments, and accrual timing. Free cash flow is closer to what actually arrives.

In the workbook the starting cash flow is built from two formulas so the per-share and total figures can never drift apart:

=FreeCashFlowPerShare("AAPL") * Shares_Outstanding("AAPL") / 1000000

Operating cash flow sits next to it with =OperatingCashFlow("AAPL") so you can see the size of the capital spending gap. A company converting most of its operating cash flow into free cash flow has a very different reinvestment profile from one converting a third of it, and that difference should inform the growth rate you pick next.

One caution worth building into your habits: a single trailing twelve month figure can be a peak or a trough rather than a normal year. For a cyclical business, look at several years before you accept the starting point. The template exposes the number rather than burying it precisely so you can override it.

Step 2: build the discount rate instead of picking one

Most discounted cash flow spreadsheets use 10 percent because 10 percent is a round number. That single choice moves the answer more than any other input in the model.

The weighted average cost of capital is the blended return that debt and equity holders require. The equity side comes from the capital asset pricing model:

Cost of Equity = Risk-Free Rate + Beta x Equity Risk Premium

With =TreasuryRate10y() supplying the risk-free rate and =Beta("AAPL") supplying the slope, the only judgement call left is the equity risk premium, which is commonly estimated somewhere between 4 and 6 percent.

The debt side is simpler. Take an approximate yield on the company's borrowings, then tax it down, because interest is deductible:

After-Tax Cost of Debt = Pre-Tax Cost of Debt x (1 - Tax Rate)

Weight the two by market capitalisation and total debt and you have a WACC that came from observable inputs rather than from a round number. The workbook does this on a dedicated sheet so the build-up is visible rather than buried inside one long formula.

Worth knowing before you trust the output: beta estimates vary meaningfully between data providers depending on the lookback window and the benchmark index. Two reasonable analysts can produce WACC figures a full point apart on the same company. That is a limitation of the method, not a bug in the spreadsheet, and it is why the next section matters more than the base case.

Step 3: use two growth stages, not one

Applying a single growth rate for ten years implies competitive advantage never decays. It always does. The template splits the projection:

• Stage 1, years 1 to 5. Near-term growth, where visibility is best. Anchor it on realised free cash flow growth and consensus estimates, not on hope.
• Stage 2, years 6 to 10. The fade. Competition arrives, the addressable market fills up, and growth should step down.
• Terminal growth, year 11 onward. At or below long-run nominal GDP, roughly 2 to 3 percent.

That last one is not a stylistic preference. If terminal growth exceeds the discount rate, the perpetuity formula divides by a negative number and the model returns nonsense. If it merely exceeds long-run nominal GDP, the model is quietly asserting that the company eventually becomes the entire economy. The workbook checks both conditions explicitly on the Inputs sheet and reports PASS or REVIEW.

Step 4: understand how much of your answer is the terminal value

This is the check almost every DCF tutorial skips, and it is the one that tells you how much to trust the output.

The terminal value is the present value of everything past year ten. In the workbook it is reported as a percentage of enterprise value. Running the model across twenty large caps with a 7 percent stage 1 growth rate, a 4 percent fade and 2.5 percent terminal growth, the terminal value share ranged from 32 percent for a high-growth semiconductor name to 77 percent for a major integrated energy company.

When that number crosses roughly 75 percent, your valuation is no longer an analysis of the next decade. It is an assumption about the perpetuity with a decade of decoration attached. The model still runs. It just is not telling you what you think it is telling you.

A worked example, and why the base case is the least interesting output

Take Apple with data as of 7 August 2026: a share price of $312.13, trailing free cash flow of about $98.8 billion, 14.59 billion shares outstanding, $98.7 billion of total debt, $54.7 billion of cash, and a beta of 1.09. With a 4.63 percent ten-year Treasury yield and a 5 percent equity risk premium, the CAPM cost of equity lands near 10 percent, and the blended WACC comes out at 9.93 percent.

Feed that into a two-stage model at 7 percent then 4 percent growth with 2.5 percent terminal growth and the fair value lands at roughly $115 per share against a $312 quote.

That output is not a claim that the stock is mispriced. It is a demonstration of what a DCF does to any business trading on a high multiple of current cash flow: it converts the entire question into an argument about the growth assumption. Run the same model across the five scenarios in the template and the fair value moves from $64 in the deep bear case to $380 in the blue sky case. A six-fold spread from five sets of defensible assumptions is the honest output. The single base case number is the least informative thing on the sheet.

This is the point at which many people conclude that DCF models are useless. The better conclusion is that you are asking the model the wrong question.

The reverse DCF asks a question you can actually answer

Instead of forecasting cash flow and comparing the result to the price, hold the price fixed and solve for the growth rate that justifies it.

For the same Apple inputs, the stage 1 growth rate that makes the model output equal the $312.13 market price is approximately 25 percent per year for five years, followed by a fade to half that rate for the next five.

Now you have a question a human can evaluate. Has this business sustained 25 percent free cash flow growth before? Is the addressable market large enough? What specifically would have to go right? You have replaced an unanswerable question, what is this worth, with an answerable one, is this growth rate plausible.

The Reverse DCF sheet in the template does this with a ladder of growth rates from negative 4 percent to 21 percent, computing the implied fair value at each and using INDEX and MATCH to surface the rate that lands closest to the current quote. No solver, no iterative calculation setting, no circular references.

Where a discounted cash flow model stops working

A screener that never returns "not applicable" is hiding something. Two of the twenty companies in the template's watchlist deliberately fail, and the failures are instructive.

Banks and insurers. A large bank in the watchlist reports deeply negative trailing free cash flow. Nothing is wrong with the business. Loan growth runs through operating cash flow, so free cash flow measures balance sheet expansion rather than cash generation. A free cash flow DCF is simply the wrong tool here. A dividend discount model or an excess return model is the right one.

Heavy investment phases. A large enterprise software company in the list also prints negative trailing free cash flow, this time because of a capital expenditure cycle building out infrastructure. That is a timing problem, not a quality problem, but a mechanical DCF cannot distinguish between the two. It would either return a negative value or, worse, return a plausible-looking number built on a cash flow base that does not represent the business.

The template flags both cases in a suitability column rather than producing a number anyway. Other situations where the method struggles include pre-profit companies with no stable cash flow base, and deep cyclicals where the trailing figure is a peak or a trough.

Building the model in Excel

Here are the formulas the workbook actually uses, all verified against the MarketXLS function library:

' Company identity and price
=Name("AAPL")
=Sector("AAPL")
=QM_Last("AAPL")

' The cash flow being discounted
=FreeCashFlowPerShare("AAPL")
=OperatingCashFlow("AAPL")
=FreeCashFlowOneYearGrowth("AAPL")

' The equity bridge
=Shares_Outstanding("AAPL")
=TotalDebt("AAPL")
=TotalCash("AAPL")
=MarketCapitalization("AAPL")
=EnterpriseValue("AAPL")

' The discount rate
=Beta("AAPL")
=TreasuryRate10y()
=TotalDebtToEquity("AAPL")

' Cross-checks and comparison multiples
=PriceToFreeCash("AAPL")
=EnterpriseValueToEBITDA("AAPL")
=EarningsEstimates_consensusEpsGrowthRate_next5year("AAPL")

The discount factor and present value columns are plain Excel:

' Discount factor for year N
=1/(1+$WACC)^N

' Present value of that year's free cash flow
=FCF_N * DiscountFactor_N

' Terminal value at year 10
=FCF_10*(1+g)/($WACC-g)

The one structural trick worth stealing: the whole enterprise value calculation collapses into a single SUMPRODUCT over the cash flow strip and a year index row, which is what makes the scenario grid and the reverse DCF ladder possible without repeating the projection block dozens of times.

=SUMPRODUCT($C15:$L15/(1+$WACC)^$C$13:$L$13)

That works because SUMPRODUCT handles the exponent vector natively, with no array entry required.

What is in the template

Ten sheets, with every assumption in a yellow input cell on one sheet that drives all the others.

Two details worth calling out. The Base scenario reads directly from the Inputs sheet and the WACC Builder, so it reconciles exactly with the DCF Model tab rather than drifting from it. And the screener shows its working: nine columns exposing each step of the two-stage arithmetic, so you can audit any row instead of trusting it.

Download the templates:

• - Real values as of 7 August 2026, with the formula behind every number shown alongside it
• - Every data cell is a live formula that refreshes on open

Both files open in Excel 2016 or later. The formula version needs the MarketXLS add-in installed and signed in.

Frequently asked questions

What is the difference between levered and unlevered free cash flow in a DCF?

Unlevered free cash flow is the cash available to all capital providers before debt payments, and discounting it at WACC produces enterprise value. Levered free cash flow is what remains after debt service, and discounting it at the cost of equity produces equity value directly. This template uses the unlevered approach with an explicit equity bridge, which is the more common convention and makes the debt and cash adjustments visible rather than embedded.

What terminal growth rate should I use in a DCF model?

At or below long-run nominal GDP growth, which puts most practitioners in the 2 to 3 percent range. Anything higher implies the company grows faster than the economy forever, which cannot be true. The template checks this automatically and flags terminal growth above 3 percent for review.

Why does my DCF give such a different answer than the market price?

Usually because the discount rate or the growth assumption differs from what the market is implicitly using, and both compound over ten years and then again through the terminal value. A one percentage point change in the discount rate commonly moves a large-cap DCF value by 15 to 25 percent. Rather than trying to reconcile the gap by adjusting inputs until the answer looks reasonable, run the reverse DCF and examine the growth rate the price implies.

Can I use a DCF model to value a bank?

Not with free cash flow. Loan book changes flow through operating cash flow, which makes free cash flow a measure of balance sheet growth rather than cash generation, and it is frequently deeply negative for healthy banks. Use a dividend discount model or an excess return model instead. The template flags financial companies rather than producing a misleading number.

How many years should the explicit forecast period be?

Ten years is the common standard and is what this template uses, split into two five-year stages. Shorter periods push more of the value into the terminal assumption, which is already the least reliable part of the model. Longer periods create an illusion of precision about years that nobody can forecast.

Do I need Excel's iterative calculation or Solver for a reverse DCF?

No. The template uses a pre-computed ladder of growth rates with INDEX and MATCH to find the closest match to the current price. It recalculates instantly, works in any Excel version, and avoids the circular reference problems that catch people out with Solver-based approaches.

How often should the inputs be refreshed?

Prices move daily, but the fundamental inputs, free cash flow, debt, cash and share count, only change quarterly. The practical rhythm is to rebuild assumptions after each earnings release and let the price-dependent outputs refresh continuously. The formula version handles the second part automatically.

The bottom line

A discounted cash flow model in Excel is not hard maths. It is a present value calculation, an equity bridge, and a division. The difficulty has always been that the model needs a dozen live inputs per company, and hand-typing them from filings means the model is stale the day after you build it.

Pulling free cash flow, debt, cash, share count and beta with formulas removes that friction, and removing it changes how the model gets used. Instead of building one valuation and defending it, you can run twenty companies through the same assumptions in an afternoon, or run one company through a hundred assumption sets and look at the distribution.

The most valuable habit this workbook encourages is the reverse DCF. Forecasting a decade of cash flow is genuinely hard. Evaluating whether a company can sustain the growth rate its current price already implies is a question you can actually research, argue about, and be wrong about in a useful way.

None of this is investment advice, and no output from this workbook is a price target. A DCF is a tool for structuring an argument about value, and it is sensitive enough to its inputs that it can be badly wrong while the arithmetic is perfectly correct. Treat the range as the answer, not the midpoint.

Explore the full function library at MarketXLS, or book a demo to see the valuation workflow built live on your own watchlist.