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.6   based on that how is the model going to change  that's what's known as the 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 evaluation 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 and i'll catch you guys in the next one

Channel: Kenji Explains

Views: 890,038

Keywords: dcf, dcf model, discounted cash flow, discounted cash flow model, valuation model, excel dcf model, dcf step by step, discounted cash flow start to finish, discounted cash flow explained, valuation methods explained, valuing a company using dcf, relative valuation, intrinsic value, free cash flow, free cash flow model, Wacc, weighted average cost of capital, cost of equity, terminal value, free cash flow formula, terminal value formula, implied share price, investment banking

Id: gLULdxrS-CU

Length: 21min 41sec (1301 seconds)

Published: Sun Oct 17 2021