Build a Discounted Cash Flow (DCF) Model in Excel | Step-by-Step Valuation Guide

Hi everyone, Tim Vipond here, co-founder and CEO of Corporate Finance Institute.

Today I'm going to walk you through how to build a DCF model in Excel. We're

going to start with some theory where I'm going to break down the principles of discounted cash flow analysis. Then

we'll flip back over to Excel where we'll build it together step by step.

Now, this lecture builds step by step over time. So, I recommend watching it

over time. So, I recommend watching it from start to finish so you don't miss anything along the way. I'll also put a download link to this model in the course notes as well. So, please be sure

to check those out. So, as you can see here on my screen, we've got a discounted cash flow analysis, this section here, where we're going to calculate a company's share price based

on our forecast of how it's going to perform in the future. We'll be

calculating the company's cash flows, as you can see right here, and then discounting them back to today. If I

flip through the model, you can see that it's all driven by assumptions. These

are three different cases that we could hypothetically imagine for this business that we want to value. And we can see how they flow through the model to help us calculate its intrinsic value. We can

use charts and graphs to represent these different cases and give us a real sense of how the company is going to perform.

So with all that in mind, let's flip over to review some theory on discounted cash flow analysis.

All right, let's review what discounted cash flow valuation really is. We're

calculating the net present value of the future cash flows of a business. So what

we do is we forecast your one, two, three, four, five, etc. into the future in our Excel model and we discount them back to today at a discount rate.

Typically a company's weighted average cost of capital and that gives us the net present value of the business which shows us what it's worth or what we

might be willing to pay for it today.

As a reminder, discounted cash flow analysis is just one type of valuation.

There are three broad categories of valuation as shown here. On the left, we have the cost approach. In the middle, there's relative valuation where we look at multiples compared to other

companies. And then on the right, we

companies. And then on the right, we have discounted cash flow analysis or intrinsic value of a company. That's

where we're focused today and that's what I personally find the most interesting and the most detailed when it comes to valuing a company.

So there are two parts to a DCF analysis. The first part is stage one

analysis. The first part is stage one where we have a discrete forecast. In

Excel we are going to model out revenues, expenses and cash flows for a company for 5 years. This is a common

forecast period 5 years and then discount those uh numbers back to today.

Then stage two is a terminal value.

Since we're not going to build our forecast out for say 100 years, it would just be far too much detail. We're going

to assume a terminal value that could either be a perpetual growth rate, so it grows into perpetuity at some low growth rate, or an exit multiple assumption where we assume the business could

theoretically be acquired by another company at a certain valuation multiple.

And this is the more common approach.

So, we're going to employ that approach when building this model together. Then

we add together stage one and stage two and we get our total net present value or total DCF.

There are a lot of assumptions that go into building a DCF model. And this is why I personally like them and why they're so commonly used because you really get to paint a complete picture

of a business. We will look at everything from the size of a market to sales mix, price volume analysis, costs, balance sheet assumptions, and so on.

You could really have an endless number of an assumptions that go into your model. Or you could keep it simple. And

model. Or you could keep it simple. And

in this case, to make the point in Excel, we're just going to keep it somewhat simple, focused mostly on revenue and operating costs for our model. But when we take all these

model. But when we take all these assumptions together, not only are we able to model the financial statements of the company, but we're able to get the unlevered free cash flow or free

cash flow to the firm for the business, which is what we want to use to calculate our DCF analysis.

So as a reminder unlevered free cash flow to the firm means if the company doesn't have any debt it's before interest payments then that cash flow is

available to all types of investors debt and equity investors and that is how we calculate firm value and that is what we want to focus on here.

Now to calculate unlevered free cash flow, the most common way to do it is to start with EBIT which is operating profit.

Deduct taxes from that which gives you no PAT or net operating profit after tax. Then you add back depreciation and

tax. Then you add back depreciation and amortization since it's a non-cash expense. Deduct capital expenditures

expense. Deduct capital expenditures and deduct the increase in networking capital. The sum of this gives you

capital. The sum of this gives you unlevered free cash flow or free cash flow to the firm. And this is what we're going to build up together in Excel.

When we work through an example together, I think you'll find it clear.

And as a recap, let's look at weighted average cost of capital, which will be our discount rate. In this model, we have to look at the cost of equity and the cost of debt for the company. To

calculate the weighted average cost of capital, we're going to use the capital asset pricing model, which assumes a risk-free rate plus a premium for risk.

And this combined gives us the discount rate to bring the cash flows back to today. Because as an investor, I need to

today. Because as an investor, I need to be compensated not only for the time value of money, for all the years it's going to take to get this cash flow, but

also for the risk. I need to be paid if you will for the extra risk I'm taking from investing in this company.

Now let's look at terminal value and how we would calculate that part of the DCF model. There are two types of terminal

model. There are two types of terminal values. the perpetual growth method

values. the perpetual growth method where you assume the company just grows on forever at some relatively conservative growth rate or the terminal

multiple method or exit multiple method where you assume that the company could be acquired for say an IBA multiple like 8 time IBDA which gives us a lump sum of

cash to pay us for the remaining life of the company. So we have our forecast

the company. So we have our forecast period plus our terminal value to get the total.

And when we go over to Excel, we're going to use the X NPV function to calculate the present value. The reason

we use XNPV is we can apply specific dates to each of the cash flows that we receive and bring them all back to today. So we're going to go through and

today. So we're going to go through and link that up together in Excel. And the

number that it spits out from this XNPV formula is our DCF value. And if we divide it by the number of shares after we make a couple of calculations along

the way, we can get the share price for this business. So let's flip over to

this business. So let's flip over to Excel and get going at building this DCF model together.

