DCF Automation Script v2.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8f5c3d87-c679-4169-be53-4dd3cd17429b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import yfinance as yf\n",
    "from sklearn.linear_model import LinearRegression\n",
    "\n",
    "# Fetch financial data for the stock\n",
    "def fetch_financial_data(stock_ticker):\n",
    "    stock = yf.Ticker(stock_ticker)\n",
    "    balance_sheet = stock.balancesheet\n",
    "    income_statement = stock.financials\n",
    "    cash_flow_statement = stock.cashflow\n",
    "    return balance_sheet, income_statement, cash_flow_statement\n",
    "\n",
    "def fetch_stock_and_market_returns(stock_ticker, market_index):\n",
    "    stock_returns = stock_data['Adj Close'].pct_change().dropna()\n",
    "    market_returns = market_data['Adj Close'].pct_change().dropna()\n",
    "    return stock_returns, market_returns\n",
    "\n",
    "# Calculate growth rates for a given financial term\n",
    "def calculate_growth_rate(financial_data, term):\n",
    "    num_columns = min(5, len(balance_sheet.columns))\n",
    "    growth_rates = []\n",
    "    for i in range(0, num_columns):\n",
    "        growth = (financial_data.loc[term][i] - financial_data.loc[term][(i+1)]) / financial_data.loc[term][(i+1)]\n",
    "        growth_rates.append(growth)\n",
    "    avg_growth_rate = np.mean(growth_rates)\n",
    "    return avg_growth_rate\n",
    "\n",
    "def calculate_beta(stock_returns, market_returns):\n",
    "    model = LinearRegression()\n",
    "    model.fit(market_returns.values.reshape(-1, 1), stock_returns.values)\n",
    "    beta = model.coef_[0]\n",
    "    return beta\n",
    "\n",
    "# Get user input with fetching data\n",
    "def get_input_data():\n",
    "    stock_ticker = input(\"Enter the stock ticker as per Yahoo Finance: \")\n",
    "    country = input(\"Enter home country of company: \")\n",
    "    market_index = input(\"Enter the market index (ticker) for the home country: \")\n",
    "\n",
    "    # Fetch financial data\n",
    "    balance_sheet, income_statement, cash_flow_statement = fetch_financial_data(stock_ticker)\n",
    "\n",
    "    # Manually enter data if fetching fails\n",
    "    try:\n",
    "        total_revenue = income_statement.loc['Total Revenue'][0]\n",
    "        COGS = income_statement.loc['Cost of Revenue'][0] - income_statement.loc['Selling And Marketing Expense'][0]\n",
    "        SGnA = income_statement.loc['Selling General & Administration'][0]\n",
    "        RnD = income_statement.loc['Research And Development'][0]\n",
    "        DnA = cash_flow_statement.loc['Reconciled Depreciation'][0]\n",
    "        current_assets = balance_sheet.loc['Current Assets'][0]\n",
    "        current_liabilities = balance_sheet.loc['Current Liabilities'][0]\n",
    "        cash = balance_sheet.loc['Cash And Cash Equivalents'][0]\n",
    "        total_debt = balance_sheet.loc['Total Debt'][0]\n",
    "        cap_ex = cash_flow_statement.loc['Capital Expenditure'][0]\n",
    "        ebit = income_statement.loc['EBIT'][0]\n",
    "        interest_expenses = income_statement.loc['Interest Expense'][0]\n",
    "        \n",
    "    except KeyError:\n",
    "        print(\"Error fetching some financial data, please enter the following values manually.\")\n",
    "        total_revenue = float(input(\"Enter total revenue of the firm: \"))\n",
    "        COGS = float(input(\"Enter COGS: \"))\n",
    "        SGnA = float(input(\"Enter SG&A expenses: \"))\n",
    "        RnD = float(input(\"Enter R&D expenses: \"))\n",
    "        DnA = float(input(\"Enter Depreciation and Amortization: \"))\n",
    "        current_assets = float(input(\"Enter current assets: \"))\n",
    "        current_liabilities = float(input(\"Enter current liabilities: \"))\n",
    "        cash = float(input(\"Enter current cash balance: \"))\n",
    "        total_debt = float(input(\"Enter total debt (short term + long term): \"))\n",
    "        cap_ex = float(input(\"Enter Capital Expenditure: \"))\n",
    "        ebit = float(input(\"Enter EBIT: \"))\n",
    "        interest_expenses = float(input(\"Enter the company's Interest Expenses: \"))\n",
    "        \n",
    "\n",
    "    # Calculate operating expenses and profit\n",
    "    gross_profit = total_revenue - COGS\n",
    "    operating_expenses = SGnA + RnD\n",
    "    operating_profit = gross_profit - operating_expenses\n",
    "    after_tax_operating_profit = operating_profit * (1 - tax_rate)\n",
    "    interest_coverage_ratio = ebit/interest_expenses\n",
    "    non_cash_change_working_capital = (current_assets - cash) - current_liabilities\n",
    "\n",
    "\n",
    "    if interest_coverage_ratio > 12.5:\n",
    "        company_default_spread = 0.59\n",
    "        RATING = \"AAA\"\n",
    "    elif interest_coverage_ratio > 9.5:\n",
    "        company_default_spread = 0.7\n",
    "        RATING = \"AA\"\n",
    "    elif interest_coverage_ratio > 7.5:\n",
    "        company_default_spread = 0.92\n",
    "        RATING = \"A\"\n",
    "    elif interest_coverage_ratio > 6:\n",
    "        company_default_spread = 1.07\n",
    "        RATING = \"A2\"\n",
    "    elif interest_coverage_ratio > 4.5:\n",
    "        company_default_spread = 1.21\n",
    "        RATING = \"A3\"\n",
    "    elif interest_coverage_ratio > 4:\n",
    "        company_default_spread = 1.47\n",
    "        RATING = \"BBB\"\n",
    "    elif interest_coverage_ratio > 3.5:\n",
    "        company_default_spread = 1.74\n",
    "        RATING = \"BB+\"\n",
    "    elif interest_coverage_ratio > 3:\n",
    "        company_default_spread = 2.21\n",
    "        RATING = \"BB\"\n",
    "    elif interest_coverage_ratio > 2.5:\n",
    "        company_default_spread = 3.14\n",
    "        RATING = \"B\"\n",
    "    elif interest_coverage_ratio > 2:\n",
    "        company_default_spread = 3.61\n",
    "        RATING = \"B2\"\n",
    "    elif interest_coverage_ratio > 1.5:\n",
    "        company_default_spread = 5.24\n",
    "        RATING = \"B3\"\n",
    "    elif interest_coverage_ratio > 1.25:\n",
    "        company_default_spread = 8.51\n",
    "        RATING = \"CCC\"\n",
    "    elif interest_coverage_ratio > 0.8:\n",
    "        company_default_spread = 11.78\n",
    "        RATING = \"CC\"\n",
    "    elif interest_coverage_ratio > 0.5:\n",
    "        company_default_spread = 17\n",
    "        RATING = \"C\"\n",
    "    else:\n",
    "        company_default_spread = 20\n",
    "        RATING = \"D\"\n",
    "    \n",
    "\n",
    "    # Check if it's a young company for growth rates\n",
    "    is_young = input(\"Is this a young company? (Y/N): \").strip().upper()\n",
    "    if is_young == 'Y':\n",
    "        growth_rate = float(input(\"Enter the industry young company growth rate (in %): \")) / 100\n",
    "    else:\n",
    "        revenue_growth_rate = calculate_growth_rate(income_statement, 'Total Revenue')\n",
    "        cogs_growth_rate = calculate_growth_rate(income_statement, 'Cost of Revenue')\n",
    "        sgna_growth_rate = calculate_growth_rate(income_statement, 'Selling General & Administration')\n",
    "        rnd_growth_rate = calculate_growth_rate(income_statement, 'Research And Development')\n",
    "        capex_growth_rate = calculate_growth_rate(cash_flow_statement, 'Capital Expenditure')\n",
    "        dna_growth_rate = calculate_growth_rate(cash_flow_statement, 'Reconciled Depreciation')\n",
    "        wc_growth_rate = calculate_growth_rate(balance_sheet, 'Change In Working Capital')\n",
    "\n",
    "\n",
    "    # Gather remaining user inputs\n",
    "    tax_rate = float(input(\"Enter tax rate: \"))/100\n",
    "    shares_issued = float(input(\"Enter new shares issued this year: \"))\n",
    "    shares_repurchased = float(input(\"Enter shares repurchased this year: \"))\n",
    "    debt = float(input(\"Enter the market value of debt: \"))\n",
    "    equity = float(input(\"Enter the market value of equity: \"))\n",
    "    domestic_revenue_percentage = float(input(\"Enter the percentage of domestic revenue for the company: \"))\n",
    "    sector_domestic_revenue_percentage = float(input(\"Enter the percentage of domestic revenue for the average company in the same sector: \"))\n",
    "    implied_equity_risk_premium_sp500 = float(input(\"Enter the implied equity risk premium for S&P 500 (in %): \"))\n",
    "    bond_market_stddev = float(input(\"Enter the standard deviation of the bond market index (in %): \")) / 100\n",
    "    longest_gov_bond_rate = float(input(\"Enter the longest term government bond return rate (in %): \"))\n",
    "    default_spread = float(input(f\"Enter the default spread for {country} (in %): \"))\n",
    "    terminal_gr = float(input(\"Enter Terminal Growth Rate (Use 3% for large, 4% for mid, 5% for small company): \"))\n",
    "\n",
    "    revenue_country = domestic_revenue_percentage/100\n",
    "    other_revenue_country = 0\n",
    "    other_default_spread = 0\n",
    "    if revenue_country < 1:\n",
    "        while revenue_country < 1:\n",
    "            other_country = input(\"Enter the name of the second country (only two countries can be added so give all revenue to second majority revenue country): \")\n",
    "            other_revenue_percentage = float(input(f\"Enter the percentage of revenue from other country: \")) / 100\n",
    "            other_default_spread = float(input(f\"Enter the default spread for other country (in %): \")) / 100\n",
    "            other_revenue_country += other_revenue_percentage\n",
    "            revenue_country += other_revenue_percentage\n",
    "            if revenue_country >= 1:\n",
    "                break\n",
    "\n",
    "    \n",
    "\n",
    "    return (company_default_spread, shares_issued, shares_repurchased, total_debt, terminal_gr, non_cash_change_working_capital, DnA, cap_ex, after_tax_operating_profit, stock_ticker, market_index, domestic_revenue_percentage, sector_domestic_revenue_percentage, debt, equity, \n",
    "            implied_equity_risk_premium_sp500, bond_market_stddev, tax_rate, longest_gov_bond_rate, default_spread, other_revenue_country, other_default_spread, company_default_spread)\n",
    "\n",
    "# Project financial data for 5 years\n",
    "def project_individual_term(current_value, growth_rate):\n",
    "    projections = []\n",
    "    for year in range(1, 7):\n",
    "        projected_value = current_value * (1 + growth_rate) ** year\n",
    "        projections.append(projected_value)\n",
    "        \n",
    "    return projections\n",
    "\n",
    "# Perform DCF calculation\n",
    "def dcf_calculate():\n",
    "    (company_default_spread, shares_issued, shares_repurchased, total_debt, terminal_gr, non_cash_change_working_capital, DnA, cap_ex, after_tax_operating_profit, stock_ticker, market_index, domestic_revenue_percentage, sector_domestic_revenue_percentage, debt, equity, \n",
    "     implied_equity_risk_premium_sp500, bond_market_stddev, tax_rate, longest_gov_bond_rate, default_spread, other_revenue_country, other_default_spread, company_default_spread) = get_input_data()\n",
    "\n",
    "    stock_returns, market_returns = fetch_stock_and_market_returns(company_name, market_index)\n",
    "    beta = calculate_beta(stock_returns, market_returns)\n",
    "    risk_free_rate = longest_gov_bond_rate - default_spread\n",
    "    country_equity_risk_premium = implied_equity_risk_premium_sp500 + default_spread * (np.std(market_returns) / bond_market_stddev)\n",
    "    company_equity_risk_premium = risk_free_rate + country_equity_risk_premium + (domestic_revenue_percentage / sector_domestic_revenue_percentage)\n",
    "    cost_of_equity = risk_free_rate + beta * (1 + (1 - tax_rate) * (debt / equity)) * (company_equity_risk_premium - risk_free_rate)\n",
    "    cost_of_debt = risk_free_rate + (domestic_revenue_percentage / 100) * default_spread + company_default_spread\n",
    "    if other_revenue_country > 0:\n",
    "        cost_of_debt += (other_revenue_country / 100) * other_default_spread\n",
    "    wacc = (cost_of_equity * (equity / (equity + debt))) + (cost_of_debt * (1 - tax_rate) * (debt / (debt + equity)))\n",
    "\n",
    "    # Project each term individually\n",
    "    revenue_projections = project_individual_term(total_revenue, revenue_growth_rate)\n",
    "    cogs_projections = project_individual_term(COGS, cogs_growth_rate)\n",
    "    SGnA_projections = project_individual_term(SGnA, sgna_growth_rate)\n",
    "    RnD_projections = project_individual_term(RnD, rnd_growth_rate)\n",
    "    capex_projections = project_individual_term(cap_ex, capex_growth_rate)\n",
    "    DnA_projections = project_individual_term(DnA, dna_growth_rate)\n",
    "    non_cash_wc_projections = project_individual_term(non_cash_change_working_capital, wc_growth_rate)\n",
    "\n",
    "    FCFF_projections = []\n",
    "    for i in range(6):\n",
    "        gross_profit = revenue_projections[i] - cogs_projections[i]\n",
    "        operating_profit = gross_profit - (SGnA_projections[i] + RnD_projections[i])\n",
    "        after_tax_operating_profit = operating_profit * (1 - tax_rate)\n",
    "        FCFF = after_tax_operating_profit - (capex_projections[i] - DnA_projections[i]) - non_cash_wc_projections[i]\n",
    "        FCFF_projections.append(FCFF)\n",
    "\n",
    "    # Calculate Terminal Value\n",
    "    terminal_value = FCFF_projections[5] * (1 + terminal_gr) / (wacc - terminal_gr)\n",
    "\n",
    "    # Calculate DCF\n",
    "    DCF = sum([FCFF_projections[i] / (1 + wacc) ** (i + 1) for i in range(5)]) + terminal_value / (1 + wacc) ** 6\n",
    "\n",
    "    # Calculate Enterprise Value\n",
    "    EV = DCF\n",
    "\n",
    "    # Calculate Equity Value\n",
    "    Equity_Value = EV - (total_debt - cash)\n",
    "\n",
    "    # Calculate Equity Value Per Share\n",
    "    Equity_Value_Per_Share = Equity_Value / (shares_issued - shares_repurchased)\n",
    "\n",
    "    # Determine valuation\n",
    "    stock_price = yf.Ticker(stock_ticker).history(period='1d').iloc[0][3]\n",
    "    if Equity_Value_Per_Share > stock_price:\n",
    "        valuation = \"undervalued\"\n",
    "    elif Equity_Value_Per_Share == stock_price:\n",
    "        valuation = \"fairly valued\"\n",
    "    else:\n",
    "        valuation = \"overvalued\"\n",
    "\n",
    "    print(f\"Calculated WACC: {wacc:.4f}\")\n",
    "    print(f\"Cost of Equity: {cost_of_equity:.4f}\")\n",
    "    print(f\"Cost of Debt: {cost_of_debt:.4f}\")\n",
    "    print(f\"Beta: {beta:.4f}\")\n",
    "    print(f\"Enterprise Value: {EV:.4f}\")\n",
    "    print(f\"Equity Value: {Equity_Value:.4f}\")\n",
    "    print(f\"Equity Value Per Share: {Equity_Value_Per_Share:.4f}\")\n",
    "    print(f\"The stock is currently {valuation}.\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "228af710-0b58-4023-beae-f1e1b4ab16fb",
   "metadata": {},
   "outputs": [],
   "source": [
    "dcf_calculate()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}