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 one has to do with forecasting the free  cash flows typically for a five to ten 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 five to ten years the second step is to calculate the weighted  average cost of capital which is also known as the wak 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 zero 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 third step is to calculate the terminal value see after the forecasted period  of say five to ten 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 it 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 fourth 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 zero 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 a 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 a formula for the free cash flow firstly you have ebit which stands for earnings  before interest and tax times 1 minus a tax rate so tax percentage essentially plus depreciation  amortization because they're known 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 copics 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 and 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 going to 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 going to go equals  depreciation plus amortization in this case then for the copics 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 some 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 one 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 five to ten year period depending on how stable they are basically so if  they're not very stable so they're still growing a lot or you're 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 five 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 four 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 kenji 25 you can get 25 off all  right back to video secondly we have the walk 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 that 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 that 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 cup m 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 a market has a beta of one so it means that if you're if your company's stock has a beta of two  then when the market goes up by 10 your company goes up by 20 and vice versa when the market goes  down by 10 percent 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 cup m out of the way here's the  full formula for the whack 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 one minus t because the  interest payments on the debt are tax deductible so that gives you the cost of capital or the  walk 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 wag calculation and then i've also left the two formulas over  here just in case you forget for reference but before we actually get to the walk 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 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 gonna  times that by putting brackets here we're gonna get the market return the eight percent here minus  the risk-free rate which is the treasury again then we're gonna close that and we get the 10  percent as the cost of equity then here we want to actually get the proportions of the equity and the  debt amount so these two things over here so for the equity that's just the total equity value over  the sum of the debt plus equity right 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 plus equity so these two  okay so you got both percentages and now we can actually get to the walk so for the walk the  equity over the debt plus equity is this one here times up by the return on equity so  sorry the cost of equity so that's right here this one we calculated plus the that  to total value proportion here times the cost of debt in this case that's a five percent and  then we're going to times that put it in brackets one 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 five 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 five years if you have infinity it's gonna be a lot bigger and there's two main ways  to calculate this the perpetuity growth method and the excel 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 the  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 five 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 u. s is growing by three percent  you would put that same three percent or if it's a growing industry you can base it off the industry  so if the industry is going up four percent then you would take that as your terminal value  then on the denominator you have the whack 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 two methods i'm not really  sure which one's most popular i've seen both of them being used so what i'm going to do here  just so you can see both of them is just i'm going to take an average of the two 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 hard coded 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  one plus dg so that's a two point five percent close the brackets then divide by um in this case  that's the walk so open up the brackets put the lock 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 seven in this case so it'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 two methods should  be somewhat similar if they're not there might be a problem somewhere all right so the next step is  discounting and the idea here is that we're gonna discount the free cash flows back to the present  and we'll also discount the terminal value back to the present now with those two 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 going to  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 one over but in brackets one plus r the r in this case is the walk and  then you're gonna put that to the one in this case period one right now if you want to drag  these along over here to the sides we're just going to go in there and lock the lock this value  so it's not moving so we're going to highlight it and then you're going to press f4 that's going  to lock it for you so now once we copy it you can drag it along and then paste it like that if you  look here the value hasn't moved but these 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 going to move it along so the  same thing for all of these then for a terminal value this one's actually in year five right now  and we want to take it back to year zero as well so we're gonna go equals get the terminal value  of 800 and then times up by the discount factor okay and then for the enterprise value that's the  sum of the free cash flows plus the terminal value and you're zero 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 that 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 open to 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 a marketable securities that's also cash in this case and then we have two types  of debt so for this one we're going to go equals enterprise value plus sum of the cash and the  marketable securities close the bracket minus the sum of the short term debt and the long-term debt  and then close the bracket so that's going to give us the equity value of 1250 and then we say we  have 150 shares outstanding these are the shares that are out there in the market so 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. 3 dollars 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 the  set of assumptions that we need and then we're going to 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 and then once we have that we actually  want to try to lock this one so the c c8 we're gonna wanna lock so we do that by pressing f4 and  then we're just gonna drag this along just like so and then once once we have that we're just  to go get this free cash flow by summing it there you go then drag these along as well not  just like so then the cost of equity and this one is a cup m we need to calculate this and  based on these numbers over here i think so we're going 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 risk free rate then close the brackets so that's the cost of equity that we have and  then we're to get the different proportions here the debt is the value here and then divided  by the sum of the debt plus the equity so these two then same thing over here where  we're going to get the equity in this case divided by the sum of the debt plus the  equity so these two okay and then for the walk 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 that 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 going to get the ebit here then we're also going to add the depreciation  amortization and that should give us the ebitda right from there we can calculate the exit  multiple which is just the ebitda times the multiple which is here the seventh 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 walk 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 one over brackets one plus the walk which is the cost of capital phase company and  then we're going to 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 dragged 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 the all of these up just like so and then we can we're going to 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 1000 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 going to get in the future based on say the  past three years same thing goes for the costs and so on it is a very lengthy process 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 one year two year  three and so on usually they happen throughout the year so instead what you do is a mid-year  adjustment so usually instead of saying one 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 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 walk 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 two percent growth rate you have a 1. 8 or a 1.