Discounted Cash Flow Formula in Excel: A Practical Guide

Formulas & functions · Updated November 22, 2026

In Excel, a basic discounted cash flow (DCF) formula is =NPV($B$2,B5:B9)+B10/(1+$B$2)^5. With the discount rate in B2, cash flows for years 1–5 in B5:B9, and the terminal value at the end of year 5 in B10, this returns the present value of those cash flows and the terminal value; using the example below, the result is about 1,292.72.

What a discounted cash flow calculates

A DCF estimates what future cash flows are worth today. The central idea is that a dollar expected in the future is worth less than a dollar available now, because money today could be invested and because future cash flows are uncertain. A discount rate represents the required return used to translate future amounts into present values.

In a company valuation, analysts usually forecast free cash flow for a defined period, discount each forecast amount, estimate the value of cash flows beyond that period, and discount that terminal value too. Adding those present values produces an estimate of enterprise value when the cash flow is free cash flow to the firm and the discount rate is the weighted average cost of capital (WACC). That is not automatically the value of shareholders’ equity: debt, cash, and other relevant claims generally need to be considered separately.

DCF is a valuation framework, not a guaranteed prediction. Its result depends heavily on assumptions about cash generation, risk, and long-run growth. Excel performs the arithmetic; it cannot determine whether those assumptions are reasonable.

Set up the Excel worksheet

For a straightforward annual model, put assumptions and forecast cash flows in clearly labeled cells. The example uses five years of free cash flow, with each year’s cash flow set to 100 units. These are illustrative units, not a claim about a real company.

  • Enter the annual discount rate, or WACC, in B2 as 10%.
  • Enter the perpetual growth rate in B3 as 3%.
  • Use A5:A9 for periods 1 through 5 and B5:B9 for the corresponding free cash flows. Enter 100 in each cash-flow cell.
  • Use B10 for the terminal value measured at the end of year 5.

Format B2 and B3 as percentages and the cash-flow and valuation cells as numbers or currency. Keeping assumptions separate from forecasts makes the model easier to review and lets you change inputs without rewriting formulas. Add labels beside the cells, such as “WACC,” “Perpetual growth,” “Year 1 FCF,” and “Terminal value,” so a reader can follow the calculation.

Calculate terminal value

A forecast cannot usually extend year by year forever. The terminal value captures the estimated value, at the end of the explicit forecast period, of cash flows after that period. One common approach is the perpetual-growth method, also called the Gordon growth method. It assumes cash flow grows at a stable rate indefinitely.

With the final forecast cash flow in B9, the discount rate in B2, and perpetual growth in B3, enter this formula in B10:

Cell layout: B9 contains year 5 free cash flow of 100, B2 contains a 10% WACC, and B3 contains a 3% perpetual growth rate. Formula: =B9*(1+$B$3)/($B$2-$B$3). The result is approximately 1,471.43.

The calculation first grows year 5’s cash flow by the assumed 3% to estimate year 6 cash flow, then divides that amount by the difference between the discount rate and growth rate. The perpetual-growth method requires the discount rate to be greater than the perpetual growth rate. If growth equals or exceeds the discount rate, the denominator is zero or negative and the result is not meaningful under this model. A large terminal value is common in long-horizon DCFs, so the assumption deserves particular scrutiny.

Use Excel’s NPV function correctly

Excel’s NPV function treats the first value in its list as a cash flow received one period from now, not as a cash flow occurring today. That timing convention is a frequent source of mistakes. In the example, B5 is year 1 and B9 is year 5, so those forecast amounts belong inside NPV. B10 is also a year 5 amount, but it is kept outside the NPV range here so its timing is explicit: it is divided by the discount factor for five periods.

Cell layout: B2 contains the 10% annual discount rate, B5:B9 each contain a year 1–5 cash flow of 100, and B10 contains the year 5 terminal value of about 1,471.43. Enter the following formula in another cell to calculate enterprise value:

=NPV($B$2,B5:B9)+B10/(1+$B$2)^5

The five forecast cash flows have present values of about 90.91, 82.64, 75.13, 68.30, and 62.09, totaling about 379.08. The terminal value’s present value is about 913.64. Added together, they produce approximately 1,292.72. Small differences in the last decimal can result from display rounding; Excel retains more precision internally.

Do not include a time-zero amount in the NPV range as though it were a future cash flow. If a model includes an initial investment at time zero, add that amount separately to the NPV of later cash flows. Also be consistent about units: a cash-flow forecast in thousands produces a valuation in thousands unless you convert it.

Choose the right discount rate and growth assumption

