Streamline Your Discounted Cash Flow Model with Python Automation

Learn to calculate a company’s intrinsic value using Python, from data preprocessing to Discounted Cash Flow (DCF) modeling and WACC estimation.

The Discounted Cash Flow (DCF) model is one of the most commonly used tools for evaluating investments. It is pivotal in estimating the intrinsic value of a public company’s stock by analyzing its current and projected future cash flows.

The intrinsic value represents the stock’s perceived worth based on fundamental financial metrics. By comparing this intrinsic value to the company’s current market valuation, investors can assess its potential as an investment. This analysis helps determine whether the stock is undervalued or overvalued, providing critical insights into its investment viability.

A Real Case Study: Tesla

For this case study, we leverage the EODHD Python Financial Library to effortlessly download financial statement data directly into a Jupyter notebook using the API service. Our analysis focuses on Tesla stock (ticker: TSLA.US), a topic that frequently generates intense discussion on Wall Street due to its unique valuation dynamics.

We begin by retrieving the latest company data with the client.get_fundamental_equity function. This powerful tool provides access to the three primary documents that make up an annual company report:

• Balance Sheet
• Income Statement
• Cash Flow Statement

Together, these documents offer a detailed picture of Tesla’s financial health and operational performance, forming the foundation of our DCF analysis.

import eod
client = EodHistoricalData(api_key)

stock_ticker = "TSLA.US"

#download BS, IS, CFS from EOD historical data
BalanceSheet = client.get_fundamental_equity(stock_ticker, filter_='Financials::Balance_Sheet::yearly')
IncomeStat = client.get_fundamental_equity(stock_ticker, filter_='Financials::Income_Statement::yearly')
CashFlowStat = client.get_fundamental_equity(stock_ticker, filter_='Financials::Cash_Flow::yearly')
OutShares = client.get_fundamental_equity(stock_ticker, filter_='SharesStats::SharesOutstanding')

# transpose and concatenate
df_bal = pd.DataFrame(BalanceSheet).T
df_inc = pd.DataFrame(IncomeStat).T
df_cfs = pd.DataFrame(CashFlowStat).T
df_all = pd.concat([df_bal, df_inc, df_cfs], axis=1).sort_index()
df_all = df_all.loc[:, ~df_all.columns.duplicated()] #remove duplicated columns

We start with preprocessing steps to prepare the data for analysis. These steps include converting data to the float type and replacing datetime entries with their corresponding years. Due to the table’s extensive size, we apply a filter to focus on a few sample columns. The result is displayed below:

#convert all None values to np.NaN datatypes
df = df_all.iloc[-10 :, 3:,].applymap(lambda x: float(x) if x is not None else np.NaN)
df.index = pd.to_datetime(df.index)
df.index = df.index.year
df = df.applymap(format_value)
df.head(10)

Tesla Stock Fundamental Historical Data

Preparing the Ingredients for the DCF Model

With the company’s financial data retrieved, we can now calculate the variables required for the Discounted Cash Flow (DCF) model:

• % Revenue Growth
• % EBIT/Sales
• % D&A/Sales (Depreciation & Amortization)
• % Capex/Sales (Capital Expenditures)
• % Δ Net Working Capital/Sales (Change in Net Working Capital)
• % Tax/EBIT

Additionally, we calculate EBIAT (Earnings Before Interest After Taxes) to complete the necessary inputs for the model.

# compute % of sales and % of ebit variables
df["rev_growth"] = df["totalRevenue"].pct_change()
df["delta_nwc"] = df["netWorkingCapital"].diff()
df["ebit_of_sales"] = df["ebit"]/df["totalRevenue"]
df["dna_of_sales"] = df["depreciationAndAmortization"]/df["totalRevenue"]
df["capex_of_sales"] = df["capitalExpenditures"]/df["totalRevenue"]
df["nwc_of_sales"] = df["delta_nwc"]/df["totalRevenue"]
df["tax_of_ebit"] = df["taxProvision"]/df["ebit"]
df["ebiat"] = df["ebit"] - df["taxProvision"]
last_year = df.iloc[-1, :]

We also store data from the most recent financial statement in the variable last_year. This step is essential for calculating projections using linear interpolation, ensuring a smoother and more accurate forecasting process.

Assumptions

