Intrinsic Valuation of Stocks Using Python

Numpy and Python is all you need to create a DCF template

Pendora
Pendora
May 3, 2020 · 8 min read

A project that always fascinated me was to create a program in python to create a quick DCF for myself and be able to automate my analysis of companies using simple assumptions. Through just using NumPy and Pandas, we’ll be going through a simple exercise on how to find an implied Enterprise Value. The code can be used as a template for further and more complicated analysis that you can tweak to your liking.

Discounted Cash Flow Models simply put are used to calculate the intrinsic value of a company. Based on what a company is valued at on the stock market, or in private, DCF models can be used to see whether or not a company is overvalued.

Image for post
Image for post
Photo by Elizeu Dias on Unsplash

An example of how intrinsic value works is through understanding how a coconut tree can be valued. If we calculate the value of the coconuts sold throughout the life of the tree, that is the intrinsic value. Similarly, for a business, the value of a company should be valued based on how much cash the business will generate over the life of the company. Would you pay $2,000 for a tree that could only produce $200 in coconuts? How about if it could produce $2,500, but over 10 years?

Setup and Base Model Assumptions

import numpy as np
import pandas as pd
years=['2019A', '2020F', '2021F', '2022F', '2023F', '2024F']
sales = pd.Series(index=years)
sales['2019A'] = 15
sales

The above code will allow us to create a Dataframe with the columns Year and Sales, ranging from the current year 2019 to five years later, 2024.

Image for post
Image for post
Expected Output of Sales

Since we only inputted one year of sales, NaN shows up which makes sense as the values are missing. Our next step is to populate the rest of the Sales column with sales numbers driven by a growth rate. We’ll also use a simple for loop to repeat the process of calculating the rest of the sales values.

2020 Sales is equal to 2019 multiplied by (1+Sales Growth %). The future year sales are also just the previous year sales multiplied by (1+Sales Growth %).

growth_rate = 0.1# Loop to populate the data series of sales
for year in range(1,6):
sales[year] = sales[year-1] * (1+growth_rate)

Now, the data looks a lot better to work with since most of the next amounts that we’ll be using are driven from sales.

Image for post
Image for post
Expected Output of Sales

Before we continue further, just to make sure that everyone’s on the same page, we need to have a quick finance lesson. Free Cash Flow (FCF) is the key metric that we need to calculate for a DCF, and this is how you calculate it from sales. Simply put, FCF is a proxy for cash generated from the operations of a business, less the expenditures paid out as investments.

Image for post
Image for post
Example Calculation of Free Cash Flow

Operating Income includes non-cash income and expenditures, such as depreciation & amortization expense, which means that we need to reverse the effects on the amounts. Non-cash expenses should not affect our cash generation calculation of the business, so if it subtracts from our FCF, we must add it back. To calculate up to the NOPAT step, we need the following variables. We’ll use dummy numbers, for now, so adjust accordingly to your company afterwards.

ebitda_margin = 0.20
depr_percent = 0.03
ebitda = sales * ebitda_margin
depreciation = sales * depr_percent
ebit = ebitda - depreciation
tax_rate = 0.30
tax_payment = -ebit * tax_rate
tax_payment = tax_payment.apply(lambda x: min(x,0))
nopat = ebit + tax_payment
nopat

With modelling, the thought process behind using sales to drive the other numbers is that most expenses and expenditures support the production of products, which are eventually sold. Higher sales should mean a corresponding increase in expenses. If we make expenses, or EBITDA margin (which includes the effects of expenses), driven as a % of sales, it already incorporates the increase in production. If we retrieve what NOPAT looks like, the numbers are the following:

Image for post
Image for post
Expected Output of NOPAT calculation

It makes sense because we calculated EBITDA first using the EBITDA margin of 20%, and then subtracted Depreciation & Amortization expense. After that, we calculated tax expenses using a function that only computes a tax expense if EBIT is positive. It doesn’t make sense to tax a company that doesn’t generate income.

20% of EBITDA Margins are generally pretty high, and for comparison, the Gross Margin for a Tesla vehicle is around 20%. EBITDA Margin for Tesla would be lower at around 12–15%. Remember that there are two types of businesses, (1) Volume Driven Businesses and (2) Price Driven Businesses. Volume Driven Businesses will generally have a lower profitability margin, but much higher volume, Price Driven Businesses are the opposite.

Next, the harder thing to calculate is the Net Working Capital (NWC). This amount is the difference between a company’s current assets and its current liabilities. We only care about the Change in Net Working Capital, because an increase means that we received assets for which we need to pay out in the future. An increase in Net Working Capital from one year to the next represents a decrease in cash. For the sake of simplicity and brevity, we’ll calculate NWC as a percentage of sales too.

