IFRS17 waterfall chart notebook¶
To run this notebook and get all the outputs below, Go to the Cell menu above, and then click Run All.
About this notebook¶
This noteook demonstrates the usage of ifrs17sim project in lifelib, by building and running a model and drawing waterfall graphs that depict how balancesheet items change and cashflows emerge over one or multiple periods using cashflows of a single modelpoint.
Those charts are: * Present value of expected cashflows * CSM amortization * Actual cashflows * IFRS17 Financial performance
under the senario that actual cashflows are equal to expected.
Warning:
The primary purpose of this model is to showcase the capability of lifelib and its base system modelx, and less attention has been paid to the accuracy of the model or the compliance with the accounting standards. At very least, following items are identified as over-simplification or lack of implementation.
The timing of cashflows is either the beginning or end of each step.
All expenses are included in insurance cashflows.
Loss component logic is not yet incorporated, so
CSM_Unflooredcan be negative.Coverage unit is set to sum assured
Amortization of acquisition cash flows is not yet implemented.
All insurance cashflows are considered non-market sensitive, i.e. no TVOG is considered.
Risk adjustment is not yet modeled.
How to use Jupyter Notebook¶
Jupter notebook enables you to run a Python script piece by piece. You can run each piece of code (called a “cell”) by putting the cursor in the cell and pressing Shift + Enter, and get the output right below the input code of the cell.
If you want to learn more about Jupyter Notebook, this tutorial will help you. There are also plenty of other resources on the internet as Jupyter Notebook is quite popular.
Initial set-up¶
The first line %matplotlib notebook, is for specifying drawing mode.
The next few lines are import statements, by which functions defined in other modules become avaialbe in this script.
ifrs17sim and draw_charts modules are in the project directory
of this project. To see what fiels are in the project directory, select
Open from the File menu in the tool bar above.
Building the model¶
The next line is to create a model from build function defined in
ifrs17sim module which has just been imported.
By supplying True to load_saved parameter of the build
function, the input data is read from ifrs17sim.mx, the ‘pickled’
file to save loading time. To read input from input.xlsm, call
build with load_saved=False or without any parameter because
False is the default value of load_saved.
If you run this code multiple time, the previous model is renamed to
ifrs17sim_BAK*, and a new model is created and returned as
model.
In model thre is a space called OuterProjection and other
spaces. OuterProjection is parametrized by Policy ID, i.e. each of
the spaces with parameters corresponds to a projection of one policy.
For example, model.OuterProjection[1] return the projection of
policy ID 1, model.OuterProjection[171] return the projection of
policy ID 171.
The first line below sets proj as a shorthand for the projection of
Policy ID 1. To see what cells are in proj, execute proj.cells
in an empty cell.
proj.cells
You can change the sample policy to output by supplying some other ID.
In [2]:
model = ifrs17sim.build(True)
proj = model.OuterProjection[1]
Present value of expected cashflows¶
The code below generate a waterfall chart that shows how the present value of expected insurance cashflows unwinds over time. No adjustment is made to match actual in-force and no changes in assumptions are made.
In [3]:
data = collections.OrderedDict()
for cells in ['PV_FutureCashflow',
'ExpectedPremium',
'ExpectedInterestCashflow',
'ExpectedAcqCashflow',
'ExpectedClaims',
'ExpectedExps']:
data[cells] = [proj.cells[cells](t) for t in range(3)]
estcf = pd.DataFrame(data)
for inflow in ['ExpectedPremium']:
estcf[inflow] = -1 * estcf[inflow]
plt.figure()
draw_waterfall(estcf, title='Expected Cashflows')
Out[3]:
<matplotlib.axes._subplots.AxesSubplot at 0x3e2f870>
CSM amortization¶
The CSM amortization chart below depicts items that increase/decrease CSM balance under the senario that actual cashflows are equal to expected. The chart below is explained in more details in another notebook.
In [4]:
data = collections.OrderedDict()
for cells in ['CSM_Unfloored',
'IntAccrCSM',
'AdjCSM_FulCashFlows',
'TransServices']:
data[cells] = [proj.cells[cells](t) for t in range(3)]
df = pd.DataFrame(data)
df['TransServices'] = -1 * df['TransServices']
plt.figure()
draw_waterfall(df, title='CSM Amortization')
Out[4]:
<matplotlib.axes._subplots.AxesSubplot at 0x161b5330>
Actual cashflows¶
The code below generate a waterfall chart that simulate actual insurance cashflows that are assumed to be equal to the expected. The net cashflow of each period is withheld as accumulated cashflow and credited with interests at the same rate as the discount rate.
In [5]:
data = collections.OrderedDict()
for cells in ['prj_AccumCashflow',
'prj_incm_Premium',
'prj_InterestAccumCashflow',
'prj_exps_AcqTotal',
'prj_bnft_Total',
'prj_exps_MaintTotal']:
data[cells] = [proj.cells[cells](t) for t in range(3)]
actcf = pd.DataFrame(data)
for outflow in ['prj_exps_AcqTotal',
'prj_bnft_Total',
'prj_exps_MaintTotal']:
actcf[outflow] = -1 * actcf[outflow]
plt.figure()
draw_waterfall(actcf, title='Actual Cashflows')
Out[5]:
<matplotlib.axes._subplots.AxesSubplot at 0x1621e030>
IFRS17 Financial performance¶
The chart below simulates P&L accounts based on IFRS17 from the
background data used to draw the charts above. The profit in each period
is retained and accumulated as NetBalance
In [6]:
data = collections.OrderedDict()
for cells in ['NetBalance',
'InsRevenue',
'InsServiceExps',
'InsFinanceIncomeExps']:
data[cells] = [proj.cells[cells](t) for t in range(5)]
ifrspl = pd.DataFrame(data)
for pl in ['InsServiceExps']:
ifrspl[pl] = -1 * ifrspl[pl]
plt.figure()
draw_waterfall(ifrspl, title='IFRS17 Profit/Loss')
Out[6]:
<matplotlib.axes._subplots.AxesSubplot at 0x164ad6f0>
