A discounted cash flow (DCF) model Excel template forecasts a business’s future cash flows, discounts them to today’s value, and adds a terminal value to estimate enterprise value. In Excel for Microsoft 365 on Windows, you can build a simple five-year version with forecast cash flows, a discount rate, a terminal growth rate, and a few formulas; the worked example below returns an illustrative enterprise value of about $1,556.
What a DCF Excel template includes
A useful template separates assumptions from calculations so readers can see what drives the result. It should not hide the forecast or rely on unexplained hard-coded values. At a minimum, include an input area, an annual cash flow forecast, discounting calculations, a terminal value calculation, and a valuation summary.
- Operating forecast: projected revenue, margins, taxes, investment, and working-capital needs, or forecast free cash flow entered directly.
- Valuation assumptions: forecast period, discount rate, and terminal growth rate when using the perpetual-growth method.
- Present-value calculations: each forecast cash flow and the terminal value discounted to the valuation date.
- Output and checks: estimated enterprise value, and—if needed—an equity value bridge and sensitivity analysis.
For a compact learning model, forecast free cash flow directly. A more detailed business valuation model calculates it from operating assumptions, making it easier to trace why cash flow rises or falls. Keep input cells visually distinct from formula cells, label units (such as dollars or millions of dollars), and state the valuation date. Formatting helps people review a workbook, but color alone should not replace labels or documentation.
How discounted cash flow valuation works
The core idea is that a dollar expected in the future is worth less today because it could be invested elsewhere and because future cash flows are uncertain. A DCF estimates the present value of forecast cash flows using a discount rate that reflects the relevant time value of money and risk assumptions. It then estimates cash flows beyond the explicit forecast period with a terminal value.
For an enterprise DCF, analysts commonly discount free cash flow to the firm (FCFF) using the weighted average cost of capital (WACC). FCFF is cash flow available to both debt and equity providers, before debt repayments. If the model instead forecasts cash flow to equity, the discount rate and valuation interpretation must match that equity cash flow. Mixing FCFF with a cost of equity, or equity cash flow with WACC, can produce a misleading result.
A DCF is an estimate based on assumptions, not a precise prediction or a guaranteed market price. Small changes to the discount rate, long-term growth, or forecast margins can materially change the output, particularly when much of the value comes from the terminal value.
Set up a simple five-year workbook
On a blank worksheet, reserve a small assumptions area and a forecast table. The following example uses dollars, annual year-end cash flows, and a five-year forecast. You can enter the values directly in Excel; percentage inputs such as 10% and 2% can be typed with the percent sign.
- Enter the discount rate, 10%, in B2 and the perpetual growth rate, 2%, in B3.
- Enter year-one through year-five FCFF in B4:F4: 100, 110, 120, 130, and 140.
- Enter period numbers 1, 2, 3, 4, and 5 in B5:F5.
- Use row 6 for the present value of each annual cash flow, then use separate cells for terminal value, its present value, and total enterprise value.
Use consistent units throughout. If the cash flows are in millions, label the output in millions too; do not combine a cash flow of 100, meaning $100 million, with debt or cash balances entered as individual dollars. Keep assumptions editable and avoid putting the same assumption into multiple formula cells.
Discount the forecast cash flows
For year-end cash flows, present value equals cash flow divided by one plus the discount rate raised to the number of years until receipt. With FCFF in B4:F4, period numbers in B5:F5, and the discount rate in B2, enter this in B6 and copy it across through F6:
=B4/(1+$B$2)^B5
The dollar signs lock the discount-rate reference when the formula is copied; the relative references to the cash flow and year shift across columns. The five present values are approximately $90.91, $90.91, $90.16, $88.79, and $86.93. Their sum is about $447.69. These amounts are lower than the corresponding future cash flows because each is discounted to the valuation date.
This setup assumes cash arrives at the end of each year. If cash flows occur throughout the year, a midyear convention may be more appropriate, but it changes the timing assumption and should be applied consistently. Do not silently change timing conventions simply to increase or decrease the result.
Calculate and discount terminal value
The explicit forecast usually covers only a limited number of years, so the model needs a way to represent cash flows after year five. One common approach is the Gordon growth, or perpetual-growth, method: terminal value equals the next period’s cash flow divided by the discount rate minus the perpetual growth rate.
With year-five FCFF in F4, the discount rate in B2, and perpetual growth in B3, enter the terminal value at the end of year five in F7:
=F4*(1+$B$3)/($B$2-$B$3)
The formula uses year-six cash flow, calculated by growing year-five FCFF by 2%. In this example, year-six FCFF is $142.80 and terminal value is $1,785. That value is measured at the end of year five, not today. With the terminal value in F7 and period five in F5, discount it to the valuation date in F8:
=F7/(1+$B$2)^F5
The present value of the terminal value is approximately $1,108.35. With annual present values in B6:F6 and terminal present value in F8, calculate enterprise value in B9:
=SUM(B6:F6)+F8
The result is approximately $1,556.04. This is an illustrative calculation, not a valuation of a real company. The terminal value is about 71% of this result, showing why a seemingly small change in long-term assumptions can have a large effect.
Choose assumptions and interpret the output
The spreadsheet performs arithmetic; selecting reasonable inputs is the harder task. Forecasts should reflect a company’s circumstances and be internally consistent. For example, sustained revenue growth may require hiring, capital investment, or additional working capital. A forecast that raises sales while ignoring the cash needed to support them can overstate free cash flow.
- Discount rate: Match the rate to the cash flow being discounted and the valuation currency. A different rate may be needed if the risk or financing assumptions change.
- Terminal growth: Use a long-run assumption that can be supported by the business and its economic setting. In the perpetual-growth formula, the growth rate must be below the discount rate; otherwise the denominator is zero or negative and the method is not meaningful.
- Forecast horizon: Choose enough years for the business to approach a sustainable operating condition. A short forecast can make the terminal value do too much work.
- Cash flow definition: Clearly identify whether the forecast is FCFF or cash flow to equity, and keep taxes, financing, and discount rate treatment consistent.
Enterprise value is not automatically the value attributable to shareholders. To estimate equity value, a model typically adjusts enterprise value for cash, debt, and other relevant claims, using figures from a consistent date. Dividing equity value by an appropriate diluted share count gives an implied value per share. Each bridge item needs clear definitions; for instance, the treatment of leases or minority interests depends on the valuation approach and available data.
Common DCF template errors
Several spreadsheet mistakes can look like legitimate outputs. Check the model’s timing and references before focusing on the final number. A formula copied across the forecast can accidentally move the discount-rate reference if the reference is not locked. A terminal value can also be counted twice—for example, included once in the forecast cash flows and then added again as a separate terminal value.
- Confirm the discount rate is greater than the perpetual growth rate and both are entered as percentages, not whole numbers such as 10 and 2.
- Check that the terminal value uses the first cash flow after the forecast period, not the final forecast-year cash flow without growth.
- Make sure annual cash flows and terminal value are discounted to the same valuation date.
- Check that units, currency, tax assumptions, and forecast periods are consistent across inputs and outputs.
- Review whether a negative or unusually large output comes from a genuine forecast assumption or a broken cell reference.
A common misconception is that the most detailed-looking template is necessarily the most reliable. Extra rows do not improve a model if their assumptions are unsupported. A transparent model that explains its inputs and makes its key sensitivities visible is more useful than a complicated workbook with unexplained formulas.
Sensitivity analysis and template selection
Because DCF outputs depend on assumptions, review a range of plausible discount rates and terminal growth rates rather than relying on one case. A two-variable sensitivity table can show how enterprise value changes across combinations of these inputs. In Excel for Microsoft 365 on Windows, select the prepared table and use Data > What-If Analysis > Data Table. The row and column input cells must point to the model’s discount-rate and growth-rate assumptions. On Mac, the Data Table feature is also available from the Data tab, though the ribbon layout can vary slightly by Excel release.
When choosing a downloadable template, check whether it:
- states whether it values FCFF or equity cash flow and identifies the matching discount rate;
- shows formulas rather than only fixed outputs, and uses clearly labeled inputs;
- separates enterprise value from equity value and makes any debt, cash, or share-count assumptions visible;
- includes an understandable forecast period and terminal value method; and
- allows you to inspect formula references and revise assumptions without breaking the workbook.
There is no single universally correct layout. A simple template is suitable for learning the mechanics or making a transparent high-level estimate. A company-specific model may need detailed schedules for revenue, operating costs, taxes, capital expenditure, depreciation, and working capital. The more detailed model is only useful when the underlying data supports that detail.
Frequently Asked Questions
Can I download a free DCF Excel template?
A template may be available from finance education sites, universities, or spreadsheet publishers, but review its formulas and assumptions before relying on it. The layout and formulas in this guide can also be entered into a blank Excel worksheet without downloading a file.
What is the difference between DCF and NPV in Excel?
DCF describes a valuation approach that discounts future cash flows. Excel’s NPV function calculates the present value of a series of equally spaced future cash flows under its timing convention; it does not by itself choose a forecast, discount rate, or terminal value. Check the function’s timing assumptions before using it as a substitute for a custom DCF schedule.
Should I use WACC or cost of equity?
Use a rate that matches the cash flow definition. WACC is commonly paired with FCFF to estimate enterprise value; cost of equity is paired with cash flow available to equity holders to estimate equity value. The model should state which pairing it uses.
Why is my terminal value larger than the forecast cash flows?
Terminal value represents cash flows beyond the explicit forecast, often over a long period, so it can be substantial. A large share of total value in the terminal value is also a signal to test the growth rate, discount rate, forecast horizon, and long-term cash flow assumptions carefully.
Is a DCF result financial advice?
No. It is an estimate built from assumptions and is not a recommendation to buy, sell, or hold an investment. This example is for spreadsheet education, not financial, tax, investment, or legal advice; check actual company information and consult a qualified professional where appropriate.
Related
- Convert an Excel Binary Worksheet (.XLSB) to Excel
- Content Calendar Template Excel: Create and Use One
- Ctrl+G in Excel: Open Go To and Select Cells
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Power Query Incremental Refresh: How to Set It UpExcel Power Query incremental refresh lets you load only new or changed rows from a large data source…
- Identify Duplicate Values in Excel: A Step-by-Step GuideTo identify duplicate values in Excel, select the cells you want to check, then choose Home > Conditional…
- Excel Slicer Video: How to Add, Use, and TroubleshootIf you’re looking for an Excel slicer video, search Microsoft’s Excel help and training resources for slicer tutorials;…
- Box Plot in Excel Without Outliers: Hide or Exclude?To hide outlier markers in an Excel box plot, select the chart, right-click a data series, choose Format…
- How to Add a Header Template in WordTo add a header template in Microsoft Word, open the document, choose Insert > Header, select a built-in…
- Percentage of a Number in Excel: How to Calculate ItTo find a percentage of a number in Excel, multiply the number by the percentage. For example, with…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.