The Discounted Cash Flow (DCF) model is one of the most important tools in finance. Analysts use it to value companies, evaluate investments, decide whether to buy a stock, or assess whether a business project is worth pursuing. It sounds complex — but at its core, it is just a series of Excel formulas applied to future cash flow estimates.
In this article, we build a complete DCF model from scratch in Excel — step by step. No finance degree required. By the end, you will understand what DCF is, why it matters, and how to build one yourself using formulas you already know.
What is a DCF Model?
The DCF model answers one question: "How much is a business worth today, based on the cash it will generate in the future?"
The core idea is simple: ₹100 today is worth more than ₹100 a year from now — because today's ₹100 can be invested and earn returns. So when we value a business's future cash flows, we need to "discount" them back to their present value using a discount rate.
The DCF formula in one line:
' DCF Formula:
Intrinsic Value = Sum of (Future Cash Flow / (1 + Discount Rate)^Year)
+ Terminal Value / (1 + Discount Rate)^Final Year
Three things you need to build a DCF model:
Free Cash Flows (FCF)
How much cash the business actually generates after all expenses and investments — projected for 5–10 years.
Discount Rate (WACC)
The rate used to bring future cash flows back to today's value. Reflects the risk of the investment.
Terminal Value (TV)
The estimated value of the business beyond the forecast period — usually the biggest number in any DCF.
Present Value (PV)
Each future cash flow discounted back to today. Sum of all PVs + Terminal Value = Intrinsic Value.
Set Up the Excel Structure
Open a new Excel workbook. We will build the model on one sheet. Set up the following structure:
- Row 1: Title — "DCF Valuation Model — [Company Name]"
- Row 3–15: Assumptions section (inputs with yellow background)
- Row 17–30: Free Cash Flow projections (Years 1–5)
- Row 32–40: Discounting and Present Value calculations
- Row 42–50: Terminal Value and Final Valuation
Best practice — colour coding:
- 🟡 Yellow background — input cells (assumptions you enter manually)
- ⬜ White/default — formula cells (calculated automatically)
- 🟢 Green background — output cells (final answer)
- Never hardcode numbers inside formulas — always reference assumption cells
Enter Your Assumptions
All inputs go in a clearly labelled Assumptions section. These are the only cells you change when analysing different scenarios.
For our example — a hypothetical company "TechCo Ltd":
| Row | Assumption | Value | Cell |
|---|---|---|---|
| B3 | Base Year Free Cash Flow (₹) | ₹50,00,000 | B3 — input (yellow) |
| B4 | Revenue Growth Rate — Year 1–3 | 15% | B4 — input |
| B5 | Revenue Growth Rate — Year 4–5 | 10% | B5 — input |
| B6 | Discount Rate (WACC) | 12% | B6 — input |
| B7 | Terminal Growth Rate | 4% | B7 — input |
| B8 | Shares Outstanding | 10,00,000 | B8 — input |
| B9 | Net Debt (₹) | ₹20,00,000 | B9 — input |
Project Free Cash Flows — Years 1 to 5
Now project the company's Free Cash Flow for the next 5 years using the growth rate assumptions.
| Row / Label | Base Year | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 |
|---|---|---|---|---|---|---|
| Year Number | 0 | 1 | 2 | 3 | 4 | 5 |
| Growth Rate | — | =B4 (15%) | =B4 (15%) | =B4 (15%) | =B5 (10%) | =B5 (10%) |
| Free Cash Flow (₹) | ₹50,00,000 | =B3*(1+B4) | =C20*(1+B4) | =D20*(1+B4) | =E20*(1+B5) | =F20*(1+B5) |
| FCF Values (₹) | — | ₹57,50,000 | ₹66,12,500 | ₹76,04,375 | ₹83,64,813 | ₹92,01,294 |
' Year 1 FCF formula (in cell C20):
=B3*(1+$B$4) ' Base Year × (1 + Growth Rate Year 1-3)
' Year 2 FCF formula (in cell D20):
=C20*(1+$B$4) ' Previous Year × (1 + Growth Rate)
' Year 4 FCF formula (switches to slower growth rate):
=E20*(1+$B$5) ' Uses Year 4-5 growth rate from B5
' Lock growth rate cells with $ so they don't shift when copied:
=$B$4 and =$B$5
Calculate Present Value of Each Cash Flow
Now discount each year's FCF back to present value using the discount rate. The formula is: PV = FCF / (1 + WACC)^Year
| Row / Label | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 |
|---|---|---|---|---|---|
| FCF (₹) | ₹57,50,000 | ₹66,12,500 | ₹76,04,375 | ₹83,64,813 | ₹92,01,294 |
| Discount Factor | 1/(1+12%)^1 | 1/(1+12%)^2 | 1/(1+12%)^3 | 1/(1+12%)^4 | 1/(1+12%)^5 |
| Discount Factor Value | 0.8929 | 0.7972 | 0.7118 | 0.6355 | 0.5674 |
| Present Value (₹) | ₹51,33,929 | ₹52,71,685 | ₹54,13,498 | ₹53,15,766 | ₹52,21,873 |
' Discount Factor for Year N (in row 25):
=1/(1+$B$6)^C17 ' C17 = Year number (1, 2, 3...)
' Present Value of each FCF (in row 26):
=C20/(1+$B$6)^C17 ' FCF ÷ (1+WACC)^Year
' OR use Excel's built-in PV function:
=PV($B$6, C17, 0, -C20) ' rate, nper, pmt, fv — note negative FCF
' Sum of all Present Values (PV of forecast period):
=SUM(C26:G26) ' = ₹2,63,56,751
Calculate the Terminal Value
The 5-year forecast captures only a small part of the company's value. The Terminal Value captures everything beyond Year 5 — and is usually the largest component of a DCF valuation (often 60–80% of total value).
We use the Gordon Growth Model (Perpetuity Growth Method):
' Terminal Value formula:
Terminal Value = FCF_Year5 × (1 + Terminal Growth Rate) / (WACC - Terminal Growth Rate)
' In Excel (assuming Year 5 FCF is in G20, WACC in B6, Terminal Growth in B7):
=G20*(1+$B$7)/($B$6-$B$7)
' With our numbers:
= ₹92,01,294 × (1 + 4%) / (12% - 4%)
= ₹92,01,294 × 1.04 / 0.08
= ₹95,69,346 / 0.08
= ₹11,96,16,825 ' Terminal Value (undiscounted)
Now discount Terminal Value back to today:
' Present Value of Terminal Value:
=Terminal_Value / (1+$B$6)^5
= ₹11,96,16,825 / (1.12)^5
= ₹11,96,16,825 / 1.7623
= ₹6,78,77,263 ' PV of Terminal Value
Calculate Intrinsic Value Per Share
Now put it all together — add the PV of forecast cash flows and the PV of Terminal Value, subtract Net Debt, and divide by shares outstanding.
| Component | Amount (₹) | Formula |
|---|---|---|
| PV of FCF — Year 1 to 5 | ₹2,63,56,751 | =SUM(C26:G26) |
| PV of Terminal Value | ₹6,78,77,263 | =TV/(1+B6)^5 |
| Enterprise Value (EV) | ₹9,42,34,014 | =PV_FCF + PV_TV |
| Less: Net Debt | (₹20,00,000) | =$B$9 |
| Equity Value | ₹9,22,34,014 | =EV - NetDebt |
| Shares Outstanding | 10,00,000 | =$B$8 |
| Intrinsic Value Per Share | ₹92.23 | =EquityValue/Shares |
' Complete final valuation formulas:
' Enterprise Value:
=SUM(C26:G26) + PV_TerminalValue
' Equity Value (subtract net debt):
=EnterpriseValue - $B$9
' Intrinsic Value Per Share:
=EquityValue / $B$8
' If current market price is ₹75, the stock looks undervalued:
=IF(IntrinsicValue > MarketPrice, "Undervalued ✅", "Overvalued ❌")
' → "Undervalued ✅" (₹92.23 intrinsic vs ₹75 market price)
' Margin of Safety:
=(IntrinsicValue - MarketPrice) / IntrinsicValue
' → (92.23 - 75) / 92.23 = 18.7% margin of safety
Sensitivity Analysis — What If the Assumptions Change?
DCF models are only as good as their assumptions. A small change in WACC or growth rate can dramatically change the valuation. Professionals always include a sensitivity table showing how the intrinsic value changes across different scenarios.
Build a sensitivity table using Excel's Data Table:
- Create a grid — WACC values (10%–14%) across the top row, Terminal Growth (2%–6%) down the first column
- The intersection cell references your Intrinsic Value Per Share formula
- Select the entire grid (including the reference cell)
- Data tab → What-If Analysis → Data Table
- Row input cell: your WACC cell (B6)
- Column input cell: your Terminal Growth cell (B7)
- Click OK — Excel fills all combinations instantly
' Sensitivity Table output — Intrinsic Value Per Share (₹)
' Rows = Terminal Growth Rate, Columns = WACC
10% 11% 12% 13% 14%
2% ₹124 ₹105 ₹91 ₹79 ₹69
3% ₹143 ₹120 ₹103 ₹89 ₹78
4% ₹169 ₹140 ₹119 ₹103 ₹89
5% ₹209 ₹170 ₹142 ₹121 ₹104
6% ₹278 ₹220 ₹180 ₹151 ₹128
Apply Conditional Formatting → Color Scale to the sensitivity table — low values (red) to high values (green). This instantly shows which assumption combinations produce undervalued vs overvalued results.
Common DCF Mistakes to Avoid
Mistake 1 — Being too optimistic with growth rates
Projecting 25% growth for 10 years is rarely realistic. Most mature businesses grow at 3–8% long-term. Base your growth rates on historical averages and industry benchmarks — not wishful thinking.
Mistake 2 — Terminal Growth Rate above WACC
If your terminal growth rate equals or exceeds your WACC, the model breaks (division by zero). Terminal growth should approximate long-term GDP growth — typically 2%–5%. Add a formula check: =IF(B7>=B6,"ERROR — Terminal Growth must be less than WACC",TV_formula)
Mistake 3 — Not doing sensitivity analysis
A single DCF number gives false precision. The true value is a range. Always build a sensitivity table showing at least 9 combinations (3 WACC × 3 growth rate scenarios). Present the bull/base/bear case, not just the base case.
Mistake 4 — Forgetting to subtract net debt
DCF gives you Enterprise Value — the value of the whole business including debt. To get Equity Value (what shareholders own), you must subtract net debt. Net Debt = Total Debt − Cash. Missing this step overvalues the shares.
Mistake 5 — Using revenue instead of free cash flow
Discount Free Cash Flow — not revenue, not net profit. FCF = Operating Cash Flow − Capital Expenditure. A business can show profit but negative FCF if it is spending heavily on equipment. FCF is what actually ends up in investors' pockets.
🎯 Summary — Building a DCF Model in Excel
- Understand the concept — DCF values a business by discounting future cash flows to today's value
- Set up structure — Yellow inputs, white formulas, green outputs. Never hardcode numbers in formulas
- Enter assumptions — Base FCF, growth rates (Year 1–3 and Year 4–5), WACC, terminal growth, shares, net debt
- Project FCF for 5 years — Each year = Previous Year × (1 + Growth Rate)
- Discount each FCF — PV = FCF / (1 + WACC)^Year. Sum all PVs
- Calculate Terminal Value — TV = FCF_Y5 × (1 + g) / (WACC − g). Discount TV to today
- Find Intrinsic Value — (PV of FCFs + PV of TV) − Net Debt ÷ Shares = Price per share
- Build sensitivity table — Data → What-If Analysis → Data Table. Show range of outcomes
Frequently Asked Questions
Master the Excel skills needed for finance
DCF modelling uses formulas, references, and data tables you learn in our free Excel courses. Start with the basics and work your way up to financial modelling.