Building an Intrinsic Value Calculator With Python

Photo by Markus Spiske on Unsplash

The following passages shortly describe how to build a Python program that can calculate the fair value of a company, using data from Yahoo Finance. Before jumping into the Python technicalities, it makes sense to have a quick look at the factors that impact the fair value of a company.

I. Theoretical Background

Financial theory posits that the fair value of a firm is equal to the sum of all future cash flows generated by the business, discounted using a risk-adjusted rate. In other words the current value of any company is equal to the cash that the company will generate, adjusted to account for the risk-profile of that company. This essentially boils down to the following equation:

where PV is the present value of the company, FCF are the free cash flows generated in year i, and r is the risk-adjusted discount rate. As it gets difficult to forecast cash flows with any reasonable degree of accuracy for more than 5 years into the future, it makes sense to calculate a terminal company value after this time span. Here, we can treat the company as a growing perpetuity, which can be valued with the following formula:

where FCF are the free cash flows generated by the company in year 6, r is the discount rate, and g is the long-term growth rate of the company. This terminal value of the company also needs to be discounted subsequently to get its present value.

II. Implications of Financial Theory

To get a general structure for the Python program, we can basically just take the PV formula from above and split it into its main components.

II.1 Free Cash Flows

The free cash flows generated by the company in year i are usually calculated by taking the cash flows generated by the firm’s operations and deducting capital expenditures. The resulting number is the amount of cash that debt and equity holders of the business could take out of the company in year i. Forecasting these cash flows can get pretty complicated and you usually need to build extensive Excel models to get to a reasonable estimate for these values. It is possible to get around this by using several (over)simplifying assumptions, which are outlined in a bit.

II.2 Discount Rate

The second major component that is needed to calculate the enterprise value of a company is an adequate risk-adjusted discount rate. The appropriate rate to use here is the weighted average cost of capital (WACC), which is the average cost of capital to the firm (that is for both debt and equity financing combined). The formula looks as follows:

where E is the market value of the company’s equity, D is the value of the company’s debt, r denotes the return required by equity and debt holders, respectively, and tau denotes the company’s tax rate.

II.3 Simplifying Assumptions

As mentioned earlier, getting these two major components can take weeks of calculations and model building. As this is more of a sparing exercise, rather than a full-blown quant project, several simplifying assumptions are made to keep the complexity of the program to a reasonable level.

Firstly, the task of obtaining future free cash flows can be simplified by assuming the company earnings before interest and taxes (EBIT) are an adequate proxy for free cash flows generated by a company. This is reasonable especially for companies in later stages that no longer have large growth CapEx but rather maintenance CapEx that is approximately on the level of the company’s depreciation of fixed assets.

Secondly, we will assume that the average revenue growth rate and the average EBIT margin of the company in the past three years will continue to apply for the next five years. Using this approach, generating EBIT forecasts is pretty straightforward.

Finally, to calculate the WACC, we will assume that company debt is adequately priced by the market, making the yield on outstanding debt the return required by debt holders. Calculating the required equity return is a bit more tricky. Here the capital asset pricing model (CAPM) is used to get to the equity rate of return:

where the equity beta is essentially the co-movement of the stock return and the general market return, which can be proxied using the S&P500. The equity beta is calculated as the covariance of stock and market returns divided by the variance of the market returns.

III. Implementation with Python

I used Apple Inc. (AAPL) as the company being valued in the subsequent code, however, the program works with the ticker of any large publicly traded company.

For the program to work, several modules need to be imported to pull financial information from Yahoo Finance and store it appropriately.

from bs4 import BeautifulSoup as bs
import pandas as pd
import requests
import pandas_datareader as dr
import datetime

As discussed subsequently, certain values that are tedious to obtain or simply not available via Yahoo Finance are hard coded initially.

company_ticker = 'AAPL'
timespan = 100 #timespan for the equity beta calculation
market_risk_premium = 0.0523
long_term_growth = 0.01
debt_return = 0.01
tax_rate = 0.3

