Discounted Cash Flow Excel Template: Build and Use One

How-to basics · Updated November 22, 2026

Yes—a discounted cash flow (DCF) Excel template can forecast free cash flow, discount those cash flows to today, and estimate a company’s value. You can download a prebuilt workbook or create a transparent one in Excel for Microsoft 365 using the layout and formulas below; the figures are illustrative, not an investment recommendation.

What a DCF template calculates

A DCF model estimates the present value of future cash generated by a business. It combines an explicit forecast period with a terminal value for cash flows beyond that period. The result is an estimate of enterprise value; adjustments for cash and debt can then produce an estimate of equity value.

The key idea is the time value of money: a dollar expected in the future is worth less today because it could be invested in the meantime and because future results are uncertain. A spreadsheet makes the assumptions visible and allows you to see how changing them affects the result. It does not make a forecast certain or turn assumptions into facts.

Most operating-company DCFs use free cash flow to the firm (FCFF), which is cash available to both debt and equity providers after operating costs, taxes, reinvestment, and working-capital needs. A simplified calculation is operating profit after tax, plus depreciation and amortization, less capital expenditure and investment in net working capital.

A practical Excel template layout

Set up one worksheet with a clearly labeled inputs section and a five-year forecast. Use consistent units throughout—for example, dollars in millions, except per-share amounts—and state the valuation date and currency near the top. In this example, the latest annual revenue is $100 million. Inputs are in column B; forecast years 1 through 5 occupy columns B through F.

  • B2: latest revenue, 100; B3: tax rate, 25%; B4: WACC, 9%; B5: perpetual growth rate, 2.5%.
  • B6: cash, 20; B7: debt, 35; B8: diluted shares, 10 million.
  • Row 11: forecast year numbers 1, 2, 3, 4, 5 across B:F.
  • Rows 12–16: revenue growth, EBIT margin, depreciation and amortization as a percentage of revenue, capital expenditure as a percentage of revenue, and working-capital investment as a percentage of revenue change.
  • Rows 17–26: revenue, EBIT, cash taxes, NOPAT, depreciation and amortization, capital expenditure, change in net working capital, FCFF, discount factor, and present value of FCFF.

For a worked illustration, enter revenue growth of 10%, 9%, 8%, 7%, and 6%; EBIT margins of 15%, 16%, 17%, 18%, and 19%; depreciation and amortization of 3% of revenue; capital expenditure of 4%; and working-capital investment of 1% of the revenue increase. These assumptions are examples, not typical or recommended values.

Build the forecast formulas

Keep assumptions separate from formulas. That makes it easier to audit the model, test alternate cases, and identify an accidental hard-coded value. Use formulas that refer to the assumptions rather than typing the same rate repeatedly.

With latest revenue in B2, year-one growth in B12, and forecast revenue in row 17, enter this in B17. It returns $110 million for year one; enter the year-two growth in C12, then use the next formula in C17 and fill it right through F17.

With year-one revenue growth in B12 and year-two forecast revenue in C17, the formula for C17 is =B17*(1+C12). Fill C17 right through F17 to calculate subsequent years from the prior year’s revenue and each year’s growth assumption.

With forecast revenue in row 17 and EBIT margin assumptions in row 13, enter this in B18 and fill right through F18: =B17*B13. With EBIT in row 18 and the tax rate in B3, enter this in B19 and fill right: =B18*$B$3. This simplified tax calculation applies the stated rate to EBIT; it does not model tax-loss carryforwards or other company-specific tax details.

With EBIT in row 18 and cash taxes in row 19, enter this in B20 and fill right to calculate NOPAT: =B18-B19. With revenue in row 17 and depreciation and amortization assumptions in row 14, enter this in B21 and fill right: =B17*B14. With revenue in row 17 and capital expenditure assumptions in row 15, enter this in B22 and fill right: =B17*B15.

With year-one revenue in B17, latest revenue in B2, and the working-capital rate in B16, enter this in B23: =(B17-$B$2)*B16. It returns $0.1 million for year one in the illustration: the $10 million revenue increase multiplied by 1%. With year-two revenue in C17 and year-one revenue in B17, enter this in C23 and fill right through F23: =(C17-B17)*C16.

With NOPAT in row 20, depreciation and amortization in row 21, capital expenditure in row 22, and the working-capital change in row 23, enter this in B24 and fill right: =B20+B21-B22-B23. This is FCFF. With the WACC in B4 and year number in row 11, enter this in B25 and fill right: =1/(1+$B$4)^B11. With FCFF in row 24 and the discount factor in row 25, enter this in B26 and fill right: =B24*B25. Row 26 contains the present value of each forecast year’s FCFF.

Terminal value and the valuation bridge

The explicit forecast usually covers only a limited number of years, so a DCF estimates the value of later cash flows with a terminal value. One common approach is the Gordon growth method, which assumes FCFF grows at a stable perpetual rate after the final forecast year. The perpetual growth rate must be below the discount rate for this calculation to work.

With year-five FCFF in F24, perpetual growth in B5, and WACC in B4, enter this in B28 to calculate terminal value at the end of year five: =F24*(1+$B$5)/($B$4-$B$5). With terminal value in B28 and the year-five discount factor in F25, enter this in B29 to discount terminal value to the valuation date: =B28*F25.

