In this post I calculate the intrinsic value of Apple (AAPL) using a Discounted Cash Flow (DCF) model. As of this writing (31. Oct. 2018) AAPL is trading at USD 218.86. I used Jupyter Notebook for my analysis. According to my model, Apple’s intrinsic value per share is USD 229. That is, currently it trades at a 4% discount or margin of safety.
Helper Functions & Imports
%matplotlib inline import pandas as pd import numpy as np import matplotlib.pyplot as plt import math import statsmodels.api as sm import numpy as np import locale
locale.setlocale(locale.LC_ALL, 'en_US')
millnames = ['', ' Thousand', ' Million', ' Billion', ' Trillion']
start_year = 2013
end_year = 2017
def millify(n):
n = float(n)
millidx = max(0, min(len(millnames)-1, int(math.floor(0 if n == 0 else math.log10(abs(n))/3))))
return '{:.0f}{}'.format(n / 10**(3 * millidx), millnames[millidx])
def compounded_average_growth_rate(p, f, n):
return (f / p)**(1/n) - 1
Calculating Beta Using Linear Regression
aapl_df = pd.read_csv('AAPL.csv', index_col='Date')
sp500_df = pd.read_csv('SP500.csv', index_col='Date')
# Joining the closing prices of the two datasets
daily_prices = pd.concat([aapl_df['Close'], sp500_df['Close']], axis=1)
daily_prices.columns = ['AAPL', 'SP500']
# Calculate daily returns
daily_returns = daily_prices.pct_change(1)
clean_daily_returns = daily_returns.dropna(axis=0) # drop first missing row
# Split dependent and independent variable
X = clean_daily_returns['SP500']
y = clean_daily_returns['AAPL']
# Add a constant to the independent value
X1 = sm.add_constant(X)
# Make regression model
model = sm.OLS(y, X1)
# Fit model and print results
results = model.fit()
beta = results.params[1]
print("Beta: %0.2f" % beta)
Beta: 1.05
Net Capital Expenditures
# Normalize acquisition expenditures and
# payments for intangible assets by taking
# the average over the last 5 years
# Payments for acquisition of property, plant and equipment
# 2017, 2016, 2015, 2014, 2013
acquisition_expenditures_ppe = [12451000000, 12734000000, 11247000000, 9571000000, 8165000000]
# Payments for acquisition of intangible assets
acquisition_expenditures_intagibles = [344000000, 814000000, 241000000, 242000000, 911000000]
# Depreciation & Amortization
depreciation_amortization = [10157000000, 10505000000, 11257000000, 7946000000, 6757000000]
avg_ppe = np.average(acquisition_expenditures_ppe)
avg_int = np.average(acquisition_expenditures_intagibles)
avg_da = np.average(depreciation_amortization)
avg_net_capital_expenditures = (avg_ppe + avg_int) - avg_da
print("Net Capital Expenditures: %0.2f" % avg_net_capital_expenditures)
Net Capital Expenditures: 2019600000.00
Working Capital
Working capital is the capital that companies require to meet their everyday financial obligations and commitments to operate successfully i.e. ability to pay suppliers, salaries payable, maintenance costs, replenish stocks etc. In accounting terms, it’s the difference between current assets (such as inventories, accounts receivables and cash) and current liabilities (such as accounts payable and other short term liabilities).
Working Capital = Current Assets – Current liabilities
# Working Capital
total_current_assets = np.array([128645000000, 106869000000, 89378000000, 68531000000, 73286000000])
total_current_liabilities = np.array([100814000000, 79006000000, 80610000000, 63448000000, 43658000000])
# Excess cash is not needed to run the business and thus was excluded.
excess_cash = np.array([20289000000, 20484000000, 21120000000, 13844000000, 14259000000])
working_capital = total_current_assets - total_current_liabilities - excess_cash
avg_working_capital = np.average(working_capital)
print("Working Capital: %0.2f" % avg_working_capital)
Working Capital: 1835400000.00
Change in Working Capital (ΔWC)
What Causes a Change in Working Capital?
Asset increase = spending cash = reducing cash = negative change in working capital
If an owner of a business makes an investment of $100k into his company, current assets increases by $100k without any increase in current liabilities. Therefore, working capital has increased by $100k. So the change in working capital is negative.
Liability increase = owing something = not spending cash upfront = increase in cash = positive change in working capital
Accounts payables increases by $500k, therefore, working capital has decreased by $500k. So the change in working capital is positive. We need to find the change in working capital, which is the difference in working capital levels from one year to the next:
ΔWC = Previous Working Capital – New Working Capital
If the change in working capital is negative, that means working capital increased as the company needs more capital to grow. This reduces cash flow and so it should reduce the owner earnings. If changes in working capital is positive, that means working capital decreased as the company has more cash for the company to grow and play with. This increases cash flow and so it should added to owner earnings.
# Change in WC = last year's working capital - this year's working capital
change_in_working_capital = working_capital[1:] - working_capital[:-1]
avg_change_in_working_capital = np.average(change_in_working_capital)
print("Change in Working Capital: %0.2f" % avg_change_in_working_capital)
Change in Working Capital: 1956750000.00
Net Debt Issuance
How much of the company’s reinvestment needs are being financed by equity, and therefore returning cash flows to equity? We can simply multiply our figure for reinvestment needs by (1 – debt ratio) to obtain a figure for the reinvestment needs financed by equity.
total_assets = 375319000000
total_debt = 241272000000
# Debt ratio is defined as the ratio of total debt to total assets
debt_ratio = total_debt/total_assets
print("Debt Ratio: %0.2f" % debt_ratio)
Debt Ratio: 0.64
The Discount Rate
# https://www.treasury.gov/resource-center/data-chart-center/interest-rates/Pages/TextView.aspx?data=yield
risk_free_rate = 3.19
# http://pages.stern.nyu.edu/~adamodar/
equity_risk_premium = 4.78
discount_rate = (risk_free_rate + beta * equity_risk_premium)/100
print("Discount rate: %0.2f" % discount_rate)
Discount rate: 0.08
Free Cash Flow to Equity (FCFE)
FCFE is a metric of how much cash can be distributed to the equity shareholders of the company as dividends or stock buybacks—after all expenses, reinvestments, and debt repayments are taken care of.
FCFE = Net Income – [(1–b)(Capex–D&A)+(1–b)(ΔWC)]
- D&A is the depreciation and amortisiation
- b is the debt ratio
- Capex is the capital expenditure
- ΔWC is the change in working capital
Note: a negative change in working capital is a burn thru of cash, therefore I subtract it. Why? Because the working capital requirements have increased (increased inventory, receivables or reduced payables) and I look at the change as Y1 – Y2, therefore if working capital for Y1 is 50 and for Y2 it’s 70, change in working capital is -20 therefore the company is burning 20 extra cash which flows directly down to FCF.
net_income = [48351000000, 45687000000, 53394000000, 39510000000, 37037000000]
fcfe = net_income[0] - (1-debt_ratio)*avg_net_capital_expenditures
if avg_change_in_working_capital < 0:
# A negative change in the NWC is a burn thru of cash, therefore I subtract it
fcfe += (1-debt_ratio)*avg_change_in_working_capital
else:
fcfe += avg_change_in_working_capital
print("FCFE: %0.2f (%s)" % (fcfe, millify(fcfe)))
FCFE: 49586440071.11 (50 Billion)
Compounded Average Growth Rate (CAGR)
I look at 5 years worth of data. It provides enough history to make projections easier and more trustworthy. I calculate free cash owners earnings rates for multiple periods and then calculate the median of all the periods.
- 2013-2017 (4 year period)
- 2014-2017 (3 year period)
- 2014-2016 (2 year period)
- 2013-2016 (3 year period)
- 2013-2015 (2 year period)
- 2014-2015 (1 year period)
owner_earnings = np.array(net_income) + np.array(depreciation_amortization) - (np.array(acquisition_expenditures_ppe) + np.array(acquisition_expenditures_intagibles)) + avg_change_in_working_capital
header = ('%-10s %-10s %-10s' % ('From', 'To', 'CAGR'))
print(header)
rates = []
for i in range(0, len(owner_earnings)-1):
for j in range(i+1, len(owner_earnings)):
r = compounded_average_growth_rate(owner_earnings[j], owner_earnings[i], j-i)
rates.append(r)
print("%-10s %-10s %-10s" % (end_year-j, end_year-i, "%0.2f" % r))
cagr = np.median(rates)
print("------------------------------------------------------------")
print("Median Compound Annual Growth Rate: %0.2f" % cagr)
From To CAGR 2016 2017 0.07 2015 2017 -0.07 2014 2017 0.06 2013 2017 0.07 2015 2016 -0.19 2014 2016 0.06 2013 2016 0.07 2014 2015 0.39 2013 2015 0.23 2013 2014 0.08 ------------------------------------------------------------ Median Compound Annual Growth Rate: 0.07
Future Cash Flows
# Stage 1
years = 5
future_cash_flows = [fcfe]
pv_future_cash_flows = [fcfe]
for n in range(1, years+1):
future_cash_flows.append(future_cash_flows[n-1]*(1 + cagr))
pv_future_cash_flows.append(future_cash_flows[n] / (1 + discount_rate)**n)
# Stage 2
perpetual_growth_rate = 0.03
terminal = (future_cash_flows[-1] * (1 + perpetual_growth_rate))/(discount_rate - perpetual_growth_rate)
pv_terminal = terminal / ((1 + discount_rate)**years)
header = ('%-20s %-20s %-20s' % ('Year', 'Future CF', 'PV of FCF'))
print(header)
for n in range(1, years+1):
print("%-20s %-20s %-20s" % (2018+n, locale.format('%d', future_cash_flows[n], grouping=True),locale.format('%d', pv_future_cash_flows[n], grouping=True)))
print("%-20s %-20s %-20s" % ('Terminal', locale.format('%d', terminal, grouping=True),locale.format('%d', pv_terminal, grouping=True)))
print("------------------------------------------------------------")
pv_sum = pv_terminal + np.sum(pv_future_cash_flows)
print("%-20s %-20s %-20s" % ('Sum of PVs', '', locale.format('%d', pv_sum, grouping=True)))
Year Future CF PV of FCF 2019 52,937,070,041 48,919,778,298 2020 56,514,107,093 48,262,079,418 2021 60,332,849,893 47,613,222,929 2022 64,409,630,859 46,973,089,951 2023 68,761,886,016 46,341,563,199 Terminal 1,358,878,647,949 915,806,188,475 ------------------------------------------------------------ Sum of PVs 1,203,502,362,344
Equity Value per Share
diluted_number_of_shares = 5251692000
eq_per_share = (pv_sum)/diluted_number_of_shares
print("Equity Value per Share: %0.0f" % eq_per_share)
Equity Value per Share: 229
Summary
print("Annual Growth Rate (first 5 years): %0.2f%%" % (cagr * 100))
print("Perpetual Growth Rate: %0.2f%%" % (perpetual_growth_rate * 100))
print("Discount rate: %0.2f%%" % (discount_rate * 100))
print("------------------------------------------------------------")
print("Equity Value per Share: %0.0f" % eq_per_share)
Annual Growth Rate (first 5 years): 6.76% Perpetual Growth Rate: 3.00% Discount rate: 8.21% ------------------------------------------------------------ Equity Value per Share: 229
Leave a Reply
You must be logged in to post a comment.