Apple DCF Model

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
Written on November 1, 2018