Outputting Financial Performance

This notebook is a continuation of template_example.py and demonstrates how the financial performance report is composed using the IFRS variables calculated by running template.py. The output of the financial performance table in this notebook matches the figures shown in Systemorph’s video.

The financial performance report provides a breakdown of the total comprehensive income, as shown below. The main task in this notebook is to map various changes in balance sheet items and incurred cash flows to categories such as Insurance Revenue (IR), Insurance Service Expense (ISE), Insurance Financial Income/Expense (IFIE) or Other Comprehensive Income (OCI) within the breakdown.

• Total Comprehensive Income

  • Profit and Loss

    • Insurance Service Result

      • Insurance Revenue (IR)

      • Insurance Service Expense (ISE)

    • Insurance Financial Income/Expense (IFIE)

  • Other Comprehensive Income (OCI)

Each item is futhur broken down as shown in the table below.

import pandas as pd
from template import ifrsvars, workspace, AocType, EstimateType, PnlVariableType

pnl = workspace.database.Query(PnlVariableType, as_df=True)
pnl.loc[pnl['Parent'].isin(['IR', 'ISE', 'IFIE', 'OCI'])][
    ['Parent', 'SystemName', 'DisplayName']].set_index('Parent')

	SystemName	DisplayName
Parent
IR	IR1	Premiums
IR	IR2	Exc. Investment Components
IR	IR3	CSM Amortization
IR	IR4	Acquistion Expenses Amortization
IR	IR5	Non-Financial LRC Changes (Exc. CSM Amortization)
IR	IR6	Exc. Experience Adjustment on Premiums
ISE	ISE1	Reinsurance Premiums
ISE	ISE2	Claims
ISE	ISE3	Expenses
ISE	ISE4	Commissions
ISE	ISE5	Exc. Investment Components
ISE	ISE6	Acquisition Expenses
ISE	ISE7	Reinsurance CSM Amortization
ISE	ISE8	LoReCo Release
ISE	ISE9	Loss Component Release
ISE	ISE10	Non-Financial Reinsurance LRC Changes (Exc. LC...
ISE	ISE11	Loss Component / LoReCo Changes (Exc. Releases)
ISE	ISE12	Non Financial LIC Changes
IFIE	IFIE1	Financial LRC Changes
IFIE	IFIE2	Financial LIC Changes
IFIE	IFIE3	FX Changes
OCI	OCI1	Financial LRC Changes
OCI	OCI2	Financial LIC Changes
OCI	OCI3	FX Changes

Processing Raw Data

When the template module is run, all the IFRS variables become available as a DataFrame named ifrsvars. The all_data variable is then defined by filtering ifrsvars to only include the variables from the first quarter of 2021. deltas is defined to specifically include only those variables that represent increases or decreases in amounts.

all_data = ifrsvars.loc[(ifrsvars['Year'] == 2021) & (ifrsvars['Month'] == 3)]
deltas = all_data.loc[((all_data['AocType'] != 'BOP') & (all_data['AocType'] != 'EOP'))
                      | ((all_data['AocType'] == 'BOP') & (all_data['Novelty'] == 'N'))]

Fulfillment Cashflows (excluding the Loss Component)

The fulfillment cashflows comprise the best estimate liabilities (BE) and the risk adjustment for non-financial risk (RA).

be_or_ra is a condition to filter deltas and extract variables that represent changes in BE and RA.

Changes in the fulfillment cashflows due to the difference between current discount rates and initial(lock-in) discount rates are recognized either in Profit and Loss or in Other Comprehensive Income depending on whether the Valuation Approach is BBA and the OCI option is selected.

fcf extracts the changes in the fulfillment cashflows that need to be measured using the lock-in discount rates, and also the changes that need to be measured using the current discount rates. current_fcf on the other hand extracts the changes using the current discount rates, regardless of the values of the valuation approaches and OCI options. The difference between fcf and current_fcf are the amounts to be recognized in OCI.

be_or_ra = (deltas['EstimateType'] == 'BE') | (deltas['EstimateType'] == 'RA')
as_lockin = (deltas['ValuationApproach'] == 'BBA') & deltas['OciType']

lockin = as_lockin & (deltas['EconomicBasis'] == 'L')
current = ~as_lockin & (deltas['EconomicBasis'] == 'C')

fcf = deltas.loc[be_or_ra & (lockin | current)]

current_fcf = deltas.loc[be_or_ra & (deltas['EconomicBasis'] == 'C')]

Non-Financial Component

The non-financial component in the changes in the fulfillment cashflows is recognized as either Insurance Revenue (IR) or Insurance Service Result (ISR) in the Insurance Service Result section under Profit and Loss.

The changes are identified by the values in the AocType column.

Variables in fcf are split into non_fin_fcf and fin_fcf by looking at their AocType values.

fin_fcf_aocs =  fcf['AocType'].isin(['IA', 'YCU', 'CRU', 'FX'])
non_fin_fcf_aocs = ~fin_fcf_aocs
non_fin_fcf = fcf.loc[non_fin_fcf_aocs]

non_fin_pnl_vars is a table for mapping the non-financial changes in the fulfillment cashflows to IR and ISE items.

non_fin_pnl_vars = pd.DataFrame.from_records(
    [['LRC', False, 'IR5'],
     ['LRC', True, 'ISE10'],
     ['LIC', False, 'ISE12'],
     ['LIC', True, 'ISE12']],
     columns=['LiabilityType', 'IsReinsurance', 'PnlVariableType']
)
non_fin_pnl_vars

	LiabilityType	IsReinsurance	PnlVariableType
0	LRC	False	IR5
1	LRC	True	ISE10
2	LIC	False	ISE12
3	LIC	True	ISE12

The code below adds the PnlVariableType column to non_fin_cf by looking up the non_fin_pnl_vars table, then flip the sign of the values so that the decreases in the fulfillment cashflows become positive and vice versa.

non_fin_fcf = pd.merge(non_fin_fcf, non_fin_pnl_vars, how='left', on=['LiabilityType', 'IsReinsurance'], sort=False)
non_fin_fcf['Value'] = -1 * non_fin_fcf['Value']

Financial Component

The non-financial component in the changes in the fulfillment cashflows fin_pnl_vars is a mapping table for the financial component.

fin_pnl_vars = pd.DataFrame.from_records(
    [['LRC', 'IFIE1'],
     ['LIC', 'IFIE2']],
     columns=['LiabilityType', 'PnlVariableType']
)
fin_pnl_vars

	LiabilityType	PnlVariableType
0	LRC	IFIE1
1	LIC	IFIE2

fin_fcf is defined by extracting variables that have financial AoC types. The code below adds the PnlVariableType column to fin_fcf by looking up the fin_pnl_vars table, then flip the sign of the values so that the decreases in the fulfillment cashflows become positive and vice versa.

fin_fcf = fcf.loc[fin_fcf_aocs]
fin_fcf = pd.merge(fin_fcf, fin_pnl_vars, how='left', on='LiabilityType', sort=False)
fin_fcf['Value'] = -1 * fin_fcf['Value']

OCI Component

oci_vars is a mapping table for the OCI component in the fulfillment cashflows.

oci_vars = pd.DataFrame.from_records(
    [['LRC', 'OCI1'],
     ['LIC', 'OCI2']],
     columns=['LiabilityType', 'PnlVariableType']
)
oci_vars

	LiabilityType	PnlVariableType
0	LRC	OCI1
1	LIC	OCI2

For the groups of contracts with the OCI option, the changes in the fulfillment cashflows due to the difference between current discount rates and initial(lock-in) discount rates are recognized in Other Comprehensive Income.

The code below captures the difference. current_fcf is copied as current_fcf_n and the sings of the values are flipped in current_fcf_n. oci_fin below is created by concatinating current_fcf_n and fcf. PnlVariableType column is added to oci_fin by looking up the oci_vars table.

current_fcf_n = current_fcf.copy()
current_fcf_n['Value'] = -1 * current_fcf['Value']
oci_fin = pd.merge(pd.concat([fcf, current_fcf_n]), oci_vars, how='left', on='LiabilityType', sort=False)

Change in Fulfillment Cashflows

The last step in processing the changes in fulfillment cashflows is to concatinate the 3 components, non_fin_fcf, fin_fcf and oci_fin.

fcf_chg_in_est = pd.concat([non_fin_fcf, fin_fcf, oci_fin])

Contractual Service Margin (CSM)

CSM changes can be extracted from deltas by filtering the EstimateType column for rows marked as C.

csm = deltas.loc[deltas['EstimateType'] == 'C']

To map the IFRS variables, which indicate CSM changes, to the Financial Performance items, we first map the AocType values to PnlType using the csm_type table. The PnlType values can be either NF, F, AM, or FX, where NF represents non-financial, F represents financial, AM stands for amortization, and FX means foreign exchange. The changes are then mapped to the Financial Performance items using the csm_vars table based on the PnlType values and the values in the IsReinsurance column.

Lastly, the signs of the variable values are inverted to ensure decreases in CSM are represented as positive and increases are represented as negative.

aoc_to_pnl_type = workspace.database.Query(AocType, as_df=True).rename(
    columns={'SystemName': 'AocType'})[['AocType', 'PnlType']]
aoc_to_pnl_type

	AocType	PnlType
0	BOP	NF
1	MC	NF
2	PC	NF
3	RCU	NF
4	CF	NF
5	IA	F
6	AU	NF
7	FAU	F
8	YCU	F
9	CRU	F
10	EV	NF
11	WO	NF
12	CL	NF
13	EA	NF
14	AM	AM
15	FX	FX
16	EOP	None

csm_vars = pd.DataFrame.from_records(
    [['NF', False, 'IR5'],
     ['NF', True, 'ISE10'],
     ['F', False, 'IFIE1'],
     ['F', True, 'IFIE1'],
     ['AM', False, 'IR3'],
     ['AM', True, 'ISE7'],
     ['FX', False, 'IFIE3'],
     ['FX', True, 'IFIE3']],
     columns=['PnlType', 'IsReinsurance', 'PnlVariableType']
)
csm_vars

	PnlType	IsReinsurance	PnlVariableType
0	NF	False	IR5
1	NF	True	ISE10
2	F	False	IFIE1
3	F	True	IFIE1
4	AM	False	IR3
5	AM	True	ISE7
6	FX	False	IFIE3
7	FX	True	IFIE3

csm_chg_in_est = pd.merge(
    pd.merge(csm, aoc_to_pnl_type, how='left', on='AocType', sort=False),
    csm_vars, on=['PnlType', 'IsReinsurance'], sort=False)
csm_chg_in_est['Value'] = -1 * csm_chg_in_est['Value']

Loss Component (LC)

The changes in Loss Component are mapped by the same steps as CSM: - Fitering deltas by deltas['EstimateType'] == 'L' - Mapping AocType to PnlType - Mapping PnlType to PnlVariableType

lc = deltas.loc[deltas['EstimateType'] == 'L']
lc_vars = pd.DataFrame.from_records(
    [['NF','ISE11'],
     ['F', 'IFIE1'],
     ['AM','ISE9'],
     ['FX','IFIE3']],
     columns=['PnlType', 'PnlVariableType']
)
lc_vars

	PnlType	PnlVariableType
0	NF	ISE11
1	F	IFIE1
2	AM	ISE9
3	FX	IFIE3

lc_chg_in_est = pd.merge(
    pd.merge(lc, aoc_to_pnl_type, how='left', on='AocType', sort=False),
    lc_vars, on='PnlType', sort=False)
lc_chg_in_est['Value'] = -1 * lc_chg_in_est['Value']

Loss Recovery Component (LoReCo)

The changes in Loss Recovery Component are mapped by the same steps as CSM and Locc Component: - Fitering deltas by deltas['EstimateType'] == 'LR' - Mapping AocType to PnlType - Mapping PnlType to PnlVariableType - Inverting the values

lrc = deltas.loc[deltas['EstimateType'] == 'LR']
lrc_vars = pd.DataFrame.from_records(
    [['NF','ISE11'],
     ['F', 'IFIE1'],
     ['AM','ISE8'],
     ['FX','IFIE3']],
     columns=['PnlType', 'PnlVariableType']
)
lrc_vars

	PnlType	PnlVariableType
0	NF	ISE11
1	F	IFIE1
2	AM	ISE8
3	FX	IFIE3

lrc_chg_in_est = pd.merge(pd.merge(lrc, aoc_to_pnl_type, how='left', on='AocType', sort=False), lrc_vars, on='PnlType', sort=False)
lrc_chg_in_est['Value'] = -1 * lrc_chg_in_est['Value']

Incurred Actuals

Next, variables of actual cashflows are processed according to the following steps:

• Variables for incurred cashflows are extracted from deltas by looking at their EstimateType and AocType

• Premium cashflows are extracted as premiums and mapped to either IR or ISE

• Non-investment component claims are extracted as claims_nic and mapped to Insurance Service Expense

• Investment-component claims are extracted as claims_ico_ir and claims_ico_ise, and are then recognized in the IR and ISE respectively. The values in claims_ico_ise are inverted so that they negate each other.

• expenses, commissions are also defined and mapped to respective Insurance Service Expense items.

• incurred_actuals is defined by concatinating premiums, claims_nic, claims_ico_ir, claims_ico_ise, expenses, and commissions.

written_cf = (deltas['EstimateType'] == 'A') & (deltas['AocType']=='CF')
advanced_wo = (deltas['EstimateType'] == 'AA') & (deltas['AocType']=='WO')
overdue_wo = (deltas['EstimateType'] == 'OA') & (deltas['AocType']=='WO')

actuals_data = deltas.loc[written_cf | advanced_wo | overdue_wo]
actuals_data.loc[actuals_data['AocType'] == 'WO', 'Value'] *= -1

prem_vars = pd.DataFrame.from_records(
    [[False,'IR1'],
     [True, 'ISE1']],
     columns=['IsReinsurance', 'PnlVariableType']
)
prem_vars

	IsReinsurance	PnlVariableType
0	False	IR1
1	True	ISE1

premiums = pd.merge(actuals_data.loc[
    (actuals_data['AmountType']=='PR')], prem_vars, how='left', on='IsReinsurance', sort=False)

claims_nic = actuals_data.loc[(actuals_data['AmountType']=='NIC')].copy()
claims_nic['PnlVariableType'] = 'ISE2'

claims_ico_ir = actuals_data.loc[(actuals_data['AmountType']=='ICO')].copy()
claims_ico_ise = claims_ico_ir.copy()
claims_ico_ir['PnlVariableType'] = 'IR2'
claims_ico_ise['PnlVariableType'] = 'ISE5'
claims_ico_ise['Value'] = -1 * claims_ico_ise['Value']

expenses = actuals_data.loc[
    (actuals_data['AmountType']=='AEA') | (actuals_data['AmountType']=='AEM')
].copy()
expenses['PnlVariableType'] = 'ISE3'

commissions = actuals_data.loc[
    (actuals_data['AmountType']=='ACA') | (actuals_data['AmountType']=='ACM')
].copy()
commissions['PnlVariableType'] = 'ISE4'

incurred_actuals = pd.concat([
    premiums, claims_nic, claims_ico_ir, claims_ico_ise, expenses, commissions])

Incurred Deferrals and Acquisition Expenses

incurred_deferrals include variables related to acquisition expense amortization. Amortized acquisition expenses are shown both in Insurance Revenue and Insurance Service Expense.

amort = deltas.loc[(deltas['EstimateType'] == 'DA') & (deltas['AocType'] == 'AM')]
amort_ir = amort.copy()
amort_ise = amort.copy()

amort_ir['PnlVariableType'] = 'IR4'
amort_ise['PnlVariableType'] = 'ISE6'

amort_ise['Value'] = -1 * amort_ise['Value']

incurred_deferrals = pd.concat([amort_ir, amort_ise])

Experience Adjustment on Premium (allocation to CSM vs P&L recognition)

Experience adjustments related to premium receipts for current and past periods should be included in insurance revenue. The following code creates exp_adjust_prem, which adjust Insurance Revenue for the experience adjustment on premiums.

act_prem_csm = all_data.loc[all_data['EstimateType'] == 'APA'].copy()
act_prem_csm['Value'] = -1 * act_prem_csm['Value']
act_prem_csm['PnlVariableType'] = 'IR6'

est_prem_csm = deltas.loc[deltas['EstimateType'] == 'BEPA'].copy()
est_prem_csm['PnlVariableType'] = 'IR6'

exp_adjust_prem = pd.concat([act_prem_csm, est_prem_csm])

Financial Performance

Finally, we create the financial_performance table by concatenating all the previously created tables. We then manipulate this combined table through grouping, summing, and reshaping operations to arrive at the final form of the financial performance table.

financial_performance = pd.concat([
    fcf_chg_in_est,
    csm_chg_in_est,
    lc_chg_in_est,
    lrc_chg_in_est,
    incurred_actuals,
    incurred_deferrals,
    exp_adjust_prem])

financial_performance[
    ['Value', 'LiabilityType', 'PnlVariableType']
                     ].groupby(
    ['LiabilityType', 'PnlVariableType']).sum().unstack(level=[0]).fillna(0)

	Value
LiabilityType	LIC	LRC
PnlVariableType
IFIE1	0.000000	-0.250282
IFIE2	-1.240756	0.000000
IR1	0.000000	613.000000
IR2	0.000000	-42.000000
IR3	0.000000	63.519146
IR4	0.000000	59.158616
IR5	0.000000	38.096408
IR6	0.000000	182.000000
ISE1	0.000000	-183.500000
ISE10	0.000000	72.607574
ISE11	0.000000	-8.063094
ISE12	37.111326	0.000000
ISE2	-82.000000	-367.500000
ISE3	0.000000	-35.000000
ISE4	0.000000	-70.000000
ISE5	0.000000	42.000000
ISE6	0.000000	-59.158616
ISE7	0.000000	-44.532586
ISE9	0.000000	8.128297
OCI1	0.000000	-0.006156
OCI2	0.183808	0.000000