The CashValue_SE Model¶
Overview¶
The CashValue_SE
model projects cashflows
of a generic hypothetical savings product.
The model is a monthlystep model and
projects insurance cashflows of a sample model point at a time.
The model can project both new business and inforce policies.
The present values of cashflows are also calculated.
By default, the model is configured as follows:
4 types of product specs, A, B, C, D are available. The spec parameters are read into
product_spec_table
from the file product_spec_table.xlsx in the model folder.A and B are single premium products with limited policy terms, while C and D are level premium whole life products. A and B are simulate variable annuities, while C and D are simulate variable life.
By default, 4 model points, 1 for each product type, are set up. The 4 model points are new business policies issued at time 0. The model can also project inforce policies at time 0 or future new business policies issued after time 0.
Premiums, net of premium loadings if applicable, are transferred to the account value. Maintenance fees and costofinsurance charges are deducted from the account value at the beginning of every month. The investment return is then added to/subtracted from the account value. The investment returns are calculated from standard normal random numbers read into
std_norm_rand
from the file std_norm_rand.csv.Upon death, a death benefit is paid. The amount of the death benefit is the greater of the sum assured and the account value. The entire account value is transferred for paying the death benefit.
Upon lapse, the account value is paid out as surrender benefit. Whether surrender charge applies or not varies by the product types. If it applies, the cash surrender value is the account value net of the surrender charge amount.
For A and B, the maturity benefit is paid at maturity. The remaining account value is paid as the maturity benefit.
Depending on the product types, premium loads are collected and the remaining portions of the premiums are transferred to the account value. How much premium loading is collected from each premium varies by the product types, and it is specified in product_spec_table.xlsx. Whether each type has surrender charge and if so its percentages are also specified in the file.
Policy decrement¶
The number of policies at a certain time can take different values
depending on the timing of policy inflows and outflows at the same time.
To represent different values for the number of policies
depending on the timing of the policy flows,
pols_if_at(t, timing)
is introduced.
pols_if_at(t, timing)
calculates the number of policies inforce
at time t
and has a parameter named timing
in addition to t
.
Strings are passed to timing
to indicate at what timing the number
of polices inforce is measured.
"BEF_DECR"
: Before lapse and death"BEF_MAT"
: Before maturity"BEF_NB"
: Before new business
The figure below illustrates how various policy inflows and outflows
are modeled in this model for one calculation step
from time t1
to time t
.
pols_lapse(t)
and pols_death(t)
are the number of lapse and death from t1
to t
.
It is assumed that policies mature at the beginning of each month,
and new business policies enter at the beginning of the month
but after the maturity in that month.
Although the default model points are all new business policies,
CashValue_SE
reads the duration of each model
point at time 0 from the model point file.
The duration of a model point being N months (N > 0) means
N months have elapsed before time 0 since the issue of the model point.
If the duration is N months, the model point is issued
N months after time 0.
model_point_table
has the duration_mth
column,
and the column is read into the duration_mth(0)
.
If duration_mth(0)
is positive,
the model point is inforce policies and
the number of policies
at time 0 is read from the policy_count
column in model_point_table
into pols_if_init()
, and
pols_if_at(0, "BEF_MAT")
is set from pols_if_init()
.
duration_mth()
increments by 1 each step. If duration_mth()
is negative, policy_count
is read into pols_new_biz()
when duration_mth()
becomes 0.
Account value rollforward¶
The account value per policy at each t
is calculated
from the previous balance by adding cash inflows and
subtracting cash outflows.
The order and timing of the cashflows are as follows:
At the beginning of each month, premium net of loadings, if any, is put into the account value.
At the beginning of the month after the premium inflow, fees are deducted from the account value.
The investment income is earned on the account value throughout the month. Since policy decrement due to lapse and death is assumed to occur at the middle of the month, half the monthly investment return is assumed to be earned on the account values of the exiting policies.
Basic Usage¶
Reading the model¶
Create your copy of the savings library by following the steps on the Quick Start page. The model is saved as the folder named CashValue_SE in the copied folder.
To read the model from Spyder, rightclick on the empty space in MxExplorer, and select Read Model. Click the folder icon on the dialog box and select the CashValue_SE folder.
Getting the results¶
By default, the model has Cells
for outputting projection results as listed in the
Results section.
result_cf()
outputs cashflows of the selected model point,
result_pv()
outputs the present values of the cashflows,
result_pols()
outputs the decrement table of the model point.
All the Cells outputs the results as pandas DataFrame.
See the Quick Start page for how to get the results in an MxConsole and view the results in MxDataViewer.
Changing the model point¶
The model point to be selected is determined by
point_id
in Projection
.
It is 1
by default.
model_point_table
contains 4 model points
as a pandas DataFrame.
To change the model point to another one, set the other model point’s ID
to point_id
. Setting the new point_id
clears
all the values of Cells that are specific to the previous model point.
Getting multiple results¶
The Projection
space
is parameterized with point_id
,
i.e. the Projection space can have dynamic child spaces, such as
Projection[1]
, Projection[2]
, Projection[3]
…, each of which
represents the Projection for each of the model points.
Note
Getting results for too many dynamic child spaces
takes a considerable amount of time.
The default CashValue_SE model would take more than a minute
for 1000 model points on an ordinary spec PC.
To calculate for many model points,
consider using the CashValue_ME
model.
Model Specifications¶
The CashValue_SE model has only one UserSpace,
named Projection
,
and all the Cells and References are defined in the space.
The Projection Space¶
The main Space in the CashValue_SE
model.
Projection
is the only Space defined
in the CashValue_SE
model, and it contains
all the logic and data used in the model.
Parameters and References
(In all the sample code below,
the global variable Projection
refers to the
Projection
Space.)
 point_id¶
