## Sign up for Free DCF Excel Template

DCF is discounted cash flow for short. DCF model is constructed to help us calculate the intrinsic value of a stock. According to legendary investor Warren Buffet, intrinsic value is an all-important concept that offers the only logical approach to evaluating the relative attractiveness of investments and businesses. This intrinsic value can be defined simply as the value today of all expected free cash flow from the future. Our ultimate goal of using DCF valuation is to calculate the intrinsic value or fair value of a company.

**When to use Discounted Cash Flow (DCF)**

First, we need to make sure that the company we are analyzing is well-suited for a discounted cash flow analysis. Besides having a positive free cash flow, a company needs to meet any of the four criteria below to be well-suited for FCF valuation:

- Company does not pay any dividend
- Company pays a dividend but dividends are very different from the company’s ability to pay
- Company’s free cash flow aligns with their profitability
- The investor is taking a control perspective

This valuation method likely eliminates any early-stage companies that have a lot of growth upside but do not have any free cash flow at this point. On the other hand, large bluechip established companies will likely be great candidates for the free cash flow valuation method.

**Where to Find the Data for Discounted Cash Flow**

What we did to get the financial numbers that we need for this analysis is to simply go over to Yahoo Finance and copied and pasted each of the three financial statements over into Excel. Once we did that, we added some colors to make everything a bit better for the video, but all of the data is exactly the way it was on Yahoo Finance. Now as you can see, a lot of the data is blank in here and unnecessary, so from a video perspective, we are going to be pulling out certain line items to make them more readable for everybody and they will be brought out as we go along. As you may notice, we are using **APPLE** in our example because we think it makes more sense to use an actual company versus a hypothetical company. Another thing we need to be careful of is that for some crazy reason, Yahoo does the years for each company in chronological order from right to left instead of left to right. Since we will be making projections, we would want the data to go from oldest to newest. Hence when copying this data over from Yahoo onto Excel, we switched around the columns to make sure it is going in the proper direction.

**How to C****alculate Free Cash Flow **

Usually, we have two types of free cash flow calculations: free cash flow to the firm (FCFF) and free cash flow to equity (FCFE). Equity stands for common shareholders. Free cash flow to the firm is exactly what it sounds like. It is free cash flow that is available to the entire firm including bond investors while free cash flow to equity is what is available to stock investors. The primary differences between the two free cash flows are interest payments and taxes. We elect to use free cash flow to equity as long as the leverage of the company that we analyze is fairly stable. Here is why;

The first thing we need when analyzing free cash flow is cash flow from operations (CFO). For Apple, we can see it is $77,434,000 (09/29/2018). Then we subtract capital expenditures of ($13,313,000), which comes from the second section in the cash flow statement. After we have done the math, we end up with about $64 Billion in free cash flow. It is important to make a note here that technically it is not the correct number but an estimate. We are also supposed to add net borrowings to the whole mix, therefore a cleaner formula would be:

CFO – fixed capital expenditures (CapEx) + Net Borrowings

When we adjust these numbers to account for net borrowings in 2018, it does not affect things all too much, but when we look at previous years what we can see that Apple had taken out large amounts of debt in the past few years and it has significantly impacted their free cash flow. The reason that this formula is more accurate is that imagine a company went out and borrowed a bunch of money, if they wanted to, they could use that borrowed money to pay dividends. Since free cash flow to equity is how much cash is available to stockholders, that borrowed money would be available to the shareholders but not be available in free cash flow to the firm. Many Wall Street Analysts that I know don’t do it this way. They simply take cash flow from operations (CFO) and subtract capital expenditures and one of the major reasons that they use the simpler way is that when we forecast free cash flow, we realize it is very difficult to predict when a company is going to have net borrowings.

**Forecast Free Cash Flow**