Assumptions form the backbone of a Discounted Cash Flow (DCF) analysis, playing a crucial role in determining the valuation’s accuracy and reliability. The DCF method projects future cash flows and discounts them to their present value. These projections are shaped by assumptions regarding growth rates, discount rates, terminal values, and other factors. The sensitivity of the final valuation to these assumptions will be demonstrated in the final chapter.

In this case study, we develop a 10-year DCF, meaning projections extend until 2032. The key assumptions are as follows:

• N = Number of years of projections = 10
• % Revenue Growth = 7%
• % EBIT/Sales = 23%
• % D&A/Sales = 3%
• % Capex/Sales = 5%
• % Δ Net Working Capital/Sales = 5%
• % Tax/EBIT = 21%
• TGR (Terminal Growth Rate) = 2.5%

n = 10
revenue_growth_T = 0.07
ebit_perc_T = 0.23
tax_perc_T = 0.21
dna_perc_T = 0.03
capex_perc_T = 0.05
nwc_perc_T = 0.05
TGR = 0.025

Creating the DCF Projections

With the model components established, we can now focus on projecting the company’s future values. The objective is to model the trajectories of key variables based on our expectations for the company and the broader industry.

For simplicity, we assume a linear trend for the values from time t+1 to T. This approach implies a constant rate of change for the model variables. While this is a strict assumption, linear interpolation is a widely used technique to estimate values at intermediate dates between the starting point and the final projection.

By adopting this method, we can create a straightforward yet effective representation of the company’s potential financial trajectory.

def interpolate(initial_value, terminal_value, nyears):
    return np.linspace(initial_value, terminal_value, nyears)

years = range(df.index[-1]+1, df.index[-1] + n + 1)
df_proj = pd.DataFrame(index=years, columns=df.columns)
# linear interpolation
df_proj["rev_growth"] = interpolate(last_year["rev_growth"], revenue_growth_T, n) 
df_proj["ebit_of_sales"] = interpolate(last_year["ebit_of_sales"], ebit_perc_T, n) 
df_proj["dna_of_sales"] = interpolate(last_year["dna_of_sales"], dna_perc_T, n) 
df_proj["capex_of_sales"] = interpolate(last_year["capex_of_sales"], capex_perc_T, n) 
df_proj["tax_of_ebit"] = interpolate(last_year["tax_of_ebit"], tax_perc_T, n) 
df_proj["nwc_of_sales"] = interpolate(last_year["nwc_of_sales"], nwc_perc_T, n) 
# cumulative values
df_proj["totalRevenue"] = last_year["totalRevenue"] *(1+df_proj["rev_growth"]).cumprod() 
df_proj["ebit"] = last_year["ebit"] *(1+df_proj["ebit_of_sales"]).cumprod() 
df_proj["capitalExpenditures"] = last_year["capitalExpenditures"] *(1+df_proj["capex_of_sales"]).cumprod() 
df_proj["depreciationAndAmortization"] = last_year["depreciationAndAmortization"] *(1+df_proj["dna_of_sales"]).cumprod() 
df_proj["delta_nwc"] = last_year["delta_nwc"] *(1+df_proj["nwc_of_sales"]).cumprod() 
df_proj["taxProvision"] = last_year["taxProvision"] *(1+df_proj["tax_of_ebit"]).cumprod() 
df_proj["ebiat"] = df_proj["ebit"] - df_proj["taxProvision"]

The final step before estimating the Weighted Average Cost of Capital (WACC) is to calculate the Free Cash Flow (FCF) using the following equation:

df_proj["freeCashFlow"] = df_proj["ebiat"] + df_proj["depreciationAndAmortization"] - df_proj["capitalExpenditures"] - df_proj["delta_nwc"]

Weighted Average Cost of Capital (WACC)

To calculate the WACC, additional data needs to be retrieved from the EODHD database:

• Company’s Beta
• Company’s Market Capitalization
• 10-Year US Treasury Rate (used as a proxy for the risk-free rate)
• Yield-to-Maturity of Tesla’s bonds
• Equity Risk Premium (sourced from the Damodaran website)

Cost of Equity

Using the Capital Asset Pricing Model (CAPM), the cost of equity is calculated as the expected rate of return on the company’s equity. The formula is:

To proceed, we first need to gather the following data:

• Company’s Market Capitalization
• Beta: A measure of the company’s sensitivity to market returns.
• Risk-Free Rate: Proxied by the 10-year US Treasury yield.
• Equity Risk Premium: Represents the additional return expected by investors for taking on equity market risk.