With present values of forecast FCFF in B26:F26 and discounted terminal value in B29, enter this in B30 for enterprise value: =SUM(B26:F26)+B29. With enterprise value in B30, cash in B6, and debt in B7, enter this in B31 for the simplified equity-value bridge: =B30+B6-B7. With equity value in B31 and diluted shares in B8, enter this in B32 for estimated value per share: =B31/B8.

Using the stated assumptions, forecast FCFF is approximately $11.18 million, $13.09 million, $15.12 million, $17.23 million, and $19.38 million. The forecast cash flows have a present value of about $57.74 million; terminal value is about $305.56 million, with a present value of about $198.59 million. That gives enterprise value of roughly $256.33 million, equity value of roughly $241.33 million after the cash-and-debt adjustment, and about $24.13 per share with 10 million shares. Minor differences from these rounded figures can result from displayed precision.

Choose and test assumptions carefully

The most consequential assumptions are often revenue growth, operating margins, reinvestment, WACC, and terminal growth. Small changes can produce large valuation differences, particularly when terminal value makes up much of enterprise value. The example’s terminal value contributes about $198.59 million of the approximately $256.33 million enterprise value, so the result is especially sensitive to long-term assumptions.

  • Use a forecast that reflects a plausible operating story, not merely a smooth sequence of growth rates.
  • Check whether margins and reinvestment are consistent with that story. Faster growth may require more capital expenditure or working capital.
  • Keep WACC and perpetual growth assumptions internally consistent and use the same valuation date and currency basis.
  • Compare a base case with lower-growth, higher-cost, and other reasonable scenarios instead of treating one output as precise.

A useful Excel sensitivity setup is a grid of valuation outputs with different WACC and perpetual-growth assumptions. In Microsoft 365 for Windows, Excel’s What-If Analysis Data Table can calculate a two-variable grid when the row and column inputs are linked to the relevant assumption cells. The command is on Data > What-If Analysis > Data Table. Since a two-variable table can be sensitive to worksheet layout, check that its row input cell is WACC, its column input cell is terminal growth (or vice versa), and its top-left cell references the valuation output. On Mac, the same model formulas work; ribbon placement can differ by Excel release.

Common template problems to avoid

A template can be polished and still give a misleading answer. First, check units: mixing revenue in millions with debt in whole dollars can distort the valuation by a factor of a million. Label all inputs and outputs. Second, make sure cash flow is discounted for the correct year. Year one uses an exponent of 1 in this annual model; using 0 would treat its cash flow as if it arrived today.

Also avoid counting financing flows twice. FCFF is discounted using WACC to estimate enterprise value, then cash and debt are used in the bridge to equity value. Do not subtract debt inside FCFF and then subtract it again in that bridge. Working-capital investment is normally a use of cash when it increases, so it is subtracted in this example. A decrease can be a cash release and should reduce the working-capital investment amount rather than being forced to a positive cost.

Finally, do not confuse a spreadsheet’s numerical precision with forecast confidence. A displayed price such as $24.13 per share is a model output based on assumptions, not a guaranteed fair value. Check formulas for broken references, blanks, sign errors, and unrealistic terminal growth before relying on a result.

Download, build, or use an alternative

A prebuilt DCF workbook can save setup time, but inspect its formulas, units, and assumptions before using it. Prefer a template with separate input cells, visible forecast calculations, a clear enterprise-to-equity bridge, and sensitivity analysis. Avoid relying on a file that hides formulas, hard-codes outputs, or does not explain whether its cash flow is FCFF or free cash flow to equity.

Building the sheet yourself is useful when you want to understand each calculation or tailor the forecast to a particular business. For a very early estimate, a simpler valuation approach may be easier to explain, but it will not answer exactly the same question as a DCF. A DCF is most useful when cash flows can be forecast with reasonable structure; it is less dependable when the business’s economics or financing situation is changing rapidly.

Frequently Asked Questions

Is there a free discounted cash flow Excel template?
Some finance education sites and spreadsheet libraries offer free workbooks, but terms and quality vary. Review formulas and assumptions rather than assuming a download is correct. You can also build the basic model from the layout above without special add-ins.

What is the formula for DCF in Excel?
There is no single formula for a complete DCF. A typical model forecasts FCFF, multiplies each year’s cash flow by a discount factor, discounts terminal value, and sums those present values. The formulas above show a transparent annual FCFF version.

What discount rate should I use in a DCF?
There is no universal rate. FCFF models commonly use WACC, but estimating it requires choices about the business, capital structure, and market inputs. Use a rate appropriate to the valuation assumptions and consult a qualified professional for advice specific to your circumstances.

Why is terminal value so large in my DCF?
Terminal value represents cash flows beyond the explicit forecast and can account for a large share of total value. A high share is a reason to test WACC, perpetual growth, and forecast assumptions carefully, not proof that the model is wrong by itself.

Can I use the same formulas in Excel for Mac?
These standard formulas work in current Excel for Mac as well as Excel for Microsoft 365 on Windows. Ribbon locations and some interface details may differ, but the formulas and cell references do not need to change.

DCF models are analytical tools, not financial, tax, investment, or legal advice. The example is purely illustrative; check actual company data and relevant terms with reliable sources, a lender where applicable, or a qualified professional.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.