There are a few different ways to do projections but we are going to stick with the way that is simple and comes up with a fairly effective result. Going back to our initial four criteria to use free cash flow valuation that we mentioned in the first part of the video, one of those reasons was that the company’s free cash flow should be in line with profitability. Applying that to Apple, we take our calculation of free cash flow by using the simpler version of the calculation and compare that to net income with this formula: **FCFE/Net Income**. It turned out that each of these numbers for the past four years are about in line with net income. In 2015, it was 131%; 2016, 117%; 2017, 107%; and 2018, 108%. Ideally what would have happened is that when we were doing research and for the company we were analyzing, perhaps we have developed an opinion as to where we believe that business is going. It would help sway our decision as to whether or not we believe free cash flow will continue to act as it has relative to profits. For Apple, in my case, I have done some research on the company and I have no reason to believe that Apple’s free cash flow was going to be terribly different than it has relative to profits than it has in recent years. That being said, I think it makes sense to use the 107% number, which is the lowest in the past four years and will make our estimates more conservative.

There are many different ways to come up with projections. We can either derive free cash flow from net income projections or similarly, we can estimate revenues, which then leads up to net income and finally free cash flow as well. We are going to project revenue here just to illustrate how it could be done, but if you want to try for net income, same concept would apply. For a company like Apple, usually if there is a good sample of analysts covering the company, we utilize Yahoo finance analysts’ estimates for the first two years of revenue projections as a foundation. We can see that they have revenue for 2019 to be estimated about $257 billion from 33 analysts. In 2020, they have it estimated to be at $269 billion. Alternatively, a reasonable growth rate can be used to forecast revenues. Imagine we don’t have access to any analyst projections or we don’t know if we can trust the analysts that are doing the projections. Either way, one simple and logical method of coming up with projections is to identify a reasonable growth rate. In this case, we want to project revenues but you could apply the same method to both revenue and net income. So, what should our growth rate be for revenue for the two additional years that we are going to come up with. We can take the average of what analysts expect to happen in the next two years, which are -3% growth rate in 2019 and 5% growth rate in 2020. Averaging them out, we end up with 1% growth rate. While this works, we could also take an average of all five years and if we do that, we get 3% average. To me, 3% seems reasonable. Apple is a huge company and it is going to be tough to grow at a big rate for any long period of time. Although 3% might seem on the low end, we like being conservative with our numbers. If we apply 3% to our final two years, we get ~$277 billion in 2021 and $286 billion in 2022.

Although our projections stop here, if we want to keep going because we are analyzing a company that is smaller than Apple and we think they have fantastic growth prospects for the next couple of years, maybe we could extend this beyond just the next four years by using multiple growth rates for various time periods based on what we believe would happen. However, we need to be careful because the further we go out, the more uncertain our estimates are going to become.

Once we have revenue estimates, our next step would be converting net income estimates from revenue estimates. Similarly, we could find analyst estimates, extend beyond that, find a growth rate or a combination of two. However, there is a different and more logical way of doing it given that we already had revenue estimates – through net income margin. Net income margins are simply the percentage of net income divided by revenue, which both appear on the income statement. Large companies often put up consistent and similar net income margins. Take Apple as an example, for the past four years, Apple averaged about 22% for their net income margins. We are going to use 21% average here just to be a bit conservative. We multiply this net income margin of 21% by the projected revenues. Just like that, we come up with projected net income. Then we take our free cash flow rate (**FCFE/Net Income) **of 107% and multiply it by net income, we have projected free cash flow going out the next four years accordingly. Then we can enter our free cash flow numbers into our discounted cash flow formula. This completes step 2 and the future steps are much faster than the early steps.

**Required Rate of Return (WACC)**

Now it is time for us to calculate the required rate of return. Calculating the required rate of return is interesting. Since we are using free cash flow to equity, we are supposed to be discounting with a required rate of return that is based on our individual perspectives, such as investment goals, time horizon, available capital and risk tolerance. The list of items that can influence that go on and on. If you happen to know what your required rate of return is, then by all means that’s what you should use. But if you are not sure what to use as far as the required rate of return goes, typically we can determine a general required rate of return-weighted average cost of capital (WACC), by default. For someone like me who makes videos and couldn’t possibly come up with a fair required rate of return on an individual basis for different people, it makes sense for me to use weighted average cost of capital. Typically it would be the company that uses a weighted average cost of capital and apply that to the free cash flow to the firm, not to equity, but it can work this way. I have made short videos on both the weighted average cost of capital and the Capital Asset Pricing Model and those are key concepts to coming up to required rate of return. So if you are interested in learning more about them, you can find links on your channel.