III.1 Pulling Financial Data from Yahoo Finance

Yahoo Finance has an API that provides a lot of information, however, web-scraping is needed on top to get the specific income statement and balance sheet financials. For this, BeautifulSoup and the requests module are great tools.

income_statement_url = '<https://finance.yahoo.com/quote/>' + company_ticker + '/financials?p=' + company_ticker
income_statement_html = requests.get(income_statement_url)
income_statement_soup = bs(income_statement_html.text, 'html.parser')

After creating the BeautifulSoup object, we have to navigate the website to get the correct pieces of information and store them in one pandas dataframe that contains the required information. To that end, we first create a dataframe with the correct dates as column names.

income_statement_table = income_statement_soup.find('div', class_='M(0) Whs(n) BdEnd Bdc($seperatorColor) D(itb)')
income_statement_header = income_statement_table.find('div', class_='D(tbr) C($primaryColor)')
header_lst = [] 
for i in income_statement_header.find_all('div'):
    if len(i) != 0:
       header_lst.append(i.text)
header_lst = header_lst[::-1]
del header_lst[len(header_lst)-1]
header_lst.insert(0,'Breakdown')
income_statement_df = pd.DataFrame(columns = header_lst)

After that, the revenue and EBIT figures provided by Yahoo Finance can be appended for the respective year.

revenue_row = income_statement_table.find('div', class_='D(tbr) fi-row Bgc($hoverBgColor):h')
revenue_lst = [] 
for i in revenue_row.find_all('div', attrs={'data-test':'fin-col'}):
    i = i.text
    i = i.replace(",","")
    revenue_lst.append(int(i))
revenue_lst = revenue_lst[::-1]
revenue_lst.insert(0,'Total Revenue')
income_statement_df.loc[0] = revenue_lst
EBIT_row = income_statement_table.find('div', attrs={'title':'EBIT'}).parent.parent
EBIT_lst = [] 
for i in EBIT_row.find_all('div', attrs={'data-test':'fin-col'}):
    i = i.text
    i = i.replace(",","")
    EBIT_lst.append(int(i))
EBIT_lst = EBIT_lst[::-1]
EBIT_lst.insert(0,'EBIT')
income_statement_df.loc[1] = EBIT_lst
income_statement_df = income_statement_df.drop('ttm', axis=1)

When exporting the Pandas dataframe to a csv-file, we get the following:

III.2 Forecasting Revenues and EBIT

As mentioned earlier, revenues and EBIT are forecasted for the coming five years by extrapolating from past sales and EBIT data. To do this, we calculate the revenue compound annual growth rate (CAGR) and EBIT margin for the past years.

latest_rev = income_statement_df.iloc[0,len(income_statement_df.columns)-1]
earliest_rev = income_statement_df.iloc[0,1]
rev_CAGR = (latest_rev/earliest_rev)**(float(1/(len(income_statement_df.columns)-2)))-1
EBIT_margin_lst = []
for year in range(1,len(income_statement_df.columns)):
    EBIT_margin = income_statement_df.iloc[1,year]/income_statement_df.iloc[0,year]
    EBIT_margin_lst.append(EBIT_margin)
avg_EBIT_margin = sum(EBIT_margin_lst)/len(EBIT_margin_lst)

Using these figures, it is possible to forecast revenues and EBIT.

forecast_df = pd.DataFrame(columns=['Year ' + str(i) for i in range(1,7)])
rev_forecast_lst = []
for i in range(1,7):
    if i != 6:
        rev_forecast = latest_rev*(1+rev_CAGR)**i
    else:
        rev_forecast = latest_rev*(1+rev_CAGR)**(i-1)*(1+long_term_growth)
    rev_forecast_lst.append(int(rev_forecast))
