210803 Example_운영개발사업CF_3.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import importlib\n",
    "import pandas as pd\n",
    "from pandas import Series, DataFrame\n",
    "import numpy as np\n",
    "from datetime import datetime\n",
    "from datetime import date\n",
    "from datetime import timedelta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import PrjtCF_module as cf"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Outline\n",
    "* 호텔, 골프장 등 개발 후 운영 사업(분양을 하지 않는 사업)의 개발단계 재무모델\n",
    "* PF대출을 통해 토지비 및 공사비 등 개발사업 자금을 조달\n",
    "* 금융비용은 PF대출금으로 충당\n",
    "* 준공 후 담보대출금으로 상환 가정\n",
    "* PF대출금은 필요에 따라 한도대로 인출\n",
    "* PF대출금의 조달을 Tranche A, Tranche B 두개의 Tranche로 구분하여 조달하며, Tranche A는 Tranche B에 대하여 선순위 상환 받음."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Input Index Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 기간 index 설정\n",
    "# prjt index : 사업기간 전 기간에 대한 index로 모델의 base가 되는 index\n",
    "# cstrn index : 공사기간에 대한 index로 착공일로부터 준공일 까지의 기간에 대한 index\n",
    "# loan index : 대출 기간에 대한 index로 대출 실행일로부터 대출 상환일 까지의 기간에 대한 index\n",
    "idx = cf.PrjtIndex(idxname=['prjt', 'cstrn', 'loan'],\n",
    "                   start=['2021-08', '2021-10', '2021-10'],\n",
    "                   periods=[24+1, 18+1, 20+1],\n",
    "                   freq='M')\n",
    "\n",
    "# 공정률 index 설정 : cstrn index 기간 중 적용되는 공정률을 설정\n",
    "idx.prcs = Series(np.ones(len(idx.cstrn)) / len(idx.cstrn),\n",
    "                  index=idx.cstrn.index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Input Financing Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "equity = cf.Loan(idx, amt_ntnl=10_000)\n",
    "\n",
    "tra = cf.Loan(idx.loan, amt_ntnl=40_000,\n",
    "              rate_fee=0.01, rate_IR=0.05)\n",
    "trb = cf.Loan(idx.loan, amt_ntnl=20_000,\n",
    "              rate_fee=0.03, rate_IR=0.07)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Input Cost Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "dct_cost = {}\n",
    "\n",
    "# 토지비: 최초 1회 지급\n",
    "lnd = cf.Account(idx)\n",
    "lnd.addscdd(idx.cstrn[0], 10_000)\n",
    "dct_cost['lnd'] = lnd\n",
    "\n",
    "# 공사비: 공정률에 다라 지급\n",
    "cstrn = cf.Account(idx)\n",
    "cstrn.addscdd(idx.cstrn.index, 50_000 * idx.prcs)\n",
    "dct_cost['cstrn'] = cstrn\n",
    "\n",
    "cost = cf.Merge(dct_cost)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Execution Cash Flow"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1) 사전 계산"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make accounts\n",
    "oprtg = cf.Account(idx) # 운영현금흐름의 입출을 위한 운영계좌\n",
    "repay = cf.Account(idx) # 상환자금 관리를 위한 대출금 상환계좌"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate expected financial cost\n",
    "class exptdcst:\n",
    "    def __init__(self, idxno):\n",
    "        self.idxno = idxno\n",
    "        \n",
    "        # 해당 인덱스 기간 중 cost계좌 상 지출 필요한 금액\n",
    "        self.cstamt = cost.add_scdd[idxno]\n",
    "        \n",
    "        if idxno in idx.loan.index:\n",
    "            # 해당 인덱스 기간 중 loan계좌 상 지출 필요한 수수료 금액\n",
    "            self.trafee = tra.fee.add_scdd[idxno]\n",
    "            self.trbfee = trb.fee.add_scdd[idxno]\n",
    "        \n",
    "            # 해당 인덱스 기간 중 loan계좌 상 지출 필요한 이자금액\n",
    "            self.traIR = -tra.ntnl.bal_strt[idxno] * tra.IR.rate\n",
    "            self.trbIR = -trb.ntnl.bal_strt[idxno] * trb.IR.rate\n",
    "        \n",
    "    @property\n",
    "    def ttlcst(self):\n",
    "        ttlsum = self.cstamt\n",
    "        if idxno in idx.loan.index:\n",
    "            ttlsum = (ttlsum + \n",
    "                      self.trafee + self.trbfee +\n",
    "                      self.traIR + self.trbIR)\n",
    "        return ttlsum"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2) 실행함수 설정"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Inflow of cash\n",
    "def inflow_cash(idxno, exptdcst):\n",
    "    # deposit equity amount\n",
    "    equity.ntnl.send(idxno, equity.ntnl.sub_scdd[idxno], oprtg)\n",
    "    \n",
    "    # deposit loan notional amount\n",
    "    if idxno in idx.loan.index:\n",
    "        # Expected inflow amount: 예상 지출금액을 인자로 받아 운영계좌 잔액과 비교하여 필요한 조달금액을 추정\n",
    "        exptdinflw = max(exptdcst.ttlcst - oprtg.bal_end[idxno], 0)\n",
    "        \n",
    "        # Tr.B: 필요 조달금액을 잔여 대출금 한도와 비교하여 실제 조달되는 금액 계산\n",
    "        tmpinflw = min(trb.ntnl.sub_rsdl_cum[idxno], exptdinflw)\n",
    "        trb.ntnl.send(idxno, tmpinflw, oprtg) # 운영계좌 입금 처리\n",
    "        amtinflw = tmpinflw\n",
    "        exptdinflw -= tmpinflw\n",
    "        \n",
    "        # Tr.A: 필요 조달금액을 잔여 대출금 한도와 비교하여 실제 조달되는 금액 계산\n",
    "        tmpinflw = min(tra.ntnl.sub_rsdl_cum[idxno], exptdinflw)\n",
    "        tra.ntnl.send(idxno, tmpinflw, oprtg) # 운영계좌 입금 처리\n",
    "        amtinflw += tmpinflw\n",
    "        exptdinflw -= tmpinflw"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Outflow of cash\n",
    "def outflow_cash(idxno):\n",
    "    # 운영계좌에서 토지비 지출\n",
    "    oprtg.send(idxno, cost['lnd'].add_scdd[idxno], cost['lnd'])\n",
    "    \n",
    "    # 운영계좌에서 공사비 지출\n",
    "    oprtg.send(idxno, cost['cstrn'].add_scdd[idxno], cost['cstrn'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pay financial cost\n",
    "def pay_fnclcst(idxno, exptdcst):\n",
    "    if idxno in idx.loan.index:\n",
    "        # 운영계좌에서 대출금 수수료 지출\n",
    "        oprtg.send(idxno, exptdcst.trafee, tra.fee)\n",
    "        oprtg.send(idxno, exptdcst.trbfee, trb.fee)\n",
    "        \n",
    "        # 운영계좌에서 이자금액 지출\n",
    "        oprtg.send(idxno, exptdcst.traIR, tra.IR)\n",
    "        oprtg.send(idxno, exptdcst.trbIR, trb.IR)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Repay notional amount\n",
    "def repay_ntnl(idxno):\n",
    "    if idxno in idx.loan.index:\n",
    "        # loan계좌 상 상환예정금액(add_rsdl_cum)과 인출잔액(bal_end) 고려하여 상환금액 계산 후 \n",
    "        # 운영계좌에서 상환금액 인출\n",
    "        exptdrpya = min(tra.ntnl.add_rsdl_cum[idxno], -tra.ntnl.bal_end[idxno])\n",
    "        oprtg.send(idxno, exptdrpya, tra.ntnl)\n",
    "        \n",
    "        exptdrpyb = min(trb.ntnl.add_rsdl_cum[idxno], -trb.ntnl.bal_end[idxno])\n",
    "        oprtg.send(idxno, exptdrpyb, trb.ntnl)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3) Cash Flow 실행"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Execute cash flow\n",
    "for idxno in idx.index:\n",
    "    \n",
    "    # 해당 인덱스 기간 중 지출 필요한 cost 및 금융비용 계산(instance class 생성)\n",
    "    cst_exptd = exptdcst(idxno)\n",
    "    \n",
    "    # 필요지출금액을 인자로 받아, 조달금액 계산 후 운영계좌 입금\n",
    "    inflow_cash(idxno, cst_exptd)\n",
    "    \n",
    "    # 운영계좌에서 토지비, 공사비 등 각종 비용 지출\n",
    "    outflow_cash(idxno)\n",
    "    \n",
    "    # 운영계좌에서 각종 금융비용 지출\n",
    "    pay_fnclcst(idxno, cst_exptd)\n",
    "    \n",
    "    # 운영계좌에서 대출금 상환\n",
    "    repay_ntnl(idxno)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge Accounts\n",
    "dct_acc = {'equity':equity,\n",
    "           'tra_ntnl':tra.ntnl,\n",
    "           'tra_fee':tra.fee,\n",
    "           'tra_IR':tra.IR,\n",
    "           'trb_ntnl':trb.ntnl,\n",
    "           'trb_fee':trb.fee,\n",
    "           'trb_IR':trb.IR,\n",
    "           'cost_lnd':cost['lnd'],\n",
    "           'cost_cstrn':cost['cstrn'],\n",
    "           'oprtg':oprtg}\n",
    "acc_merge = cf.Merge(dct_acc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>equity.ntnl</th>\n",
       "      <th colspan=\"3\" halign=\"left\">tra.ntnl</th>\n",
       "      <th>tra.fee</th>\n",
       "      <th>tra.IR</th>\n",
       "      <th colspan=\"3\" halign=\"left\">trb.ntnl</th>\n",
       "      <th>trb.fee</th>\n",
       "      <th>trb.IR</th>\n",
       "      <th>cost_lnd</th>\n",
       "      <th>cost_cstrn</th>\n",
       "      <th colspan=\"3\" halign=\"left\">oprtg</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>amt_sub</th>\n",
       "      <th>amt_sub</th>\n",
       "      <th>amt_add</th>\n",
       "      <th>bal_end</th>\n",
       "      <th>amt_add</th>\n",
       "      <th>amt_add</th>\n",
       "      <th>amt_sub</th>\n",
       "      <th>amt_add</th>\n",
       "      <th>bal_end</th>\n",
       "      <th>amt_add</th>\n",
       "      <th>amt_add</th>\n",
       "      <th>amt_add</th>\n",
       "      <th>amt_add</th>\n",
       "      <th>amt_add</th>\n",
       "      <th>amt_sub</th>\n",
       "      <th>bal_end</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2021-08-31</th>\n",
       "      <td>10000.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>10000.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>10000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-30</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>10000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-10-31</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>400.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3631.578947</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-3631.578947</td>\n",
       "      <td>600.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>2631.578947</td>\n",
       "      <td>3631.578947</td>\n",
       "      <td>13631.578947</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-11-30</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2652.763158</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-6284.342105</td>\n",
       "      <td>0.0</td>\n",
       "      <td>21.184211</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2631.578947</td>\n",
       "      <td>2652.763158</td>\n",
       "      <td>2652.763158</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12-31</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2668.237610</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-8952.579715</td>\n",
       "      <td>0.0</td>\n",
       "      <td>36.658662</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2631.578947</td>\n",
       "      <td>2668.237610</td>\n",
       "      <td>2668.237610</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-01-31</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2683.802329</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-11636.382044</td>\n",
       "      <td>0.0</td>\n",
       "      <td>52.223382</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2631.578947</td>\n",
       "      <td>2683.802329</td>\n",
       "      <td>2683.802329</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02-28</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2699.457843</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-14335.839887</td>\n",
       "      <td>0.0</td>\n",
       "      <td>67.878895</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2631.578947</td>\n",
       "      <td>2699.457843</td>\n",
       "      <td>2699.457843</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-03-31</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2715.204680</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-17051.044567</td>\n",
       "      <td>0.0</td>\n",
       "      <td>83.625733</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2631.578947</td>\n",
       "      <td>2715.204680</td>\n",
       "      <td>2715.204680</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-04-30</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2731.043374</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-19782.087941</td>\n",
       "      <td>0.0</td>\n",
       "      <td>99.464427</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2631.578947</td>\n",
       "      <td>2731.043374</td>\n",
       "      <td>2731.043374</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-05-31</th>\n",
       "      <td>0.0</td>\n",
       "      <td>2529.062401</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-2.529062e+03</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>217.912059</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-20000.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>115.395513</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2631.578947</td>\n",
       "      <td>2746.974460</td>\n",
       "      <td>2746.974460</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-06-30</th>\n",
       "      <td>0.0</td>\n",
       "      <td>2758.783374</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-5.287846e+03</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.537760</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-20000.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>116.666667</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2631.578947</td>\n",
       "      <td>2758.783374</td>\n",
       "      <td>2758.783374</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-07-31</th>\n",
       "      <td>0.0</td>\n",
       "      <td>2770.278305</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-8.058124e+03</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22.032691</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-20000.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>116.666667</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2631.578947</td>\n",
       "      <td>2770.278305</td>\n",
       "      <td>2770.278305</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-08-31</th>\n",
       "      <td>0.0</td>\n",
       "      <td>2781.821131</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-1.083995e+04</td>\n",
       "      <td>0.0</td>\n",
       "      <td>33.575517</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-20000.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>116.666667</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2631.578947</td>\n",
       "      <td>2781.821131</td>\n",
       "      <td>2781.821131</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-09-30</th>\n",
       "      <td>0.0</td>\n",
       "      <td>2793.412052</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-1.363336e+04</td>\n",
       "      <td>0.0</td>\n",
       "      <td>45.166438</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-20000.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>116.666667</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2631.578947</td>\n",
       "      <td>2793.412052</td>\n",
       "      <td>2793.412052</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-10-31</th>\n",
       "      <td>0.0</td>\n",
       "      <td>2805.051269</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-1.643841e+04</td>\n",
       "      <td>0.0</td>\n",
       "      <td>56.805655</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-20000.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>116.666667</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2631.578947</td>\n",
       "      <td>2805.051269</td>\n",
       "      <td>2805.051269</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-11-30</th>\n",
       "      <td>0.0</td>\n",
       "      <td>2816.738983</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-1.925515e+04</td>\n",
       "      <td>0.0</td>\n",
       "      <td>68.493369</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-20000.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>116.666667</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2631.578947</td>\n",
       "      <td>2816.738983</td>\n",
       "      <td>2816.738983</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-12-31</th>\n",
       "      <td>0.0</td>\n",
       "      <td>2828.475395</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-2.208362e+04</td>\n",
       "      <td>0.0</td>\n",
       "      <td>80.229781</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-20000.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>116.666667</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2631.578947</td>\n",
       "      <td>2828.475395</td>\n",
       "      <td>2828.475395</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-01-31</th>\n",
       "      <td>0.0</td>\n",
       "      <td>2840.260709</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-2.492388e+04</td>\n",
       "      <td>0.0</td>\n",
       "      <td>92.015095</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-20000.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>116.666667</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2631.578947</td>\n",
       "      <td>2840.260709</td>\n",
       "      <td>2840.260709</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-02-28</th>\n",
       "      <td>0.0</td>\n",
       "      <td>2852.095129</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-2.777598e+04</td>\n",
       "      <td>0.0</td>\n",
       "      <td>103.849515</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-20000.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>116.666667</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2631.578947</td>\n",
       "      <td>2852.095129</td>\n",
       "      <td>2852.095129</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-03-31</th>\n",
       "      <td>0.0</td>\n",
       "      <td>2863.978859</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-3.063996e+04</td>\n",
       "      <td>0.0</td>\n",
       "      <td>115.733245</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-20000.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>116.666667</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2631.578947</td>\n",
       "      <td>2863.978859</td>\n",
       "      <td>2863.978859</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-04-30</th>\n",
       "      <td>0.0</td>\n",
       "      <td>2875.912104</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-3.351587e+04</td>\n",
       "      <td>0.0</td>\n",
       "      <td>127.666490</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-20000.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>116.666667</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2631.578947</td>\n",
       "      <td>2875.912104</td>\n",
       "      <td>2875.912104</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-05-31</th>\n",
       "      <td>0.0</td>\n",
       "      <td>256.316124</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-3.377219e+04</td>\n",
       "      <td>0.0</td>\n",
       "      <td>139.649457</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-20000.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>116.666667</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>256.316124</td>\n",
       "      <td>256.316124</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-06-30</th>\n",
       "      <td>0.0</td>\n",
       "      <td>257.384108</td>\n",
       "      <td>34029.569944</td>\n",
       "      <td>-2.273737e-12</td>\n",
       "      <td>0.0</td>\n",
       "      <td>140.717441</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>20000.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>116.666667</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>257.384108</td>\n",
       "      <td>54286.954051</td>\n",
       "      <td>-54029.569944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-07-31</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-54029.569944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-08-31</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-54029.569944</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           equity.ntnl     tra.ntnl                             tra.fee  \\\n",
       "               amt_sub      amt_sub       amt_add       bal_end amt_add   \n",
       "2021-08-31     10000.0     0.000000      0.000000  0.000000e+00     0.0   \n",
       "2021-09-30         0.0     0.000000      0.000000  0.000000e+00     0.0   \n",
       "2021-10-31         0.0     0.000000      0.000000  0.000000e+00   400.0   \n",
       "2021-11-30         0.0     0.000000      0.000000  0.000000e+00     0.0   \n",
       "2021-12-31         0.0     0.000000      0.000000  0.000000e+00     0.0   \n",
       "2022-01-31         0.0     0.000000      0.000000  0.000000e+00     0.0   \n",
       "2022-02-28         0.0     0.000000      0.000000  0.000000e+00     0.0   \n",
       "2022-03-31         0.0     0.000000      0.000000  0.000000e+00     0.0   \n",
       "2022-04-30         0.0     0.000000      0.000000  0.000000e+00     0.0   \n",
       "2022-05-31         0.0  2529.062401      0.000000 -2.529062e+03     0.0   \n",
       "2022-06-30         0.0  2758.783374      0.000000 -5.287846e+03     0.0   \n",
       "2022-07-31         0.0  2770.278305      0.000000 -8.058124e+03     0.0   \n",
       "2022-08-31         0.0  2781.821131      0.000000 -1.083995e+04     0.0   \n",
       "2022-09-30         0.0  2793.412052      0.000000 -1.363336e+04     0.0   \n",
       "2022-10-31         0.0  2805.051269      0.000000 -1.643841e+04     0.0   \n",
       "2022-11-30         0.0  2816.738983      0.000000 -1.925515e+04     0.0   \n",
       "2022-12-31         0.0  2828.475395      0.000000 -2.208362e+04     0.0   \n",
       "2023-01-31         0.0  2840.260709      0.000000 -2.492388e+04     0.0   \n",
       "2023-02-28         0.0  2852.095129      0.000000 -2.777598e+04     0.0   \n",
       "2023-03-31         0.0  2863.978859      0.000000 -3.063996e+04     0.0   \n",
       "2023-04-30         0.0  2875.912104      0.000000 -3.351587e+04     0.0   \n",
       "2023-05-31         0.0   256.316124      0.000000 -3.377219e+04     0.0   \n",
       "2023-06-30         0.0   257.384108  34029.569944 -2.273737e-12     0.0   \n",
       "2023-07-31         0.0     0.000000      0.000000  0.000000e+00     0.0   \n",
       "2023-08-31         0.0     0.000000      0.000000  0.000000e+00     0.0   \n",
       "\n",
       "                tra.IR     trb.ntnl                        trb.fee  \\\n",
       "               amt_add      amt_sub  amt_add       bal_end amt_add   \n",
       "2021-08-31    0.000000     0.000000      0.0      0.000000     0.0   \n",
       "2021-09-30    0.000000     0.000000      0.0      0.000000     0.0   \n",
       "2021-10-31    0.000000  3631.578947      0.0  -3631.578947   600.0   \n",
       "2021-11-30    0.000000  2652.763158      0.0  -6284.342105     0.0   \n",
       "2021-12-31    0.000000  2668.237610      0.0  -8952.579715     0.0   \n",
       "2022-01-31    0.000000  2683.802329      0.0 -11636.382044     0.0   \n",
       "2022-02-28    0.000000  2699.457843      0.0 -14335.839887     0.0   \n",
       "2022-03-31    0.000000  2715.204680      0.0 -17051.044567     0.0   \n",
       "2022-04-30    0.000000  2731.043374      0.0 -19782.087941     0.0   \n",
       "2022-05-31    0.000000   217.912059      0.0 -20000.000000     0.0   \n",
       "2022-06-30   10.537760     0.000000      0.0 -20000.000000     0.0   \n",
       "2022-07-31   22.032691     0.000000      0.0 -20000.000000     0.0   \n",
       "2022-08-31   33.575517     0.000000      0.0 -20000.000000     0.0   \n",
       "2022-09-30   45.166438     0.000000      0.0 -20000.000000     0.0   \n",
       "2022-10-31   56.805655     0.000000      0.0 -20000.000000     0.0   \n",
       "2022-11-30   68.493369     0.000000      0.0 -20000.000000     0.0   \n",
       "2022-12-31   80.229781     0.000000      0.0 -20000.000000     0.0   \n",
       "2023-01-31   92.015095     0.000000      0.0 -20000.000000     0.0   \n",
       "2023-02-28  103.849515     0.000000      0.0 -20000.000000     0.0   \n",
       "2023-03-31  115.733245     0.000000      0.0 -20000.000000     0.0   \n",
       "2023-04-30  127.666490     0.000000      0.0 -20000.000000     0.0   \n",
       "2023-05-31  139.649457     0.000000      0.0 -20000.000000     0.0   \n",
       "2023-06-30  140.717441     0.000000  20000.0      0.000000     0.0   \n",
       "2023-07-31    0.000000     0.000000      0.0      0.000000     0.0   \n",
       "2023-08-31    0.000000     0.000000      0.0      0.000000     0.0   \n",
       "\n",
       "                trb.IR cost_lnd   cost_cstrn         oprtg                \\\n",
       "               amt_add  amt_add      amt_add       amt_add       amt_sub   \n",
       "2021-08-31    0.000000      0.0     0.000000  10000.000000      0.000000   \n",
       "2021-09-30    0.000000      0.0     0.000000      0.000000      0.000000   \n",
       "2021-10-31    0.000000  10000.0  2631.578947   3631.578947  13631.578947   \n",
       "2021-11-30   21.184211      0.0  2631.578947   2652.763158   2652.763158   \n",
       "2021-12-31   36.658662      0.0  2631.578947   2668.237610   2668.237610   \n",
       "2022-01-31   52.223382      0.0  2631.578947   2683.802329   2683.802329   \n",
       "2022-02-28   67.878895      0.0  2631.578947   2699.457843   2699.457843   \n",
       "2022-03-31   83.625733      0.0  2631.578947   2715.204680   2715.204680   \n",
       "2022-04-30   99.464427      0.0  2631.578947   2731.043374   2731.043374   \n",
       "2022-05-31  115.395513      0.0  2631.578947   2746.974460   2746.974460   \n",
       "2022-06-30  116.666667      0.0  2631.578947   2758.783374   2758.783374   \n",
       "2022-07-31  116.666667      0.0  2631.578947   2770.278305   2770.278305   \n",
       "2022-08-31  116.666667      0.0  2631.578947   2781.821131   2781.821131   \n",
       "2022-09-30  116.666667      0.0  2631.578947   2793.412052   2793.412052   \n",
       "2022-10-31  116.666667      0.0  2631.578947   2805.051269   2805.051269   \n",
       "2022-11-30  116.666667      0.0  2631.578947   2816.738983   2816.738983   \n",
       "2022-12-31  116.666667      0.0  2631.578947   2828.475395   2828.475395   \n",
       "2023-01-31  116.666667      0.0  2631.578947   2840.260709   2840.260709   \n",
       "2023-02-28  116.666667      0.0  2631.578947   2852.095129   2852.095129   \n",
       "2023-03-31  116.666667      0.0  2631.578947   2863.978859   2863.978859   \n",
       "2023-04-30  116.666667      0.0  2631.578947   2875.912104   2875.912104   \n",
       "2023-05-31  116.666667      0.0     0.000000    256.316124    256.316124   \n",
       "2023-06-30  116.666667      0.0     0.000000    257.384108  54286.954051   \n",
       "2023-07-31    0.000000      0.0     0.000000      0.000000      0.000000   \n",
       "2023-08-31    0.000000      0.0     0.000000      0.000000      0.000000   \n",
       "\n",
       "                          \n",
       "                 bal_end  \n",
       "2021-08-31  10000.000000  \n",
       "2021-09-30  10000.000000  \n",
       "2021-10-31      0.000000  \n",
       "2021-11-30      0.000000  \n",
       "2021-12-31      0.000000  \n",
       "2022-01-31      0.000000  \n",
       "2022-02-28      0.000000  \n",
       "2022-03-31      0.000000  \n",
       "2022-04-30      0.000000  \n",
       "2022-05-31      0.000000  \n",
       "2022-06-30      0.000000  \n",
       "2022-07-31      0.000000  \n",
       "2022-08-31      0.000000  \n",
       "2022-09-30      0.000000  \n",
       "2022-10-31      0.000000  \n",
       "2022-11-30      0.000000  \n",
       "2022-12-31      0.000000  \n",
       "2023-01-31      0.000000  \n",
       "2023-02-28      0.000000  \n",
       "2023-03-31      0.000000  \n",
       "2023-04-30      0.000000  \n",
       "2023-05-31      0.000000  \n",
       "2023-06-30 -54029.569944  \n",
       "2023-07-31 -54029.569944  \n",
       "2023-08-31 -54029.569944  "
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rslt_df = DataFrame({('equity.ntnl', 'amt_sub'):equity.ntnl.amt_sub[:],\n",
    "                     ('tra.ntnl', 'amt_sub'):tra.ntnl.amt_sub[:],\n",
    "                     ('tra.ntnl', 'amt_add'):tra.ntnl.amt_add[:],\n",
    "                     ('tra.ntnl', 'bal_end'):tra.ntnl.bal_end[:],\n",
    "                     ('tra.fee', 'amt_add'):tra.fee.amt_add[:],\n",
    "                     ('tra.IR', 'amt_add'):tra.IR.amt_add[:],\n",
    "                     ('trb.ntnl', 'amt_sub'):trb.ntnl.amt_sub[:],\n",
    "                     ('trb.ntnl', 'amt_add'):trb.ntnl.amt_add[:],\n",
    "                     ('trb.ntnl', 'bal_end'):trb.ntnl.bal_end[:],\n",
    "                     ('trb.fee', 'amt_add'):trb.fee.amt_add[:],\n",
    "                     ('trb.IR', 'amt_add'):trb.IR.amt_add[:],\n",
    "                     ('cost_lnd', 'amt_add'):cost['lnd'].amt_add[:],\n",
    "                     ('cost_cstrn', 'amt_add'):cost['cstrn'].amt_add[:],\n",
    "                     ('oprtg', 'amt_add'):oprtg.amt_add[:],\n",
    "                     ('oprtg', 'amt_sub'):oprtg.amt_sub[:],\n",
    "                     ('oprtg', 'bal_end'):oprtg.bal_end[:]})\n",
    "rslt_df.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}