Learning How to Build a Discounted Cash Flow (DCF) Model in Excel or Google Sheets is a Great Way to Check your Stock Thesis. Investing is all about finding great companies at the right price. But how do you know if a stock is undervalued or overvalued? One of the best ways to determine a company’s intrinsic value is by using a Discounted Cash Flow (DCF) model.
A DCF model helps investors estimate the true worth of a business by forecasting future cash flows and discounting them to their present value. This method is widely used by value investors, including Warren Buffett, to make informed investment decisions.
In this guide, we’ll walk through how to set up a DCF model in Excel or Google Sheets, covering:
✅ What is a DCF Model?
✅ How to Gather the Right Data
✅ Step-by-Step Guide to Building a DCF Model
✅ Common Mistakes to Avoid
✅ Why a DCF Model is Important for Investors
By the end of this article, you’ll have a fully functional DCF model that can help you evaluate stocks before investing.
What is a Discounted Cash Flow (DCF) Model?
A Discounted Cash Flow (DCF) model estimates a company’s intrinsic value based on expected future cash flows. It helps answer a crucial investing question:
💡 “If I buy this stock today, how much is it actually worth?”
A company’s value is derived from the present value of all future cash flows it will generate, adjusted for the time value of money.
DCF Formula:
Where:
- CFₜ = Cash flow in year t
- r = Discount rate (usually the company’s Weighted Average Cost of Capital, or WACC)
- t = Number of years in the projection
- TV = Terminal value (value beyond the forecasted years)
Step 1: Gather Financial Data
Before building a DCF model, you need key financial data from a company’s income statement, balance sheet, and cash flow statement. You can find these on:
📌 Yahoo Finance
📌 SEC Filings (10-K Reports)
📌 Macrotrends.net
For this example, let’s analyze Apple (AAPL). We need:
✔ Free Cash Flow (FCF) from past 5 years
✔ Revenue Growth Rate
✔ Discount Rate (WACC)
✔ Terminal Growth Rate
Step 2: Forecast Future Free Cash Flows (FCF)
Free Cash Flow (FCF) = Operating Cash Flow – Capital Expenditures
Let’s say Apple’s FCF for the last 5 years looks like this:
| Year | Free Cash Flow ($B) |
|---|---|
| 2019 | 58.9 |
| 2020 | 73.4 |
| 2021 | 92.9 |
| 2022 | 111.4 |
| 2023 | 110.5 |
Project Future Cash Flows Using Growth Rate
If we assume a growth rate of 8% per year, we can forecast the next 5 years:
📌 Excel Formula:
(Where B2 is the previous year’s FCF, and GrowthRate is 8% or 0.08.)
Step 3: Calculate the Discount Rate (WACC)
The discount rate represents the risk of investing in the company. It is usually calculated using Weighted Average Cost of Capital (WACC):
Where:
- E = Market value of equity (Market Cap)
- D = Market value of debt
- V = E + D (Total value of company)
- Re = Cost of equity
- Rd = Cost of debt
For simplicity, let’s assume Apple’s WACC is 9%.
Step 4: Calculate Present Value of Future Cash Flows
Each year’s cash flow must be discounted to present value using:
📌 Excel Formula (for Year 1):
(Where B2 is the forecasted FCF, DiscountRate is 9% or 0.09, and Year is 1, 2, 3…)
Repeat this formula for each year up to Year 5.
Step 5: Calculate the Terminal Value (TV)
Since businesses continue beyond 5 years, we estimate the Terminal Value (TV) using the Gordon Growth Model:
Where:
- FCF₆ = Year 6 cash flow
- r = Discount rate (9%)
- g = Long-term growth rate (2.5%)
📌 Excel Formula:
(Where B7 is Year 5 FCF, GrowthRate is 2.5% or 0.025, and DiscountRate is 9% or 0.09.)
Step 6: Sum Everything to Get Intrinsic Value
Now, add up:
✅ Present Value of Future Cash Flows
✅ Present Value of Terminal Value
📌 Excel Formula:
(Where D2:D6 contains discounted FCFs and D7 is the terminal value.)
Finally, divide by shares outstanding to get intrinsic value per share and compare it to the stock price.
📌 Intrinsic Value Per Share:
Common Mistakes to Avoid with Your Discounted Cash Flow Model
⚠ Overestimating Growth Rates – Be realistic with forecasts.
⚠ Using Incorrect WACC – Check for company-specific risk factors.
⚠ Not Updating Data – Market conditions change; update models frequently.
Why a Discounted Cash Flow (DCF) Model is Important for Investors
📌 Helps You Avoid Overpaying for Stocks
📌 Prevents Emotional Investing (FOMO Buying)
📌 Allows for a Data-Driven Approach to Value Investing
A well-structured DCF model ensures you’re buying stocks based on intrinsic value, not hype.
Discounted Cash Flow (DCF) Model: Free Resources
For investors seeking reputable resources to build or download Discounted Cash Flow (DCF) models, several esteemed financial education platforms offer free templates and comprehensive guides:
- Corporate Finance Institute (CFI): CFI provides a free Discounted Cash Flow (DCF) Model template that serves as a solid foundation for building your own model with various assumptions. The template is available for download upon submitting your name and email.
- Wall Street Prep: This platform offers a detailed step-by-step guide on constructing a Discounted Cash Flow (DCF) Model in Excel, complete with explanations of each component and the necessary calculations. A sample DCF model template is also available for download.
- Wisesheets: Wisesheets provides a free Discounted Cash Flow (DCF) template in Excel designed to help investors determine the intrinsic value of a stock. The guide includes instructions on how to use the template effectively for informed investment decisions.
- Financial Edge Training: This resource offers a free Discounted Cash Flow (DCF) Model template that allows users to forecast future cash flows and determine their present value by discounting. The template is user-friendly and customizable to fit various financial scenarios.
These resources not only provide downloadable templates but also offer in-depth tutorials on creating and utilizing DCF models, catering to both novice and experienced investors
Conclusion: Building a Discounted Cash Flow (DCF) Model is Important
A DCF model is a powerful way to determine a company’s fair value. With Excel or Google Sheets, you can build a DCF calculator to analyze stocks like Apple, Microsoft, and Amazon before investing.
💡 Want to practice? Pick a stock you’re interested in and build your own DCF model today!