Excel

DCF Valuation Model in Excel with Python

A DCF (Discounted Cash Flow) analysis values a company based on its projected future cash flows, discounted back to present value. It is the most fundamental valuation technique in finance. In this tutorial, we build a complete DCF model using Python to generate the Excel workbook and run sensitivity analysis.

Prerequisites

✓Understanding of DCF theory
✓Basic Excel
✓Basic Python

Step 1.Set up assumptions

These are the key inputs that drive the entire valuation. Changing any one materially impacts the result.

import numpy as np
import pandas as pd
from openpyxl import Workbook

assumptions = {
    "current_fcf": 100_000_000,
    "growth_rate_y1_5": 0.15,
    "growth_rate_y6_10": 0.08,
    "terminal_growth": 0.025,
    "wacc": 0.10,
    "shares_outstanding": 50_000_000,
    "net_debt": 200_000_000,
}

print("DCF Assumptions:")
for k, v in assumptions.items():
    print(f"  {k}: {v}")

Step 2.Project free cash flows

We project 10 years of cash flows with two growth phases, then discount each back to present value.

fcf_projections = []
current_fcf = assumptions["current_fcf"]

for year in range(1, 11):
    if year <= 5:
        growth = assumptions["growth_rate_y1_5"]
    else:
        growth = assumptions["growth_rate_y6_10"]

    current_fcf *= (1 + growth)
    discount_factor = 1 / (1 + assumptions["wacc"]) ** year
    pv = current_fcf * discount_factor

    fcf_projections.append({
        "Year": year,
        "FCF": current_fcf,
        "Discount Factor": discount_factor,
        "PV of FCF": pv
    })

df_fcf = pd.DataFrame(fcf_projections)
print(df_fcf.to_string(index=False))

Step 3.Calculate terminal value and fair value

Terminal value typically accounts for 60-80% of total value. This is why the terminal growth rate assumption matters so much.

# Terminal value using Gordon Growth Model
terminal_fcf = fcf_projections[-1]["FCF"] * (1 + assumptions["terminal_growth"])
terminal_value = terminal_fcf / (assumptions["wacc"] - assumptions["terminal_growth"])
pv_terminal = terminal_value / (1 + assumptions["wacc"]) ** 10

# Enterprise value
pv_fcfs = sum(p["PV of FCF"] for p in fcf_projections)
enterprise_value = pv_fcfs + pv_terminal

# Equity value
equity_value = enterprise_value - assumptions["net_debt"]
fair_value_per_share = equity_value / assumptions["shares_outstanding"]

print(f"PV of projected FCFs: ${pv_fcfs:,.0f}")
print(f"PV of terminal value: ${pv_terminal:,.0f}")
print(f"Enterprise value:     ${enterprise_value:,.0f}")
print(f"Equity value:         ${equity_value:,.0f}")
print(f"Fair value per share: ${fair_value_per_share:.2f}")

Step 4.Sensitivity analysis

A sensitivity table shows how fair value changes with different WACC and terminal growth rate assumptions.

wacc_range = [0.08, 0.09, 0.10, 0.11, 0.12]
growth_range = [0.01, 0.02, 0.025, 0.03, 0.035]

sensitivity = []
for w in wacc_range:
    row = {}
    for g in growth_range:
        tv = fcf_projections[-1]["FCF"] * (1 + g) / (w - g)
        pv_tv = tv / (1 + w) ** 10
        ev = pv_fcfs + pv_tv
        eq = ev - assumptions["net_debt"]
        fv = eq / assumptions["shares_outstanding"]
        row[f"{g:.1%} TGR"] = f"${fv:.2f}"
    row["WACC"] = f"{w:.0%}"
    sensitivity.append(row)

df_sens = pd.DataFrame(sensitivity)
df_sens = df_sens[["WACC"] + [c for c in df_sens.columns if c != "WACC"]]
print("Sensitivity Table (Fair Value per Share):")
print(df_sens.to_string(index=False))

Step 5.Export to Excel

The final Excel file contains assumptions, projections, and results ready for presentation.

wb = Workbook()
ws = wb.active
ws.title = "DCF Model"

row = 3
for k, v in assumptions.items():
    ws.cell(row=row, column=1, value=k)
    ws.cell(row=row, column=2, value=v)
    row += 1

row += 2
for col, header in enumerate(["Year", "FCF", "Discount Factor", "PV of FCF"], 1):
    ws.cell(row=row, column=col, value=header)
for p in fcf_projections:
    row += 1
    ws.cell(row=row, column=1, value=p["Year"])
    ws.cell(row=row, column=2, value=p["FCF"])
    ws.cell(row=row, column=3, value=round(p["Discount Factor"], 4))
    ws.cell(row=row, column=4, value=p["PV of FCF"])

wb.save("dcf_model.xlsx")
print("Saved dcf_model.xlsx")

Expected Output

PV of projected FCFs: $648,234,000
PV of terminal value: $1,246,800,000
Enterprise value:     $1,895,034,000
Equity value:         $1,695,034,000
Fair value per share: $33.90

Next Steps

→Add scenario analysis (bull/base/bear cases)
→Include revenue build-up model
→Compare DCF value to comparable company multiples