ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
2
Note: please do not request access to the current file, simply click "File" and "Create a copy" to save it in your own drive space and be able to use it and edit it freely
3
4
5
Reverse DCF Template (Watchlist)
6
7
8
Links:Option SamuraiInstallation help
9
Excel integration help articleBlog post about this template
10
11
12
Description
13
14
The idea behind the Reverse DCF model is to let you understand the implied growth rate derived from a stock valuation. In other words, by twisting the classic DCF (discounted cash flow) model, you manage to find the growth rate at which the market is pricing the company at any moment. This is priceless information, as it will let you compare the implied growth rate with your assumptions or benchmarks, such as the past EPS growth rate, the expected EPS growth rate in the future, and so on. Once you compare the implied growth rate with other relevant measurements, you can make a more informed decision. This template lets you input more than one company at the same time, a strategy that can let you run, for instance, sector analysis. Remember: if you believe the growth will be more than the implied growth, the stock is underpriced, and it's a bullish indicator. The opposite is true for the case in which you think the growth will be lower than the implied growth.
15
16
17
18
19
20
21
22
23
Instructions
24
25
1. While using the template, you'll only need to change the values in the green columns of the "Data" template (Tables A and B)
2. Choosing a stock tickers (make sure it's a valid one) will trigger our add-in to compute the current valuation information (In the “Compare” tab, you can refer to Table C, left-hand side)
3. Feed the model the Free Cash Flow (TTM). Click on the URL in the "Free cash flow TTM Source" column after choosing your ticker to retrieve this information and divide the number on the website by 1,000. If this value is not reprensetative (i.e., too volatile over the years), you may consider other solutions (e.g., take the average or median yearly value for the FCF)
4. Add the terminal growth rate in Table B. This is the rate of growth at which the companies will grow after the next 5 years (which, for simplicity, will represent our long term growth rate). For the US market, it is fair to assume a rate between 2% and 4%.
5. Add the discount rate, which experts would normally estimate between 8% and 12%.
6. Add a growth rate for years 1 to 5 for each company in the watchlist in Table A. The idea here is to play with this rate until you get an implied valuation (the last column in Table A) that is as close as possible to the current market price.
7. If you want more details on the free cash flow computation, you will be able to view the "Free Cash Flow Data" with tables E.1, E.2, and E.3 for each stock in your watchlist.
8. Table C also gives you details on the EPS growth in the past 5 years and the expected EPS growth in the next 5 years. We've also specified the implied valuation of the stock assuming these values as your implied growth rates.
9. We have also added the information on the analysts' target price from our add-in. This is useful information to compare the result of your implied growth rate. In this sense, Table C will provide you with a series of sanity checks for your analysis, just like a professional would do
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100