forecast_df.loc[0] = rev_forecast_lst
EBIT_forecast_lst = []
for i in range(0,6):
    EBIT_forecast = rev_forecast_lst[i]*avg_EBIT_margin
    EBIT_forecast_lst.append(int(EBIT_forecast))
forecast_df.loc[1] = EBIT_forecast_lst

The resulting csv file of the forecast dataframe looks as follows, where the rows indexed as 0 and 1 provide the forecasted revenue and EBIT figures, respectively.

III.3 Calculating the WACC

To calculate the WACC, we first need to find the rate of return demanded by equity holders. This requires finding the risk-free rate (proxied here by the yield of the 10-year US Treasury note) and determining the market risk premium.

Determining the risk-free rate is relatively straightforward, as we only need the current yield on the 10-year US treasury note.

current_date = datetime.date.today()
past_date = current_date-datetime.timedelta(days=timespan)
risk_free_rate_df = dr.DataReader('^TNX', 'yahoo', past_date, current_date) 
risk_free_rate_float = (risk_free_rate_df.iloc[len(risk_free_rate_df)-1,5])/100

Instead of calculating an adequate estimate of the market risk premium myself, I used the rate provided by NYU finance professor Aswath Damodaran, who also publishes a lot of other very interesting financial statistics. Currently he estimates the market premium to be 5.23% annually for the US market.

Calculating the equity beta of the company requires several inputs. First, we have to access the API of Yahoo Finance to get price information on the company stock and the market. These prices are stored in a pandas dataframe and the values are used to calculate daily returns for both sets of prices. Subsequently, the covariance of the stock and the market returns is divided by the variance of the market returns.

price_information_df = pd.DataFrame(columns=['Stock Prices', 'Market Prices'])
stock_price_df = dr.DataReader(company_ticker, 'yahoo', past_date, current_date) 
price_information_df['Stock Prices'] = stock_price_df['Adj Close']
market_price_df = dr.DataReader('^GSPC', 'yahoo', past_date, current_date)
price_information_df['Market Prices'] = market_price_df['Adj Close']
returns_information_df = pd.DataFrame(columns =['Stock Returns', 'Market Returns'])
stock_return_lst = []
for i in range(1,len(price_information_df)):
    open_price = price_information_df.iloc[i-1,0]
    close_price = price_information_df.iloc[i,0]
    stock_return = (close_price-open_price)/open_price
    stock_return_lst.append(stock_return)
returns_information_df['Stock Returns'] = stock_return_lst
market_return_lst = []
for i in range(1,len(price_information_df)):
    open_price = price_information_df.iloc[i-1,1]
    close_price = price_information_df.iloc[i,1]
    market_return = (close_price-open_price)/open_price
    market_return_lst.append(market_return)
returns_information_df['Market Returns'] = market_return_lst
covariance_df = returns_information_df.cov()
covariance_float = covariance_df.iloc[1,0]
variance_df = returns_information_df.var()
market_variance_float = variance_df.iloc[1]
equity_beta = covariance_float/market_variance_float
equity_return = risk_free_rate_float+equity_beta*(market_risk_premium)

Pulling bond information from the internet is more difficult than it is for equities, which is why we can simply look up bond yields and hardcode them manually. Apple is AA+ rated by S&P, which is why yields on the company’s outstanding bonds are relatively low, at around 1%.

Finally, to calculate the WACC, we need pull the amount of company net debt outstanding and the market value of the firm’s equity from Yahoo Finance.

balance_sheet_url = '<https://finance.yahoo.com/quote/>' + company_ticker + '/balance-sheet?p=' + company_ticker
balance_sheet_html = requests.get(balance_sheet_url)
balance_sheet_soup = bs(balance_sheet_html.text, 'html.parser')
balance_sheet_table = balance_sheet_soup.find('div', class_='D(tbrg)')
net_debt_lst = []
net_debt_row = balance_sheet_table.find('div', attrs={'title':'Net Debt'}).parent.parent
for value in net_debt_row.find_all('div'):
    value = value.text
    value = value.replace(',','')
    net_debt_lst.append(value)
