210907 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": [
    "# Setting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "40 200\n"
     ]
    }
   ],
   "source": [
    "pd.options.display.max_columns = 40\n",
    "pd.options.display.max_rows = 200\n",
    "print(pd.get_option(\"display.max_columns\"), pd.get_option(\"display.max_rows\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Outline\n",
    "* 주거시설 등 개발과 동시에 분양을 하는 사업의 재무모델\n",
    "* PF대출을 통해 토지비 및 공사비 등 개발사업 자금을 조달\n",
    "* 금융비용을 PF대출금으로 충당\n",
    "* 분양대금의 일부는 사업비로, 일부는 대출금 상환재원으로 사용\n",
    "* PF대출금은 필요에 따라 한도대로 인출"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Input Index Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 기간 Index 설정\n",
    "# prjt index : 사업기간 전 기간에 대한 index로 모델의 base가 되는 index\n",
    "# cstrn index : 공사기간에 대한 index로 착공일로부터 준공일까지의 기간\n",
    "# loan index : 대출기간에 대한 index로 대출 실행일로부터 대출 상환일 까지의 기간\n",
    "# sales index : 분양기간에 대한 index로 분양개시일로부터 분양종료일 까지의 기간\n",
    "idx = cf.PrjtIndex(idxname=['prjt', 'cstrn', 'loan', 'sales'],\n",
    "                   start=['2021-08', '2021-10', '2021-10', '2021-12'],\n",
    "                   periods=[24+1, 18+1, 20+1, 16+1],\n",
    "                   freq='M')\n",
    "\n",
    "# 공정률 Series 설정 : 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 Condition Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "equity = cf.Loan(idx, idx, amt_ntnl=10_000)\n",
    "\n",
    "dct_loan = {}\n",
    "tra = cf.Loan(idx, idx.loan, amt_ntnl=70_000,\n",
    "              rate_fee = 0.01,\n",
    "              rate_IR = 0.05)\n",
    "dct_loan['tra'] = tra\n",
    "\n",
    "trb = cf.Loan(idx, idx.loan, amt_ntnl=20_000,\n",
    "              rate_fee = 0.03,\n",
    "              rate_IR = 0.07)\n",
    "dct_loan['trb'] = trb\n",
    "\n",
    "loan = cf.Merge_loan(dct_loan)\n",
    "loan.tra = loan.dct['tra']\n",
    "loan.trb = loan.dct['trb']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Input Sales Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "dct_sales = {}\n",
    "\n",
    "# 분양매출 및 납입 일정\n",
    "# 분양상품 A #\n",
    "salesA = cf.Account(idx)\n",
    "salesA.amt = 110_000 # Total sales amount\n",
    "salesA.csh_idx = idx.sales[[0, 3, 6, 9, 12, 16]] # cashin schedule\n",
    "salesA.csh_rate = Series([0.1, 0.1, 0.1, 0.1, 0.1, 0.5],\n",
    "                         index=salesA.csh_idx) # cashin rate\n",
    "\n",
    "# 분양상품 B #\n",
    "salesB = cf.Account(idx)\n",
    "salesB.amt = 30_000 # Total sales amount\n",
    "salesB.csh_idx = idx.sales[[1,5,7,10,13,16]] # cashin schedule\n",
    "salesB.csh_rate = Series([0.1, 0.1, 0.1, 0.1, 0.1, 0.5],\n",
    "                         index=salesB.csh_idx) # cashin rate\n",
    "\n",
    "# 분양매출 가정\n",
    "salesA.sls_rate = Series(np.array([0.1, 0.1, 0.1, 0.1, 0.1, 0.1, 0.1, 0.1, 0.1, 0.1]),\n",
    "                       index=idx.sales[[0, 2, 4, 6, 7, 8, 9, 12, 14, 16]])\n",
    "salesA.sls_plan = salesA.amt * salesA.sls_rate\n",
    "\n",
    "salesB.sls_rate = Series(np.array([0.1, 0.1, 0.1, 0.1, 0.1, 0.1, 0.1, 0.1, 0.1, 0.1]),\n",
    "                       index=idx.sales[[1, 3, 5, 7, 10, 11, 12, 13, 15, 16]])\n",
    "salesB.sls_plan = salesB.amt * salesB.sls_rate\n",
    "\n",
    "# Make dictionary\n",
    "dct_sales['salesA'] = salesA\n",
    "dct_sales['salesB'] = salesB\n",
    "sales = cf.Merge(dct_sales)\n",
    "sales.salesA = sales.dct['salesA']\n",
    "sales.salesB = sales.dct['salesB']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Input Cost Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "dct_cost = {}\n",
    "\n",
    "# 토지비: 최초 1회 지급\n",
    "lnd = cf.Account(idx)\n",
    "lnd.addscdd(idx.cstrn[0], 30_000)\n",
    "dct_cost['lnd'] = lnd\n",
    "\n",
    "# 공사비: 공정률에 따라 지급\n",
    "cstrn = cf.Account(idx)\n",
    "cstrn.addscdd(idx.cstrn.index, 80_000 * idx.prcs)\n",
    "dct_cost['cstrn'] = cstrn\n",
    "\n",
    "# 분양수수료: 분양계약 발생에 따라 지급\n",
    "slsfee = cf.Account(idx)\n",
    "slsfee.feerateA = 0.03\n",
    "slsfee.feerateB = 0.05\n",
    "slsfee.addscdd(salesA.sls_plan.index, salesA.sls_plan * slsfee.feerateA)\n",
    "slsfee.addscdd(salesB.sls_plan.index, salesB.sls_plan * slsfee.feerateB)\n",
    "dct_cost['slsfee'] = slsfee\n",
    "\n",
    "# Make dictionary\n",
    "cost = cf.Merge(dct_cost)\n",
    "cost.lnd = cost.dct['lnd']\n",
    "cost.cstrn = cost.dct['cstrn']\n",
    "cost.slsfee = cost.dct['slsfee']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Execution Cash Flow\n",
    "### 1) 사전 설정"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make accounts\n",
    "dct_acc = {}\n",
    "dct_acc['oprtg'] = cf.Account(idx)\n",
    "dct_acc['sales'] = cf.Account(idx)\n",
    "dct_acc['repay'] = cf.Account(idx)\n",
    "\n",
    "acc = cf.Merge(dct_acc)\n",
    "acc.oprtg = acc.dct['oprtg']\n",
    "acc.sales = acc.dct['sales']\n",
    "acc.repay = acc.dct['repay']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2) Cash Flow 실행"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Excute cash flow\n",
    "for idxno in idx.index:\n",
    "    # If it's initial date then set loan withdrawble.\n",
    "    equity.set_wtdrbl_intldate(idxno)\n",
    "    loan.tra.set_wtdrbl_intldate(idxno)\n",
    "    loan.trb.set_wtdrbl_intldate(idxno)\n",
    "    \n",
    "    #### Sales ####\n",
    "    # 분양수입대금 sales schedule에 따라 운영계좌로 이체\n",
    "    slsmngA = cf.sls_mngmnt(idxno, sales.salesA)\n",
    "    slsmngA.make_sls_plan()\n",
    "    slsmngA.rcv_slsamt(acc.sales)\n",
    "    \n",
    "    slsmngB = cf.sls_mngmnt(idxno, sales.salesB)\n",
    "    slsmngB.make_sls_plan()\n",
    "    slsmngB.rcv_slsamt(acc.sales)\n",
    "    \n",
    "    # 분양수입금 분배\n",
    "    intl_bal_end = acc.sales.bal_end[idxno]\n",
    "    amt_to_oprtg = intl_bal_end * 0.2\n",
    "    amt_to_repay = intl_bal_end * 0.8\n",
    "    acc.sales.send(idxno, amt_to_oprtg, acc.oprtg)\n",
    "    acc.sales.send(idxno, amt_to_repay, acc.repay)\n",
    "    \n",
    "    # cost instance 생성, 조달금액 계산 후 대출인출액을 운영계좌 입금\n",
    "    cstmng = cf.cst_mngmnt(idxno, cost, loan)\n",
    "    wtdrw = cf.wtdrw_mngmnt(idxno, cstmng, acc.oprtg)\n",
    "    wtdrw.wtdrw_equity(equity)\n",
    "    wtdrw.wtdrw_loan(loan.trb)\n",
    "    wtdrw.wtdrw_loan(loan.tra)\n",
    "    \n",
    "    # 운영계좌에서 토지비, 공사비 등 각종 비용 지출\n",
    "    cstmng.pay_oprtcst(acc.oprtg)\n",
    "    \n",
    "    # 운영계좌에서 각종 금융비용 지출\n",
    "    cstmng.pay_fnclcst(acc.oprtg)\n",
    "    \n",
    "    # 상환계좌 잔액으로 대출금 상환\n",
    "    rpymngA = cf.repay_mngmnt(idxno, loan.tra)\n",
    "    rpymngA.trsf_rpy(acc.oprtg, acc.repay)\n",
    "    rpymngA.trsf_oprtg(acc.repay, acc.oprtg)\n",
    "    rpymngA.rpy_ntnl(acc.repay)\n",
    "    \n",
    "    if rpymngA.check_repaid():\n",
    "        rpymngB = cf.repay_mngmnt(idxno, loan.trb)\n",
    "        rpymngB.trsf_rpy(acc.oprtg, acc.repay)\n",
    "        rpymngB.trsf_oprtg(acc.repay, acc.oprtg, is_repaid=rpymngB.check_repaid())\n",
    "        rpymngB.rpy_ntnl(acc.repay)\n",
    "    \n",
    "    # If it was maturity date then set back loan unwithdrawble.\n",
    "    equity.setback_wtdrbl_mtrt(idxno)\n",
    "    loan.tra.setback_wtdrbl_mtrt(idxno)\n",
    "    loan.trb.setback_wtdrbl_mtrt(idxno)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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 colspan=\"2\" halign=\"left\">salesA</th>\n",
       "      <th colspan=\"2\" halign=\"left\">salesB</th>\n",
       "      <th>equity.ntnl</th>\n",
       "      <th>tra.ntnl</th>\n",
       "      <th>trb.ntnl</th>\n",
       "      <th>oprtg</th>\n",
       "      <th>repay</th>\n",
       "      <th>tra.fee</th>\n",
       "      <th>tra.IR</th>\n",
       "      <th>trb.fee</th>\n",
       "      <th>trb.IR</th>\n",
       "      <th>cost_lnd</th>\n",
       "      <th>cost_cstrn</th>\n",
       "      <th>cost_slsfee</th>\n",
       "      <th colspan=\"2\" halign=\"left\">oprtg</th>\n",
       "      <th colspan=\"2\" halign=\"left\">repay</th>\n",
       "      <th>tra.ntnl</th>\n",
       "      <th>trb.ntnl</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>amt_add</th>\n",
       "      <th>amt_sub</th>\n",
       "      <th>amt_add</th>\n",
       "      <th>amt_sub</th>\n",
       "      <th>amt_sub</th>\n",
       "      <th>amt_sub</th>\n",
       "      <th>amt_sub</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_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",
       "      <th>amt_sub</th>\n",
       "      <th>bal_end</th>\n",
       "      <th>amt_add</th>\n",
       "      <th>amt_add</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2021-08-31</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10,000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10,000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10,000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-30</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10,000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-10-31</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5,511</td>\n",
       "      <td>20,000</td>\n",
       "      <td>25,511</td>\n",
       "      <td>0</td>\n",
       "      <td>700</td>\n",
       "      <td>0</td>\n",
       "      <td>600</td>\n",
       "      <td>0</td>\n",
       "      <td>30,000</td>\n",
       "      <td>4,211</td>\n",
       "      <td>0</td>\n",
       "      <td>35,511</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-11-30</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4,350</td>\n",
       "      <td>0</td>\n",
       "      <td>4,350</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>23</td>\n",
       "      <td>0</td>\n",
       "      <td>117</td>\n",
       "      <td>0</td>\n",
       "      <td>4,211</td>\n",
       "      <td>0</td>\n",
       "      <td>4,350</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12-31</th>\n",
       "      <td>11,000</td>\n",
       "      <td>1,100</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4,478</td>\n",
       "      <td>0</td>\n",
       "      <td>5,578</td>\n",
       "      <td>880</td>\n",
       "      <td>0</td>\n",
       "      <td>41</td>\n",
       "      <td>0</td>\n",
       "      <td>117</td>\n",
       "      <td>0</td>\n",
       "      <td>4,211</td>\n",
       "      <td>330</td>\n",
       "      <td>4,698</td>\n",
       "      <td>880</td>\n",
       "      <td>880</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-01-31</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3,000</td>\n",
       "      <td>300</td>\n",
       "      <td>0</td>\n",
       "      <td>3,597</td>\n",
       "      <td>0</td>\n",
       "      <td>3,897</td>\n",
       "      <td>240</td>\n",
       "      <td>0</td>\n",
       "      <td>60</td>\n",
       "      <td>0</td>\n",
       "      <td>117</td>\n",
       "      <td>0</td>\n",
       "      <td>4,211</td>\n",
       "      <td>150</td>\n",
       "      <td>4,537</td>\n",
       "      <td>240</td>\n",
       "      <td>240</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-02-28</th>\n",
       "      <td>11,000</td>\n",
       "      <td>1,100</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4,272</td>\n",
       "      <td>0</td>\n",
       "      <td>5,372</td>\n",
       "      <td>880</td>\n",
       "      <td>0</td>\n",
       "      <td>75</td>\n",
       "      <td>0</td>\n",
       "      <td>117</td>\n",
       "      <td>0</td>\n",
       "      <td>4,211</td>\n",
       "      <td>330</td>\n",
       "      <td>4,732</td>\n",
       "      <td>880</td>\n",
       "      <td>880</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-03-31</th>\n",
       "      <td>0</td>\n",
       "      <td>2,200</td>\n",
       "      <td>3,000</td>\n",
       "      <td>300</td>\n",
       "      <td>0</td>\n",
       "      <td>3,190</td>\n",
       "      <td>0</td>\n",
       "      <td>5,690</td>\n",
       "      <td>2,000</td>\n",
       "      <td>0</td>\n",
       "      <td>93</td>\n",
       "      <td>0</td>\n",
       "      <td>117</td>\n",
       "      <td>0</td>\n",
       "      <td>4,211</td>\n",
       "      <td>150</td>\n",
       "      <td>4,570</td>\n",
       "      <td>2,000</td>\n",
       "      <td>2,000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-04-30</th>\n",
       "      <td>11,000</td>\n",
       "      <td>2,200</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2,323</td>\n",
       "      <td>0</td>\n",
       "      <td>4,523</td>\n",
       "      <td>1,760</td>\n",
       "      <td>0</td>\n",
       "      <td>106</td>\n",
       "      <td>0</td>\n",
       "      <td>117</td>\n",
       "      <td>0</td>\n",
       "      <td>4,211</td>\n",
       "      <td>330</td>\n",
       "      <td>4,763</td>\n",
       "      <td>1,760</td>\n",
       "      <td>1,760</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-05-31</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3,000</td>\n",
       "      <td>1,200</td>\n",
       "      <td>0</td>\n",
       "      <td>2,593</td>\n",
       "      <td>0</td>\n",
       "      <td>3,793</td>\n",
       "      <td>960</td>\n",
       "      <td>0</td>\n",
       "      <td>116</td>\n",
       "      <td>0</td>\n",
       "      <td>117</td>\n",
       "      <td>0</td>\n",
       "      <td>4,211</td>\n",
       "      <td>150</td>\n",
       "      <td>4,593</td>\n",
       "      <td>960</td>\n",
       "      <td>960</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-06-30</th>\n",
       "      <td>11,000</td>\n",
       "      <td>6,600</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2,503</td>\n",
       "      <td>0</td>\n",
       "      <td>9,103</td>\n",
       "      <td>5,280</td>\n",
       "      <td>0</td>\n",
       "      <td>126</td>\n",
       "      <td>0</td>\n",
       "      <td>117</td>\n",
       "      <td>0</td>\n",
       "      <td>4,211</td>\n",
       "      <td>330</td>\n",
       "      <td>4,783</td>\n",
       "      <td>5,280</td>\n",
       "      <td>5,280</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-07-31</th>\n",
       "      <td>11,000</td>\n",
       "      <td>3,300</td>\n",
       "      <td>3,000</td>\n",
       "      <td>1,800</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5,100</td>\n",
       "      <td>4,080</td>\n",
       "      <td>0</td>\n",
       "      <td>137</td>\n",
       "      <td>0</td>\n",
       "      <td>117</td>\n",
       "      <td>0</td>\n",
       "      <td>4,211</td>\n",
       "      <td>480</td>\n",
       "      <td>4,944</td>\n",
       "      <td>5,436</td>\n",
       "      <td>4,080</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-08-31</th>\n",
       "      <td>11,000</td>\n",
       "      <td>3,300</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3,300</td>\n",
       "      <td>2,640</td>\n",
       "      <td>0</td>\n",
       "      <td>137</td>\n",
       "      <td>0</td>\n",
       "      <td>117</td>\n",
       "      <td>0</td>\n",
       "      <td>4,211</td>\n",
       "      <td>330</td>\n",
       "      <td>4,794</td>\n",
       "      <td>3,942</td>\n",
       "      <td>2,640</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-09-30</th>\n",
       "      <td>11,000</td>\n",
       "      <td>11,000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>11,000</td>\n",
       "      <td>8,800</td>\n",
       "      <td>0</td>\n",
       "      <td>137</td>\n",
       "      <td>0</td>\n",
       "      <td>117</td>\n",
       "      <td>0</td>\n",
       "      <td>4,211</td>\n",
       "      <td>330</td>\n",
       "      <td>4,794</td>\n",
       "      <td>10,148</td>\n",
       "      <td>8,800</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-10-31</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3,000</td>\n",
       "      <td>2,400</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2,400</td>\n",
       "      <td>1,920</td>\n",
       "      <td>0</td>\n",
       "      <td>137</td>\n",
       "      <td>0</td>\n",
       "      <td>117</td>\n",
       "      <td>0</td>\n",
       "      <td>4,211</td>\n",
       "      <td>150</td>\n",
       "      <td>4,614</td>\n",
       "      <td>7,934</td>\n",
       "      <td>1,920</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-11-30</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3,000</td>\n",
       "      <td>1,200</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1,200</td>\n",
       "      <td>960</td>\n",
       "      <td>0</td>\n",
       "      <td>137</td>\n",
       "      <td>0</td>\n",
       "      <td>117</td>\n",
       "      <td>0</td>\n",
       "      <td>4,211</td>\n",
       "      <td>150</td>\n",
       "      <td>4,614</td>\n",
       "      <td>4,520</td>\n",
       "      <td>960</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-12-31</th>\n",
       "      <td>11,000</td>\n",
       "      <td>13,200</td>\n",
       "      <td>3,000</td>\n",
       "      <td>1,200</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>9,663</td>\n",
       "      <td>11,520</td>\n",
       "      <td>0</td>\n",
       "      <td>137</td>\n",
       "      <td>0</td>\n",
       "      <td>117</td>\n",
       "      <td>0</td>\n",
       "      <td>4,211</td>\n",
       "      <td>480</td>\n",
       "      <td>4,944</td>\n",
       "      <td>9,240</td>\n",
       "      <td>11,520</td>\n",
       "      <td>0</td>\n",
       "      <td>4,737</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-01-31</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3,000</td>\n",
       "      <td>3,600</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>720</td>\n",
       "      <td>2,880</td>\n",
       "      <td>0</td>\n",
       "      <td>117</td>\n",
       "      <td>0</td>\n",
       "      <td>117</td>\n",
       "      <td>0</td>\n",
       "      <td>4,211</td>\n",
       "      <td>150</td>\n",
       "      <td>4,594</td>\n",
       "      <td>5,365</td>\n",
       "      <td>2,880</td>\n",
       "      <td>0</td>\n",
       "      <td>2,880</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-02-28</th>\n",
       "      <td>11,000</td>\n",
       "      <td>5,500</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1,100</td>\n",
       "      <td>4,400</td>\n",
       "      <td>0</td>\n",
       "      <td>105</td>\n",
       "      <td>0</td>\n",
       "      <td>117</td>\n",
       "      <td>0</td>\n",
       "      <td>4,211</td>\n",
       "      <td>330</td>\n",
       "      <td>4,762</td>\n",
       "      <td>1,703</td>\n",
       "      <td>4,400</td>\n",
       "      <td>0</td>\n",
       "      <td>4,400</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-03-31</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3,000</td>\n",
       "      <td>1,500</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>300</td>\n",
       "      <td>1,200</td>\n",
       "      <td>0</td>\n",
       "      <td>87</td>\n",
       "      <td>0</td>\n",
       "      <td>117</td>\n",
       "      <td>0</td>\n",
       "      <td>4,211</td>\n",
       "      <td>150</td>\n",
       "      <td>4,564</td>\n",
       "      <td>-2,561</td>\n",
       "      <td>1,200</td>\n",
       "      <td>0</td>\n",
       "      <td>1,200</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-04-30</th>\n",
       "      <td>11,000</td>\n",
       "      <td>60,500</td>\n",
       "      <td>3,000</td>\n",
       "      <td>16,500</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>15,400</td>\n",
       "      <td>61,600</td>\n",
       "      <td>0</td>\n",
       "      <td>82</td>\n",
       "      <td>0</td>\n",
       "      <td>117</td>\n",
       "      <td>0</td>\n",
       "      <td>4,211</td>\n",
       "      <td>480</td>\n",
       "      <td>4,889</td>\n",
       "      <td>7,951</td>\n",
       "      <td>39,600</td>\n",
       "      <td>22,000</td>\n",
       "      <td>19,600</td>\n",
       "      <td>20,000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-05-31</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>22,000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>29,951</td>\n",
       "      <td>22,000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-06-30</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>29,951</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-07-31</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>29,951</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-08-31</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>29,951</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            salesA          salesB         equity.ntnl tra.ntnl trb.ntnl  \\\n",
       "           amt_add amt_sub amt_add amt_sub     amt_sub  amt_sub  amt_sub   \n",
       "2021-08-31       0       0       0       0      10,000        0        0   \n",
       "2021-09-30       0       0       0       0           0        0        0   \n",
       "2021-10-31       0       0       0       0           0    5,511   20,000   \n",
       "2021-11-30       0       0       0       0           0    4,350        0   \n",
       "2021-12-31  11,000   1,100       0       0           0    4,478        0   \n",
       "2022-01-31       0       0   3,000     300           0    3,597        0   \n",
       "2022-02-28  11,000   1,100       0       0           0    4,272        0   \n",
       "2022-03-31       0   2,200   3,000     300           0    3,190        0   \n",
       "2022-04-30  11,000   2,200       0       0           0    2,323        0   \n",
       "2022-05-31       0       0   3,000   1,200           0    2,593        0   \n",
       "2022-06-30  11,000   6,600       0       0           0    2,503        0   \n",
       "2022-07-31  11,000   3,300   3,000   1,800           0        0        0   \n",
       "2022-08-31  11,000   3,300       0       0           0        0        0   \n",
       "2022-09-30  11,000  11,000       0       0           0        0        0   \n",
       "2022-10-31       0       0   3,000   2,400           0        0        0   \n",
       "2022-11-30       0       0   3,000   1,200           0        0        0   \n",
       "2022-12-31  11,000  13,200   3,000   1,200           0        0        0   \n",
       "2023-01-31       0       0   3,000   3,600           0        0        0   \n",
       "2023-02-28  11,000   5,500       0       0           0        0        0   \n",
       "2023-03-31       0       0   3,000   1,500           0        0        0   \n",
       "2023-04-30  11,000  60,500   3,000  16,500           0        0        0   \n",
       "2023-05-31       0       0       0       0           0        0        0   \n",
       "2023-06-30       0       0       0       0           0        0        0   \n",
       "2023-07-31       0       0       0       0           0        0        0   \n",
       "2023-08-31       0       0       0       0           0        0        0   \n",
       "\n",
       "             oprtg   repay tra.fee  tra.IR trb.fee  trb.IR cost_lnd  \\\n",
       "           amt_add amt_add amt_add amt_add amt_add amt_add  amt_add   \n",
       "2021-08-31  10,000       0       0       0       0       0        0   \n",
       "2021-09-30       0       0       0       0       0       0        0   \n",
       "2021-10-31  25,511       0     700       0     600       0   30,000   \n",
       "2021-11-30   4,350       0       0      23       0     117        0   \n",
       "2021-12-31   5,578     880       0      41       0     117        0   \n",
       "2022-01-31   3,897     240       0      60       0     117        0   \n",
       "2022-02-28   5,372     880       0      75       0     117        0   \n",
       "2022-03-31   5,690   2,000       0      93       0     117        0   \n",
       "2022-04-30   4,523   1,760       0     106       0     117        0   \n",
       "2022-05-31   3,793     960       0     116       0     117        0   \n",
       "2022-06-30   9,103   5,280       0     126       0     117        0   \n",
       "2022-07-31   5,100   4,080       0     137       0     117        0   \n",
       "2022-08-31   3,300   2,640       0     137       0     117        0   \n",
       "2022-09-30  11,000   8,800       0     137       0     117        0   \n",
       "2022-10-31   2,400   1,920       0     137       0     117        0   \n",
       "2022-11-30   1,200     960       0     137       0     117        0   \n",
       "2022-12-31   9,663  11,520       0     137       0     117        0   \n",
       "2023-01-31     720   2,880       0     117       0     117        0   \n",
       "2023-02-28   1,100   4,400       0     105       0     117        0   \n",
       "2023-03-31     300   1,200       0      87       0     117        0   \n",
       "2023-04-30  15,400  61,600       0      82       0     117        0   \n",
       "2023-05-31  22,000       0       0       0       0       0        0   \n",
       "2023-06-30       0       0       0       0       0       0        0   \n",
       "2023-07-31       0       0       0       0       0       0        0   \n",
       "2023-08-31       0       0       0       0       0       0        0   \n",
       "\n",
       "           cost_cstrn cost_slsfee   oprtg           repay         tra.ntnl  \\\n",
       "              amt_add     amt_add amt_sub bal_end amt_sub bal_end  amt_add   \n",
       "2021-08-31          0           0       0  10,000       0       0        0   \n",
       "2021-09-30          0           0       0  10,000       0       0        0   \n",
       "2021-10-31      4,211           0  35,511       0       0       0        0   \n",
       "2021-11-30      4,211           0   4,350       0       0       0        0   \n",
       "2021-12-31      4,211         330   4,698     880     880       0        0   \n",
       "2022-01-31      4,211         150   4,537     240     240       0        0   \n",
       "2022-02-28      4,211         330   4,732     880     880       0        0   \n",
       "2022-03-31      4,211         150   4,570   2,000   2,000       0        0   \n",
       "2022-04-30      4,211         330   4,763   1,760   1,760       0        0   \n",
       "2022-05-31      4,211         150   4,593     960     960       0        0   \n",
       "2022-06-30      4,211         330   4,783   5,280   5,280       0        0   \n",
       "2022-07-31      4,211         480   4,944   5,436   4,080       0        0   \n",
       "2022-08-31      4,211         330   4,794   3,942   2,640       0        0   \n",
       "2022-09-30      4,211         330   4,794  10,148   8,800       0        0   \n",
       "2022-10-31      4,211         150   4,614   7,934   1,920       0        0   \n",
       "2022-11-30      4,211         150   4,614   4,520     960       0        0   \n",
       "2022-12-31      4,211         480   4,944   9,240  11,520       0    4,737   \n",
       "2023-01-31      4,211         150   4,594   5,365   2,880       0    2,880   \n",
       "2023-02-28      4,211         330   4,762   1,703   4,400       0    4,400   \n",
       "2023-03-31      4,211         150   4,564  -2,561   1,200       0    1,200   \n",
       "2023-04-30      4,211         480   4,889   7,951  39,600  22,000   19,600   \n",
       "2023-05-31          0           0       0  29,951  22,000       0        0   \n",
       "2023-06-30          0           0       0  29,951       0       0        0   \n",
       "2023-07-31          0           0       0  29,951       0       0        0   \n",
       "2023-08-31          0           0       0  29,951       0       0        0   \n",
       "\n",
       "           trb.ntnl  \n",
       "            amt_add  \n",
       "2021-08-31        0  \n",
       "2021-09-30        0  \n",
       "2021-10-31        0  \n",
       "2021-11-30        0  \n",
       "2021-12-31        0  \n",
       "2022-01-31        0  \n",
       "2022-02-28        0  \n",
       "2022-03-31        0  \n",
       "2022-04-30        0  \n",
       "2022-05-31        0  \n",
       "2022-06-30        0  \n",
       "2022-07-31        0  \n",
       "2022-08-31        0  \n",
       "2022-09-30        0  \n",
       "2022-10-31        0  \n",
       "2022-11-30        0  \n",
       "2022-12-31        0  \n",
       "2023-01-31        0  \n",
       "2023-02-28        0  \n",
       "2023-03-31        0  \n",
       "2023-04-30   20,000  \n",
       "2023-05-31        0  \n",
       "2023-06-30        0  \n",
       "2023-07-31        0  \n",
       "2023-08-31        0  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 현금흐름 요약\n",
    "rslt_df = DataFrame({('salesA', 'amt_add'):sales.salesA.amt_add[:],\n",
    "                     ('salesA', 'amt_sub'):sales.salesA.amt_sub[:],\n",
    "                     ('salesB', 'amt_add'):sales.salesB.amt_add[:],\n",
    "                     ('salesB', 'amt_sub'):sales.salesB.amt_sub[:],\n",
    "                     ('equity.ntnl', 'amt_sub'):equity.ntnl.amt_sub[:],\n",
    "                     ('tra.ntnl', 'amt_sub'):loan.tra.ntnl.amt_sub[:],\n",
    "                     ('trb.ntnl', 'amt_sub'):loan.trb.ntnl.amt_sub[:],\n",
    "                     ('oprtg', 'amt_add'):acc.oprtg.amt_add[:],\n",
    "                     ('repay', 'amt_add'):acc.repay.amt_add[:],\n",
    "                     ('tra.fee', 'amt_add'):loan.tra.fee.amt_add[:],\n",
    "                     ('tra.IR', 'amt_add'):loan.tra.IR.amt_add[:],\n",
    "                     ('trb.fee', 'amt_add'):loan.trb.fee.amt_add[:],\n",
    "                     ('trb.IR', 'amt_add'):loan.trb.IR.amt_add[:],\n",
    "                     ('cost_lnd', 'amt_add'):cost['lnd'].amt_add[:],\n",
    "                     ('cost_cstrn', 'amt_add'):cost['cstrn'].amt_add[:],\n",
    "                     ('cost_slsfee', 'amt_add'):cost['slsfee'].amt_add[:],\n",
    "                     ('oprtg', 'amt_sub'):acc.oprtg.amt_sub[:],\n",
    "                     ('oprtg', 'bal_end'):acc.oprtg.bal_end[:],\n",
    "                     ('repay', 'amt_sub'):acc.repay.amt_sub[:],\n",
    "                     ('repay', 'bal_end'):acc.repay.bal_end[:],\n",
    "                     ('tra.ntnl', 'amt_add'):loan.tra.ntnl.amt_add[:],\n",
    "                     ('trb.ntnl', 'amt_add'):loan.trb.ntnl.amt_add[:],\n",
    "                     })\n",
    "rslt_df.fillna(0).applymap(lambda x: f\"{x:,.0f}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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
}