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.