net_debt_int = int(net_debt_lst[3])
market_cap_url = '<https://finance.yahoo.com/quote/>' + company_ticker + '?p=' + company_ticker
market_cap_html = requests.get(market_cap_url)
market_cap_soup = bs(market_cap_html.text, 'html.parser')
market_cap_int = 0
market_cap_row = market_cap_soup.find('td', attrs={'data-test':'MARKET_CAP-value'})
market_cap_str = market_cap_row.text
market_cap_lst = market_cap_str.split('.')
if market_cap_str[len(market_cap_str)-1] == 'T':
    market_cap_length = len(market_cap_lst[1])-1
    market_cap_lst[1] = market_cap_lst[1].replace('T',(9-market_cap_length)*'0')
    market_cap_int = int(''.join(market_cap_lst))
if market_cap_str[len(market_cap_str)-1] == 'B':
    market_cap_length = len(market_cap_lst[1])-1
    market_cap_lst[1] = market_cap_lst[1].replace('B',(6-market_cap_length)*'0')
    market_cap_int = int(''.join(market_cap_lst))
company_value = market_cap_int + net_debt_int
WACC = market_cap_int/company_value * equity_return + net_debt_int/company_value * debt_return * (1-tax_rate)

III.4 Discounting the Cash Flows

Finally, we need to discount all the cash flows to get the present value and the overall enterprise value of the company. First, the forecasted EBIT (i.e. FCF) for the next five years are discounted and the present value of the terminal value of the company is added to this amount.

As the resulting value gives us the enterprise value of the company, which is the value to both equity and debt holders, we need to deduct the company’s net debt to arrive at the equity value of the firm.

discounted_EBIT_lst = []
for year in range(0,5):
    discounted_EBIT = forecast_df.iloc[1,year]/(1+WACC)**(year+1)
    discounted_EBIT_lst.append(int(discounted_EBIT))
terminal_value = forecast_df.iloc[1,5]/(WACC-long_term_growth)
PV_terminal_value = int(terminal_value/(1+WACC)**5)
enterprise_value = sum(discounted_EBIT_lst)+PV_terminal_value
equity_value = enterprise_value-net_debt_int

IV. Model Performance

Just to get a feeling for whether or not the program is performing somewhat accurately, I calculated intrinsic values for large US stocks and compared the valuations with the market caps of the respective companies. As stock in the companies chosen is heavily traded, great discrepancies between intrinsic and market value are unlikely, which is why market prices should provide a good indication of the actual value of the companies.

Model performance compared to company market cap, all values in thousands.

As can be seen, the model does a reasonably good job for certain companies, while its quite off for others. The two main issues at play here are firstly that the somewhat questionable assumptions on which the model is built are simply unreasonable for certain companies. Secondly, the model output is highly sensitive to the WACC input data due to the approach used to calculate the terminal company value. A more appropriate approach would be to apply an EBIT or EBITDA multiple based on comparable companies, as this entails a lower sensitivity of the program output to the WACC and the terminal growth rate.

V. Concluding Remarks

Obviously, the program described above is a simplified approach to valuation and is unlikely to be accurate if the underlying assumptions are violated. Still, the tool might be useful in determining the aggregate intrinsic value of a large number of companies.

V.1 Disclaimer

The code above is only a side-project and should not be used to make investment decisions. Further, the information pulled from Yahoo Finance should not be used for any commercial purposes. As mentioned earlier, the program is simply a fun exercise of applying Python programming to finance and I recommend against employing web-scraping if resulting information is to be used for commercial purposes.

V.2 Final Code

The final code looks as follows. In order to run it, you will have to install Python (I use version 3.7.8.) and pip install BeautifulSoup, pandas, requests, and pandas-datareader.

from bs4 import BeautifulSoup as bs
import pandas as pd
import requests
import pandas_datareader as dr
import datetime
'''---------- // Hard-coded variables below // ----------'''