The ID of the selected model point.
point_id
is defined as a Reference, and its value is used for determining the selected model point. By default,1
is assigned. To select another model point, assign its model point ID to it:>>> Projection.point_id = 2
point_id
is also defined as the parameter of theProjection
Space, which makes it possible to create dynamic child space for multiple model points:>>> Projection.parameters ('point_id',) >>> Projection[1] <ItemSpace CashValue_SE.Projection[1]> >>> Projection[2] <ItemSpace CashValue_SE.Projection[2]>
See also
 model_point_table¶
All model points as a DataFrame. By default, 4 model points are defined. The DataFrame has an index named
point_id
, andmodel_point()
returns a record as a Series whose index value matchespoint_id
. The DataFrame has the following columns:spec_id
age_at_entry
sex
policy_term
policy_count
sum_assured
duration_mth
premium_pp
av_pp_init
Cells defined in
Projection
with the same names as these columns return the corresponding column’s values for the selected model point.>>> Projection.model_poit_table spec_id age_at_entry sex ... premium_pp av_pp_init poind_id ... 1 A 20 M ... 500000 0 2 B 50 M ... 500000 0 3 C 20 M ... 1000 0 4 D 50 M ... 1000 0 [4 rows x 10 columns]
The DataFrame is saved in the Excel file model_point_samples.xlsx placed in the model folder.
model_point_table
is created by Projection’s new_pandas method, so that the DataFrame is saved in the separate file. The DataFrame has the injected attribute of_mx_dataclident
:>>> Projection.model_point_table._mx_dataclient <PandasData path='model_point_table_samples.xlsx' filetype='excel'>
 disc_rate_ann¶
Annual discount rates by duration as a pandas Series.
>>> Projection.disc_rate_ann year 0 0.00000 1 0.00555 2 0.00684 3 0.00788 4 0.00866 146 0.03025 147 0.03033 148 0.03041 149 0.03049 150 0.03056 Name: disc_rate_ann, Length: 151, dtype: float64
The Series is saved in the Excel file disc_rate_ann.xlsx placed in the model folder.
disc_rate_ann
is created by Projection’s new_pandas method, so that the Series is saved in the separate file. The Series has the injected attribute of_mx_dataclident
:>>> Projection.disc_rate_ann._mx_dataclient <PandasData path='disc_rate_ann.xlsx' filetype='excel'>
See also
 mort_table¶
Mortality table by age and duration as a DataFrame. See basic_term_sample.xlsx included in this library for how the sample mortality rates are created.
>>> Projection.mort_table 0 1 2 3 4 5 Age 18 0.000231 0.000254 0.000280 0.000308 0.000338 0.000372 19 0.000235 0.000259 0.000285 0.000313 0.000345 0.000379 20 0.000240 0.000264 0.000290 0.000319 0.000351 0.000386 21 0.000245 0.000269 0.000296 0.000326 0.000359 0.000394 22 0.000250 0.000275 0.000303 0.000333 0.000367 0.000403 .. ... ... ... ... ... ... 116 1.000000 1.000000 1.000000 1.000000 1.000000 1.000000 117 1.000000 1.000000 1.000000 1.000000 1.000000 1.000000 118 1.000000 1.000000 1.000000 1.000000 1.000000 1.000000 119 1.000000 1.000000 1.000000 1.000000 1.000000 1.000000 120 1.000000 1.000000 1.000000 1.000000 1.000000 1.000000 [103 rows x 6 columns]
The DataFrame is saved in the Excel file mort_table.xlsx placed in the model folder.
mort_table
is created by Projection’s new_pandas method, so that the DataFrame is saved in the separate file. The DataFrame has the injected attribute of_mx_dataclident
:>>> Projection.mort_table._mx_dataclient <PandasData path='mort_table.xlsx' filetype='excel'>
See also
 std_norm_rand¶
