Discounted Cash Flow - How to Calculate the Intrinsic Value of a Stock [Free Excel Model Download]

hey guys it's sim from novus investing and in this video i'm going to show you how you can create this dcf evaluation that you see in front of you step by step from beginning to end uh we'll go over all of the inputs all the formulas you know how to project everything and basically this will allow you to calculate the intrinsic value of a company's stock and we just hope that it really helps you out we're going to be leaving a downloadable link down in the description below so let's just say that if you don't want to make this you know step by step yourself all you'll have to do is just download the link in the description and you'll get this template exactly like you see and all you have to do is just get rid of the things that you see in yellow enter in uh whatever companies numbers that you want to enter in and everything else that you see in the green will calculate by itself and all we ask in return is that if you find uh you know this video to be helpful and you if you would like to us to show you some more evaluation techniques uh don't forget to show your support by hitting that like button don't forget to comment and don't forget to subscribe uh now before i just show you the tutorial i just want to break down the valuation that you see in front of you because this is for an actual company this is for apple ticker symbol aapl and uh based off of uh you know these assumptions that we used a 17.93 growth rate over the next five years a 2.5 perpetual growth rate and a discount rate of 9.61 uh we get a per share value of 117 or 118 dollars per share and compared to the current price that represents about 12 downside and therefore uh that has a sell rating now uh this is just for one company apple if you were to uh get rid of everything that you see in yellow uh essentially all you have to do is just uh delete uh everything in those cells and you could enter it for a different company you could enter it you know for a company like facebook you could enter in their growth rate a different perpetual growth rate a different discount rate if you're uh projecting let's say if you see this video in 2021 2022 sometime in the future uh you can just change up the numbers and as you'll see everything is very dynamic uh that the cells in green will change themselves oh but now let's not waste any time and let me just show you guys how you can make this yourself so you know normally whether you're doing this uh in a excel workbook or whether you're doing this on google sheets you're gonna have an empty sheet right in front of you and the first and main important thing is you're gonna need a company under question in order to value so we'll just type in company and just to keep things consistent we're going to use apple ticker symbol aapl uh next up for the rest of our inputs we already typed in the company we're going to need the growth rate of free cash flows the perpetual growth rate and the discount rate and once again i'm going to show you how exactly you can calculate these numbers so we have the growth rate of free cash flows we have the perpetual growth rate or uh the long-term growth rate of free cash flows and then a last but not least we have uh the discount rate also known as the weighted average cost of capital or whack and since these are all of the inputs i'm gonna color code this in yellow now before we get into how exactly you can calculate these numbers i'm going to show you how to make this uh projection you know box right here so what we need is the years so for apple the most recent free cash flow data that we have is up until the year 2020 so we're just going to type in 2020 in the box and this is a five year uh projection period you know we have 2021 2022 2023 24 and 25 so in order to type that out we'll just do equals uh we'll select this cell 2020 we'll hit plus and we'll hit one and hit enter and then from 2021 to 2025 is the projection period so we can just click and drag this bottom right to green square up until 2025 and this is just for minor purposes i want to center everything so i'll hit center and i'll bold everything now we also need the free cash flow since this is a discounted uh free cash flow valuation so we're going to type in free cash flows then we're going to type in the terminal value in this cell right below the free cash flow cell and i'll show you how to calculate terminal value and then we're gonna type in total now since this entire thing is a projection period uh we're gonna you know make it uh full of uh functions and formulas to make it very dynamic so we're gonna make this in green so there's nothing really over here that you have to uh put in yellow besides these two cells because these are two things that you're going to have to enter yourself so everything in yellow is going to be entered by you so one of the reasons why we have year for 2020 in yellow is because again if you're seeing this in the future let's say it's 2022 if you type in the year 2022 well you're projecting free cash flows from 2023 to 2027 and then of course you need the free cash flow for that specific year so that would be the free cash flow for year 2022 uh but now for apple we'll go and uh put this number back the first thing actually we're gonna look for is the free cash flow numbers uh for the most recent year for apple and then we'll populate the rest of the inputs so that's very simple if you're on google all you're going to type in is apple stock and hit enter and we're actually going to be using yahoo finance uh for the information so this is going to lead us to the home page of uh you know the apple it's kind of the summary for it uh we want to head over to financials and then we're going to head over to the cash flow statement that's where you're going to find the free cash flow numbers and we see we have free cash flow for the most recent year of 2020. we have september 30th 2020 and if we scroll all the way down in this sheet uh this line item uh it says free cash flows so that number is at 73 365 so that's what we're going to type we're gonna omit and get rid of uh the last three zeros let me just expand this so you can see it a little bit better but if we go all the way down we see 73 365 that is the number that we're going to enter right here and we'll hit enter and just to make everything in currency we're going to type in the dollar sign up next we're going to need the growth rate of the free cash flows for this we're going to go right back to yahoo finance as you can see we're on the financials tab but we're gonna head over to the analysis tab and we're gonna scroll all the way down up until we get to growth estimates for growth estimates for the next five years it says 17.93 just for baseline right now we're going to use 17.93 as you know you get better with your valuation your company research as you dig more into the company uh you're gonna find a more appropriate growth rate that you can use you know one that makes uh sense based on your set of uh assumptions about the company but just as of right now we're going to leave it at 17.93 next up we're going to need the perpetual growth rate of for the free cash flows so we already have the regular growth rate and this is what we're going to grow their free cash flows by from 2021 to 2025 but then after that you know we have to decide what what rate is free cash flows going to continue to grow at in perpetuity or basically uh for the end of time now would be kind of unreasonable to assume that the free cash flows would continue to grow at a 17.93 growth rate uh up until the end of time because that wouldn't really make any sense that would mean that this company would actually grow to be larger than the us and the united states economy which grows at about a two to two and a half percent uh to sometimes even a three percent growth rate and that's exactly the perpetual growth rate we're gonna go with we're gonna go with the you know somewhere between two to three percent uh but uh just to uh keep everything uh kind of consistent we'll leave it at 2.5 kind of right in the middle the next thing we need is the discount rate or the weighted average cost of capital uh since these are free cash flows that we're projecting into the future we have to discount them to the present day and the weighted average cost of capital uh is going to be the discount rate that we use now you can do one of two things one you could either just enter in a discount rate of your choice you know if you want to earn a 15 return on your investments every single year feel free to enter in 15 but instead what we're going to be doing is we're going to use the weighted average cost of capital now included in the template if you download it from the description below uh is going to be our wac calculator and this is going to be in conjunction with the dcf template we'll make a video in the future showcasing how you can calculate uh the whack by yourself in excel but until then if you want you can just download that excel file down below and just uh enter in the numbers in the yellow everything else in green will calculate itself and we'll spit out a value for uh the weighted average cost of capital so i've already calculated it for apple and i get 9.61 but i know there's maybe some of you who want me to show you step by step how i calculated and you know got these numbers uh i did get everything from yahoo finance so i'm just going to delete everything and i'll show you how to enter them in so for the first thing we need we need the interest expense we'll go over to yahoo finance we'll go over to the financials tab and for this we're going to head over to the income statement uh we're going to expand the income statement and what we're looking for is interest expense which is in this line item it says interest expense and we're going to type in 2873. so i type in 2873 and hit enter and up next i need the short term and the long term debt for this one i need to head to the balance sheet uh i will scroll down and this is going to be for the most recent year 2020 make sure all of your data is for the recent year and we're going to scroll down until we see current debt so that would be short-term debt so 13769 and i'll enter 13769 and up next we need a long-term debt which if we were to keep scrolling we see we get 98 667 so i'll type in 98667 then we need the income tax expense and the income before tax both of which can be found on the income statement so we're going to head over to the income statement once again and we're gonna scroll down until we see the tax provision which is at nine six eight zero i type in nine six eight zero and the income before tax or the pre-tax income is the line item right above that which is sixty seven zero nine one so i'll type in 67091 and we're pretty much almost done we just need to enter in uh three more things uh what we need first is the risk free rate uh for this what we're gonna do is we're gonna scroll to the top up until we get to the market section and then we're going to click on u.s treasury bond rates and this is where we see the treasury yield 10 years which is sitting at 1.472 percent so i'll go back and type in 1.472 and hit enter and then we need the beta of apple stock so for the beta of apple stock we're just gonna go right back to apple over here we're going to head to the statistics section and then once it loads we're going to scroll down where it says beta and we see that it says 1.21 so i will enter that in and hit enter and then last but not least we need the market capitalization which if we scroll up you know we already got the the beta of 1.21 if we scroll up the market cap is 2.21 trillion dollars you want to be careful with your zero so we're going to enter in uh 2.21 trillion dollars and hit enter and we get a weighted average cost of capital of 9.61 which is what we're going to enter for our discount rate so i'll enter 9.61 percent now we're going to get to projecting so we're going to project the free cash flows for the next five years and to do that we're going to uh go to the cell right underneath 2021 and we're going to hit equals and we're going to select the 2020 or the prior years of free cash flow numbers and hit a multiply parenthesis one plus the growth rate of free cash flows just to make everything simpler if we head to this toolbar and before the c we can actually enter in a dollar sign and before the four we can enter in a dollar sign just to fix that cell and i'll show you in just one second why we're doing that and you can even see right here it says equals c10 times parentheses one plus dollar sign c dollar sign 4 and parentheses and i hit enter so now i've gotten the free cash flow number for 2021 but now if all i need to do is just literally just click and drag this up until 2025 and we'll see that we get the free cash flow numbers i just have to uh expand these column lines and we get the free cash flow numbers now the reason that i kept everything fixed is uh because if we were to uh select we see that the growth rate is 17.93 for each of the following periods because that's what we're growing the free cash flows at if instead you know we were to get rid of the uh parentheses and just hit enter and then drag everything what we see is that the number comes out incorrect because instead of referring to uh the 17.93 number uh right now this is referring to the cell which has nothing in it and say so on and so forth for the following cells so it's very important to keep this cell fixed we hit enter and we drag everything up until the end now this is where we're going to calculate the terminal value for the company or the continuing value of the firm whatever you want to call it and for this we're going to use a formula so we're going to hit equals and we're going to select the 20 25 years uh free cash flows and we're gonna do multiplied by parentheses one plus the perpetual growth rate now make sure you select the perpetual growth rate and not the growth rate of free cash flows uh because the perpetual growth rate is uh what the free cash flows are going to grow at you know forever and for this one we don't need to uh you know fix it and add dollar signs we're going to hit end parentheses divided by parentheses the weighted average cost of capital or discount rate minus the perpetual growth rate and we hit parentheses again and we hit enter so now we have the terminal value of the firm and all i'm going to do is add the terminal value with uh the 20 25 years of free cash flow numbers and then for everything else i'm just gonna hit equals and just select everything to uh just uh bring everything uh to the bottom so we see that we have all of the free cash flow numbers now now this is where we're up to near the end and we're just going to enter in this final output section for this i'm going to just do merge and center and type in dcf evaluation and i'm going to bold this we need enterprise value cash and marketable securities and debt which will lead us to the equity value so i'll type in enterprise value and i'm going to do parentheses and plus and the reason i'm doing that is so you remember that we're going to be adding uh cash and marketable securities then i'm going to do parentheses minus because we're going to be subtracting out the debt and then that's going to lead us to the equity value now we're going to calculate the enterprise value uh through a simple formula whereas for debt and cash and marketable securities you're going to have to look for that externally therefore we're going to put it in yellow everything else we can put in green because those are gonna be the outputs from the equity value we move on to shares outstanding uh this is going to be a number that you have to look up yourself so we're gonna put that in yellow and as a matter of fact just to keep everything consistent we'll put this in yellow as well so you know that this entire line item is going to be something that you have to look up yourself and then after we divide the equity value by the shares outstanding that's going to give us our per share value you could type in per share value or intrinsic value and this is going to be something that's calculated on its own so we're going to type in intrinsic value now i'll show you how to calculate the beginning portion before i show you how to calculate uh the last three lines now in order to calculate enterprise value we're going to use a simple uh formula we're going to type in equals and pv and we're going to hit parentheses so the first thing it asks for is the rate and this is referring to our discount rate so we're going to select our discount rate we're going to hit comma and then it's asking for the values so this is going to be the values that we discount to the present day and the values that we want to discount to the present day are these free cash flow numbers so i'm just going to drag and you know select them right across hit end parentheses and hit enter so there we go now i have my enterprise value the next two things i need are the cash and marketable securities and the debt now cash and marketable securities we could find on good old yahoo finance for this we're gonna head to the financial section and we're going to head over to the balance sheet and then once we get to the balance sheet we're gonna hit expand all and we can see that we have uh cash and cash equivalents and includes other investments so that is at ninety nine four three so i'll type in ninety nine four three and then next we need the total debt uh for this you could go right back to yahoo finance and check it out or it's kind of already calculated in the wac calculator we can see that the total debt is 112 436 but if you want to look for it on the uh balance sheet at yahoo finance if you scroll all the way down you would see total debt 112 436 so i'll type in 1 1 2 4 3 6. for the equity value in order to calculate this there's a reason that i put a plus and minus because we're going to hit equals we're going to take the enterprise value and select that and we're going to add the marketable securities which is why we have a plus then we're going to subtract the debt which is why we have a minus and then i'll hit enter now we have the equity value up next we need our shares outstanding for this i'm going to scroll to the top and i'm going to go back to statistics tab and if i scroll down we see we get shares outstanding which is at 16.69 billion now i have to make sure that everything is consistent and therefore i'm not going to enter in 16.69 rather i'm going to enter in 16690 because it's in billing now if it makes it easier for you what you can actually do is you can uh leave all of the zeros because as i had mentioned i was going to i i omit the last three zeros just to make it a little bit easier to read but if you find that it makes it easier for you to kind of follow through feel free to use whatever works best for you so now we're uh up pretty much to the last step uh until we have to find the intrinsic value and for this we're gonna go back to the cell we're going to hit equals we select our equity value we hit divided by the shares outstanding and hit enter and we get a fair value of apple stock of 117.59 per share the reason that i have these last three line items is because yes it's great we know what the per share value of uh apple is that we calculated you know the intrinsic value of the stock but i want to compare it to the current price and see how much upside there is and you know get an automatic buy slash sell signal based off of a formula that i type in so for this we're going to type in currentprice we'll type in upside and we're going to type in buy slash sell now for buy and sell and the upside this will automatically calculate itself so we're going to put in the green and the current price we have to look up ourselves so we're going to put that in yellow now for apple if we go on yahoo finance whatever you know platform you use we can see that the current price for apple is at 133.98 so i'll type in 133.98 and clearly we can compare our intrinsic value calculation with that of the current price and we see that the intrinsic value is less than the current price but by how many percent and this is where we're going to do equals we're going to select our intrinsic value divided by the current price minus one and hit enter we're going to get something that comes in decimal but to change this we can just do percent and i like to keep it at two decimal points so what this essentially says is that our intrinsic value calculation states that there is 12 percent a downside in comparison to the current price of uh apple stock and therefore just to get something that kind of throws out a buy or sell rating in order to do that we can use an if function so we're gonna do equals if parentheses if our intrinsic value is greater than the current price then comma so the value of true is going to be quotes we're going to have the stock rated as a buy for the value if false you know the opposite of buy would be cell so we're going to do quotation mark cell quotation mark parentheses and hit enter and we get a cell rating there we go we've done it you know step by step we've gone over how to value a company using the dcf evaluation if you know you made this valuation yourself feel free to customize it however you want to uh for your personal liking and it's very simple because if you want to use this for the next company under question all you have to do is just delete everything that's in yellow and then enter everything once again like we did throughout the video and everything in green will calculate on its own and it will spit out an intrinsic value calculation for you now if you downloaded the template below and you would just want to enter in the numbers you can do that as well with the dcf template along with our weighted average cost of capital calculator you know which you can use uh for the discount rate for the dcf template so we really hope you found this a video to be helpful and let us know if you would like to see any other intrinsic value calculation techniques by us you know we have a whole bunch of other models and valuation techniques so if you'd like to see those let us know down in the comment section below we hope this was helpful and we'll see in the next video

Channel: Novus Investing

Views: 81,677

Keywords: novus investing, novus, discounted cash flow, discounted cash flow model, dcf, discount rate, how to calculate discounted cash flow, how to calculate dcf, discounted cash flow analysis, discounted cash flow analysis template, dcf model, how to calculate the intrinsic value of a company, intrinsic value, what is a dcf, what is dcf, discounted cash flow valuation, what is a discounted cash flow, financial modeling, DCF tutorial, Discount cashflow, intrinsic value calculation, DCF

Id: bQB5T9cpqRQ

Length: 21min 24sec (1284 seconds)

Published: Fri Jun 25 2021