Sheet ReserveSpreadsheets worth keeping.

Finance

What is discounted cash flow (DCF)?

Short answer

Discounted cash flow (DCF) values a business or project by forecasting its future free cash flows and discounting each back to today at a rate that reflects risk, usually the weighted average cost of capital. The present values of the forecast years plus a terminal value are summed to give enterprise value. Subtracting net debt gives equity value.

Updated

Discounted cash flow is based on one idea: a dollar received in the future is worth less than a dollar today, so future cash is converted to present value before it is added up. A DCF model forecasts the cash a business can pay out to its investors, discounts it, and sums the result.

The formula

Enterprise value = SUM(FCF_t / (1 + r)^t) + TV / (1 + r)^N

  • FCF_t is free cash flow in year t: cash from operations after tax and after the capital spending needed to keep the business going and growing.
  • r is the discount rate. For free cash flow to the whole firm, this is usually the weighted average cost of capital (WACC).
  • N is the last forecast year, and TV is the terminal value, which stands for all cash flows after year N.

A common terminal value is the Gordon growth formula: TV = FCF_N * (1 + g) / (r - g), where g is a long-run growth rate. It only works when g is below r, and in practice g is kept at or below the long-run growth rate of the economy. The alternative is an exit multiple, such as a multiple of earnings before interest, taxes, depreciation and amortization (EBITDA) applied to the final year.

Worked example

These are example numbers in millions of dollars: five years of free cash flow, a 10 percent discount rate and 2.5 percent terminal growth.

Year FCF Discount factor Present value
1 100 0.9091 90.91
2 108 0.8264 89.26
3 116 0.7513 87.15
4 124 0.6830 84.69
5 132 0.6209 81.96

The five present values sum to 433.97. The terminal value is 132 * 1.025 / (0.10 - 0.025) = 1,804.0, which discounts to 1,120.14 at year 0. Enterprise value is therefore 433.97 + 1,120.14 = 1,554.12.

To get to a value for shareholders, subtract net debt. With net debt of 150, equity value is 1,404.12, and with 40 million shares that is about $35.10 per share.

In Excel or Google Sheets, with cash flows in B2:F2 and the rate in B5, the forecast-period value is =NPV(B5, B2:F2). NPV treats the first cash flow as arriving one full period from now, which matches the table. For cash flows on specific dates, use XNPV.

What drives the answer

The result is very sensitive to two inputs. In the example, the terminal value is 72 percent of enterprise value. Changing only the discount rate gives:

Discount rate Enterprise value
9 percent 1,798.7
10 percent 1,554.1
11 percent 1,367.2

A one-point change in the rate moves the value by about 12 to 16 percent, so a DCF is best read as a range supported by stated assumptions, not as a single precise price. Sensitivity tables of discount rate against terminal growth are standard for that reason.

Good practice

  • Tie forecasts to drivers such as revenue growth, margins, working capital and capital spending, and keep them in labeled input cells.
  • Use a discount rate consistent with the cash flows: WACC for firm-level free cash flow, cost of equity for cash flows to shareholders.
  • Check that the terminal value does not dominate without a reason, and cross-check the implied exit multiple.
  • Keep inputs, calculations and outputs on separate, labeled areas of the workbook.

A DCF is a framework for stating assumptions, not investment advice. The DCF valuation model sheet lays out this structure with example data, and the blog post on a DCF valuation spreadsheet is a walkthrough of building one.