Skip to content

Instantly share code, notes, and snippets.

@zachlim98

zachlim98/blog.md Secret

Created Jan 7, 2021
Embed
What would you like to do?
title categories
Using a Monte Carlo simulation to determine the Fair Value of a stock
coding
trading

Introduction

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.

https://gist.github.com/2955c380be158c4e4198553f6de76a84

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.

DCF Valuation

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

https://gist.github.com/64bcbc6fa2a889093905382df1c518e4

2015 2016 2017 2018 2019
revenue 3991000000 4272000000 5253000000 6475000000 6731000000
netincome -660000000 -497000000 -33000000 337000000 341000000
freecash -322000000 13000000 -101000000 -129000000 276000000
incomeMargin -6.04697 -8.59557 -159.182 19.2136 19.739
FCFr -12.3944 328.615 -52.0099 -50.1938 24.3877

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.

https://gist.github.com/503914c0c98e32f9d243a0000f292b53

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).

image

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.

https://gist.github.com/2d24dba53e5d4ffc2ef0b909ea60ce82

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.

https://gist.github.com/35db29204eba3932c93d88e138f48ca0

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: image

We go through the list of projected cash flows and apply the discount formula to them

https://gist.github.com/39353a3eb5b74ea186031b86f7114f85

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.

https://gist.github.com/dcab59b94a093138b650065691722018

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.

https://gist.github.com/209051d80b1f59c190b682ce017e68b7

Median Fair Value: 15.17

output_15_1

Conclusion

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment