Discounted Cash Flow | DCF Model Step by Step Guide

What's up everyone, Kenji here, and today I'm going to walk you through a discounted cash flow model, also known as a DCF. And we'll be looking at both the theory side and we'll also be looking at a practical example on Excel, which you can actually download by clicking the link in the description. I'm going to be sharing it as a Google Sheet, so if you want to edit it, go to File, Make a Copy, or File, Download if you have Excel. And for this video, I really don't recommend skipping around too much as everything is obviously quite linked together. And thank you to Financial Edge for sponsoring this video. So what is a DCF? And in short, it's a valuation method to estimate the value of an asset today based on its future cash flows. And these assets can be anything from a company like, say, Apple, a big company, a lemonade stand, or even your house if you own. All of these things can be valued using a discounted cash flow. It's also referred to as an intrinsic valuation method, which basically means that it's independent of external factors and instead just relies on the company's ability to generate cash flows. Now there are other valuation methods out there like relative valuation, which is basically based on competitors and how they're performing, but I have made a video just on that, so feel free to check it out. I'll leave it somewhere up here or even in the description down below. So here's the key steps in creating a discounted cash flow model. Number 1 has to do with forecasting the free cash flows, typically for a 5 to 10 year period. Here's when you use a set of assumptions based on historical data to be able to project how much the company is going to be generating in cash in the next 5 to 10 years. The second step is to calculate the weighted average cost of capital, which is also known as the WACC. And this is the discount rate that you're going to be using to bring back all the future cash flows back to the present, so to year 0. And the reason you need to do this discounting thing has to do with the time value of money, which is a concept that says that a sum of money today is worth more than a sum of money in the future. That's because of things like being able to invest it and grow it over time. So to have an accurate representation, say You actually need to bring everything back to the present value. The 3rd step is to calculate the terminal value. See, after the forecasted period of, say, 5 to 10 years, the company doesn't just disintegrate after that, right? There's usually a life after that, and it keeps on going and keeps on selling. So that means that after that forecasted period, you need to assume a value for it. That's what's known as the terminal value. So it's the value of the company after the forecasted period. The 4th step is to discount the cash flows back to the present, and that applies for both the free cash flow as well as the terminal value that you want to take back to year 0, essentially. And the last step is to get to a valuation. So you're going to calculate the enterprise value, the equity value, and eventually get to the implied share price. And if you didn't understand many of these concepts, don't worry, we'll go through them one by one, and we'll also be looking at them on an Excel-based exercise. So firstly, the free cash flow, and let me briefly define it. In short, it's the cash flow that's available to both debt and equity holders after a business pays for everything it needs to continue operating. So things like their operating expenses, their capital expenditures, and any other investments. Overall, you should see it as the more free cash flow the company has, the more attractive it is for investors because it's able to either pay down its debts or at the same time it's able to invest in new business opportunities. And here's the formula for the free cash flow. Firstly, you have EBIT, which stands for earnings before interest and tax, times 1 minus the tax rate, so a tax percentage essentially, plus depreciation amortization. Because they're non-cash expenses. The reason depreciation and amortization are added is because there's actually no real cash outflow or cash leaving the company. When you think of it, say you buy a fixed asset like a car, for instance, you pay for it then, so that's when the cash outflow is actually happening. By using depreciation, they want to actually allocate the cost over the useful life of the car. In this case, let's say that's 10 years, so they're going to allocate that cost over a 10-year period. But in reality, the cash outflow already happened back then. So that's why they actually have to add back depreciation amortization. Then you subtract capital expenditures, which is also known as CAPEX. So these are things like buying a new factory, which is obviously a cash outflow, right? Then an increase in non-cash working capital is subtracted, while a decrease in non-cash working capital would be added. In short, non-cash working capital is capital that the company uses on its day-to-day for its operations. The formula is just current assets minus cash minus current liabilities. So if non-cash working capital is increasing, that means that the company's assets are going up, and for that to go up, it's spending cash. So the company's cash flow is actually decreasing, hence the negative sign. So let's put it all together in the following Excel exercise. So here we've got the task of calculating the free cash flow, and over here we have all the assumptions in blue, and then all of this area is the one that we're going to have to fill in. So for the EBIT, we're just going to get it from up here. Secondly, for the tax rate here, we have it as a percentage, but we want the actual number. So we're gonna do the 150 times the minus 25% because we want it to be a negative number. Obviously, for depreciation amortization, we want to add these back, so we're gonna go equals depreciation plus amortization in this case. Then for the CapEx, we want it to be a negative number, so minus 50 in this case. And then lastly, for the non-cash working capital In this case, it's an increase, so we're just gonna go equals, just like so. And then for the sum, we're gonna go just SUM formula, for instance, and then go up here and get them all together. So it should give you 86. In this case, we only calculated the free cash flow for 1 year, but later in the video we'll get to a full one where we're gonna do it for multiple years. Now generally speaking, you want to project the free cash flows for a 5 to 10 year period depending on how stable they are, basically. So if they're not very stable, so they're still growing a lot or going up and down a lot, then you might want to project them out for a longer period, like say 10 years. But if it's a very stable company, like say General Motors for instance, then you can just leave it to 5 years. And if you're interested in a career in finance, I recommend you check out Financial Edge, which provides certified online finance courses. They're the instructors that teach new hires at the top 4 investment banks, so you can get the exact same training online just at your own pace. And these instructors are usually ex-investment bankers, and they have over 150 years of experience in the industry. For example, their investment banker course pack goes over all the relevant skills to succeed in an analyst role, from financial accounting to Excel modeling to valuation. They've got plenty of other courses on things like asset management, private equity, and much more. So if you're interested, do check out the link in the description down below, and using the code KENJI25, you can get 25% off. All right, back to the video. Secondly, we have the WACC, also known as the weighted average cost of capital, and this essentially measures the cost of financing for a company. Now, financing can come either in the form of debt or in the form of equity. When it's debt, that's usually through a bond or some kind of a loan, whilst equity is just shares, right? So share ownership. Now both of these do have a cost, so there's both a cost of debt and a cost of equity. The cost of debt is just the interest payment on the debt, so it's the same as me taking out a loan and having to pay interest on it. Whilst the cost of equity is a bit more tricky. Think of it like one being the expectation that the market has for owning your shares, and at the same time the expectation for bearing the risk of owning your shares. It's usually calculated using the CAPM formula, which stands for the Capital Asset Pricing Model. And here's the formula for that, where you have the risk-free rate, which is typically the 10-year US Treasury as it's regarded as the safest thing out there. Then you have the beta, which is a measure of how volatile your stock is relative to the market. So market has a beta of 1, so that means that if your, if your company stock has a beta of 2, then when the market goes up by 10%, your company goes up by 20%, and vice versa. When the market goes down by 10%, your company goes down by 20%. And then you multiply that by the annual return of the market minus the risk-free rate. So with the CAPM out of the way, here's the full formula for the WACC, and it is a bit long. So on the one hand, you have the equity value over the total value of the company, so the debt plus the equity, times the cost of equity in this case, and then you add it. On the other hand, you have the cost of debt over the total value, so the debt plus the equity, times the cost of debt, and then you times that by 1 minus t because the interest payments on the debt are tax deductible. So that gives you the cost of capital, or the WACC. And let's do the exercise for that together in Excel. So here we're given all the relevant data that we need for the WACC calculation, and then I've also left the 2 formulas over here just in case you forget, for reference. But before we actually get to the WACC, we actually need to know the cost of equity. So we're going to calculate that first. So we're going to go equals, here it says the risk-free rate, so we'll take that, which in this case that's a 10-year Treasury like I said earlier, plus the beta. The beta is the 1.3, and then we're going to times that by putting brackets here. We're going to get the market return, the 8% here, minus the risk-free rate, which is the Treasury again. Then we're going to close that and we get the 10% as the cost of equity. Then here we want to actually get the proportions of the equity and the debt amount, so these 2 things over here. So for the equity, that's just the total equity value over the sum of the debt plus equity, right? So So these two, okay. And then for this one here, it's the same thing but the opposite side. So the debt over the sum of the debt plus equity. So these two, okay. So you got both percentages, and now we can actually get to the WACC. So for the WACC, the equity over the debt plus equity is this one here, times that by the return on equity— so sorry, the cost of equity. So that's right here, that's the one we calculated, plus the debt to total value proportion here times The cost of debt. In this case, that's a 5%, and then we're going to times that, put it in brackets, 1 minus the tax rate. The tax rate is 30% here. Close that. That should give us the 6.9. Step 3 is the terminal value, and this is the value of the business after the forecasted period. So suppose you forecast it for 5 years after the current date. Anything after that is the terminal value. Now obviously it is all the way to infinity, and you assume a steady state of growth throughout that period. So you really want to be careful with your assumptions here, as it's obviously going to have a big, big portion of your value, right? Compared to 5 years, if you have infinity, it's gonna be a lot bigger. And there's 2 main ways to calculate this: the perpetuity growth method and the exit multiple method. And I'll teach you both. So the first one is the perpetuity growth, and this one assumes that the cash flows will grow at a steady state forever, where in this case the free cash flow N is the final year of forecasting. So in this case, let's say that's year 5, for instance. The G is the growth rate. Now the growth rate assumption is very, very important here as that has a huge impact on the terminal value. And overall, a good practice is to do it either in the GDP of the country— so if the US is growing by 3%, you would put that same 3%— or if it's a growing industry, you can base it off the industry. So if the industry is going up 4%, then you would take that as your terminal value. Then on the denominator, you have the WACC minus the growth rate. Now the other method is known as the exit multiple, which assumes that the company is sold using a multiple of a metric. In this case, that's EBITDA, and the multiple being the enterprise value over EBITDA. Now that EV to EBITDA multiple is usually calculated by looking at other similar companies. So for instance, if you're Google, that might be Microsoft, and you look at their multiple, and based on that you're going to be able to derive your own terminal value. Now to be honest, out of the 2 methods, I'm not really sure which one's most popular. I've seen both of them being used. So what I'm gonna do here, just so you can see both of them, is just I'm gonna take an average of the 2. So let's hop on to Excel and calculate them together. So here's the terminal value exercise, and over here you have all the assumptions. Out here in blue, and then the hardcoded numbers here in blue as well. So let's start off with the perpetuity growth method. And here you're gonna need the free cash flow of the last year, so that's equals to the 72. And then we're gonna do times, put brackets, 1 plus dg, so that's a 2.5%, close the brackets, then divide by, um, in this case that's a WACC. So open up the brackets, put the WACC in there, minus the growth rate So there you go. And then we're going to close these brackets, and that should give us 984. And then the EV to EBITDA multiple, in this case, that's just timesing the EBITDA. So here we go, times the multiple. So the multiple is a 7 in this case, so that's 994. Then just for this one, I'm going to average them out. So get the average formula and then times it. And overall, the 2 methods should be somewhat similar. If they're not, there might be a problem somewhere. All right, so the next step is the discounting. And the idea here is that we're going to discount the free cash flows back to the present, and we'll also discount the terminal value back to the present. Now with those 2 things, if we sum them up, we're probably going to get the enterprise value. So let's get into that. So over here on Excel, I've actually left the formula for both things. The discount factor in this case is what's gonna be able to discount you back to the present value, and then here's the cash flows and what to do with them to discount them back. So firstly, we got to calculate the discount, the discount factor. In this case, that's just the 1 over, put in brackets, 1 plus r. The r in this case is the WACC. And then you're gonna put that to the 1, in this case period 1, right? Now if you want to drag these along over here to the sides, we're just gonna go in there and lock the— lock this value so it's not moving. So we're gonna highlight it and then you're gonna press F4. That's gonna lock it for you. So now once we copy it, you can drag it along and then paste it like that. And if you look here, the value hasn't moved, but these one— these ones have, as they should, right? So once we have the discount factors, we gotta find the present value of the free cash flow. So that's actually just equals to this times the discount factor. Okay, and then we're gonna move it along. So the same thing for all of these. Then for a terminal value, this one's actually in year 5 right now, and we want to take it back to year 0 as well. So we're going to go equals, get the terminal value of 800, and then times that by the discount factor. Okay, and then for the enterprise value, that's the sum of the free cash flows plus the terminal value in year 0. So we're gonna go equals sum, and then I'm just gonna drag them like that and sum them all up. So that's it. And the last step here is to go from the enterprise value to the equity value and eventually get to an implied share price. So let me show you the formula for that. So as you can see, the enterprise value has both debt and the equity in it, and the formula for the equity value is actually fairly straightforward. It's the enterprise value minus debt plus cash, which is the same thing as net debt essentially. Now there is a more complex version of this formula, but I'm not really going to talk about it here as I don't want the video to get too long. So let's hop onto Excel here and go from the enterprise value to the equity value. And overall, like I mentioned earlier, for enterprise value it should be minus all of the cash. Now marketable securities is cash-like items, so usually when they say cash and equivalents, they refer to marketable securities. That's also cash in this case. And then we have 2 types of debt. So for this one, we're gonna go equals enterprise value plus sum of the cash and the marketable securities, close that bracket, minus the sum of the short-term debt and the long-term debt, and then close that bracket. So that's going to give us the equity value of $1,250. Then we say we have 150 shares outstanding. These are the shares that are out there in the market. So that basically means that If we divide the equity value over the number of shares, that's going to give us the share price. In this case, that's $8.30 per share. And now putting it all together, I've made this Excel modeling exercise which goes all the way from the first step to getting to an actual implied share price. I've basically highlighted all the areas that need to be filled in yellow, and I'm gonna be doing it a bit more quickly than before, mainly because I've already explained all the steps. So you can either follow along or you can try it out for yourself and then watch how I do it to see if you have the same answers. So let's get into it. Over here we have all of this set of assumptions that we need, and then we're gonna go step by step all the way till we get to an implied share price down here. So firstly, we want to find the tax. So we're going to go equals this number times minus 25% because we want it to be negative. Then once we have that, we actually want to try to lock this one. So the C8, we're going to want to lock. So we do that by pressing F4, and then we're just going to drag these along. Just like so. And then once, once we have that, we're just gonna go get the free cash flow by summing it. There you go. Then drag these along as well, just like so. Then the cost of equity, and this one is a CAPM. We need to calculate this based on these numbers over here, I think. So we're gonna go once the risk-free rate, so treasury in this case, plus the beta times, and then in brackets we're gonna put the market return minus the the risk-free rate, then close the brackets. So that's the cost of equity that we have. And then we're gonna get the different proportions here. The debt is the debt value here, and then divided by the sum of the debt plus the equity. So these 2. Then same thing over here where we're gonna get the equity in this case, divided by the sum of the debt plus the equity. So these 2. Okay, and then for the WACC, we're gonna get the whole thing. So we're gonna do equity times the cost of equity plus, plus the debt times the cost of debt, which is over here, and then times that by 1 minus the tax rate. And there you go, 8.9— 8.49 is what you should be getting. Then for the EBITDA, in this case we don't have the EBITDA up here, but we can actually calculate it. When you see you have the EBIT here and then you have the depreciation amortization over here. So EBITDA stands for earnings before interest and tax, depreciation and amortization. So basically means that we're gonna get the EBIT here, then we're also gonna add the depreciation amortization, and that should give us the EBITDA. All right, from there we can calculate the exit multiple, which is just the EBITDA times the Multiple, which is here the seven. Then we got the perpetuity growth, which is equals to the free cash flow times the one plus the growth rate, which in this case is the 1.7 percent. Then divide that by the WACC, which is the 8.49 minus the growth rate. So there you go. Okay, and then we gotta calculate the discount factors to discount all of the free cash flows and terminal value. So for that, we're gonna go to equals 1 over brackets 1 plus the WACC, which is the cost of capital for this company. And then we're gonna put that to the first, and like we did earlier, let's not forget to lock the C26 in this case. So once we have that, we're going to be able to drag it along. And just to make sure I'm not making a mistake here, let me get into this one. Yeah, so this is stuck here, which is how it should be. Then the present value of the free cash flows is just this one times this one, then drag that along. And the terminal value is a similar concept with this one times the average here. Okay, and then for the enterprise value, we're just going to sum all of these up. Just like so. And then we're gonna get to the equity value. So for this one, we need to find— it's the enterprise value, we gotta plus the cash and the marketable securities. So cash plus marketable securities minus the short-term debt minus the long-term debt. So that's the $22,000. We got 1,000 shares outstanding, so we're gonna divide these, and that should give you your share price. Hopefully you got the same number. If not, feel free to look over it again. And here's some important assumptions that I've made just to make it a bit easier for the video, but I think are worth noting. The first one has to do with the forecasts. In this case, I just gave you the EBIT and the free cash flow as is. I didn't actually calculate it based on historical information. So that's usually how it's done. You calculate the expected revenues that you're gonna get in the future based on, say, the past 3 years. Same thing goes for the costs and so on. It is a very lengthy process, so that's why I decided to omit it. But that's usually how it's done. The second assumption is that the free cash flows usually don't happen at the end of the year like we've been saying in year 1, year 2, year 3, and so on. Usually they happen throughout the year. So instead what you do is a mid-year adjustment. So usually instead of saying 1, you say 0.5. Instead of saying 2, you say 1.5, and so on, just because it's a lot more accurate. And if you're wondering whether it makes a difference, it actually does make a good difference, especially when it comes to the discount factor. As that number is going to change based on that. Another important thing to point out is that I didn't do a sensitivity analysis. Now what this is is basically a table that shows how these numbers would differ if a certain assumption was different. So for example, the WACC or the growth rate, things like that that are very important to the model— if those differ by a bit, like say instead of having a 2% growth rate you had a 1.8% or a 1.6%, based on that, how is the model going to change? That's what's known as a sensitivity analysis, and it is quite common to do. And the last thing here is that a DCF isn't always applicable. Sometimes a company has negative free cash flows, and if we continue doing the valuation with that, that would basically mean that it has a negative valuation, meaning that if I were to sell you my company, I would actually have to pay you to buy the company for me, which just doesn't make any sense, right? So usually with companies that are startups, for instance, or highly growing companies These sometimes might have negative free cash flows, so instead you have to use other valuation methods. Like I said earlier, I made a video on the other one, so you can look at it somewhere up here. If you feel like I've made an error somewhere, feel free to comment it down below and I'll pin it if it's right. And then if you have any other video suggestions, I'm thinking of doing something like a relative valuation model as well to explain that. Let me know in the comments, I'd be interested to hear if you're interested. That's all for this video, I hope you enjoyed it. Feel free to like and subscribe down below, really helps out the channel. Channel, and I'll catch you guys in the next one.