Tab #13: Discounted Cash Flows (DCFs) | Terminal Values (TVs)

Discounted cash flow (DCF) valuation follows the premise that the value of a company (aka its intrinsic value) can be derived from the present value (PV) of its projected free cash flow (FCF). The outputs of a DCF model provide investors with the company's estimated intrinsic value, which can then be compared to its current market price to determine its valuation. This tab lets you select a discount rate method, lets you select between multiple DCF formula input assumptions, shows how FCF is forecasted, and shows the output calculations and scenarios for the DCF model. This tab also includes a terminal value section, where you can calculate four different ways of estimating the terminal value of a company, which is required in the DCF calculation and applies after the end of the FCF forecast period. More specifics on this sheet are described below:

1. DCF Model Description
   • The top of this sheet features a collapsible section, which describes the premise of the DCF valuation model, the assumptions the model comes with, the user defined inputs, and the DCF formula.
2. Discount Rate Method
   • This section lets you see all of the possible discount rate options. The options are your personal required rate of return (Personal RRR), the weighted average cost of capital (WACC), the capital asset pricing model (CAPM), and a risk-free rate option between the 10-year risk-free rate (10-Yr RF Rate), the 3-month Treasury bill rate (3-Mo T-Bill Rate), or the long term average risk-free rate (LT Avg RF Rate).
     • The discount rate you select here depends on the DCF valuation approach you're following. If you were following Warren Buffett's approach, you'd use one of the 3 risk-free rates as the discount rate. If you were following a traditional finance valuation approach, you'd likely use the WACC or the CAPM. If you want to simplify things and have flexibility in your model, then use the personal required rate of return, as it should reflect the annualized rate of return you're expecting from purchasing an undervalued security.
3. DCF Formula Inputs
   • This is where you select all of the inputs that go into the DCF formula calculation The 5 inputs are described below:
     • Calendarization & Mid-Year Convention: This is a yes/no dropdown option. If "yes" is selected, you must input a date of analysis. Calendarization converts irregular cash flows into an annualized cash flow stream. Mid-year convention is used to account for the time value of money, and assumes that cash flows received at the beginning of the year are received halfway through the year, and that cash flows received at the end of the year are received at the end of the next year. Therefore, an equal distribution of cash flows is assumed throughout the year.
     • FCF Forecast Period (Years): This selection represents the number of years you will forecast FCF to grow. The number of years you select here (up to 10 years) will depend on your confidence in the company's FCF forecast numbers, as the further out your forecast, the more estimation error you're subject to. Traditional finance theory models usually forecast FCF for 5 years, whereas value investors like Warren Buffet may opt to forecast FCF for 10 years. However, this really depends on the company, the market, the company's industry, and your confidence in your FCF forecast.
     • FCF Forecast Method: This is where you select between either FCFF or Owners Earnings. Selecting "FCFF" will reference the FCFF you should've already forecasted in the FCFF | FA | NWC sheet. Selecting "Owners Earnings" will reference the final owners earnings number from the BUFFETT OE sheet, and then provide you with 3 input options for conservative, base, and optimistic case FCF scenarios, where you can input in your assumptions for annual FCF growth. Selecting either method will also affect the "DCF Formula Outputs" section further below in the sheet, and what's hidden there (in gray).
     • Discount Rate Method: This references the "Discount Rate Method" section discussed above, and just asks you to select one of the discount rate options for the model to use.
     • Terminal Value Method: This represents the estimated value of the investment beyond the FCF forecast period, which will grow into perpetuity. The calculations for these methodologies are found at the bottom of this sheet, as discussed below.
4. DCF Formula Outputs
   • This section shows how enterprise value is converted into equity value, for FCFF DCF valuations, and how net present value is calculated for Owners Earnings DCF valuations. In either case, the intrinsic share prices and buy prices are also provided, which will tell you whether the conservative, base, and optimistic cases are undervalued or not. You'll also be presented with two sensitivity tables where you can select between the different growth scenarios, and see associated charts below (Google Sheets version only has one simplified sensitivity table for margin of safety). Comparison charts between market value and intrinsic value, and between market value and buy prices are also presented. Lastly, you'll see a bar chart that visualizes the growth of FCF over the forecast period, amongst the 3 different FCF scenarios.
5. Terminal Value Methods
   • At the bottom of this sheet, you'll find four different methods of estimating terminal value. Generally speaking, the two exit multiple methods are applicable if you're following a traditional finance valuation approach, otherwise decide between the no-growth perpetuity method and Gordon Growth method for estimating the terminal value of a company. The Gordon Growth method is the only method where you can input a terminal value growth rate, which is why there's also a small warnings subsection to ensure you're not over-estimating terminal growth.