discounted-cash-flow.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## WTH is this crap?\n",
    "\n",
    "Idk man it's just an attempt to have fun with (crappy) codes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## References\n",
    "\n",
    "1. [Investopedia](http://i.investopedia.com/inv/pdf/tutorials/dcf.pdf). It covers not only the how to, but also the advantages and disadvantages of using discounted cash flow analysis for financial valuation,\n",
    "2. [Put another link here](https://.com),\n",
    "3. [Another one](https://.com)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Intro"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "$$ DCF = \\frac{FCFF_1}{(1 + r) ^ 1} + \\frac{FCFF_2}{(1 + r) ^ 2} + ... + \\frac{FCFF_n}{(1 + r) ^ n} $$"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Where $FCFF$ stands for unlevered free cash flow for a given year ($FCFF_1$ means free cash flow in year 1, and so on); and $r$ stands for the discount rate."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "$ FCFF = EBITDA - CAPEX - \\Delta NWC - tax\\_expenses $\n",
    "\n",
    "Where:\n",
    "1. EBITDA = earnings before interest, depreciation, and amortization, or pretax income,\n",
    "2. CAPEX = capital expenditure, or fixed assets,\n",
    "3. $ \\Delta $ NWC = changes in net working capital, which can be derived by subtracting total current assets and total current liabilities in current and previous years,\n",
    "4. tax_expenses is already self-explanatory (hopefully)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas\n",
    "import requests\n",
    "import string\n",
    "from bs4 import BeautifulSoup\n",
    "from functools import partial, reduce"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "INPUT_PATH = os.getcwd() + '/input/'\n",
    "OUTPUT_PATH = os.getcwd() + '/output/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_single_dataframe(year):\n",
    "    \"\"\"\n",
    "    Returns dataframe in a given year.\n",
    "    \n",
    "    Argument:\n",
    "        - year = four digits integer to get YYYY format.\n",
    "    \"\"\"\n",
    "    \n",
    "    # find files in the given directory in .csv format and store them in a list\n",
    "    file_name = [x for x in os.listdir('input/') if x.endswith('{}.csv'.format(year))]\n",
    "    \n",
    "    # read each file in that list and store them in a list\n",
    "    dfs = []\n",
    "    for f in file_name:\n",
    "        dfs.append(pandas.read_csv(INPUT_PATH + f))\n",
    "        \n",
    "    # merge them into a single dataframe\n",
    "    return reduce(partial(pandas.merge, on = 'ticker_code'), dfs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_multiple_dataframes(year, subtrahend):\n",
    "    \"\"\"\n",
    "    Returns multiple periods dataframe into a single dataframe.\n",
    "    \n",
    "    Arguments:\n",
    "        - latest_year = four digits integer to get YYYY format.\n",
    "        - subtrahend = a quantity or number to be subtracted from another.\n",
    "    \"\"\"\n",
    "    \n",
    "    # if subtrahend is 1 then total periods will be 2 years\n",
    "    # example: 2018 - 1 = 2017, so it will be between 2017 and 2018\n",
    "    # year + 1 is needed because otherwise it only ranges from 2017 to 2017\n",
    "    dfs = []\n",
    "    for y in range(year - subtrahend, year + 1):\n",
    "        dfs.append(get_single_dataframe(year=y))\n",
    "        \n",
    "    return pandas.concat(dfs, sort = False).reset_index(drop = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "baseline_dataframe = get_multiple_dataframes(2018, 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "baseline_dataframe = get_multiple_dataframes(2018, 1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Calculate Free Cash Flow"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_free_cash_flow(year):\n",
    "    \"\"\"\n",
    "    Returns free cash flow dataframe.\n",
    "    \n",
    "    Argument:\n",
    "        - year = four digits integer to get YYYY format.\n",
    "    \"\"\"\n",
    "    \n",
    "    # select necessary variables\n",
    "    # sort by ticker code in ascending order\n",
    "    # reset index\n",
    "    nwc = baseline_dataframe[[\n",
    "        'year', \n",
    "        'ticker_code', \n",
    "        'total_current_assets', \n",
    "        'total_current_liabilities'\n",
    "    ]].sort_values(by=['ticker_code'], ascending=True) \\\n",
    "    .reset_index(drop=True)\n",
    "    \n",
    "    # add net working capital as a column\n",
    "    nwc['net_working_capital'] = nwc['total_current_assets'] - nwc['total_current_liabilities']\n",
    "    \n",
    "    # changes in net working capital\n",
    "    nwc['net_working_capital_delta'] = nwc.groupby('ticker_code')['net_working_capital'].diff()\n",
    "    \n",
    "    # filter dataframe only in year latest year\n",
    "    nwc = nwc[nwc['year'] == year][[\n",
    "        'ticker_code', \n",
    "        'net_working_capital_delta'\n",
    "    ]].reset_index(drop=True)\n",
    "    \n",
    "    # calculate free cash flow\n",
    "    fcff = baseline_dataframe[baseline_dataframe['year'] == year][[\n",
    "        'year', \n",
    "        'ticker_code', \n",
    "        'pretax_income', \n",
    "        'fixed_assets', \n",
    "        'tax_expenses'\n",
    "    ]]\n",
    "    \n",
    "    fcff = pandas.merge(fcff, nwc, how = 'inner', on = 'ticker_code').reset_index(drop = True)\n",
    "    \n",
    "    fcff['free_cash_flow'] = fcff['pretax_income'] \\\n",
    "    - fcff['fixed_assets'] \\\n",
    "    - fcff['net_working_capital_delta'] \\\n",
    "    - fcff['tax_expenses']\n",
    "    \n",
    "    return fcff[['year', 'ticker_code', 'free_cash_flow']].reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fcff = get_free_cash_flow(2018)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Calculate Discount Rate (r)\n",
    "\n",
    "What we need to find out:\n",
    "1. Cost of Equity \n",
    "2. Cost of Debt\n",
    "3. Weighted Average Cost of Capital\n",
    "\n",
    "### Cost of Equity\n",
    "\n",
    "Cost of equity, or RE, can obtained by calculating [Capital Asset Pricing Model](https://www.investopedia.com/terms/c/capm.asp). Following are the items:\n",
    "\n",
    "1. Risk-free rate\n",
    "2. Beta\n",
    "3. Equity market risk premium (market rate subtracted by risk-free rate)\n",
    "\n",
    "After getting the items, we need to calculate the cost of equity as follows:\n",
    "\n",
    "$$ RE = risk\\_free\\_rate + beta * (market\\_rate - risk\\_free\\_rate) $$\n",
    "\n",
    "### Risk-free rate\n",
    "\n",
    "Risk-free rate is commonly derived from T-bills rate. In Indonesia, the equivalent is [Obligasi Negara](https://www.bi.go.id/id/moneter/obligasi-negara/Default.aspx), which is traded in the secondary market. Currently there are many traded bonds with various coupon rates, so I am going to use the average rate. First, we need to scrape the table to get the coupon rate.\n",
    "\n",
    "### Cost of Debt\n",
    "\n",
    "Cost of debt, or symbolized as RD, can be calculated as follows:\n",
    "\n",
    "$$ RD = 1 - tax\\_rate $$\n",
    "\n",
    "According to [Trading Economics](https://tradingeconomics.com/indonesia/corporate-tax-rate), Indonesia's corporate tax rate is 25%. Hence, the cost of debt is 75%.\n",
    "\n",
    "### Capital Structure (Weighted Average Cost of Capital)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_risk_free_rate(retry=3, timeouts=5):\n",
    "    \"\"\"\n",
    "    Returns average coupon rates from Central Bank of Indonesia's website.\n",
    "    \n",
    "    Arguments:\n",
    "        - retry: total attempts that will be made to the website in case of connection failure.\n",
    "        - timeouts = number of seconds to establish a connection with the client.\n",
    "        see https://requests.kennethreitz.org/en/master/user/advanced/#advanced for suggested number of timeouts.\n",
    "    \"\"\"\n",
    "    \n",
    "    # get bonds rate from Bank Central of Indonesia\n",
    "    # attempt thrice in case of connection failure\n",
    "    i = 0\n",
    "    while i < retry:\n",
    "        try:\n",
    "            url = requests.get('https://www.bi.go.id/id/moneter/obligasi-negara/Default.aspx', timeout=timeouts)\n",
    "            if url.status_code == 200:\n",
    "                soup = BeautifulSoup(url.text, 'html')\n",
    "            i = retry\n",
    "        except Exception:\n",
    "            i += 1\n",
    "    \n",
    "    # find table which contains the bonds rate\n",
    "    table = soup.find('table', {'class': 'table1'})\n",
    "    trs = table.find_all('tr')\n",
    "    \n",
    "    # get the bonds rate\n",
    "    tds = []\n",
    "    for t in trs:\n",
    "        try:\n",
    "            tds.append(t.find_all('td')[-1]\\\n",
    "                       .text\\\n",
    "                       .replace(' ', ''))\n",
    "        except IndexError:\n",
    "            pass\n",
    "        \n",
    "    for t in range(len(tds)):\n",
    "        if '\\r' in tds[t]:\n",
    "            tds[t] = tds[t].replace('\\r', '')\n",
    "        elif '\\n' in tds[t]:\n",
    "            tds[t] = tds[t].replace('\\n', '')\n",
    "        try:\n",
    "            tds[t] = float(tds[t]) / 100\n",
    "        except ValueError:\n",
    "            pass\n",
    "        \n",
    "    risk_free_rate = []\n",
    "    for t in tds:\n",
    "        if type(t) == float:\n",
    "            risk_free_rate.append(t)\n",
    "    \n",
    "    return sum(risk_free_rate) / len(risk_free_rate)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "risk_free_rate = get_risk_free_rate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_cost_of_equity(beta, market_rate):\n",
    "    \"\"\"\n",
    "    Returns capita asset pricing model for calculating cost of equity.\n",
    "    \n",
    "    Arguments:\n",
    "        - risk_free_rate = see get_risk_free_rate function,\n",
    "        - beta =  the covariance of asset returns with the market relative to variance of the market,\n",
    "        - market_rate = expected stock returns of a given equity.\n",
    "    \"\"\"\n",
    "    \n",
    "    return risk_free_rate + beta * (market_rate - risk_free_rate)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cost_of_equity = get_cost_of_equity(0.05, 0.05)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_wacc(year, tax_rate=0.25):\n",
    "    \"\"\"\n",
    "    Returns weighted average cost of capital.\n",
    "    \n",
    "    Arguments:\n",
    "        - year = four digits integer to get YYYY format.\n",
    "        - tax_rate = corporate tax rate in Indonesia. Basic value is 25%. Perhaps it is subject to change.\n",
    "    \"\"\"\n",
    "\n",
    "    # get capital structure\n",
    "    wacc = baseline_dataframe[baseline_dataframe['year'] == year][[\n",
    "        'ticker_code', \n",
    "        'total_assets', \n",
    "        'total_equity'\n",
    "    ]].reset_index(drop = True)\n",
    "    \n",
    "    wacc['equity_proportion'] = wacc['total_equity'] / wacc['total_assets']\n",
    "    wacc['liabilities_proportion'] = 1 - wacc['equity_proportion']\n",
    "    \n",
    "    # cost of equity\n",
    "    re = cost_of_equity\n",
    "    \n",
    "    # cost of debt\n",
    "    rd = 1 - tax_rate\n",
    "    \n",
    "    # weighted cost of capital\n",
    "    wacc['wacc'] = (re * wacc['equity_proportion']) + (rd * (1 - wacc['equity_proportion']))\n",
    "    wacc = wacc[['ticker_code', 'wacc']]\n",
    "    \n",
    "    return wacc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "wacc = get_wacc(2018, 0.05)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fcff = pandas.merge(fcff, wacc, how = 'inner', on = 'ticker_code')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Calculate Fair Value\n",
    "\n",
    "What we need to find out is the terminal value\n",
    "\n",
    "$$ terminal\\_value = \\frac{FPYCF * (1 + LTCFGR)}{r - LTCFGR} $$\n",
    "\n",
    "Jeez, what's that?!?!?!\n",
    "\n",
    "1. FPYCF = Final projected year cash flow\n",
    "2. LTCGR = Long-term cash flow growth rate\n",
    "3. r = discounted rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_fair_value(growth_rate, total_period, year):\n",
    "    \n",
    "    \"\"\"\n",
    "    Returns fair value.\n",
    "    \n",
    "    Arguments:\n",
    "        - growth_rate = projected cash flow growth rate,\n",
    "        - num_year = total projected years,\n",
    "        - year = four digits integer to get YYYY format.\n",
    "    \"\"\"\n",
    "    \n",
    "    fcff['fpycf'] = fcff['free_cash_flow'] * (1 + (growth_rate * total_period))\n",
    "    fcff['terminal_value'] = (fcff['fpycf'] * (1 + growth_rate)) / (fcff['wacc'] - growth_rate)\n",
    "    fcff['enterprise_value'] = fcff['free_cash_flow'] / ((1 + growth_rate) ** total_period)\n",
    "    fcff['fair_value'] = fcff['terminal_value'] - fcff['enterprise_value']\n",
    "    \n",
    "    return fcff[['year', 'ticker_code', 'fair_value']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_dataframe = get_fair_value(0.0001, 1, 2018)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Save Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_dataframe\\\n",
    ".sort_values(by='fair_value', ascending=False)\\\n",
    ".to_csv(OUTPUT_PATH + 'discounted-cash-flow-result.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}