If you're viewing this page as a static HTML page on https://lifelib.io, the same contents are also available here on binder as Jupyter notebook executable online (it may take a while to load)

To run this notebook and get all the outputs below, Go to the **Cell** menu above, and then click **Run All**.

This notebook draws several waterfall charts that show how sources of change in IFRS17 accounts emerge when the actual lapse rate changes at a future point in time and future lapse rate assumptions from that point change accordingly for current-estimate liability valuations at future points.

Those charts are:

- Actual cashflows
- Present value of expected cashflows
- CSM amortization
- IFRS17 Financial performance

A notebook for the baseline simulation is also available on lifelib. The baseline simulation assumes that actual cashflow emerge exacltly as esimated at the beginning of simulation.

**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` can be negative.
- Coverage unit is set to sum assured.
- The amortization schedule of acquisition cashflows is constant over time.
- All insurance cashflows are considered non-market sensitive, i.e. no TVOG is considered.
- Risk adjustment is not yet modeled.

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. 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.

You can play around with this notebook, by changeng input values and formulas, re-running code and checking how output tables and charts change accodingly. Note that change in code in one cell may change the results of other cells. To reflect change in one cell on the output of other cells, the other cells need to be re-run after the change.

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.

In [ ]:

```
%matplotlib notebook
import pandas as pd
import collections
import matplotlib.pyplot as plt
from draw_charts import draw_waterfall, get_waterfalldata, draw_actest_pairs
```

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 `OuterProj`

and other spaces. `OuterProj`

is parametrized by Policy ID, i.e. each of the spaces with parameters corresponds to a projection of one policy. For example, `model.OuterProj[1]`

return the projection of policy ID 1, `model.OuterProj[171]`

return the projection of policy ID 171.

The first line below sets `proj`

as a shorthand for the projection of Policy ID 1.
You can change the sample policy by supplying some other ID.

In [ ]:

```
import modelx as mx
model = mx.read_model("model")
proj = model.OuterProj[1]
inner = proj.InnerProj
```

By defalut, base lapse rates are read in from the input file. The initial rates are constant at 8%.

In [ ]:

```
proj.asmp.SurrRate.to_frame(range(6))
```

`SurrRateMult`

.
The cells `SurrRateMult`

in outer and in inner spaces have have different formulas.

`SurrRateMult`

is set to 1 by default, but can be overwritten by user input. If the user overwrite `SurrRateMult(t)`

, then the new value applies from `t`

goint forward.

In [ ]:

```
proj.asmp.SurrRateMult.formula
```

For the inner projectins, `SurrRateMult`

is set to the same value as the outer simulation at time 0.

At each step of outer simulation(`t0`

), the lapse rates for the inner projection starting at `t0`

, are set to the lapse rate of the actual(outer) simulation applied in the previos period (from `t0-1`

to `t0`

)

In [ ]:

```
inner.asmp.SurrRateMult.formula
```

In [ ]:

```
proj.asmp.SurrRateMult[1] = 1
proj.asmp.SurrRateMult[2] = 2 # Actual lapse rate from t=2
inner[1].asmp.SurrRateMult[1] = 1
inner[2].asmp.SurrRateMult[2] = 1
inner[3].asmp.SurrRateMult[3] = 2 # The lapse assumption for estimated cashflows at t=3 and thereafter
```

The figure below shows how underlying poicies change over time. The 3 columns in the fiture represents 3 cells, and filled lines denote actual(outer) while the dotted lines denote expected(inner). For each column, rows from the top to the bottom represent the time steps of the outer simulation, stating at time 0.

The leftmost column is the graph of policies in-force at the end of each period.
The column in the middle shows how `SurrRateMult`

for actual lapse rate and estimated lapse rates change over time.
`SurrRateMult`

stays at time 0 and 1 (1st and 2nd rows). Then it doubles for the acutal at time 2 (3rd row), while the estimate stays still at 1 for 1 year. Then the estimate catches up the actual at time 3 (4th row).

The right most column show the movement in terms of the number of surrender policies.

In [ ]:

```
draw_actest_pairs(proj, inner, ['PolsIF_End', 'SurrRateMult', 'PolsSurr'], 5, 5)
```

The code below generates a waterfall chart that simulates actual insurance cashflows that are assumed to be equal to the expected. The net asset balance is reset to zero at the end of each period, so the assets are equal to the liabilities at the beginning of each period. The assets are held as cash, and bear interest at the same rate as discount rate.

In [ ]:

```
proj.IntAccumCF.formula
```

In [ ]:

```
actcf = get_waterfalldata(
proj,
items=['PremIncome',
'IntAccumCF',
'ExpsAcqTotal',
'BenefitTotal',
'ExpsMaintTotal',
'ActualNetCF'],
length=4,
reverseitems=['ExpsAcqTotal',
'BenefitTotal',
'ExpsMaintTotal'])
actcf
```

In [ ]:

```
draw_waterfall(actcf, stocks=[0, 5], title='Actual Cashflows')
```

The code below generate a waterfall chart that shows how the present value of expected insurance cashflows unwinds over time.

The waterfall bars are disconected at t = 3, because we change the lapse assumption there. The present value of future cashflows decrease due to the decrease in future policies.

In [ ]:

```
estcf = get_waterfalldata(
proj,
items=['PV_FutureCF',
'EstPremIncome',
'EstIntOnCF',
'EstAcqCashflow',
'EstClaim',
'EstExps'],
length=4,
reverseitems=['EstPremIncome'])
estcf
```

In [ ]:

```
draw_waterfall(estcf, title='Expected Cashflows')
```

The CSM amortization chart below depicts items that increase/decrease CSM balance.

The adjustment to CSM for changes in fulfilment cashflows (`AdjCSM_FlufCF`

) is negative in the 3rd period.
This offsets to a great extent the decrease in `PV_FutureCF`

in the chart above (or increase in liability, if `PV_FutureCF`

is negative unlike this sample, which is often the case)

`AdjCSM_FlufCF`

takes the difference of `PV_Cashflows`

with different parameters.

`PV_Cashflow(t+1, t+1, 0)`

starts projection at `t+1`

into the future using the lapse assumption updated at `t+1`

, and discount the cashflows using the discount rate fixed at time zero, back to time `t+1`

.

`PV_Cashflow(t, t+1, 0)`

starts projection at `t`

into the future using the lapse rate assumption before the change, and discount the cashflows using the discount rate fixed at time zero, back to time `t+1`

.

The sources of difference between these valuese are the difference in the number of policies in-force, and the difference in future laps rates, which impact projected policies in-force and cashflows from `t+1`

.

In [ ]:

```
proj.AdjCSM_FlufCF.formula
```

In [ ]:

```
csmrf = get_waterfalldata(
proj,
items=['CSM',
'IntAccrCSM',
'AdjCSM_FlufCF',
'TransServices'],
length=4,
reverseitems=['TransServices'])
csmrf
```

In [ ]:

```
draw_waterfall(csmrf, title='CSM Amortization')
```

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 released and the outstanding net balance (`NetBalance`

) is reset to zero.

The profit in the 3rd period is decreased due to the adverse change in lapse assumption, but its impact on the current profit is limited because of the offset between the change in CSM and the PV of future cashflows.

In [ ]:

```
proj.InsurRevenue.formula
```

In [ ]:

```
proj.InsurServiceExps.formula
```

In [ ]:

```
ifrspl = get_waterfalldata(
proj,
items=['InsurRevenue',
'InsurServiceExps',
'InsurFinIncomeExps',
'ProfitBefTax'],
length=5,
reverseitems=['InsurServiceExps'])
ifrspl
```

In [ ]:

```
draw_waterfall(ifrspl, stocks=[0, 3], title='IFRS17 Profit/Loss')
```