DCF Valuations in Excel

Sarah Martin

30 years: Corporate Valuations

This video walks through how to carry out a Discounted Cash Flow (DCF) valuation using Excel. Sarah Martin breaks down the key steps from setting up assumptions and calculating free cash flow to deriving terminal values and computing enterprise value using the NPV function. This video prepares you to model DCFs independently.

M&A

Subscribe to watch

Access this and all of the content on our platform by signing up for a 7-day free trial.

DCF Valuations in Excel

13 mins

Key learning objectives:

Understand how to structure and execute a DCF model in Excel

Overview:

A DCF valuation is only as good as the model behind it. In this video, we move beyond theory and into the Excel sheet, learning to translate assumptions into forecasts, and forecasts into value. With clear steps, learn how to test your thinking and avoid the common pitfalls that derail valuations (like forgetting reinvestment or misplacing terminal value timing).

Subscribe to watch

Access this and all of the content on our platform by signing up for a 7-day free trial.

Summary

What are the main steps to calculate the DCF?

Structure the sheet and fill in details and set up an income statement
Calculate operating profit after tax and unlevered free cash flows
Find the terminal value using 2 different methods (perpetuity formula and EBITDA multiple)
Use Excel’s NPV function to discount future cash flows
Combine the components to compute enterprise value

What assumptions are needed to start the DCF?
The model requires inputs for tax rate (20%), WACC (8%), terminal growth rate (1.5%), and an EBITDA exit multiple (10x), all of which are changeable.

How is operating profit after tax (OPAT) calculated?
OPAT is calculated by starting with EBITDA, subtracting non-cash items like depreciation and impairment, and applying the tax rate to EBIT. This reflects pre-financing operating profits after tax.

Why is OPAT important for DCF?
It forms the starting point for calculating unlevered free cash flow the cash available to all capital providers.

How do we calculate unlevered free cash flow?
From OPAT, add back non-cash charges (like depreciation), and subtract expected reinvestments such as capex and changes in working capital. These reflect ongoing operational cash requirements.

What are the two methods used for terminal value?

Perpetuity method – assumes a constant growth rate beyond forecast years
Multiple method – assumes the firm can be sold for a multiple of its final year (exit) EBITDA

How is terminal value incorporated into the DCF?
It’s discounted back to the valuation date (end of 2023) using the same NPV approach, and added to the present value of free cash flows to calculate total enterprise value.

What are common pitfalls in this step?
Failing to include zero placeholders in intermediate years when discounting the terminal value can distort results by shortening the discount period.

Subscribe to watch

Access this and all of the content on our platform by signing up for a 7-day free trial.

Sarah Martin

Sarah Martin has a degree in economics from the London School of Economics and stock exchange and regulatory qualifications from London and New York. She has worked in investment banking for 17 years, as well as private equity transactions and as an expert witness in financial trials. She became a financial trainer 15 years ago and specialises in credit, distressed debt, and valuation. Recent assignments have included the European Central Bank, the European Investment Bank, the EBRD, Gibbs Business School in Johannesburg, the Bahrain Institute of Business Finance, the Bank of China, BBVA, the African Development Bank, Siemens, Carnegie Bank, Rand Merchant Bank, the Hamburg Central Bank, and Mizuho Bank.

There are no available Videos from "Sarah Martin"