This is the formula for the weighted average cost of capital:

W=weight; r=cost (required rate of return); t=tax rate

W stands for weights and the little d and the little e that represents debt and equity. Now the r is the rate we are trying to calculate for both debt and equity. We are going to run through where we get the information for each of these.

Starting with debt, generally if we are looking for the weighted average cost of debt, we should be able to find it in the company’s annual filings. If not, a crude way of computing it would be to take the company’s average annual interest expense and compare that to the company’s total debt. This only works if the company has not issued a ton of debt in this year. But in Apple’s case, we saw on their cash flow statement that their net borrowings was fairly low for this year, so this should work just fine. Taking their interest expense line item from the income statement for 2018 and compare that to their total debt (short-term debt and long-term debt off the balance sheet), we end up getting an average rate of ~3.16%. This number makes sense because this is the type of rate that Apple would be paying. However, we cannot just take this amount the way it is because in most countries, interest expense is a tax deductible item, which means the company would pay less taxes because of that interest payments. The true cost of debt is 3.16%*(1-tax rate). To calculate the tax rate, we take the income tax expense from the income statement and we divide that by income before tax, we get a calculated effective tax rate of 18.34%. If we are comparing this to the number listed in their 10k because most companies list their effective tax rate there, sure enough Apple gives us 18.3%. Therefore, Apple’s cost of debt is 3.16%*(1-18.34%) = 2.58%.

Now we need to calculate cost of equity. To do that, we are going to use the Capital Asset Pricing Model (CAPM):

**E(R) = Rf + B (Rmarket – Rf)**

Going over to Yahoo Finance, we can find it under the markets, then we go to the U.S. Treasury Bond Rates, we use the 10-year U.S. Treasury Rate of ~2.32%. This is generally accepted as the risk free rate. Plugging that into the formula here under both sections. Next, we need beta for the stock, in Yahoo Finance, we can find beta on the Statistics page for Apple, they list beta to be 0.89 there. Finally, we need the expected return in the market to finish up the calculation. This one is a little trickier because there is no real clean place to find that, so what I did is I pulled down the average returns of the S&P 500 going back as long as I could find. I broke it up by different time periods and then we can elect to use whichever one we think is most appropriate. I don’t think it would be smart to use the 15% which represents the past 10 years because we are more than 10 years away from the Great Recession. The past 10 years would be so good because it is coming off the lows after the recession. The 20-year number is the opposite of that, which was right at the high right before the tech bubble burst, so in that 20-year number, essentially what we get is the crash of the tech bubble, another rally before the Great Recession, the Great Recession and the rally since then. Hence, I personally think 10% is a fair representation. Just to make sure we are on the same page, 10% is not the expected return this year or net year. It is the average expected return over a long period of time and you can adjust if you want to. Plugging in all the components for CAPM model, we get E(R) of 9.16%.

It is quite simple to obtain the weights for both debt and equity. We take the market cap, which represents the equity and then we take the total amount of debt out there that represents the total debt. Apple has a market cap of ~$826 billion and total debt of ~$112 billion. Adding these two numbers up, we get total amount of capital in the company of about $939 billion. If we divided total debt of $112B by total capital of $939B, we get 12% of weight of debt. Similarly, equity accounts for 88% of the total capital structure. Lastly, we have our WACC of 8.4% and plug that number into the required rate of return field on our original DCF calculation.

**Finding Shares Outstanding**

If we go over to Yahoo Finance, we can see that they say it is 4.6 billion shares outstanding and this seems a bit vague. However, if we go to any company’s most recent annual or quarterly filing, in one of the first two or three pages, we can find all the way at the bottom the exact number of shares outstanding. We can simply take that, copy and paste it over into our formula. One thing to realize with this is that the financial numbers are in thousands. While this is an exact number on quarterly/annual filing, we need to remember to chop off the last three numbers here.

