Building Your First DCF Model in Excel

Walk through creating a complete DCF spreadsheet from scratch, including revenue projections, expense assumptions, and cash flow calculations. This covers the essentials you need to get started.

7 min read Beginner July 2026

Laptop screen displaying financial spreadsheet with columns of numbers and formulas

DCF Mastery Editorial Team

Editorial Team

Why Build a DCF Model?

A DCF model isn't just some theoretical exercise. It's your way of understanding what a company's actually worth based on the cash it'll generate. You're not guessing — you're projecting. You're not hoping — you're calculating.

Whether you're analyzing a stock, evaluating a business opportunity, or just trying to understand valuation, you'll need to build one eventually. The good news? It's not as intimidating as it sounds. We're going to walk through every step, and you'll have a working model by the end of this guide.

Financial analyst reviewing spreadsheet calculations with growth projections

The Core Components You Need

Every DCF model has the same basic structure. You're projecting three to five years of detailed cash flows, then estimating what happens after that. That's it. Everything flows from revenue down through expenses to get your free cash flow.

Revenue Projections

Start with historical revenue if you've got it. Then make reasonable assumptions about growth. Maybe it's 10% per year, maybe it's declining. Your job is to be realistic, not optimistic. Most analysts make the mistake of projecting growth that's too aggressive. Don't be that person.

Once you've got revenue, you'll layer in your operating expenses. Cost of goods sold, salaries, rent, marketing — whatever applies to your company. Express these as a percentage of revenue so they scale with your projections. This is where you'll spend most of your time testing assumptions.

Working Capital and CapEx

Don't forget these. They're cash outflows that reduce your free cash flow. Working capital changes when you're holding more inventory or giving customers longer payment terms. CapEx is your capital expenditures — the equipment, buildings, or infrastructure you need to support growth.

Excel spreadsheet showing five-year financial projections with revenue, EBITDA, and free cash flow calculations

Educational Information

This guide is intended for educational purposes. We're showing you the mechanics of building a DCF model and how the pieces fit together. It's not investment advice, and circumstances vary widely depending on your specific situation. Always consult with financial professionals before making investment decisions. DCF modeling is a tool for understanding value — it's not a crystal ball.

Building It in Excel: Step by Step

Here's the practical part. You'll want to organize your spreadsheet into sections. Most analysts use a simple layout: assumptions on the left, calculations in the middle, outputs on the right. This keeps everything clean and easy to follow.

Set Up Your Assumptions Sheet

Create a dedicated area for all your input assumptions. Revenue growth rate, operating margins, tax rate, discount rate — everything goes here. You'll be changing these numbers as you stress-test your model, so keeping them separate saves you from hunting through formulas.

Build Your Projection Years

Use five years as your explicit forecast period. That's standard. For each year, calculate revenue starting from your base year, apply your growth assumptions, and layer in your expenses. You'll end up with EBITDA, then subtract depreciation to get to EBIT, then apply your tax rate.

Calculate Free Cash Flow

Start with your NOPAT (net operating profit after tax). Add back depreciation since it's not a cash expense. Subtract your CapEx and working capital changes. That's your free cash flow. This is what you'll discount back to today.

The formulas aren't complicated. It's the assumptions that matter. Get those wrong, and your entire valuation's off.

Person working on laptop with multiple windows showing Excel formulas and financial calculations

Spreadsheet showing terminal value calculation with perpetual growth method

Terminal Value: The Big Number

Terminal value is typically 60-80% of your enterprise value. It represents everything after year five. You'll use one of two methods: the perpetuity growth method or the exit multiple method. We're going to focus on perpetuity growth here since it's cleaner.

Take your year five free cash flow, grow it by a modest perpetual growth rate — usually 2-3% — and divide by your discount rate minus that growth rate. That's your terminal value. It's a simple formula, but it's powerful because small changes in your assumptions create big changes in value.

The Discount Rate Matters

Your discount rate (WACC) determines how much future cash is worth today. Higher discount rates mean lower valuations. Lower discount rates mean higher valuations. This single assumption can swing your answer by 30-50%, so get it right. Most junior analysts use a discount rate between 8-12%, but it depends on the company's risk profile.

Putting It All Together

Now you've got all your pieces. Sum up the present value of your five-year free cash flows. Add the present value of your terminal value. Subtract your net debt. Divide by shares outstanding. That's your per-share intrinsic value.

But don't stop there. Your first model is just the starting point. You'll want to test different scenarios. What if revenue grows at 5% instead of 10%? What if your margins compress? What if you need twice as much CapEx? That's sensitivity analysis, and it's where you'll actually understand your model instead of just having numbers on a spreadsheet.

The truth is, building your first DCF takes a few hours. Getting it right takes practice. You're going to build dozens of these, tweak your assumptions, and gradually develop a feel for what reasonable numbers look like. That's the real skill. The mechanics are straightforward — it's the judgment that takes time.

Ready to Dive Deeper?

Once you've built your first model, you'll want to understand the nuances better. Terminal value calculation methods and sensitivity analysis are your next stops.

Explore Terminal Value Methods

Continue your learning with these related guides