annuity.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Step 0. System setup\n",
    "import sys\n",
    "import glob\n",
    "import os\n",
    "import platform\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "pd.set_option('display.max_rows', 500)\n",
    "pd.set_option('display.max_columns', 500)\n",
    "# if platform.system() == 'Windows':\n",
    "#     server = '\\\\courageous'\n",
    "# else:\n",
    "#     server = '//Volumes'\n",
    "# datapath = os.path.join('/Users', 'wenlianghou', 'Dropbox', 'BC Work', 'mortality', 'SSA2019')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    " # Step 1. Load data\n",
    "# cohort_file = os.path.join(datapath, \"cohort.xlsx\")\n",
    "\n",
    "df_male = pd.read_excel(\"cohort.xlsx\", index_col=0,sheet_name='male')\n",
    "df_female = pd.read_excel(\"cohort.xlsx\", index_col=0,sheet_name='female')\n",
    "year_min = df_male.index.min()\n",
    "year_max = df_male.index.max()\n",
    "age_min = df_male.columns.min()\n",
    "age_max = df_male.columns.max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Step 2. Given interest and sex, calculate Annuity Factor\n",
    "# interest = 5%\n",
    "# male\n",
    "r = 0.045\n",
    "df_male_af5 = df_male\n",
    "# AF calculation\n",
    "df_mort = df_male\n",
    "for birthyear in range(year_min,year_max+1):\n",
    "    for age in range(age_min,age_max+1):\n",
    "        # calc AF\n",
    "        df_calc = df_mort.iloc[birthyear - year_min,age:].to_frame(name = 'q')\n",
    "        df_calc['p'] = 1 - df_calc.q\n",
    "        df_calc['cump'] = df_calc.p.cumprod(axis = 0)\n",
    "        df_calc['discount'] = 1/ (1 + r)\n",
    "        df_calc['discount'] = df_calc.discount.cumprod(axis = 0)\n",
    "        df_calc['epv'] = df_calc.cump * df_calc.discount\n",
    "        af = (df_calc.epv.sum())\n",
    "        af = 1/af\n",
    "        df_male_af5.iloc[birthyear - year_min,age] = af\n",
    "%time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_male_af5.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# with pd.ExcelWriter('annuity_male_' + str(r) + '_.xlsx') as writer:\n",
    "#     df_male_af5.to_excel(writer, sheet_name=str(r))\n",
    "\n",
    "# df_female_af5.to_csv('annuity_female_' + str(r) + '_.csv')\n",
    "\n",
    "# with pd.ExcelWriter('annuity_female_total.xlsx') as writer:\n",
    "#     for f in glob.glob(os.path.join('/Users/jacob.chuslo/BC/female', '*.csv')):\n",
    "#         df = pd.read_csv(f)\n",
    "#         df.to_excel(writer, sheet_name=os.path.basename(f))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# with pd.ExcelWriter('annuity_male_' + str(r) + '_.xlsx') as writer:\n",
    "#     df_male_af5.to_excel(writer, sheet_name=str(r))\n",
    "    \n",
    "# with pd.ExcelWriter(path, mode='a') as writer:\n",
    "#     s.to_excel(writer, sheet_name='another sheet', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### overwrite excel sheet test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta = np.random.rand(1,3)\n",
    "dta = dta.T\n",
    "dta = pd.DataFrame(data=dta)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import load_workbook\n",
    "\n",
    "with pd.ExcelWriter('NEW.xlsx', engine='openpyxl', mode='a') as writer:\n",
    "    dta.to_excel(writer, sheet_name='num3')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta = np.random.rand(1,4)\n",
    "dta = dta.T\n",
    "dta = pd.DataFrame(data=dta)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#OVERWRITE WITHOUT DISRUPTING\n",
    "\n",
    "book = load_workbook('NEW.xlsx')\n",
    "writer = pd.ExcelWriter('NEW.xlsx', engine='openpyxl') \n",
    "writer.book = book\n",
    "writer.sheets = dict((ws.title, ws) for ws in book.worksheets)\n",
    "\n",
    "# using a new dataframe...\n",
    "dta.to_excel(writer, \"num3\") # other options: startrow = num, startcol = num\n",
    "writer.save()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Cumulative survival of male age 65 onwards at present day"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Optimal number of simulations at 95% and 90% confidence intervals"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "import glob\n",
    "import os\n",
    "import platform\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import math"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel(\"cohort.xlsx\", sheet_name=\"to_read\", header=0, index_col=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "age = df['age'].to_numpy()\n",
    "dead = df['dead at age'].to_numpy()\n",
    "dead = dead.cumsum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.plot(dead,age)\n",
    "# You can see that this is an \"inverted CDF\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# dead = dead.cumsum()\n",
    "\n",
    "draws = np.random.uniform(0,1,1450)\n",
    "\n",
    "values = np.interp(draws, dead, age)\n",
    "\n",
    "values[:10]\n",
    "\n",
    "values = np.ceil(values)\n",
    "\n",
    "values[:10]\n",
    "\n",
    "mean = values.mean()\n",
    "var = values.var()\n",
    "\n",
    "# Optimal number of simulation trials\n",
    "\n",
    "#95% confidence\n",
    "n1 = ( (1.96/0.05)*( var/mean ) )**2\n",
    "\n",
    "#90% confidence\n",
    "n2 = ( (1.645/0.05)*( var/mean ) )**2\n",
    "\n",
    "print(len(values))\n",
    "print(mean, var)\n",
    "print(n1,n2)"
   ]
  },
  {
   "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
}