Running the Tempate and Outputting Tables#

This notebook runs template.py and output results in tables. The result tables replicate figures shown in tutorial videos on Systemortph’s YouTube channel.

To run this notebook, the ifrs17 and template modules in your copy of the ifrs17a library need to be importable. To ensure they are importable, make sure the current directory is set to your ifrs17a copy. Typically, the current directory is set to the location where you started this notebook, so it should run fine unless you have moved the notebook elsewhere. To check the current directory from Python, import os, and execute os.getcwd(). To change the current directory in Python, use the os.chdir function.

Click the badge below to run this notebook online on Google Colab. You need a Google account and need to be logged in to it to run this notebook on Google Colab.

The next code cell below is relevant only when you run this notebook on Google Colab. It installs lifelib and creates a copy of the library for this notebook.

[ ]:

import sys, os

if 'google.colab' in sys.modules:
    lib = 'ifrs17a'; lib_dir = '/content/'+ lib
    if not os.path.exists(lib_dir):
        !pip install lifelib
        import lifelib; lifelib.create(lib, lib_dir)

    %cd $lib_dir

Running `template.py`#

template.py is executed when it’s imported by the import statement. ifrsvars defined in the template module refers to a DataFrame that contains all the IFRS variables as rows.

[1]:

import pandas as pd
from template import ifrsvars

[2]:

ifrsvars

[2]:

	DataNode	AocType	Novelty	AmountType	AccidentYear	EstimateType	EconomicBasis	Value	ReportingNode	Year	...	FunctionalCurrency	LineOfBusiness	ValuationApproach	OciType	AnnualCohort	LiabilityType	Profitability	Portfolio	YieldCurveName	Partner
0	DT3.1	BOP	I		0.0	C		72.200000	CH	2020	...	CHF	ANN	BBA	Default	2020	LRC	P	DT3
1	DT3.1	BOP	I	PR	0.0	AA		-1.500000	CH	2020	...	CHF	ANN	BBA	Default	2020	LRC	P	DT3
2	DT3.1	BOP	I	PR	0.0	OA		1.500000	CH	2020	...	CHF	ANN	BBA	Default	2020	LRC	P	DT3
3	DT5.1	BOP	N	PR	0.0	BE	L	-798.536731	CH	2020	...	CHF	ANN	BBA	Default	2020	LRC	P	DT5
4	DT5.1	BOP	N	NIC	0.0	BE	L	598.752945	CH	2020	...	CHF	ANN	BBA	Default	2020	LRC	P	DT5
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
1751	DTR2.1	EOP	C		0.0	C		-23.389003	CH	2021	...	CHF	ANN	BBA	NaN	2020	LRC	P	DTR2		PT1
1752	DTR2.1	EV	N		0.0	LR		-27.567249	CH	2021	...	CHF	ANN	BBA	NaN	2020	LRC	P	DTR2		PT1
1753	DTR2.1	IA	I		0.0	C		-0.038546	CH	2021	...	CHF	ANN	BBA	NaN	2020	LRC	P	DTR2		PT1
1754	DTR2.1	IA	N		0.0	LR		-0.008150	CH	2021	...	CHF	ANN	BBA	NaN	2020	LRC	P	DTR2		PT1
1755	DTR2.1	MC	I		0.0	C		-52.922953	CH	2021	...	CHF	ANN	BBA	NaN	2020	LRC	P	DTR2		PT1

1756 rows × 23 columns

In this notebook, we only use data for the reporting period ending at the end of March 2021. The code below extract rows for the March-2021 reporting period, and create a new DataFrame containing the rows and assigns it to df.

[3]:

df = ifrsvars.loc[(ifrsvars['Year'] == 2021) & (ifrsvars['Month'] == 3)]

All the tables from here are created by filtering df based on specific EstimateTypes and then organizing them into a tabular format using properties, such as Novelty, AocType, EconomicBasis, and LiabilityType.

Best Estimate#

The code below creates a DataFrame that shows breakdowns of the best estimate liability.

The DataFrame below replicates figures in the table shown around the 2:15 mark in the video, Systemorph #Techucation for IFRS17: How to read the full set of financial reports – Part 1

[4]:

df_BE = df.loc[df['EstimateType'] == 'BE'][['AocType', 'Novelty', 'AmountType', 'LiabilityType', 'EconomicBasis', 'Value']]
BE = df_BE.set_index(['Novelty', 'AocType', 'EconomicBasis', 'LiabilityType'])['Value'].groupby(
    level=list(range(4))).sum().unstack(level=[-1, -2]).fillna(0)
BE

[4]:

	LiabilityType	LRC		LIC
	EconomicBasis	C	L	C	L
Novelty	AocType
C	CL	74.987448	74.994992	0.000000	0.000000
C	EOP	-379.954884	-380.062226	271.367348	271.706254
I	BOP	-499.330813	-499.330813	299.906093	299.906093
	CF	249.832118	249.832118	-60.083941	-60.083941
	CRU	2.369316	2.373251	1.184658	1.186626
	IA	-0.366044	-0.366044	0.109789	0.109789
	MC	-499.584732	-499.584732	-59.981219	-59.981219
	YCU	0.522270	0.000000	-0.223970	0.000000
