A Discounted Cash Flow (DCF) analysis is one of the most important tools an investor can use to determine the intrinsic value of a stock. This guide will teach you how to get and use a free DCF template in Excel. We'll also review how to use the template to make informed investment decisions. Let's get started!
What is the DCF template, and what is it used for?
The DCF template is an Excel spreadsheet that allows you to input data and perform calculations to determine the intrinsic value of a stock. The template uses the Discounted Cash Flow (DCF) method, which discounts future cash flows back to present value.
By simply changing the company along with different assumptions, you can quickly get a good idea of what that company is worth. This guide will teach you how to get and use a free DCF template in Excel.
Downloading the free DCF template on Excel
Once you have downloaded the template, open it in Excel and familiarize yourself with the input cells. The input cells are located across the "Company Comparison", "Statements Model", and "DCF". You can spot them by the yellow fill color applied to them.
On the "Statements Model" tab, you can see how at the top, you have the ability to change fields like the company ticker, estimated tax rate, and year of analysis. By changing these cells, you will notice how the rest of the spreadsheet will provide you with the data, such as historical and real-time financials, as well as any automatic calculations performed on this data.
*Note that in order to use the template and automatically get all the data, you need to get a Wisesheets account which you can get for free here. More on how to use the DCF template below.
How to use the DCF template to calculate a company's value?
Now that you have downloaded and opened the DCF template let's review how to use it.
The first thing you need to do is create a free Wisesheets account. After you have to install the Wisesheets add-in in Excel and log in with the account, you have created.
If you skip this step, you won't be able to automatically retrieve the template's stock data, but you can enter it manually if you'd like.
Once you are fully set up, the next step is to make any changes you'd like across the various tabs on the input cells, which are colored in yellow.
The most important parts to change are:
- The ticker
- Current year
- Estimated tax rate
- Discount rate
- Terminal growth rate
- Risk-free rate
- Statements assumptions like revenue growth etc.
With all these inputs complete, the company's intrinsic value will be computed and available in cell M19 of the DCF tab. Remember that this number will change depending on the assumptions you enter in your model.
If you are wondering how this value can be computed, the assumptions you enter provide calculations for the expected future cash flow of the company across 5 years. These cash flows are then discounted to the current value using the discount rate you entered.
Things to keep in mind when using the DCF template
The forecast rates you use on the "Statement Model" tab in terms of the revenue growth, gross profit percentage, inventory, depreciation as a percentage of revenue, etc., play a significant role in the final value of the company. Therefore, make sure to use reasonable assumptions to keep your model realistic.
As Warren Buffet says, "it's better to be approximately right than precisely wrong."
The terminal value is the value you believe the company will have when you sell the stock. There are many ways to calculate the expected terminal value of a stock. The DCF template uses the perpetual growth method, where the final free cash flow is expected to grow by the terminal growth rate and brought back to the present using the discount rate.
You can adjust the template to your preference and change the terminal value calculation as you wish. For more methods on stock valuation, check out this post.
The different tabs in the DCF template
The Excel DCF template has different tabs with a unique purpose. Here is the breakdown of each one.
This tab is designed to be able to quickly compare similar companies across various key metrics such as EPS, Dividend yield, PE ratio, etc., which you can then reference when you make your assumptions.
This is the most essential part of the model. Here is where you select the company you are looking to do the discount cash flow and enter your assumptions that will dictate the intrinsic value of the stock.
This is where you can see how your assumptions affect the projected free cash flow as well as change essential assumptions like the discount rate and terminal growth value. In the end, you will see the implied price per share in cell M19 and the potential upside based on the company's current stock price.
Discounted cash flow is a powerful tool that can be used to value a company. The DCF template provided by Wisesheets makes it easy to get started with DCF and understand how it works. Then, with a few clicks, you can change assumptions and see how they affect the intrinsic value of a stock. Finally, try it out yourself and see how it can help you make better investment decisions.
To your investing success!