company_ticker = 'AAPL'
timespan = 100 #timespan for the equity beta calculation
market_risk_premium = 0.0523
long_term_growth = 0.01
debt_return = 0.01
tax_rate = 0.3
'''---------- // Hard-coded variables above // ----------'''
'''----- // I. Financial Information from Yahoo Finance // -----'''

income_statement_url = '<https://finance.yahoo.com/quote/>' + company_ticker + '/financials?p=' + company_ticker

income_statement_html = requests.get(income_statement_url)
income_statement_soup = bs(income_statement_html.text, 'html.parser')

income_statement_table = income_statement_soup.find('div', class_='M(0) Whs(n) BdEnd Bdc($seperatorColor) D(itb)')
income_statement_header = income_statement_table.find('div', class_='D(tbr) C($primaryColor)')
header_lst = [] 
for i in income_statement_header.find_all('div'):
    if len(i) != 0:
       header_lst.append(i.text)
header_lst = header_lst[::-1]
del header_lst[len(header_lst)-1]
header_lst.insert(0,'Breakdown')
income_statement_df = pd.DataFrame(columns = header_lst)

revenue_row = income_statement_table.find('div', class_='D(tbr) fi-row Bgc($hoverBgColor):h')
revenue_lst = [] 
for i in revenue_row.find_all('div', attrs={'data-test':'fin-col'}):
    i = i.text
    i = i.replace(",","")
    revenue_lst.append(int(i))
revenue_lst = revenue_lst[::-1]
revenue_lst.insert(0,'Total Revenue')
income_statement_df.loc[0] = revenue_lst

EBIT_row = income_statement_table.find('div', attrs={'title':'EBIT'}).parent.parent
EBIT_lst = [] 
for i in EBIT_row.find_all('div', attrs={'data-test':'fin-col'}):
    i = i.text
    i = i.replace(",","")
    EBIT_lst.append(int(i))
EBIT_lst = EBIT_lst[::-1]
EBIT_lst.insert(0,'EBIT')
income_statement_df.loc[1] = EBIT_lst

income_statement_df = income_statement_df.drop('ttm', axis=1)
'''---------- // II. Forecasting Revenues and EBIT // ----------'''

latest_rev = income_statement_df.iloc[0,len(income_statement_df.columns)-1]
earliest_rev = income_statement_df.iloc[0,1]
rev_CAGR = (latest_rev/earliest_rev)**(float(1/(len(income_statement_df.columns)-2)))-1

EBIT_margin_lst = []
for year in range(1,len(income_statement_df.columns)):
    EBIT_margin = income_statement_df.iloc[1,year]/income_statement_df.iloc[0,year]
    EBIT_margin_lst.append(EBIT_margin)
avg_EBIT_margin = sum(EBIT_margin_lst)/len(EBIT_margin_lst)

forecast_df = pd.DataFrame(columns=['Year ' + str(i) for i in range(1,7)])

rev_forecast_lst = []
for i in range(1,7):
    if i != 6:
        rev_forecast = latest_rev*(1+rev_CAGR)**i
    else:
        rev_forecast = latest_rev*(1+rev_CAGR)**(i-1)*(1+long_term_growth)
    rev_forecast_lst.append(int(rev_forecast))
forecast_df.loc[0] = rev_forecast_lst

EBIT_forecast_lst = []
for i in range(0,6):
    EBIT_forecast = rev_forecast_lst[i]*avg_EBIT_margin
    EBIT_forecast_lst.append(int(EBIT_forecast))
forecast_df.loc[1] = EBIT_forecast_lst

'''---------- // III. Calculating the WACC // ----------'''

current_date = datetime.date.today()
past_date = current_date-datetime.timedelta(days=timespan)

risk_free_rate_df = dr.DataReader('^TNX', 'yahoo', past_date, current_date) 
risk_free_rate_float = (risk_free_rate_df.iloc[len(risk_free_rate_df)-1,5])/100