N	BOP	60.913655	61.072687	60.425664	60.523857
	CF	-15.534843	-15.534843	-15.267421	-15.267421
	EV	246.140729	246.442443	45.227891	45.284376
	IA	0.093247	0.037419	0.068421	0.027447

Risk Adjustment#

The code below creates a DataFrame that shows breakdows of risk margin.

[5]:

df_RA = df.loc[df['EstimateType'] == 'RA'][['AocType', 'Novelty', 'AmountType', 'EconomicBasis', 'Value', 'LiabilityType']]
RA = df_RA.set_index(['Novelty', 'AocType', 'LiabilityType', 'EconomicBasis'])['Value'].groupby(
    level=list(range(4))).sum().unstack(level=[-1, -2]).fillna(0)
RA

[5]:

	EconomicBasis	C	L	C	L
	LiabilityType	LRC	LRC	LIC	LIC
Novelty	AocType
C	CL	-22.453303	-22.481279	0.000000	0.000000
C	EOP	89.813213	89.925114	22.453303	22.481279
I	BOP	149.837775	149.837775	29.967555	29.967555
	CF	-30.000000	-30.000000	-6.000000	-6.000000
	IA	0.054895	0.054895	0.010979	0.010979
	MC	-29.967555	-29.967555	-5.993511	-5.993511
	YCU	-0.111901	0.000000	-0.022380	0.000000
N	BOP	59.838185	59.935110	11.967637	11.987022
	CF	-15.000000	-15.000000	-3.000000	-3.000000
	EV	-22.453303	-22.481279	-4.490661	-4.496256
	IA	0.068421	0.027447	0.013684	0.005489

Written Actuals#

The DataFrame below replicates figures in the table shown around the 0:45 mark in the video, Systemorph #Techucation for IFRS17: How to read the full set of financial reports – Part 2.

[6]:

df_WA = df.loc[df['EstimateType'] == 'A'][['AmountType', 'Value', 'LiabilityType']]
WA = df_WA.set_index(['AmountType' , 'LiabilityType'])['Value'].groupby(
    level=list(range(2))).sum().unstack(level=[-1]).fillna(0)
WA

[6]:

LiabilityType	LIC	LRC
AmountType
ACA	0.0	-70.0
AEA	0.0	-35.0
ICO	0.0	-42.0
NIC	-82.0	-350.0
PR	0.0	450.0

Advance, Overdue Actuals#

The DataFrame below replicates figures in the table shown around the 1:15 mark in the video, Systemorph #Techucation for IFRS17: How to read the full set of financial reports – Part 2

[7]:

df_AAOA = df.loc[(df['EstimateType'] == 'AA') | (df['EstimateType'] == 'OA')][['AocType', 'Novelty', 'Value', 'EstimateType']]
AAOA = df_AAOA.set_index(['AocType', 'EstimateType'])['Value'].groupby(level=list(range(2))).sum().unstack(level=[-1])
AAOA

[7]:

EstimateType	AA	OA
AocType
BOP	7.5	-15.5
CF	2.5	-2.5
EOP	18.5	11.5
WO	8.5	29.5

Deferrable Actuals#

The DataFrame below replicates figures in the table shown around the 1:30 in the video, Systemorph #Techucation for IFRS17: How to read the full set of financial reports – Part 2

[8]:

df_DA = df.loc[df['EstimateType'] == 'DA'][['AocType', 'Novelty', 'AmountType', 'Value']]
DA = df_DA.set_index(['AocType']).groupby(level=0).sum()
DA

[8]:

	Value
AocType
AM	59.158616
BOP	-36.095865
CF	-105.000000
EOP	-81.937249

Contractual Service Margin / Loss Component / Loss Recovery Component#

The DataFrame below replicates figures in the table shown around the 1:25 in the video, Systemorph #Techucation for IFRS17: How to read the full set of financial reports – Part 3

[9]:

df_TM = df.loc[(df['EstimateType'] == 'C') | (df['EstimateType'] == 'L') | (df['EstimateType'] == 'LR')]
TM = df_TM[['AocType', 'Novelty', 'Value', 'EstimateType']].set_index(['Novelty', 'AocType', 'EstimateType'])['Value'].groupby(
    level=list(range(3))).sum().unstack(level=[-1]).fillna(0)
TM

[9]:

	EstimateType	C	L	LR
Novelty	AocType
C	AM	-18.986561	-8.128297	0.000000
	CL	-404.060267	-524.588133	173.041579
	EA	-299.871703	8.128297	0.000000
	EOP	39.887800	0.000000	0.000000
I	BOP	228.735710	0.000000	0.000000
	CRU	-2.373251	0.000000	0.000000
	IA	0.378860	0.000000	0.000000
	MC	529.552287	0.000000	0.000000
N	BOP	8.004654	191.752434	-62.739983
	EV	-1.492264	332.737896	-110.268995
	IA	0.000336	0.097803	-0.032601