Example 2: Annuity with DAV Annuity Table

In the current example we build on the previous one and demonstrate two additional things:

using the DAV2004R table as the base mortality assumption
starting the projector from code

The DAV2004R table can be used in a variety of ways for different use cases and pyprotolinc provides some convenience code to use some of those with little effort.

To follow along change into the directory examples/02_annuities_in_payment.

Download the DAV Tables

It’s not so clear why the DAV tables aren’t easily available for downloading from official sources. Luckily, the maintainer of the R-Project MortalityTables (cf. https://www.rdocumentation.org/packages/MortalityTables/versions/2.0.3) provides the required files. If you are willing to get them (the legal situation around this is not clear to the author of this tutorial) open up a command prompt and change into the base directory of this example and run:

pyprotolinc download_dav_tables

or run the folling code from a script:

[1]:

from pyprotolinc.utils import download_dav_tables
download_dav_tables()

Now a new folder called tables should have been created in the working folder with two subfolders. In the present case the one of interest is this:

[2]:

# use when not on Windows "!ls"
!dir /B tables\Germany_Annuities_DAV2004R

Germany_Annuities_DAV2004R.csv
Germany_Annuities_DAV2004R_AV.csv
Germany_Annuities_DAV2004R_AVBase.csv
Germany_Annuities_DAV2004R_Select.csv
Germany_Annuities_DAV2004R_Trends.csv

PyProtolinc provides an interface class to these files. On instantiation pass the base folder from which to load the files as an argument.

[3]:

from pyprotolinc.assumptions.dav2004r import DAV2004R
dav2004r = DAV2004R(base_directory="tables/Germany_Annuities_DAV2004R", trend_t1=10, trend_t2=25)

The second and third argument above control the trend factor extrapolation between the start and target trends.

As an aside it is quickly demonstrated here how one can work with this raw object (i.e. outside the context of a projection run). In this case one first has to create a rates provider object. Specifically, in this case we parametrize the aggregate best estimate rates, i.e. the aggregate (non-select) version using no additional prudency loadings (“2. Ordnung”).

[4]:

rates_provider = dav2004r.rates_provider(table_type='AGGREGATE', estimate_type="BE")
rates_provider

[4]:

<CStandardRateProvider with RF (CalendarYear, Gender, Age)>

To query for rates three risk factors need to be provided: CalendarYear, Gender and Age. Two of them are encoded in the canonical way, to be sure we get the gender right we import the corresponding RiskFactor class from pyprotolinc and then proceed creating three arrays with two entries each:

[5]:

import numpy as np
from pyprotolinc.riskfactors.risk_factors import Gender

ages = np.array([60, 70], dtype=np.int32)
genders = np.array([Gender.M, Gender.F], dtype=np.int32)
calendar_years = np.array([2022, 2022], dtype=np.int32)

These arrays parametrize two individuals in the year 2022: a 60 year old male and a 70 year old female. For both of them we can now lookup the mortality rates as follows.

[6]:

rates_provider.get_rates(2, age=ages, gender=genders, calendaryear=calendar_years)

[6]:

array([0.00392406, 0.00597981])

Using the DAV Table via Configuration

The key change when compared to the example 1 is found in the longevity_assumptions.yml file which reads as follows:

[13]:

with open('longevity_assumptions.yml', 'r') as f:
    print(f.read())

# for the annuity model
assumptions_spec:

  be:
    # annuitant's deaths:
    # Note: DAV2004R_B20 not yet working with the C-Kernel
    # - [0, 1, ["DAV2004R_B20", "estimate_type:B20", "base_directory:tables/Germany_Annuities_DAV2004R"]]
    - [0, 1, ["DAV2004R", "table_type:AGGREGATE", "estimate_type:BE",
              "base_directory:tables/Germany_Annuities_DAV2004R"]]

  res:
    # annuitant's deaths
    # Note: DAV2004R_B20 not yet working with the C-Kernel
    # - [0, 1, ["DAV2004R_B20", "estimate_type:B20", "base_directory:tables/Germany_Annuities_DAV2004R"]]
    - [0, 1, ["DAV2004R", "table_type:AGGREGATE", "estimate_type:LOADED",
              "base_directory:tables/Germany_Annuities_DAV2004R"]]

Note that the parametrization is not a simple scalar as in example 1 but uses a syntax which should be easy to grasp with the knowledge of the above.

We can now immediately start a run from the sheel by typeing pyprotolinc run in the main directory of this example. We can also start the run programmatically as follows.

[8]:

from pyprotolinc.main import get_config_from_file, project_cashflows
run_config = get_config_from_file('config.yml')
project_cashflows(run_config);

INFO - 2023-03-26 12:10:18,835 - pyprotolinc.main - Multistate run with config: {'working_directory': WindowsPath('D:/programming/pyprotolinc/examples/02_annuities_in_payment'), 'model_name': 'GenericMultiState', 'years_to_simulate': 119, 'portfolio_path': 'D:\\programming\\pyprotolinc\\examples\\02_annuities_in_payment\\portfolio/portfolio_annuity_small.xlsx', 'assumptions_path': 'longevity_assumptions.yml', 'steps_per_month': 1, 'state_model_name': 'AnnuityRunoffStates', 'timestep_duration': 0.08333333333333333, 'outfile': 'results/ncf_out_generic.csv', 'portfolio_cache': 'D:\\programming\\pyprotolinc\\examples\\02_annuities_in_payment\\portfolio/portfolio_cache', 'profile_out_dir': 'D:\\programming\\pyprotolinc\\examples\\02_annuities_in_payment\\.', 'portfolio_chunk_size': 1024, 'use_multicore': False, 'kernel_engine': 'PY', 'max_age': 120}
DEBUG - 2023-03-26 12:10:18,889 - pyprotolinc.portfolio - Porfolio file not found in cache.
INFO - 2023-03-26 12:10:18,890 - pyprotolinc.portfolio - Reading portfolio data from file D:\programming\pyprotolinc\examples\02_annuities_in_payment\portfolio\portfolio_annuity_small.xlsx.
INFO - 2023-03-26 12:10:19,357 - pyprotolinc.portfolio - Porfolio saved in cache
INFO - 2023-03-26 12:10:19,359 - pyprotolinc.portfolio - Portolio rows: 1
DEBUG - 2023-03-26 12:10:19,361 - pyprotolinc.portfolio - Splitting portfolio for product ANNUITYINPAYMENT.
DEBUG - 2023-03-26 12:10:19,364 - pyprotolinc.portfolio - Initializing portfolio from dataframe
INFO - 2023-03-26 12:10:19,375 - pyprotolinc.main - Executions in single process for 1 units
INFO - 2023-03-26 12:10:19,376 - pyprotolinc.main - Projecting subportfolio 1 / 1 with Python engine
DEBUG - 2023-03-26 12:10:19,377 - pyprotolinc.runner - Creating a <Projector> object for chunk 1 of 1
DEBUG - 2023-03-26 12:10:19,379 - pyprotolinc.runner - Starting the simulation for chunk 1 of 1
INFO - 2023-03-26 12:10:19,575 - root - Runner for chunk 1: Early termination in 4/2111
DEBUG - 2023-03-26 12:10:19,576 - pyprotolinc.runner - Starting backwards loop to calculate the reserves for chunk 1 of 1
DEBUG - 2023-03-26 12:10:19,640 - pyprotolinc.main - Combining results from subportfolios
INFO - 2023-03-26 12:10:19,641 - pyprotolinc.results - Exporting NCF to results/ncf_out_generic.csv
INFO - 2023-03-26 12:10:19,685 - pyprotolinc.main - Elapsed time 0.8 seconds.

Rerun using Different Reserving Interest Rates

Let’s have a quick look at the result and note that not only did we change the table but in this case also a reserving interest rate of 1% was used.

[12]:

import pandas as pd
pd.read_csv("results/ncf_out_generic.csv", index_col=0).head()

[12]:

	YEAR	QUARTER	MONTH	ANNUITY_PAYMENT1	RESERVE_BOM(DIS1)	...	MV_DIS1_DEATH
0	2021	4	12	0.000000	0.000000	...	0.000000
1	2022	1	1	-100.000000	45697.836566	...	0.000111
2	2022	1	2	-99.988915	45634.456253	...	0.000111
3	2022	1	3	-99.977832	45571.036132	...	0.000111
4	2022	2	4	-99.966750	45507.576171	...	0.000111

5 rows × 27 columns

If we want to rerun using another reserve rate we can do that by loading and changing the portfolio as follows:

[10]:

df_portfolio = pd.read_excel("portfolio/portfolio_annuity_small.xlsx")
df_portfolio.head()

[10]:

	DATE_PORTFOLIO	ID	DATE_OF_BIRTH	DATE_START_OF_COVER	SUM_INSURED	CURRENT_STATUS	SEX	PRODUCT	PRODUCT_PARAMETERS	SMOKERSTATUS	RESERVING_RATE	DATE_OF_DISABLEMENT
0	2021-12-31	1	1976-04-23	2022-01-01	1200	DIS1	m	AnnuityInPayment	NaN	U	0.01	2021-12-31

[11]:

df_portfolio["RESERVING_RATE"] = 0.03
res = project_cashflows(run_config, df_portfolio_overwrite=df_portfolio, export_to_file=False)
pd.DataFrame(res).head()

INFO - 2023-03-26 12:10:19,772 - pyprotolinc.main - Multistate run with config: {'working_directory': WindowsPath('D:/programming/pyprotolinc/examples/02_annuities_in_payment'), 'model_name': 'GenericMultiState', 'years_to_simulate': 119, 'portfolio_path': 'D:\\programming\\pyprotolinc\\examples\\02_annuities_in_payment\\portfolio/portfolio_annuity_small.xlsx', 'assumptions_path': 'longevity_assumptions.yml', 'steps_per_month': 1, 'state_model_name': 'AnnuityRunoffStates', 'timestep_duration': 0.08333333333333333, 'outfile': 'results/ncf_out_generic.csv', 'portfolio_cache': 'D:\\programming\\pyprotolinc\\examples\\02_annuities_in_payment\\portfolio/portfolio_cache', 'profile_out_dir': 'D:\\programming\\pyprotolinc\\examples\\02_annuities_in_payment\\.', 'portfolio_chunk_size': 1024, 'use_multicore': False, 'kernel_engine': 'PY', 'max_age': 120}
DEBUG - 2023-03-26 12:10:19,826 - pyprotolinc.portfolio - Initializing portfolio from dataframe
DEBUG - 2023-03-26 12:10:19,836 - pyprotolinc.portfolio - Splitting portfolio for product ANNUITYINPAYMENT.
DEBUG - 2023-03-26 12:10:19,840 - pyprotolinc.portfolio - Initializing portfolio from dataframe
INFO - 2023-03-26 12:10:19,850 - pyprotolinc.main - Executions in single process for 1 units
INFO - 2023-03-26 12:10:19,851 - pyprotolinc.main - Projecting subportfolio 1 / 1 with Python engine
DEBUG - 2023-03-26 12:10:19,852 - pyprotolinc.runner - Creating a <Projector> object for chunk 1 of 1
DEBUG - 2023-03-26 12:10:19,854 - pyprotolinc.runner - Starting the simulation for chunk 1 of 1
INFO - 2023-03-26 12:10:19,991 - root - Runner for chunk 1: Early termination in 4/2111
DEBUG - 2023-03-26 12:10:19,993 - pyprotolinc.runner - Starting backwards loop to calculate the reserves for chunk 1 of 1
DEBUG - 2023-03-26 12:10:20,048 - pyprotolinc.main - Combining results from subportfolios
INFO - 2023-03-26 12:10:20,049 - pyprotolinc.main - Elapsed time 0.3 seconds.

[11]:

	YEAR	QUARTER	MONTH	ANNUITY_PAYMENT1	RESERVE_BOM(DIS1)	...	MV_DIS1_DEATH
0	2021	4	12	0.000000	0.000000	...	0.000000
1	2022	1	1	-100.000000	30203.276681	...	0.000111
2	2022	1	2	-99.988915	30176.722349	...	0.000111
3	2022	1	3	-99.977832	30150.114342	...	0.000111
4	2022	2	4	-99.966750	30123.452528	...	0.000111

5 rows × 27 columns

Note that the reserve is 30,203 when using 3% compared to 45,698 when using 1%.