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.

[1]:
import pandas as pd
from template import ifrsvars, workspace, AocType, EstimateType, PnlVariableType
[2]:
pnl = workspace.database.Query(PnlVariableType, as_df=True)
pnl.loc[pnl['Parent'].isin(['IR', 'ISE', 'IFIE', 'OCI'])][
    ['Parent', 'SystemName', 'DisplayName']].set_index('Parent')
[2]:
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.

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

[4]:
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)]
[5]:
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.

[6]:
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.

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

[8]:
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.

[9]:
fin_pnl_vars = pd.DataFrame.from_records(
    [['LRC', 'IFIE1'],
     ['LIC', 'IFIE2']],
     columns=['LiabilityType', 'PnlVariableType']
)
fin_pnl_vars
[9]:
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.

[10]:
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.

[11]:
oci_vars = pd.DataFrame.from_records(
    [['LRC', 'OCI1'],
     ['LIC', 'OCI2']],
     columns=['LiabilityType', 'PnlVariableType']
)
oci_vars
[11]:
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.

[12]:
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.

[13]:
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.

[14]:
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.

[15]:
aoc_to_pnl_type = workspace.database.Query(AocType, as_df=True).rename(
    columns={'SystemName': 'AocType'})[['AocType', 'PnlType']]
aoc_to_pnl_type
[15]:
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
[16]:
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
[16]:
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
[17]:
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

[18]:
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
[18]:
PnlType PnlVariableType
0 NF ISE11
1 F IFIE1
2 AM ISE9
3 FX IFIE3
[19]:
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

[20]:
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
[20]:
PnlType PnlVariableType
0 NF ISE11
1 F IFIE1
2 AM ISE8
3 FX IFIE3
[21]:
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.

[22]:
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
[23]:
prem_vars = pd.DataFrame.from_records(
    [[False,'IR1'],
     [True, 'ISE1']],
     columns=['IsReinsurance', 'PnlVariableType']
)
prem_vars
[23]:
IsReinsurance PnlVariableType
0 False IR1
1 True ISE1
[24]:
premiums = pd.merge(actuals_data.loc[
    (actuals_data['AmountType']=='PR')], prem_vars, how='left', on='IsReinsurance', sort=False)
[25]:
claims_nic = actuals_data.loc[(actuals_data['AmountType']=='NIC')].copy()
claims_nic['PnlVariableType'] = 'ISE2'
[26]:
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']
[27]:
expenses = actuals_data.loc[
    (actuals_data['AmountType']=='AEA') | (actuals_data['AmountType']=='AEM')
].copy()
expenses['PnlVariableType'] = 'ISE3'
[28]:
commissions = actuals_data.loc[
    (actuals_data['AmountType']=='ACA') | (actuals_data['AmountType']=='ACM')
].copy()
commissions['PnlVariableType'] = 'ISE4'
[29]:
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.

[30]:
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.

[31]:
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.

[32]:
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)
[32]:
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