The discount rate should match the type of cash flow being valued. WACC is commonly paired with free cash flow to the firm to estimate enterprise value. A cost of equity is generally paired with cash flows available to equity holders to estimate equity value. Mixing the two can produce a misleading result even if the spreadsheet formula itself is correct.

The growth rate used for terminal value should represent a sustainable long-term assumption, not simply a short-term forecast copied forward without thought. It must be below the discount rate in the perpetual-growth calculation. The discount rate also needs to reflect the risk and currency of the cash flows, and the periods need to match: annual cash flows call for a rate and timing convention consistent with annual periods.

Because valuation responds to these inputs, it is useful to examine multiple reasonable assumptions rather than treating one output as exact. A sensitivity table can compare results across different discount rates and growth rates. In Excel for Microsoft 365 on Windows, use Data > What-If Analysis > Data Table for a two-variable sensitivity table. On Mac, the Data Table command is available through the Data tab, though its exact placement may vary by Excel release. Check the labels and row/column input cells carefully before relying on the table.

Irregular dates and the XNPV alternative

Standard NPV assumes equally spaced periods. That is suitable for many annual or quarterly forecasts when cash flows occur at regular intervals. If dates are irregular—for example, an initial date followed by cash flows on varying calendar dates—Excel’s XNPV function can calculate present value using the actual dates and a 365-day year convention. Its values and dates ranges must correspond item by item, and the first date anchors the timing.

Do not switch between NPV and XNPV casually: they make different timing assumptions. Use the method that fits the cash-flow schedule and document it in the workbook. XNPV is available in current desktop Excel, including Microsoft 365 for Windows; when sharing a workbook with someone using an older spreadsheet program, confirm function compatibility and whether that program interprets the function the same way.

Common DCF errors to check

  • Discounting the terminal value for the wrong number of periods: A terminal value at the end of year 5 is discounted for five periods, not four or six.
  • Including year zero in NPV: Excel’s NPV list begins at period 1. Keep a time-zero cash flow outside that list.
  • Using inconsistent rates and cash flows: Match the discount rate to the cash-flow definition, currency, and timing.
  • Entering 10 instead of 10%: If the model expects a rate, enter 10% or 0.10, not 10. A value of 10 means 1,000% when used as a decimal rate.
  • Using a growth rate that is too high: In the perpetual-growth method, growth must be below the discount rate; even a small change in either assumption can materially affect terminal value.
  • Confusing enterprise value with equity value: A firm-level DCF result does not by itself account for debt, cash, or other claims on the business.
  • Overlooking units and signs: Keep dollars, thousands, or millions consistent, and make sure cash inflows and outflows have the intended signs.

Useful review checks include confirming that year labels run in the right order, tracing the terminal-value formula back to the final forecast year, and comparing the terminal value’s share of total present value with expectations. A very high share does not automatically mean an error, but it means the estimate depends substantially on assumptions beyond the explicit forecast.

What the result does—and does not—mean

A DCF output is an estimate conditional on the model’s assumptions, not a market price or a promise of future performance. A forecast that is slightly different from reality can change the result, particularly when the valuation relies heavily on terminal value. It is therefore helpful to retain source notes for forecasts, show assumptions plainly, and avoid reporting excessive decimal places that imply false precision.

DCF can complement other valuation approaches, such as comparing a company with similar businesses using market multiples. Those approaches answer related but different questions and have their own limitations. A DCF makes assumptions about future cash generation explicit; a market-multiple comparison reflects current pricing of selected peers. Neither method removes uncertainty. This is educational information, not financial, tax, investment, or legal advice; check actual terms and assumptions with the relevant lender or a qualified professional where appropriate.

Frequently Asked Questions

What is the simplest DCF formula in Excel?
For annual future cash flows, a basic setup is Excel’s NPV of the forecast cash flows plus the terminal value discounted separately to the valuation date. The exact cell references depend on where the assumptions, forecast, and terminal value are stored.

Does Excel’s NPV include the initial investment?
No. Excel treats the first value supplied to NPV as occurring one period in the future. Add a time-zero investment separately to the NPV result, rather than placing it at the start of the NPV range.

Should terminal value be included inside NPV?
It can be included if the terminal value is placed in the period in which it occurs and the range is constructed carefully. Discounting it separately, as in the example, makes its end-of-forecast timing easier to see and audit.

Why is my DCF result negative or extremely large?
Check the discount rate’s input format, cash-flow signs, units, forecast range, and terminal-value assumptions. Also verify that the discount rate is higher than the perpetual growth rate. A very large result can arise when those two rates are close together.

What is the difference between NPV and XNPV in Excel?
NPV assumes cash flows occur at regular intervals. XNPV uses a corresponding set of dates and is intended for irregularly timed cash flows. Choose the function that reflects the actual timing in the model.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.