DCF valuation template (Excel)

What it is

A discounted cash flow (DCF) valuation estimates what a business is worth today from the cash it is expected to produce. Cash expected in future years is worth less than cash today, so each year's cash is discounted at a rate that reflects its risk, commonly a weighted average cost of capital.

This workbook uses unlevered free cash flow: operating profit after tax, plus depreciation and amortization, less capital spending and the increase in working capital. A terminal value) stands for every year after the fifth, assuming cash flow then grows at a steady rate. The formulas are:

• Enterprise value = the sum of each year's free cash flow divided by (1 + r) to the power of the year, plus the terminal value divided by (1 + r) to the power of 5.
• Terminal value = Year 5 free cash flow times (1 + g), divided by (r - g), where r is the discount rate and g the terminal growth rate.
• Equity value = enterprise value less net debt.

Who it is for

Analysts and students learning valuation, founders and finance teams testing what a price implies, and investors who want a transparent base to extend.

When to use it, and when not to

• Use it to understand what must be true for a price to make sense, to compare cases, and to see which assumption matters most.
• Use it when the business has cash flows you can forecast with some confidence.
• Do not use it for a business with no forecastable cash flow, or treat the output as a single true number. A DCF turns assumptions into a value, so the value is only as good as the assumptions.
• Do not use it alone to set a price. Compare with similar businesses and with the three-statement model behind your forecast.

What is inside

• Inputs. Valuation inputs (discount rate, terminal growth rate, tax rate, net debt, shares outstanding, sensitivity steps) and a five-year forecast: growth, operating margin, depreciation and amortization, capital spending and working capital.
• DCF. The free cash flow build, discount factors and present values, the valuation, three sense checks, two sensitivity tables (five discount rates by five growth rates) and a checks block.

Named ranges, such as Discount_Rate, Terminal_Growth and Enterprise_Value, mark inputs and results.

How to fill it in

1. Replace the made-up forecast with your own. Anchor growth and margins to history, capacity and a stated plan.
2. Choose the discount rate and explain how. If you use a cost of capital, say what it assumes about the cost of equity, the cost of debt and the mix.
3. Choose a terminal growth rate. It is usually kept at or below the long-run growth rate of the economy the business sells into. It must be below the discount rate.
4. Enter net debt at the valuation date: debt less cash, in the same units. Enter shares outstanding in the same units if you want a per-share value.
5. Read the valuation and the sense checks. If the terminal value is most of the total, the answer rests on the discount rate and growth rate more than on the forecast.
6. Study the sensitivity tables. Quote a range, not a point.
7. Make sure every check reads OK.

Worked example (an illustration)

Illustration only. The company and every number below are made up, in thousands. They show how the template reads. They are not a valuation of any business.

With the made-up inputs in the file (Year 0 revenue of 5,000, growth easing from 12% to 5%, an operating margin of 14% rising to 16%, a 10% discount rate, 2.5% terminal growth, net debt of 1,500 and 2,000 shares):

A one-point move in the discount rate changes the value by roughly 1,100 to 1,400 here, while half a point of terminal growth moves it by about 500. With 73% of the value in the terminal value, that is why the sensitivity tables matter.

Common mistakes

• A terminal growth rate near or above the discount rate. The formula then explodes or turns negative.
• A terminal year that is not steady: capital spending far below depreciation, or margins at a peak.
• Discounting cash flows before interest at a cost of equity, or cash flows after interest at a cost of capital. Match the two.
• Forgetting net debt, or using the wrong date for it.
• Presenting one number. Show the range from the sensitivity tables.

Download

Free to download. The numbers in the file are made up, so replace them.

Use and limits

Free to use and adapt for your own work. If you publish or share it, a link back is appreciated.

This template is a learning and planning tool. It is not investment, valuation or financial advice. A value built from made-up inputs is not a valuation of anything.

Sources and further reading

Sources are linked so you can check them. Statements that vary by employer, or that accounts disagree about, are hedged in the text. Found a mistake? Email support@forwardeployed.work and we will correct the page and update its date.

1. Discounted cash flow. Encyclopedia entry.
2. Weighted average cost of capital. Encyclopedia entry.
3. Terminal value (finance). Encyclopedia entry.
4. Free cash flow. Encyclopedia entry.
5. Sensitivity analysis. Encyclopedia entry.