# company's beta and marketcap
beta = client.get_fundamental_equity(stock_ticker, filter_='Technicals::Beta')
marketcap = client.get_fundamental_equity(stock_ticker, filter_='Highlights::MarketCapitalization')

# risk-free rate
US10Y = client.get_prices_eod("US10Y.INDX", period='d', order='a')
US10Y = pd.DataFrame(US10Y).set_index("date")["adjusted_close"]
US10Y.rename("10Y", inplace=True)
rf_rate = US10Y.values[-1] /100

# get ERP
excel_url = "https://www.stern.nyu.edu/~adamodar/pc/datasets/histimpl.xls"
df_ERP = pd.read_excel(excel_url, skiprows=6)
df_ERP = df_ERP.dropna(subset=["Year"]).iloc[:-1, :].set_index("Year")
ERP = df_ERP["Implied ERP (FCFE)"].values[-1]

CostOfEquity = beta*(ERP) + rf_rate

Tesla Inc.’s cost of equity is calculated at 17.9%, influenced by the company’s high sensitivity to market returns (beta) and the elevated interest rate environment observed in 2023.

Cost of Debt

To estimate Tesla’s cost of debt, we use the yield-to-maturity (YTM) of its bond issues as a proxy. Bond data can be retrieved using either:

1. Search API: To locate specific bonds.
2. Exchange API: By passing BOND as the exchange name to retrieve bond-related data

import requests
url = f'https://eodhd.com/api/exchange-symbol-list/BOND?api_token={apikey}&fmt=json&delisted=1'
data = requests.get(url).json()
bond_issues = pd.DataFrame(data)
tsla_bond_issues = bond_issues[bond_issues["Name"].str.contains("Tesla")]

YTM = np.zeros(len(tsla_bond_issues))
today = datetime.now()
c = 0

for i in tsla_bond_issues["Code"]:
    bond_data = client.get_fundamentals_bonds(i, bond_only=1)
    YTM[c] = bond_data["YieldToMaturity"]
    c+=1

tsla_bond_issues["YTM"] = YTM
CostOfDebt = tsla_bond_issues["YTM"].mean() /100

We will adopt 3.885% as the proxy for Tesla’s cost of debt.

The final step is to calculate the Weighted Average Cost of Capital (WACC), which represents a weighted average of the company’s cost of debt and cost of equity. The weights correspond to the proportions of debt and equity in the company’s balance sheet.

The resulting WACC serves as the discount rate, which is applied to the projected future cash flows, enabling us to estimate the company’s intrinsic value.

Where:

• C(D): Cost of debt.
• C(E): Cost of equity.
• T: Corporate tax rate.
• E: Total equity.
• D: Total debt.

Assets =  last_year["totalAssets"] 
Debt = last_year["totalLiab"]
total = marketcap + Debt
AfterTaxCostOfDebt = CostOfDebt * (1-tax_perc_T)
WACC = (AfterTaxCostOfDebt*Debt/total) + (CostOfEquity*marketcap/total)

In this example, Tesla’s WACC is calculated at 17.2%, a figure significantly higher than typical market standards. This elevated value reflects several factors:

• Positive equity market performance.
• Elevated interest rates in the current financial environment.
• Tesla’s high sensitivity to equity market returns (beta).

These elements collectively contribute to a higher discount rate for Tesla’s future cash flows.

Calculating Present Value of FCF

With the company’s projections and WACC estimated, the next step is to calculate the present value (PV) of the Free Cash Flow (FCF). The WACC is used as the discount rate in this calculation:

def calculate_present_value(cash_flows, discount_rate):
    # Calculate the present value using: PV = CF / (1 + r)^t + TV/(1 + r)^T
    present_values_cf = [cf / (1 + discount_rate) ** t for t, cf in enumerate(cash_flows, start=1)]
    return present_values_cf
df_proj["pv_FCF"] = calculate_present_value(df_proj["freeCashFlow"].values, WACC)

FCF Projections

Finally, we calculate the Terminal Value, which represents the present value of all future cash flows beyond the explicit forecast period, in this case, 2032. The formula for Terminal Value is:

Where TGR is the Terminal Growth Rate, representing the expected growth rate of Tesla’s free cash flows beyond the explicit forecast period.

