Yes. A discounted cash flow (DCF) valuation Excel template forecasts free cash flow, discounts each forecast to today, adds a discounted terminal value, and bridges enterprise value to equity value. You can build a practical five-year version in Microsoft Excel for Microsoft 365 on Windows using the cell layout and formulas below, then adjust its assumptions to fit the business being analyzed.
What a DCF template calculates
A DCF estimates the value of a business from the cash it may generate in the future. It does not simply add forecast cash flows: cash received later is worth less than cash received now, so the model discounts future amounts using a required return. The result is an estimate based on assumptions, not a guaranteed market price or a prediction of what a share will trade for.
A typical company DCF has three main parts:
- Forecast period: Estimates unlevered free cash flow (FCF), commonly for five to ten years.
- Terminal value: Represents cash flows beyond the explicit forecast, usually using a perpetual-growth formula or an exit multiple.
- Value bridge: Adds the present values to estimate enterprise value, then adjusts for cash, debt, and other claims to estimate equity value.
Unlevered FCF is cash available to all capital providers before interest and debt repayments. That is why a common DCF convention discounts it using the weighted average cost of capital (WACC), rather than a cost of equity alone. Keep the cash flow definition and discount rate consistent: mixing equity cash flows with WACC, for example, can produce a misleading result.
Set up the Excel template
Create a worksheet named DCF. Put the assumptions in column B and the five annual forecast periods in columns B:F. The following layout uses dollars in millions, except for per-share values; using a consistent unit throughout is essential.
- B2: WACC, set to 10% for the illustration.
- B3: Perpetual growth rate, set to 2.5%.
- B4: Cash and cash equivalents, 50.
- B5: Debt and debt-like claims, 120.
- B6: Diluted shares outstanding, 40 million.
- B10:F10: Forecast periods 1, 2, 3, 4, and 5.
- B11:F11: Unlevered FCF of 100, 110, 121, 133.1, and 146.41.
- B12:F12: Discount factors; B13:F13: present value of forecast FCF.
The assumptions and forecast numbers are illustrative, not company data. Enter percentages as percentages (for example, 10%) and use one monetary scale consistently. If forecasts are entered in thousands while cash and debt are entered in millions, the value bridge will be wrong even if every formula is syntactically correct.
Forecast unlevered free cash flow
For a quick template, forecast FCF directly in the forecast row. For a more transparent model, calculate it from operating drivers. A commonly used simplified relationship is EBIT after tax, plus depreciation and amortization, less capital expenditures and the increase in net working capital. Interest expense is excluded because the cash flow is unlevered.
For example, suppose a single forecast year has EBIT of 150, a tax rate of 25%, depreciation and amortization of 20, capital expenditure of 30, and an increase in net working capital of 5, all in millions. Put those values in B18:B22 respectively, with the tax rate in B19. With EBIT in B18, tax rate in B19, D&A in B20, capital expenditure in B21, and the change in working capital in B22, enter this in B23: =B18*(1-B19)+B20-B21-B22. It returns 97.5 million: 150 × 75% + 20 − 30 − 5. A positive increase in working capital uses cash; if working capital releases cash, enter a negative change.
This compact calculation is not a substitute for a considered forecast. Revenue growth, margins, taxes, reinvestment, and working-capital needs should reflect the company and its economics. In an operating model, forecast those drivers year by year and calculate FCF for each period; avoid applying one growth rate mechanically if the business is changing materially.
Discount forecast cash flow and terminal value
Discount each annual FCF by the number of years between today and the forecast period. With WACC in B2, forecast years in B10:F10, and the first discount factor in B12, enter =1/(1+$B$2)^B10 in B12 and fill it right through F12. For year 1 at 10% WACC, the factor is about 0.9091. The dollar sign fixes the WACC reference when the formula is copied; the period reference changes across columns.
With forecast FCF in B11:F11 and discount factors in B12:F12, enter =B11*B12 in B13 and fill it right through F13. For year 1, 100 multiplied by 0.9091 gives present value of about 90.91 million. Each forecast year is discounted separately; do not discount all years with the same factor.
The Gordon growth method estimates terminal value at the end of the final forecast year. It assumes FCF grows at a stable perpetual rate after that point. With year-five FCF in F11, perpetual growth in B3, and WACC in B2, enter =F11*(1+$B$3)/($B$2-$B$3) in F14. Using the illustration, terminal value is about 2,000.94 million: 146.41 × 1.025 ÷ (0.10 − 0.025). This formula is only meaningful when WACC is greater than the perpetual growth rate.
Terminal value is measured at the end of year five, so it also needs discounting to today. With the terminal value in F14 and the year-five discount factor in F12, enter =F14*F12 in F15. It returns approximately 1,242.4 million. A common mistake is to add the undiscounted terminal value to present-value forecast cash flows, which overstates enterprise value.
Bridge enterprise value to equity value
Enterprise value represents the value of operating assets to all capital providers under the model’s assumptions. Add the present values of the five forecast cash flows and the present value of terminal value. With present values in B13:F13 and discounted terminal value in F15, enter =SUM(B13:F13)+F15 in B17. The illustrative result is about 1,696.97 million: roughly 454.55 million of forecast cash-flow value plus 1,242.4 million of discounted terminal value.
To estimate equity value, add cash and subtract debt and other relevant senior claims. With enterprise value in B17, cash in B4, and debt in B5, enter =B17+B4-B5 in B18. This gives about 1,626.97 million. This simplified bridge assumes the listed cash and debt figures capture the relevant adjustments; real analyses may need to consider leases, minority interests, preferred securities, pension obligations, or excess cash separately.
To calculate an illustrative value per diluted share, divide equity value by diluted shares outstanding. With equity value in B18 and shares in B6, enter =B18/B6 in B19. The result is about 40.67 per share. This is an output of the stated assumptions, not a target price or a recommendation to buy or sell a security.
Choose and test the assumptions
WACC and perpetual growth have an outsized effect on a DCF, particularly because terminal value can form a large portion of total value. The example’s 10% WACC and 2.5% perpetual growth are placeholders, not universal defaults. A growth rate intended to continue forever should be supportable over the long term and must remain below WACC in the Gordon growth formula.
Use sensitivity analysis to show how the answer changes rather than presenting one output as precise. For example, calculate valuation under several WACC and growth combinations, keeping the underlying operating forecast fixed. Excel’s What-If Analysis tools include a Data Table feature, available from Data > What-If Analysis > Data Table in Excel for Microsoft 365 on Windows. A two-variable table can show the output for each pairing, but set up its row and column input cells carefully and verify a few cells by changing the assumptions directly. On Mac, the Data Table command is also found under Data > What-If Analysis in current Excel versions, though ribbon layout can vary.
Also consider a scenario analysis with distinct operating cases, such as lower, base, and higher revenue growth or margins. Scenario changes should be economically coherent: lower growth may affect margins, reinvestment, and working capital as well as sales. Report the assumptions alongside the results so a reader can see what drives the range.
Common template problems to check
- Inconsistent cash flow and discount rate: Unlevered FCF generally pairs with WACC; equity cash flow generally pairs with cost of equity.
- Wrong signs or double counting: Treat capital expenditure and increases in working capital as cash uses. Do not subtract interest from unlevered FCF and then also discount it using WACC.
- Incorrect timing: A year-one cash flow is discounted one period in a standard year-end convention. Make the timing assumption explicit if cash flows occur midyear or at other dates.
- Unrealistic terminal assumptions: A small gap between WACC and perpetual growth can make terminal value extremely large. Check the denominator and test reasonable changes.
- Misleading precision: Formatting an output to two decimals does not make the estimate accurate to two decimals. The assumptions are uncertain.
- Omitted claims or inconsistent units: Review debt-like items and share count, and confirm all amounts use the same currency and scale.
Before relying on the worksheet, inspect formulas, compare the terminal value with total enterprise value, and check whether the resulting margins and growth rates make business sense. If terminal value dominates the calculation, the estimate is especially sensitive to assumptions beyond the explicit forecast.
Using Excel features and keeping the file usable
In Excel for Microsoft 365 on Windows, use clear labels, input formatting, and cell references rather than embedding assumptions repeatedly in formulas. For instance, referencing B2 for WACC makes the assumption easy to audit and change. You can format assumption cells differently from formula cells, add units to row labels, and use comments or cell notes to record sources and dates. Keep the formulas visible and avoid hiding key adjustments in a distant worksheet.
The formulas shown use standard Excel arithmetic and SUM, so they work in current desktop Excel without newer dynamic-array functions. Excel for the web supports these basic formulas too, though the ribbon and some What-If Analysis features may differ from desktop Excel. On Mac, formula syntax is the same; keyboard and ribbon details can differ. If you use a downloaded template, check for external links, hidden sheets, hard-coded values, and formulas that do not match the labels before entering company data.
Alternatives and when a DCF is useful
A DCF is useful when cash flows can be forecast with some discipline and the reader wants to see how assumptions translate into value. It is less dependable when cash flows are highly unpredictable, the business is in an unusual transition, or key inputs cannot be estimated credibly. In those cases, the spreadsheet can still organize scenarios, but its output should not be mistaken for a robust point estimate.
Other valuation approaches include comparing a company with similar businesses using valuation multiples, reviewing precedent transactions, or using asset-based methods where appropriate. These methods answer different questions and have their own limitations; a market multiple, for example, depends on the choice of comparables and the metric being used. Analysts may compare several methods as a reasonableness check rather than treating one template as definitive.
A spreadsheet valuation is not financial, tax, or investment advice. Check the company’s filings and the definitions behind reported figures, and consult a qualified financial professional for advice tailored to a specific decision.
Frequently Asked Questions
Is there a built-in DCF function in Excel?
Excel does not provide a single built-in function that creates a complete DCF valuation. You can build the model from ordinary formulas, as above, or use functions such as NPV for certain cash-flow schedules. NPV discounts a series as if its first cash flow occurs one period from now, so timing conventions matter; it does not by itself calculate terminal value or bridge enterprise value to equity value.
Can I use Excel’s NPV function instead of discount factors?
Yes, for evenly spaced end-of-period cash flows, NPV can calculate their present value. With annual FCF in B11:F11 and WACC in B2, =NPV(B2,B11:F11) returns about 454.55 million in this example. NPV excludes a time-zero cash flow, and the terminal value still needs to be added as a year-five cash flow before discounting or discounted separately as shown.
Why must the terminal growth rate be below WACC?
The perpetual-growth formula divides by WACC minus growth. If growth equals or exceeds WACC, the denominator is zero or negative, so the result is not a meaningful finite terminal value under the model. Even when growth is lower, a very small gap can make the estimate highly sensitive.
How many years should a DCF forecast?
There is no fixed requirement. Choose a period long enough for the company’s economics to approach a sustainable state, while keeping forecasts defensible. A stable business may need less explicit detail than a business undergoing a major investment cycle or transition.
Can a DCF give a negative value per share?
It can, particularly when estimated enterprise value is lower than debt and other claims or when the assumptions produce weak cash flows. Treat that as a signal to inspect the model, timing, and capital structure rather than automatically assuming an Excel error; the result may also be economically implausible if key assumptions are inappropriate.
Related
- Excel Conditional Formatting: Find Cells That Contain Text
- Construction Project Cash Flow Template Excel Guide
- Construction Cash Flow S Curve Excel Template Guide
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Outlook Email Preview: Change Lines and Reading PaneTo change the email preview in Outlook, adjust the message-preview lines to show more or less text in…
- Attendance Excel Template: Free Options and SetupYes. You can get a free attendance Excel template from Excel’s template gallery or create a simple tracker…
- Randomize Numbers in Excel: Methods That WorkYes. In Excel for Microsoft 365 on Windows, use =RAND() for a random decimal between 0 and 1,…
- Excel Cell Highlight Color: How to Change ItTo change a cell’s highlight color in Excel for Microsoft 365 on Windows, select the cell or range,…
- Excel Keyboard Shortcuts: What You Can Customize in ExcelIn Excel for Microsoft 365 on Windows, you cannot directly remap most built-in keyboard shortcuts through an Excel…
- Word Mail Merge: Remove Decimal Places from Number FieldsTo remove decimal places from a Word mail merge result, edit the merge field and add the number-format…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.