Random numbers drawn from the standard normal distribution.
A Series of random numbers drawn from the standard normal distribution indexed with
scen_id
andt
. Used for generating investment returns. Seeinv_return_table()
.
 scen_id¶
Selected scenario ID
An integer indicating the selected scenario ID.
scen_id
is referenced in byinv_return_mth()
as one of the keys to select a scenario fromstd_norm_rand
.
 surr_charge_table¶
Surrender charge rates by duration.
A DataFrame of multiple patterns of surrender charge rates by duration. The column labels indicate
surr_charge_id()
. By default,"type_1"
,"type_2"
and"type_3"
are defined.
 product_spec_table¶
Table of product specs.
A DataFrame of product spec parameters by
spec_id
.model_point_table
andproduct_spec_table
columns are joined inmodel_point_table_ext()
, and theproduct_spec_table
columns become part of the model point attributes. Theproduct_spec_table
columns are read by the Cells with the same names as the columns:>>> Projection.product_spec_table premium_type has_surr_charge surr_charge_id load_prem_rate is_wl spec_id A SINGLE False NaN 0.10 False B SINGLE True type_1 0.00 False C LEVEL False NaN 0.10 True D LEVEL True type_3 0.05 True
Projection parameters¶
The time step of the model is monthly. Cashflows and other timedependent
variables are indexed with t
.
Projection is carried out separately for individual model points.
proj_len()
calculates the number of months to be
projected for the selected model point.
Cashflows and other flows that accumulate throughout a period
indexed with t
denote the sums of the flows from t
til t+1
.
Balance items indexed with t
denote the amount at t
.

Projection length in months 
Model point data¶
The model point data stored in an Excel file named model_point_table.xlsx
under the model folder is read into model_point_table
.
Policy attributes that only vary by product spec are
stored separately in another Excel file named product_spec_table.xlsx,
and read into product_spec_table
.
The product_spec_table
attributes are joined with
model_point_table
in model_point_table_ext()
and referenced from model_point()
.
The selected model point as a Series 

Extended model point table 


The sex of the selected model point 
The sum assured of the selected model point 

The policy term of the selected model point. 


The attained age at time t. 
The age at entry of the selected model point 


Duration of the selected model point at 

Duration of the selected model point at 
Whether surrender charge applies 


Whether the model point is whole life 
Rate of premium loading 

ID of surrender charge pattern 

Type of premium payment 

Initial account value per policy 
Assumptions¶
The mortality table is stored in an Excel file named mort_table.xlsx
under the model folder, and is read into mort_table
as a DataFrame.
mort_rate()
looks up mort_table
and picks up
the annual mortality rate to be applied for the selected
model point at time t
.
mort_rate_mth()
converts mort_rate()
to the monthly mortality
rate to be applied during the month starting at time t
.
The discount rate data is stored in an Excel file named disc_rate_ann.xlsx
under the model folder, and is read into disc_rate_ann
as a Series.
The lapse by duration is defined by a formula in lapse_rate()
.
expense_acq()
holds the acquisition expense per policy at t=0.
expense_maint()
holds the maintenance expense per policy per annum.
The maintenance expense inflates at a constant rate
of inflation given as inflation_rate()
.
The last age of mortality tables 


Mortality rate to be applied at time t 
Monthly mortality rate to be applied at time t 

Discount factors. 

Monthly discount rate 


Lapse rate 
Acquisition expense per policy 

Annual maintenance expense per policy 

The inflation factor at time t 

Inflation rate 
Policy values¶
By default, the amount of death benefit for each policy (claim_pp()
)
is set equal to sum_assured
.
premium_pp()
is the single premium amount if the model point
represents single premium policies (i.e. premium_type()
is "SINGLE"
),
or the monthly premium amount if the model point represents
level premium policies (i.e. premium_type()
is "LEVEL"
).

Claim per policy 

Premium amount per policy 
Maintenance fee per account value 


Cost of insurance rate per account value 
Surrender charge rate 
Policy decrement¶
At t=0
If the selected model point represents inforce policies, i.e.
the duration_mth
of the model point in model_point_table
is positive, pols_if_at(0, "BEF_MAT")
is set to
the value through pols_if_init()
.
At each projection step
pols_if_at(t, timing)
represents
the number of policies at t
.
The timing
parameter can take the following string values.
"BEF_MAT"
: Before maturity"BEF_NB"
: Before new business"BEF_DECR"
: Before lapse and death
Policy flows and inforce at each timing from t1
to t
are calculated recursively as follows:
pols_if_at(t1, "BEF_DECR")
is calculated by addingpols_new_biz(t1)
topols_if_at(t1, "BEF_NB")
.pols_if_at(t, "BEF_MAT")
is calculated by deductingpols_lapse(t)
andpols_death(t)
frompols_if_at(t1, "BEF_DECR")
.pols_if_at(t, "BEF_NB")
is calculated by deductingpols_maturity(t)
frompols_if_at(t, "BEF_MAT")
.pols_if_at(t, "BEF_DECR")
is calculated bypols_new_biz(t)
frompols_if_at(t, "BEF_NB")
.
It is assumed that policies mature at the beginning of each month,
and new business policies enter at the beginning of the month
but after the maturity in that month.
pols_if(t)
is an alias
for pols_if_at(t, "BEF_MAT")
.
The figure below illustrates how various policy inflows and outflows
are modeled in this model for one calculation step
from time t1
to time t
.