nwc_percent = 0.24
nwc = sales * nwc_percent
change_in_nwc = nwc.shift(1) - nwc
capex_percent = depr_percent
capex = -(sales * capex_percent)

Notice here that we are also setting the capital expenditures percentage to the depreciation expense percentage. The thought process here is that eventually, the company will have its capital expenditures equal to its depreciation expense in the long-run. This makes sense because if one of these amounts were vastly different since a DCF is calculated for the infinite future, your assumption for one of these amounts will skew the company value. These amounts are also highly linked since depreciation expense primarily accrues on the capital assets that you purchase as CAPEX.

We can finally calculate the Free Cash Flow to the Firm amounts using the code:

free_cash_flow = nopat + depreciation + capex + change_in_nwc
free_cash_flow

Company Value and DCF Calculation

The next part requires an understanding of the time value of money, and the cost of capital. To quickly summarize, finance is all about the sum of future cash flows, and $1 today is worth much more than $1 ten years in the future. For a deeper analysis, please check out a detailed explanation of these calculations:

We are using a 5-year DCF with 5 years of FCF forecasted. A DCF has a near-future component and a long-term future component. The near-future part is the 5 years that we have calculated above, and the long-term part is perpetuity calculated using the 5th forecasted year’s FCF as a base into the future. This larger lump sum is called the Terminal Value. A company grows into perpetuity at a specific growth rate, lower than the domiciled country GDP growth rate. To compute this calculation, this means that we have to take the present value of the 5 FCF amounts, as well as the present value of the terminal value.

cost_of_capital = 0.10
terminal_growth = 0.02
terminal_value = ((free_cash_flow[-1] * (1 + terminal_growth))/
(cost_of_capital - terminal_growth))
discount_factors = [(1 / (1 + cost_of_capital)) ** i for i in range (1,6)]
dcf_value = (sum(free_cash_flow[1:]*discount_factors) +
terminal_value * discount_factors[-1])
dcf_value

Terminal value is calculated using the last year’s FCF forecast after applying the terminal growth rate of 2%, essentially giving us the 6th year’s FCF. We apply the discount factor, a number ranging from 0 to 1, to each of the numbers, and summing the 5 year PV FCF amounts with the PV of the terminal value to give us a DCF value of $25.8746 million.

Our last steps include outputting to Excel and visualizing all of the lines that lead us to the final answer. We want the line items as the rows, and the years as the columns, so we need to set the line items as the index. We’ll also round all of the values to 2 decimal places to reduce clutter.

# Exporting the Data to Excel
output = pd.DataFrame([sales, ebit, tax_payment, nopat,
depreciation, capex, change_in_nwc,
free_cash_flow],
index=["Sales", "EBIT", "Tax Expense",
"NOPAT", "D&A Expense",
"Capital Expenditures",
"Increase in NWC",
"Free Cash Flow"]).round(2)
output.to_excel('Python DCF Model.xlsx')
output

Doing so allows us to easily double-check numbers and formulas, as well as keep an eye on how things are calculated.

Image for post
Image for post
Expected Final Output to Excel

You’ve now completed your first DCF in Python, and learned about the finance behind things along the way! Things aren’t done though, there’s much more we can do in the future.

Further Steps

In the future, we can improve the project to back solve for margins and expense percentages. If we input the current value of a business, with this project we can quickly backsolve to see what the market estimates the margins and growth of the business are. From there, we can easily visualize whether or not the business is overvalued, in our own opinion.

For example, if we back solve the current market value of Tesla, and we see that the sales growth that the market estimates are around 30%, and margins are 10%, we can see that the assumptions are pretty aggressive, and could symbolize an overvaluation.

Also, this DCF model is a very high level, as it should only be a template for further improvement. Different businesses should be modeled differently. To be more granular with the sales growth assumptions, for a business like Tesla, we should be looking to forecast the number of Model X, S, Y, and 3 cars sold, as well as their prices. From there, we have automotive revenues, but Tesla is trying to be much more than just a car company. Their services business is growing, and the solar business is supposed to be a huge growth driver for the business in the future. If we project granularly the different lines of business, and its corresponding costs, we can get a much more detailed valuation.

Other Python Finance Projects:

The Startup

Medium's largest active publication, followed by +752K people. Follow to join our community.

Pendora

Written by

Pendora

Investment banker, global citizen interested in the pursuit and sharing of knowledge. Inquiries to pendorapubs@gmail.com

The Startup

Medium's largest active publication, followed by +752K people. Follow to join our community.

Pendora

Written by

Pendora

Investment banker, global citizen interested in the pursuit and sharing of knowledge. Inquiries to pendorapubs@gmail.com

The Startup

Medium's largest active publication, followed by +752K people. Follow to join our community.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store