DCF model Excel searches almost always come from the same place: you want to know what a stock is actually worth, not just what it trades for, and you want to do the math yourself in a spreadsheet you control. A discounted cash flow model is the cleanest way to do that. It values a business as the present value of the cash it will generate for its owners over time. The problem is never the concept. The problem is the plumbing: pulling the right free cash flow number, the right debt and cash figures, the right share count, and keeping them current as new filings land. This guide walks through how a DCF model works, how to build one in Excel, and how a ready-made MarketXLS template wires every input to a live formula so the valuation refreshes itself. A downloadable workbook is included at the end.
DCF Model Excel: The Core Idea in One Table
Before the mechanics, here is the whole model on one screen. A two-stage DCF has exactly five moving parts, and everything else is arithmetic.
| Component | What it is | Where the number comes from |
|---|---|---|
| Free cash flow (FCF0) | The latest annual cash left after capital spending | =hf_Free_Cash_Flow("AAPL", 2025) |
| Stage-1 growth (g1) | How fast FCF grows over the explicit 5-year forecast | Your assumption, anchored to history |
| Discount rate (r / WACC) | The return you demand, used to discount future cash | Your assumption, informed by =Beta("AAPL") |
| Terminal growth (g) | Perpetual growth after year 5 | Your assumption, usually 2% to 3% |
| Net debt | Total debt minus cash, to bridge to equity value | =hf_Total_Debt(...) - hf_Cash_at_end_of_period(...) |
From those five inputs the model computes a single output: intrinsic value per share. Compare that to the current price and you have a margin of safety, positive or negative. That is the entire exercise. The rest of this article is about doing each step honestly and letting Excel keep the data fresh.
This is educational analysis, not investment advice. A DCF is an estimate that depends heavily on assumptions you choose, and small changes to the discount rate or terminal growth can move the answer by a wide margin. Treat the output as one input among many.
What a Discounted Cash Flow Model Actually Does
The logic behind a DCF is simple. A business is worth the cash it can hand to its owners across its life, adjusted for the fact that a dollar next year is worth less than a dollar today. Money has a time cost, so future cash flows get discounted back to the present.
A two-stage model splits the future into two parts. The first stage is an explicit forecast, usually five years, where you project free cash flow year by year. The second stage captures everything after that with a terminal value, which assumes cash flow settles into a slow, steady perpetual growth rate. Adding the present value of both stages gives enterprise value. Subtract net debt and you get equity value. Divide by shares outstanding and you get intrinsic value per share.
Written out, the model looks like this:
Enterprise Value = SUM( FCF_t / (1 + r)^t ) for t = 1..N
+ [ FCF_N * (1 + g) / (r - g) ] / (1 + r)^N
Equity Value = Enterprise Value - Net Debt
Intrinsic Value per Share = Equity Value / Shares Outstanding
Here r is the discount rate, g is terminal growth, and N is the number of forecast years. The first line is the explicit forecast. The bracketed piece is the Gordon growth terminal value, discounted back to today. That is the whole engine.
Where Most DCF Models in Excel Go Wrong
Building the formula structure is the easy part. The hard part, and the reason most homemade DCF spreadsheets quietly rot, is the data. Three failure points show up again and again.
The first is a stale starting free cash flow. If FCF0 is copied from a filing you read six months ago, every projected year and the terminal value inherit that error, magnified. The second is a sloppy net debt bridge. Enterprise value has to be reduced by debt and increased by cash to reach equity value, and pulling those two numbers from different periods introduces noise. The third is share count drift. Buybacks and issuance change the denominator constantly, and a share count that is a year out of date can distort intrinsic value per share by several percent.
None of these are conceptual mistakes. They are maintenance mistakes. The fix is to stop hardcoding and let the spreadsheet fetch each figure on demand. That is exactly what the MarketXLS formula layer does.
Building the DCF Model in Excel with MarketXLS
MarketXLS is an Excel add-in that turns fundamental and market data into worksheet functions. Instead of pasting a free cash flow number, you write a formula that returns it and recalculates when you refresh. Every function below was verified against the MarketXLS function library before it went into the template.
Step 1: Pull the Free Cash Flow Starting Point
Free cash flow is the foundation of the model. The historical function returns the annual figure for a given fiscal year:
=hf_Free_Cash_Flow("AAPL", 2025)
Because the year is a parameter, you can point it at a cell on your dashboard and change every company's fiscal year in one place. To sanity-check the trend, pull the two prior years and compare:
=hf_Free_Cash_Flow("AAPL", 2024)
=hf_Free_Cash_Flow("AAPL", 2023)
If reported FCF has been choppy, a smooth projected growth rate is a red flag worth revisiting.
Step 2: Set the Discount Rate
The discount rate is the return you require to hold the stock. Many analysts anchor it to a weighted average cost of capital, and the cost-of-equity piece leans on the stock's sensitivity to the market:
=Beta("AAPL")
A higher beta implies more volatility and usually a higher required return. In the template the discount rate is a yellow input cell so you can set it deliberately rather than burying it in a formula. Beta is a reference to inform that choice, not a mechanical output.
Step 3: Build the Net Debt Bridge
Enterprise value is a claim on the whole business. To get to what equity holders own, subtract debt and add back cash. Net debt captures both:
=hf_Total_Debt("AAPL", 2025) - hf_Cash_at_end_of_period("AAPL", 2025)
Pulling both figures for the same fiscal year keeps the bridge internally consistent. Cash-rich companies produce negative net debt, which correctly increases equity value.
Step 4: Get the Share Count
Intrinsic value per share needs an accurate denominator. Use the historical common share count so it matches the fiscal year of the rest of your inputs:
=hf_Total_common_shares_outstanding("AAPL", 2025)
Step 5: Compare to the Market Price
The output of a DCF only means something next to the current price. The live quote closes the loop:
=QM_Last("AAPL")
Intrinsic value per share divided by the current price, minus one, gives the implied upside or downside. That single percentage is what the whole model builds toward.
The Template: What Is Inside
The workbook applies this model across a 15-stock large-cap watchlist so you can compare intrinsic value to price on a consistent basis, then adapt it to any ticker you like. There are seven sheets.
How To Use
A plain-language walkthrough of each sheet, the DCF formula, and the assumptions behind it. It also states clearly that the workbook is educational and that a DCF is sensitive to its inputs.
Main Dashboard
The control panel. Yellow input cells hold the discount rate, stage-1 growth, terminal growth, forecast years, and required margin of safety. Every other sheet references these cells, so changing one number reprices the entire watchlist. A snapshot table shows live price, market cap, P/E, free cash flow, and beta for each name using formulas like:
=QM_Last("MSFT")
=MarketCapitalization("MSFT")
=PERatio("MSFT")
=hf_Free_Cash_Flow("MSFT", 2025)
DCF Model
The engine. For each ticker it projects five years of free cash flow, discounts each year, computes a Gordon terminal value on year-5 FCF, sums to enterprise value, subtracts net debt, and returns intrinsic value per share plus the implied upside. The projection columns are transparent, so you can see the present value of each individual year rather than trusting a single black-box cell. The discount rate, growth, and terminal growth all reference the Main Dashboard, which means the whole table is a live function of your assumptions.
FCF Forecast
A reality check. It shows the projected FCF path next to reported free cash flow from prior years, alongside revenue growth:
=hf_Revenue_Growth("AAPL", 2025)
=FreeCashFlowPerShare("AAPL")
=PriceToFreeCash("AAPL")
If your projected path runs far above the company's actual history, the intrinsic value is probably too optimistic and the growth input needs to come down.
Scenario Analysis
A sensitivity grid. Pick one stock, enter its free cash flow, net debt, and shares, and the grid recomputes intrinsic value per share across a range of discount rates and terminal growth rates. The spread between the corners of the grid is the honest picture of how fragile any single point estimate is. This is the sheet that teaches humility.
Quality Screener
A DCF only makes sense for businesses whose cash flows are reasonably predictable. This sheet filters the watchlist on return on invested capital, return on equity, debt-to-equity, and operating margin, then scores each name:
=ReturnOnInvestedCapitalOneYear("AAPL")
=ReturnOnEquity("AAPL")
=TotalDebtToEquity("AAPL")
=OperatingMargin("AAPL")
Names that score poorly are weak DCF candidates because their future cash flows carry a much wider error band.
Comparison Matrix
The final ranking. It lines up current price against the DCF Model's intrinsic value, computes a margin of safety, pulls in the quality label, and produces a verdict such as "Undervalued and durable" or "Above intrinsic." Margin of safety here is defined as one minus price divided by intrinsic value, so a positive number means the model's assumptions imply the stock trades below its estimated worth.
Two Versions: Static Sample and Live Formulas
The download includes two files. The sample workbook is pre-filled with static illustrative values so you can explore the structure immediately, even without the add-in, and every sheet lists the MarketXLS functions that power it. The template workbook replaces those static values with live formulas that recalculate when you refresh in Excel. The sample is the tour. The template is the tool.
Download the templates:
- - Pre-filled with illustrative data and formula notes
- - Live-updating discounted cash flow model
You can learn more about the underlying functions in the MarketXLS features overview and the function documentation.
A Worked Example of the Logic
Suppose a company generates 100 in annual free cash flow. You assume 8% growth for five years, a 9% discount rate, and 2.5% terminal growth. Year 1 free cash flow is 108, discounted to about 99. Year 5 free cash flow is roughly 147, and the terminal value applies the Gordon model to that figure: 147 times 1.025 divided by the difference between 9% and 2.5%, which is a large number precisely because a perpetuity is worth a lot. That terminal value is then discounted back five years.
Notice how much the terminal piece matters. Because it captures every year beyond the forecast, it often makes up the majority of enterprise value. That is also why the terminal growth rate is dangerous. Nudging it from 2.5% to 3.5% shrinks the gap in the denominator and can lift intrinsic value sharply. The Scenario Analysis sheet exists to make that sensitivity visible rather than hidden.
DCF Model Excel FAQ
What free cash flow should I use in a DCF model?
Most DCF models use free cash flow to the firm, which is cash from operations minus capital expenditures, then bridge to equity with net debt. The template starts from the reported free cash flow figure via =hf_Free_Cash_Flow("AAPL", 2025) and subtracts net debt to reach equity value. Consistency matters more than perfection: use the same definition across every company you compare.
How do I choose the discount rate?
The discount rate reflects the return you require and the riskiness of the cash flows. A common approach is the weighted average cost of capital, where the cost of equity rises with the stock's beta. Beta is available through =Beta("AAPL"), but the discount rate itself stays a deliberate input in the template so you can set it with judgment rather than accept a mechanical number.
Why does the terminal value dominate my DCF?
Because it represents an infinite stream of cash beyond the explicit forecast, the terminal value usually accounts for most of enterprise value. That is normal, but it also means your terminal growth assumption is the single most influential number in the model. Keep it conservative, typically at or below long-run economic growth, and use the sensitivity grid to see the range it implies.
How is intrinsic value per share calculated?
Add the present value of the explicit forecast to the present value of the terminal value to get enterprise value, subtract net debt to get equity value, then divide by shares outstanding from =hf_Total_common_shares_outstanding("AAPL", 2025). The result is the model's estimate of what one share is worth under your assumptions.
Can I use this DCF template for any stock?
Yes. Change the tickers in the watchlist and the formulas fetch that company's data automatically. Keep in mind that a DCF works best for businesses with stable, predictable cash flows. The Quality Screener sheet flags names whose cash flows are too erratic for a reliable DCF, where you should demand a larger margin of safety or use a different framework.
Does the model update automatically?
The live template recalculates whenever you refresh MarketXLS in Excel, so as new filings and prices arrive the intrinsic value updates itself. The static sample holds fixed illustrative values and does not change, which is why it is meant as a structural reference rather than a working valuation.
The Bottom Line
DCF model Excel work rewards discipline in two places: the honesty of your assumptions and the freshness of your data. The framework is not complicated. Project free cash flow, discount it, add a terminal value, bridge to equity, and compare to price. What separates a spreadsheet you trust from one you abandon is whether the inputs stay current and consistent. By wiring free cash flow, debt, cash, shares, and price to verified MarketXLS functions, this template removes the maintenance burden and lets you focus on the assumptions that actually drive value. Use the sensitivity grid to stay humble, use the quality filters to pick appropriate candidates, and treat the intrinsic value as an estimate to pressure-test rather than a verdict to obey.
Explore more at MarketXLS or book a demo to see the live formulas build a discounted cash flow model in real time.