TV = df_proj["freeCashFlow"].values[-1] *(1+TGR) / (WACC - TGR)
pv_TV = TV/(1+WACC)**n

As the final step, we sum the present value of the FCF and the terminal value to calculate the Enterprise Value. To derive the Equity Value, we subtract the outstanding debt and add the cash balance. Finally, the Implied Share Price is determined by dividing the Equity Value by the total number of outstanding shares.

Ent_Value = np.sum(df_proj["pv_FCF"]) + pv_TV
Cash = last_year["cash"]
Eq_Value = Ent_Value - Debt + Cash
ImpliedSharePrice = Eq_Value/OutShares

Based on our assumptions, we calculated a Tesla share price of $42 per share, significantly lower than its current trading price of $250. This discrepancy arises from Tesla’s very high cost of equity (17.9%) and its extremely low debt levels.

As demonstrated in the next chapter, with more moderate WACC levels (around 8%), the valuation easily exceeds $300. Investors are encouraged to adjust assumption values to align with their own market expectations and perspectives.

Sensitivity Analysis

In DCF modeling, sensitivity analysis is a method used to evaluate how changes in key variables affect the Net Present Value (NPV) of a project or investment, and by extension, the implied share price.

In this example, we adjust the WACC and TGR to observe their effects on the final valuation:

• WACC: A higher WACC significantly decreases the future value of FCF, leading to a lower overall valuation.
• TGR: Conversely, an increase in the TGR positively influences the Terminal Value (TV), enhancing the company’s overall valuation.

This analysis helps investors understand the sensitivity of valuation to these critical assumptions, providing a range of possible outcomes based on varying market conditions.

waccs = np.linspace(0.06, 0.18, 25)  
tgrs = np.linspace(0.01, 0.04, 25)   

# Create a meshgrid for cost of debt and growth rate
waccs_mesh, tgrs_mesh = np.meshgrid(waccs, tgrs)

# Calculate NPV for each combination of cost of debt and growth rate
dcf_results = np.zeros_like(waccs_mesh)
for i in range(len(tgrs)):
    for j in range(len(waccs)):
         dcf_results[i, j] = Discount_Cash_Flow(df,  n=10, OutShares=OutShares, revenue_growth_T = 0.07, ebit_perc_T = 0.23,  tax_perc_T = 0.21, dna_perc_T = 0.03, capex_perc_T = 0.05, nwc_perc_T = 0.05, WACC = waccs[j],  TGR = tgrs[i])

fig = plt.figure()
ax = fig.add_subplot(111, projection='3d')
ax.plot_surface(waccs_mesh, tgrs_mesh, dcf_results, cmap='viridis')

ax.set_xlabel('WACC')
ax.set_ylabel('TGR')
ax.set_zlabel('Implied Share Price')
ax.set_title('Tesla Sensitivity Analysis')

plt.show()

Tesla Sensitivity Analysis

This analytical approach enables a thorough evaluation of the model’s robustness, offering valuable insights into how fluctuations in key parameters impact the valuation’s sensitivity.

Conclusion

This tutorial offered a step-by-step guide on performing a Discounted Cash Flow (DCF) analysis in Python, equipping users with the tools to evaluate a company’s intrinsic stock value based on projected cash flows.

Using the EODHD Python Financial Library, we efficiently retrieved key financial data and covered all essential steps to calculate the implied share price, including:

• Data preprocessing.
• Assumption setting.
• Conducting the DCF calculation.

Additionally, we explored the concept of Free Cash Flow (FCF), explaining its formula and emphasizing its role as a cornerstone in determining intrinsic value. FCF reflects a company’s operational efficiency, capital expenditures, and overall financial health.

Finally, we demonstrated a practical approach to calculating the Weighted Average Cost of Capital (WACC), a critical component in the DCF model, using the retrieved financial data.

This tutorial serves as a comprehensive framework for analyzing investments and gaining insights into a company’s financial potential.

Please note that this article is for informational purposes only and should not be taken as financial advice. We do not bear responsibility for any trading decisions made based on the content of this article. Readers are advised to conduct their own research or consult with a qualified financial professional before making any investment decisions.

For those eager to delve deeper into such insightful articles and broaden their understanding of different strategies in financial markets, we invite you to follow our account and subscribe for email notifications.

Stay tuned for more valuable articles that aim to enhance your data science skills and market analysis capabilities.