← Financial Modeling in Excel from Scratch
Lesson
Practice: DCF with formulas — from FCF to equity value
Build a DCF with formulas on a timeline of years: FCF, discount factors and PVs of cash flows, Gordon TV with a g < WACC flag, EV, and equity value.
Practice: Zerno's DCF on one sheet
Practice: Zerno's DCF on one sheet
Let's build the DCF of the Zerno coffee shop on one sheet. At the top are the assumptions: WACC 15%, g 4%, tax 25%, debt and cash at the end of 2026. Below are the years 2027–2031 in columns B–F and the calculation rows. You write a formula for one year and fill it across the row for all years, so the reference to an assumption is locked with a $ sign.
Worked example — 2027 (table below): from EBIT we get NOPAT, from it FCF, then the year-1 factor and the PV of the cash flow. 2028–2031 work the same way; only EBIT, depreciation, and the year number n change.
Then the model's outputs:
• the sum of the five PVs; NPV over the FCF series gives the same number;
• TV at the end of 2031 with the Gordon model, and the g < WACC flag: “OK” or “ERROR”;
• PV(TV) — TV × the 2031 factor (n = 5);
• EV = sum of the PVs + PV(TV).
From EV to equity value. EV belongs to all investors. In general, you subtract debt, preferred stock, and minority interests from EV and add cash. Zerno has no preferred stock or minority interests, so we simplify: equity value = EV − net debt, where net debt = debt − cash. Debt of 24 and cash of 14 are at the end of 2026, from the balance sheet in lesson 2.4.
2027, column B
| Row | How we calculate it | 2027 |
|---|---|---|
| NOPAT | EBIT × (1 − tax) | 44 × 0.75 = 33 |
| FCF | NOPAT + depreciation − CapEx − increase in WC | 33 + 8 − 12 − 2 = 27 |
| Factor | 1 / (1 + WACC)^n, n = 1 | 1 / 1.15 = 0.8696 |
| PV of cash flow | FCF × factor | 27 / 1.15 = 23.48 |
Lesson notes
Practice: Zerno's DCF on one sheet
Let's build the DCF of the Zerno coffee shop on one sheet. At the top are the assumptions: WACC 15%, g 4%, tax 25%, debt and cash at the end of 2026. Below are the years 2027–2031 in columns B–F and the calculation rows. You write a formula for one year and fill it across the row for all years, so the reference to an assumption is locked with a $ sign.
Worked example — 2027 (table below): from EBIT we get NOPAT, from it FCF, then the year-1 factor and the PV of the cash flow. 2028–2031 work the same way; only EBIT, depreciation, and the year number n change.
Then the model's outputs:
• the sum of the five PVs; NPV over the FCF series gives the same number;
• TV at the end of 2031 with the Gordon model, and the g < WACC flag: “OK” or “ERROR”;
• PV(TV) — TV × the 2031 factor (n = 5);
• EV = sum of the PVs + PV(TV).
From EV to equity value. EV belongs to all investors. In general, you subtract debt, preferred stock, and minority interests from EV and add cash. Zerno has no preferred stock or minority interests, so we simplify: equity value = EV − net debt, where net debt = debt − cash. Debt of 24 and cash of 14 are at the end of 2026, from the balance sheet in lesson 2.4.