Number of death 

Number of policies inforce 

Number of policies inforce 
Initial number of policies inforce 


Number of lapse 
Number of maturing policies 


Number of new business policies 
Account Value¶
av_pp_at(t, timing)
calculates the account value per policy
at t
.
Since av_pp_at()
can take multiple values for the same t
depending on the timing of various cashflows into and out of the
account value, the second parameter timing
is used
to specify the timing of measuring the account value.
av_pp_at(t, 'BEF_PREM')
indicates
the account value before premium payment for the month.
At time 0, it is read from av_pp_init()
,
otherwise its calculated from the previous period
as av_pp_at(t1, 'BEF_INV')
plus
inv_income_(t1)
.
av_pp_at(t, 'BEF_FEE')
indicates
the account value after the premium payment before fee deduction, and
is calculated as av_pp_at(t, 'BEF_PREM')
plus
prem_to_av_pp()
.
av_pp_at(t, 'BEF_INV')
indicates
the account value after then fee deduction before earning investment yield, and
is calculated as av_pp_at(t, 'BEF_FEE')
minus
maint_fee_pp()
and coi_pp()
.
av_pp_at(t, 'MID_MTH')
indicates
the account value at t+0.5
, and half of inv_income()
is earned. av_pp_at(t, 'MID_MTH')
is
for policies exiting by lapse and death during the month.

Investment income on account value 
Investment income on account value per policy 

Rate of investment return 

Table of investment return rates 


Account value per policy 
Net amount at risk per policy 


Cost of insurance charges per policy 
Perpolicy premium portion put in the account value 


Maintenance fee per policy 

Account value inforce 

Premium portion put in account value 

Account value taken out to pay claim 
Claim in excess of account value 


Cost of insurance charges 

Maintenance fee deducted from account value 

Change in account value 
Check account value rollforward 
Cashflows¶
Cashflows are calculated as its perpolicy amount times the number of policies.
The expense cashflow consists of acquisition expenses at issue and monthly maintenance expenses spent each month.
By default, commissions are defined as 5% premiums.

Surrender charge 

Claims 

Commissions 

Premium income 

Expenses 

Net cashflow 
Margin Analysis¶
net_cf()
can be expressed as the sum of expense and mortality
margins. The expense margin is defined as the sum of
premium loading, surrender charge and maintenance fees
net of commissions and expenses.
The mortality margin is defined coi()
net of claims_over_av()
.
Expense margin 

Mortality margin 

Check consistency between net cashflow and margins 
Present values¶
The Cells whose names start with pv_
are for calculating
the present values of the cashflows indicated by the rest of their names.
pv_pols_if()
is not used
in CashValue_SE
and BasicTerm_ME
.

Present value of claims 
Present value of commissions 

Present value of expenses 

Present value of net cashflows. 

Present value of policies inforce 

Present value of premiums 

Present value of change in account value 

Present value of investment income 

Check present value summation 
Results¶
result_cf()
outputs the cashflows of the selected model point
as a DataFrame:
>>> result_cf()
Premiums Death ... Change in AV Net Cashflow
0 50000000.0 999.844857 ... 4.447174e+07 2.033342e+06
1 0.0 991.084783 ... 1.065060e+06 3.292919e+04
2 0.0 982.401460 ... 2.039757e+05 3.208843e+04
3 0.0 973.794216 ... 2.527055e+05 3.228511e+04
4 0.0 965.262383 ... 7.053975e+05 3.210189e+04
.. ... ... ... ... ...
116 0.0 1346.032341 ... 2.851405e+04 2.368209e+04
117 0.0 1343.713636 ... 2.927039e+05 2.370171e+04
118 0.0 1341.398924 ... 4.096877e+05 2.347573e+04
119 0.0 1339.088201 ... 4.207922e+05 2.381819e+04
120 0.0 0.000000 ... 3.263268e+07 0.000000e+00
[121 rows x 9 columns]
result_pv()
outputs the present values of the cashflows:
>>> result_pv()
Premiums Death ... Commissions Net Cashflow
PV 50000000.0 135032.740399 ... 2500000.0 4.957050e+06
Result table of cashflows 

Result table of present value of cashflows 

Result table of policy decrement 