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