DCF Model: Build a Discounted Cash Flow Model Step-by-Step

Learn how to build a professional DCF (Discounted Cash Flow) model in Excel and Google Sheets. Complete guide with formulas, templates, and AI automation to value companies accurately.

What is a DCF Model?

A Discounted Cash Flow (DCF) model is a valuation method used to estimate the value of an investment based on its expected future cash flows. The model discounts projected cash flows back to their present value using a discount rate (typically WACC - Weighted Average Cost of Capital).

Key Components

  • Revenue Projections: Forecast future revenue growth
  • Free Cash Flow: Calculate FCF = Operating Cash Flow - CapEx
  • Terminal Value: Estimate value beyond forecast period (Gordon Growth or Exit Multiple)
  • WACC: Weighted Average Cost of Capital as discount rate
  • Present Value: Discount all cash flows to present value

Why Use SheetXAI?

  • Generate complex NPV and discounting formulas automatically
  • Build revenue projection models with growth rate formulas
  • Calculate WACC and terminal value automatically
  • Create sensitivity analysis tables for key assumptions

How to Build a DCF Model: Step-by-Step Guide

Follow these steps to build a professional DCF model in Excel or Google Sheets.

1

Build Revenue Projections

Forecast revenue growth based on historical trends, market analysis, and company-specific factors. Include assumptions for growth rates by segment or product line.

Key Formulas Needed:

  • Growth rate formulas
  • CAGR calculations
  • Percentage growth formulas

💡 SheetXAI Tip:

Use SheetXAI to generate these formulas automatically. Just describe what you need: "Calculate NPV of cash flows using WACC" or "Build revenue projection with 10% growth rate."

2

Project Operating Expenses

Estimate operating expenses including COGS, SG&A, and other expenses. Model margins and expense ratios based on historical data and industry benchmarks.

Key Formulas Needed:

  • Margin calculations
  • Percentage of revenue formulas
  • Fixed vs variable cost modeling

💡 SheetXAI Tip:

Use SheetXAI to generate these formulas automatically. Just describe what you need: "Calculate NPV of cash flows using WACC" or "Build revenue projection with 10% growth rate."

3

Calculate Free Cash Flow

Calculate Free Cash Flow = Operating Cash Flow - Capital Expenditures. Include working capital changes and tax adjustments.

Key Formulas Needed:

  • EBITDA calculations
  • Tax adjustments
  • CapEx formulas
  • Working capital changes

💡 SheetXAI Tip:

Use SheetXAI to generate these formulas automatically. Just describe what you need: "Calculate NPV of cash flows using WACC" or "Build revenue projection with 10% growth rate."

4

Calculate WACC

Determine the Weighted Average Cost of Capital using: WACC = (E/V × Re) + (D/V × Rd × (1-Tc)). This is your discount rate.

Key Formulas Needed:

  • Cost of equity (CAPM)
  • Cost of debt
  • Tax shield calculations
  • Weighted averages

💡 SheetXAI Tip:

Use SheetXAI to generate these formulas automatically. Just describe what you need: "Calculate NPV of cash flows using WACC" or "Build revenue projection with 10% growth rate."

5

Calculate Terminal Value

Estimate the company's value beyond the forecast period using either the Gordon Growth Model or Exit Multiple method.

Key Formulas Needed:

  • Gordon Growth: TV = FCF × (1+g) / (WACC - g)
  • Exit Multiple: TV = EBITDA × Multiple
  • Present value of terminal value

💡 SheetXAI Tip:

Use SheetXAI to generate these formulas automatically. Just describe what you need: "Calculate NPV of cash flows using WACC" or "Build revenue projection with 10% growth rate."

6

Discount Cash Flows to Present Value

Discount all projected free cash flows and terminal value back to present value using the WACC as the discount rate.

Key Formulas Needed:

  • NPV function
  • PV function
  • Discount factor calculations
  • Sum of present values

💡 SheetXAI Tip:

Use SheetXAI to generate these formulas automatically. Just describe what you need: "Calculate NPV of cash flows using WACC" or "Build revenue projection with 10% growth rate."

7

Calculate Enterprise Value and Equity Value

Enterprise Value = Sum of discounted cash flows. Equity Value = Enterprise Value - Net Debt + Cash. Calculate per-share value.

Key Formulas Needed:

  • Enterprise value calculations
  • Net debt adjustments
  • Equity value formulas
  • Per-share calculations

💡 SheetXAI Tip:

Use SheetXAI to generate these formulas automatically. Just describe what you need: "Calculate NPV of cash flows using WACC" or "Build revenue projection with 10% growth rate."

Common DCF Model Mistakes to Avoid

Learn from common errors to build more accurate DCF valuations.

Incorrect WACC Calculation

Using wrong weights for debt and equity, or incorrect cost of capital components. WACC must reflect the company's actual capital structure.

Solution: SheetXAI can help calculate WACC correctly with proper formula generation.

Terminal Value Too High

Using unrealistic terminal growth rates or exit multiples. Terminal value often represents 60-80% of total value, so accuracy is critical.

Solution: SheetXAI can generate terminal value formulas with proper assumptions.

Ignoring Working Capital

Forgetting to account for changes in working capital, which affects free cash flow calculations.

Solution: SheetXAI can help build comprehensive working capital schedules.

Wrong Discount Period

Discounting cash flows for the wrong number of periods or using incorrect timing assumptions.

Solution: SheetXAI can generate accurate discounting formulas with proper period references.

Automate DCF Model Creation with SheetXAI

SheetXAI can automate many of the complex calculations involved in building a DCF model, saving you hours of work.

Generate Valuation Formulas

Create complex NPV, WACC, and terminal value formulas using natural language commands.

Build Projection Models

Automatically generate revenue and expense projection formulas with growth rate assumptions.

Create Sensitivity Analysis

Build data tables showing how valuation changes with different assumptions automatically.

FAQ's

Everything you need to
know about SheetXAI

When you use our credits, you're accessing AI models through our API keys. Credits are consumed based on this simple formula:

Base cost × Model cost = Total credits used

Different operations have different base costs:

  • - Chatbot operations have a base cost of 5 credits per operation the agent performs
  • - Cell-by-cell operations (SAI commands) have a base cost of 1 credit
  • - Deep analysis operations (typically when you need an analysis report on something) have a base cost of 2 credits + 0.1 credits per row analyzed (with bulk discounts after 1000 rows)

The model cost varies depending on which AI model you choose. Lighter models like GPT-4o mini are cheaper (this one is exactly 2 credits), while more powerful models like Claude Sonnet cost more.

Examples:

  • - Chatbot operation with GPT-4o mini: 5 × 2 = 10 credits per operation
  • - SAI command with GPT-4o mini: 1 × 2 = 2 credits per row
  • - Deep analysis of 100 rows with GPT-4o mini: (2 + 10) × 2 = 24 credits
  • - Chatbot operation with Claude Sonnet: 5 × 5 = 25 credits per operation

Important notes about credits:

  • - Operations like creating pivot tables or marking many rows count as ONE operation, not per row
  • - If you have a sheet with 1 million rows and ask the AI to delete all rows that are blank, that still counts as 1 operation
  • - Deep analysis operations get bulk discounts for large datasets (over 1000 rows)

Boost your productivity today.
Start automating your spreadsheets.

Join thousands of professionals saving hours every week. No credit card required to start.

Learn more