All right, so here we are in the Excel file. This is the DCF model that we are

file. This is the DCF model that we are going to calculate together. You can see the cover sheet here. We always start our models at CFI with a nice cover sheet like this. And if we scroll

through, first we have the outputs tab.

Why is that? Because we like to start with the end, the final presentation of what we're going to create here. And we

can see different growth scenarios for this company.

And that's all based on different inputs or assumptions. We have assumptions

or assumptions. We have assumptions about revenue growth, cost of goods sold, and SGNA expenses. And those flow into the actual model here. If I click

on the model tab, that enable us to forecast an income statement for this business that flows down into our unlevered free cash flow schedule as I

described earlier in the theory section of this. And then finally, we can

of this. And then finally, we can calculate the discounted cash flow and the per share equity value of $3.3.

So, let's go through and build this together. I'm going to remove all the

together. I'm going to remove all the formulas that we need to calculate and we'll complete it together.

And finally, the formula that we're going to use to calculate the net present values, the NPV function in Excel. There's also a more advanced

Excel. There's also a more advanced version, the XNPV function, but for the purposes of this course, let's keep it simple and simply use the NPV function

in Excel, where we take each cash flow and discount it back to today. So, with

that said, let's flip over to Excel and start building this DCF model together.

All right. So, here we are back in the DCF model and we're going to calculate the ultimate goal is to calculate this price per share for the business. And in

order to do that, we have to fill in the unlevered free cash flow as I described in the slides earlier, then apply an exit multiple to get a terminal value and discount that all back. So, to do

that, we have our schedule here to calculate unlevered free cash flow. And

it's based on this forecast. You can see here the five years of the forecast 1 2 3 4 5 and then a terminal value. So to

do this, let's link it up in Excel. We

start with EBIT, as you'll recall when I outlined that in the slides, which is the same thing as operating profit.

We will then multiply that by the tax rate to get the tax expense. And if we add

those up, alt equals is the shortcut, we get the total notepad. We can copy this.trl C. And then selecting across

this.trl C. And then selecting across alt s to paste special as formulas.

And we now have our notepad for each of the five years plus the terminal value.

Now what we need to do is add back depreciation and amortization.

So I'm going to flip the sign. So equals

negative sign the depreciation number that was used above. Then for simplicity sake to calculate capital expenditures we're going to say that it's equal to

tax depreciation. As you can see right

tax depreciation. As you can see right here this is just an assumption we've made. So I'm going to type alt equals

made. So I'm going to type alt equals negative and press enter to get that. And then we've provided this number, the change in working capital. That's based on some balance

capital. That's based on some balance sheet adjustments. And we cover that in

sheet adjustments. And we cover that in detail in more advanced courses, but for now it's not a huge number. It doesn't

net out to make much difference, but we've given it to you to build this model together. And then the unlevered

model together. And then the unlevered free cash flow is simply the sum of the NOAT, the DNA that's added back, the capex that's deducted, and the working

capital changes. So I can copy these

capital changes. So I can copy these numbers here and then select across

and paste these as formulas. Alt EF

and same thing here. Copy.

I hold down shift while I'm pressing the over arrow. Alt es F for formulas. The

over arrow. Alt es F for formulas. The

reason I paste this way, paste specialist formulas, is to preserve the formatting. You can see that the

formatting. You can see that the formatting here has like a special border around it and some underlining and stuff and I don't want those numbers to get messed up or that formatting to

get messed up. So here's my unlevered free cash flow. I started with EBIT just as I outlined in the slides earlier.

Deducted taxes that gave me no PAT net operating profit after tax added back DNA deducted capex and then adjusted for the changes in

working capital.

So, what I can do down here, let's just fill this in together, is first of all, reference the unlevered free cash flow

in year one of my forecast.

Copy that and paste as formulas.

This gives me the unlevered free cash flow for every year. And then I'm going to reference IBIDA, which is contained up in the income statement. Here you can see the terminal

statement. Here you can see the terminal period ibida that I'm going to reference there and that's going to be used for my terminal value.

So let's link one more time the discrete forecast period unlevered free cash flow. I'm bringing it down here so that

flow. I'm bringing it down here so that we can calculate the net present value all in one spot. For terminal value we

put zero in each of these years because there's no terminal value. until the

end. To calculate it in the end, I just take the EBIDA and since I'm going to use the exit multiple approach, I multiply it by the terminal value

multiple of eight times. And as you can see, it's a really big number because if we sell the company, we're going to get all the future value for it.

So what we can do down here is sum up with alt equals these two sets of cash flows.

Fill it in with pasting special.

And you can see here the total cash flow of the discrete forecast and the terminal value.

Okay. And now let's calculate the present value of the discrete forecast period equals NPV reference the discount rate

comma and then select each of these cash flows that make up the discrete forecast period

close bracket and press enter. So you

can see that number here and then let's calculate equals NPV reference the discount rate comma here we're going to select this is

another way to do it instead of selecting each of the individual years we'll just select the whole series close bracket and press enter and we can see

the net present value All right. So now we can see our

All right. So now we can see our enterprise value here which is the sum of two parts. The discrete forecast and the terminal value. I've shown you two different ways to use the function. You

can select each cell individually or you can select the range. Both work to get the enterprise value.

And then what we're going to do down here is calculate the equity value which simply subtracts the net debt from the enterprise value. Since it's unlevered

enterprise value. Since it's unlevered free cash flow, we get enterprise value when we c when we use NPV. And then we have to adjust for net debt to get

equity value. And then once we have

equity value. And then once we have that, we can calculate the equity value per share by dividing the equity value by the number of shares outstanding.