One of the most popular ways to value a company is using a model known as the Discounted Cash Flow Model. This model attempts to find the intrinsic value of a company by projecting its future cash flows and then discounting it. This is typically done using an excel spreadsheet and is relatively simple (just requiring a little bit of math and some manual work to find the financial information of a company). In this article, we’re going to explore how we can easily automate this in Python (both the calculation as well as the pulling of financial data!), adding in a monte carlo simulation to try to get a better range of intrinsic value calculations.
Pulling Financial Information
The first step is to pull financial data. We are using the FundamentalAnalysis package which allows us to access data from FinancialModelingPrep (FMP). In order to use FMP, you need to get a free API key, which you can get here. The first step is really simple — all we’re doing is downloading the Cash Flow statement, the Income Statement, and the Balance Sheet via FMP’s API.
FMP returns the statements in descending order which we don’t want. Hence, there’s a need to use the
.iloc[:,::-1] to flip it around.
There are three main parts to doing a DCF valuation.
1. Project Future Cash Flow.
There are many ways to do this and the most direct would be to just find the mean growth of cash flow and project it forward. However, another way to do it would be to project revenue first. From revenue, we can then project net income, and finally project free cash flow. To do this, we would need revenue/income ratios (i.e. income margin) and also free cash flow/revenue ratios. Being conservative, we then take the lowest positive number of the income margin ratios and free cash flow/revenue ratios.
Below, we first gather the last 5 years of data that we have, and then derive our estimates for revenue change, income margin, and fcf/revenue. As you can see, we use
.min() to be conservative. Since we want to do a Monte Carlo simulation, we need both the mean and the standard deviation for our revenue growth. For our later simulation, we will need the risk free rate of return. This is usually assumed to be the returns of the 10-year treasury bonds and we use
yfinance to obtain that
We continue with our projections of future cash flows below. Using the estimations we got above, we plug these into a 5-period
for loop to obtain our forward estimations. We use
np.random.normal() with the mean and standard deviation of the revenue change obtained above. The code is then plugged into a parent
for loop in order to run the simulation.
2. Discount Projected Cash Flow
We now have our projected cash flows but need to discount them to find the Present Value (PV). This is because $100 today will not have the same value as $100 in the future. Hence, we need to discount the future value of the cash (i.e. reduce its value to what it would be worth today). Again, there are many ways to do this but one way to get the discount rate is to use the Weighted Average Cost of Capital (WACC).
The equation seems really scary at first but its really basic math. Essentially, we find the cost of the firm’s debt and the cost of the firm’s equity and then weigh them (hence the word “weighted”). If you want to go into greater depth as to how this is done, this video is particularly helpful. The code is basically a translation of the equation. We find the past 5 years of Wacc in order to calculate the standard deviation.
We also need to find “Terminal Value” which is the value of the company beyond the 5 years that we are projecting. This requires an estimation of the perpetual growth rate of the company. We typically use 2.5% because this is lower than the growth rate of the economy (and it cannot be higher than the economy unless we think that one day this company will be LARGER than the entire economy)
For the discount value, we also use
np.random.normal(), setting the mean as the current WACC and the standard deviation as the std dev. of the last 5 years of WACC.
After obtaining the Terminal Value and calculating the Discount Value, we now have to apply the discount to all the projected cash flows. The discount equation is as follow:
We go through the list of projected cash flows and apply the discount formula to them
3. Calculating Net Present Value and Fair Value
And… we’re almost done! All that’s left to do is calculate the Net Present Value by summing up the discounted cash flows. And then dividing by the total number of shares issued by the company. We then append the calculated Fair value to a list so that we can keep track of all the calculated FVs in our simulation.
With that, we have our simulated output of Fair Values! We can then work with these outputs by, for instance, plotting them in a histogram and determining the median Fair Value.
Median Fair Value: 15.17
Thanks for reading! This was a simple guide to how one can use a Monte Carlo simulation in order to ascertain the range of Fair Value when performing stock valuation using a Discounted Cash Flow model.
Originally published at zachlim98.github.io/me.