**Perpetual Growth Rate**

This brings up to the last point, the key data we need – our perpetual growth rate. You might also hear it is called the constant growth rate. Basically, the perpetual growth rate is the growth rate that free cash flow is going to grow at forever. My personal free cash flow perpetual growth rate is 2.5%. That’s about in line with the expected growth of the U.S. economy or most developed global economies. It is a fairly safe number to use on the conservative side. We need to use a number that is not too large. Imagine we used 5%/6%, if the global economy is growing at 3% and we used 6%, we are implying that one day this company would be larger than the global economy and that seems crazy.

**Calculate Present Value of Free Cash Flow**

Now we have all of our key data and our goal is to calculate the present value of each of our expected free cash flows. 2019 is time period 1, 2020 is time period 2 and move right down the line.

**Calculate Terminal Value**

The next step is to calculate terminal value, this is the formula for terminal value calculation:

What we do is basically we take our last time period, in our case time period 4, and then we take our final free cash flow and grow it one year by our perpetual growth rate of 2.5%. At that point, we are going to assume that everything grows at 2.5%. 1.025 times $64 billion in the final year, which is essentially time period 5’s free cash flow. Then we take this time period 5’s free cash flow and divide that by required rate of return of 8.4% minus perpetual growth rate of 2.5% (5.9%), we end up with our terminal value of ~$1.1 trillion. Technically this $1.1 trillion is the time value at the end of period 4 or the beginning of period 5. Now we have the free cash flow for each time period. Don’t forget that the terminal value and the $64 billion 2022 are the same time period and we just separated them so we can see it.

**Calculate Present Value of Forecasted Free Cash Flow & Fair value of stock**

I believe the easiest way to calculate this at this point is to come up with a discount factor. The way we can do that is we take the required rate of return, which is WACC (8.4%) in our example, and add 1 to it, then we raise it to whatever time period we are in. So if it is time period 4, you raise it to the power of 4. Hence, the discount factor in 2019 is 1.084 raised to the power of 1; 2020 is 1.084 raised to the power of 2, etc. If we keep doing this discount factor until we hit our time period 4, which is 1.084 raised to the power of 4, gives us about 1.381. We simply take the free cash flow from each time period and divide it by that discount factor and then we will get the present value as of the start of our calculation. We perform this calculation on each of our time periods. Do not forget that the terminal value gets discounted by time period 4 and all we need to do is adding up each of these discounted cash flow numbers to give us today’s value. Finally, we take today’s value and divide that by the number of shares outstanding and we get the intrinsic value of Apple stock today.

**Issues with DCF calculation**

The biggest issue with calculating DCF is the amount of assumptions that have to be made to come up with the calculation. Personally as I’ve said before, this is my favorite calculation to use. But even though there’s a ton of assumptions, I think if we are conservative throughout the whole process, it tends to lead to pretty good results. The whole calculation is very sensitive to the inputs that go into it, so we need to be very aware of that. If we were to adjust perpetual growth rate, or our required rate of return, you will see when you do this type of calculation, how much this swings the result. It might make sense to do what they call a scenario analysis or a sensitivity analysis. Sensitivity analysis is basically where you say based on our 1% move in any direction for the different variables that go into the formula, how would that impact things. I personally like to play with that once I have the excel sheet all set up, see the impact and try to pick the most conservative number.

When we pick the conservative number of company’s intrinsic value/fair value that we like, the last step would be to place our own personal margin of safety to that number. The reason that any investor, professional investors like Warren Buffett or normal investors like you and I, would want to add a margin of safety is because we understand that there were certain guesses made in this calculation. The less confident we are, the bigger the gap, and the more confident we are, the smaller the gap. Either way, I suggest not messing with the inputs too much to get the numbers we want, wait for the stock to fall to the price that you really should be buying it at to meet your personal required rate of return.