IFRS17 CSM Waterfall Chart Notebook¶

About this notebook¶

This noteook demonstrates the usage of ifrs17sim project in lifelib, by building and running a model and drawing a waterfall graph of CSM amortization on a single modelpoint.

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.

The entire script¶

Below is the entire script of this example. The enire scipt is broken down to several parts in differenc cells, and each part is explained below. The pieces of code in cells below are executable one after another from the top.

%matplotlib notebook
import collections
import pandas as pd
from draw_charts import draw_waterfall
import ifrs17sim

model = ifrs17sim.build()

proj = model.OuterProjection[1]
proj.CSM_Unfloored(15)

data = collections.OrderedDict()
for cells in ['CSM_Unfloored',
              'IntAccrCSM',
              'AdjCSM_FulCashFlows',
              'TransServices']:
    data[cells] = [proj.cells[cells](t) for t in range(15)]

df = pd.DataFrame(data)
df['TransServices'] = -1 * df['TransServices']

draw_waterfall(df)

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 [2]:

model = ifrs17sim.build(load_saved=True)

To see what space is inside model, execute model.spaces in an empty cell.

model.spaces

Calculating CSM¶

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 [3]:

proj = model.OuterProjection[1]

Exporting values into DataFrame¶

The code below is to construct a DataFrame object containing data for drawing the waterfall chart, from the cells that make up bars in the waterfall chart.

The values of the cells are first imported to an ordered dictonary data, which is a mapping of cell names to their vectors of values, then the DataFrame object df is created from data.

The last line is to change the sign of TransServices values, as we want to draw them as negative values that push down the CSM balance.

In [4]:

data = collections.OrderedDict()
for cells in ['CSM_Unfloored',
              'IntAccrCSM',
              'AdjCSM_FulCashFlows',
              'TransServices']:
    data[cells] = [proj.cells[cells](t) for t in range(15)]

df = pd.DataFrame(data)
df['TransServices'] = -1 * df['TransServices']

Tha table below show the DataFrame values.

In [5]:

df

Out[5]:

Draw waterfall chart¶

The last line is to draw the waterfall graph. The function to draw the graph was imported from the separate module draw_charts in this project directory, and was imported at the first part of this script.

In [6]:

draw_waterfall(df)

Out[6]:

<matplotlib.axes._subplots.AxesSubplot at 0x149aba10>