price_information_df = pd.DataFrame(columns=['Stock Prices', 'Market Prices'])

stock_price_df = dr.DataReader(company_ticker, 'yahoo', past_date, current_date) 
price_information_df['Stock Prices'] = stock_price_df['Adj Close']

market_price_df = dr.DataReader('^GSPC', 'yahoo', past_date, current_date)
price_information_df['Market Prices'] = market_price_df['Adj Close']

returns_information_df = pd.DataFrame(columns =['Stock Returns', 'Market Returns'])

stock_return_lst = []
for i in range(1,len(price_information_df)):
    open_price = price_information_df.iloc[i-1,0]
    close_price = price_information_df.iloc[i,0]
    stock_return = (close_price-open_price)/open_price
    stock_return_lst.append(stock_return)
returns_information_df['Stock Returns'] = stock_return_lst

market_return_lst = []
for i in range(1,len(price_information_df)):
    open_price = price_information_df.iloc[i-1,1]
    close_price = price_information_df.iloc[i,1]
    market_return = (close_price-open_price)/open_price
    market_return_lst.append(market_return)
returns_information_df['Market Returns'] = market_return_lst

covariance_df = returns_information_df.cov()
covariance_float = covariance_df.iloc[1,0]
variance_df = returns_information_df.var()
market_variance_float = variance_df.iloc[1]

equity_beta = covariance_float/market_variance_float
equity_return = risk_free_rate_float+equity_beta*(market_risk_premium)

balance_sheet_url = '<https://finance.yahoo.com/quote/>' + company_ticker + '/balance-sheet?p=' + company_ticker

balance_sheet_html = requests.get(balance_sheet_url)
balance_sheet_soup = bs(balance_sheet_html.text, 'html.parser')

balance_sheet_table = balance_sheet_soup.find('div', class_='D(tbrg)')

net_debt_lst = []

net_debt_row = balance_sheet_table.find('div', attrs={'title':'Net Debt'}).parent.parent
for value in net_debt_row.find_all('div'):
    value = value.text
    value = value.replace(',','')
    net_debt_lst.append(value)
net_debt_int = int(net_debt_lst[3])

market_cap_url = '<https://finance.yahoo.com/quote/>' + company_ticker + '?p=' + company_ticker
market_cap_html = requests.get(market_cap_url)
market_cap_soup = bs(market_cap_html.text, 'html.parser')

market_cap_int = 0

market_cap_row = market_cap_soup.find('td', attrs={'data-test':'MARKET_CAP-value'})
market_cap_str = market_cap_row.text
market_cap_lst = market_cap_str.split('.')

if market_cap_str[len(market_cap_str)-1] == 'T':
    market_cap_length = len(market_cap_lst[1])-1
    market_cap_lst[1] = market_cap_lst[1].replace('T',(9-market_cap_length)*'0')
    market_cap_int = int(''.join(market_cap_lst))

if market_cap_str[len(market_cap_str)-1] == 'B':
    market_cap_length = len(market_cap_lst[1])-1
    market_cap_lst[1] = market_cap_lst[1].replace('B',(6-market_cap_length)*'0')
    market_cap_int = int(''.join(market_cap_lst))

company_value = market_cap_int + net_debt_int

WACC = market_cap_int/company_value * equity_return + net_debt_int/company_value * debt_return * (1-tax_rate)

'''-------- // IV. Discounting the Forecasted EBIT // --------'''

discounted_EBIT_lst = []

for year in range(0,5):
    discounted_EBIT = forecast_df.iloc[1,year]/(1+WACC)**(year+1)
    discounted_EBIT_lst.append(int(discounted_EBIT))

terminal_value = forecast_df.iloc[1,5]/(WACC-long_term_growth)
PV_terminal_value = int(terminal_value/(1+WACC)**5)

enterprise_value = sum(discounted_EBIT_lst)+PV_terminal_value
equity_value = enterprise_value-net_debt_int