Free Discounted Cash Flow (DCF) Model Calculator | Google Sheets

  • By: Walter Dunphy ACCA
  • Date: May 14, 2023
  • Time to read: 3 min.

The trickiest part of investing is trying to assess whether a potential investment is currently over or undervalued at its current market price.

One of the most popular methods of valuing a company is by discounting all its expected future cash flows back to today’s terms using an appropriate discount rate. This method is commonly known as the Discounted Cash Flow valuation model.

In this blog post, we will take you through some of the pros and cons of using the DCF method; and also give you access to our very own DCF calculator on Google Sheets, which you download for free for your own use.

Download your own Discounted Cash Flow Valuation Model Calculator on Google Sheets

Along with the calculator, we have supplied detailed instructions (including a video) on where you can the required inputs for the calculation, such as; the TTM Free Cash Flow, TTM EBITDA, Net Debt, and how to calculate the FCF, & EBITDA growth rates.

Another required input is the company’s Weighted Average Cost of Capital (WACC). Many online resources readily calculate the WACC of most public companies, which you can use for this calculation, but if you want to calculate the WACC yourself, we also have another more detailed calculator that you can download.

There are several different ways you can tweak the DCF calculation, the most common technique is to discount the expected cash flows over the next 5-10 years back to today’s value using the WACC and to work out the terminal value of the following years by taking the final years cash flow expectation and dividing it by the terminal growth rate (usually using growth equal to the expectations for long term inflation).

For our model, we take a slightly different approach to calculating the terminal value by forecasting the EBITDA over 10 years and then working out the terminal value as a multiple of the EBITDA in year 10. The multiple you choose can either be the current EV/EBITDA multiple of the company in question, or you could choose the multiple of a competitor or the industry average.

Is the Discounted Free Cash Flow (DCF) Model reliable at valuing companies?

The DCF valuation tool is fantastic, but we must also be aware of its limitations. Firstly, the DCF tool will not be suitable for startups that have not yet started generating positive free cash flows and profit.

Another very important weakness to be aware of is, the intrinsic value that your calculation returns is heavily dependent on the assumptions you make on variables such as the WACC, future growth rate of free cash flows, and the multiple used to work out the terminal value.

To negate this weakness, it is prudent to do some sensitivity analysis by changing the variable inputs to your calculation and seeing how this changes the result. For example, if I increase the WACC by 1%, does this change the company valuation from being under to over valued?

Secondly, it is always wise to use a couple of different valuation models to sense check your figures. Other valuation models you might use, could include, Gordon’s Growth Model, Price to Sales ratios, Price to Book ratios, and EV/EBITDA ratios.

Using multiple valuation models can help you get comfortable in your analysis, especially if they all point towards the same price indication.

Disclaimer: this blog post is for informational and educational purpose only and should not be construed as financial advice.

Previous Post

The Only WACC Calculator You Will Need – Free Download

Next Post

Peter Lynch’s Fair Value Model Calculator – Free Download

%d bloggers like this: