The CashValue_ME Model#

Overview#

The CashValue_ME model is a faster reimplementation of the CashValue_SE model.

The CashValue_ME model reproduces the same results as CashValue_SE, but is more suitable for processing a large number of model points. Each formula to be applied to all the model points operates on the entire set of model points at once with the help of Numpy and Pandas.

The default product specs, assumptions and input data are the same as CashValue_SE.

Changes from CashValue_SE#

Below is the list of Cells and References that are newly added or updated from CashValue_SE.

Running with 10000 model points#

The main advantage of the CashValue_ME model over the CashValue_SE model is its speed. By default, CashValue_ME is configured to run the same 4 model points as the ones in CashValue_SE, but a larger table of 10000 model points is also included in the model. The larger model point table is saved in the model folder as an Excel file named model_point_10000.xlsx, and this table is read into the model as a DataFrame named model_point_10000. The 10000 model points are all new business at time 0, and created by modifying the sample model points in basiclife.

To run the model with the larger model point table, assign the table to model_point_table:

>>> Projection.model_point_table = Projection.model_point_10000

In the code above, Projection must be defined beforehand to refer to the Projection space.

Below is the speed result of running the entire 10000 model points on a consumer PC equipped with Intel Core i5-6500T CPU and 16GB RAM.

10000 model points with CashValue_ME without split by product#

>>> timeit.timeit("Projection.result_pv()", globals=globals(), number=1)
34.3045132

>>> Projection.result_pv()
               Premiums         Death  ...  Change in AV  Net Cashflow
policy_id                              ...
1          5.349200e+07  4.852612e+05  ...  4.465141e+06  1.881696e+06
2          4.446054e+07  1.356468e+07  ...  1.604824e+07  1.177058e+07
3          1.210841e+08  7.179706e+07  ...  2.796228e+07  2.192224e+07
4          3.038400e+07  2.938897e+05  ...  4.840159e+06  4.346383e+06
5          5.989500e+07  3.342985e+05  ...  7.238519e+06  7.671440e+06
                ...           ...  ...           ...           ...
9996       2.067500e+07  5.335816e+05  ...  3.627718e+06  1.951471e+06
9997       6.690600e+07  4.291283e+05  ...  8.984202e+06  4.590207e+06
9998       7.629662e+06  4.007463e+06  ...  1.982635e+06  1.441142e+06
9999       2.835552e+06  1.258307e+06  ...  8.583928e+05  4.917449e+05
10000      1.513202e+07  3.834462e+06  ...  6.555234e+06  3.667102e+06

[10000 rows x 9 columns]

The above run projects all model points for the max length of the entire model points:

>>> Projection.max_proj_len()
1141

Since product A and B are limited term up to 20 years and C and D are whole life, it may be more efficient to run the limited term and whole life model points separately, because the limited term model points don’t need as long the length of projection period as C and D model points. You can do so by defining, for example, seg_id to filter model points in the formula of model_point(). The code below is an example of the modified formula of model_point() to filter the model points by seg_id:

>>> Projection.model_point.formula
def model_point():
    """"Target model points
    ...
    """
    cond = model_point_table_ext()['is_wl'] == (True if seg_id == 'WL' else False)
    return model_point_table_ext().loc[cond]

Assigning "WL" to seg_id results in running only whole life model points, while assigning anything other than "WL" results in running limited term model points:

>>> Projection.seg_id = 'WL'

>>> timeit.timeit("Projection.result_pv()", globals=globals(), number=1)
24.311953799999998

>>> Projection.result_pv()
               Premiums         Death  ...  Change in AV  Net Cashflow
policy_id                              ...
2          4.446054e+07  1.356468e+07  ...  1.604824e+07  1.177058e+07
3          1.210841e+08  7.179706e+07  ...  2.796228e+07  2.192224e+07
6          5.051520e+06  3.011482e+06  ...  1.152065e+06  4.933033e+04
9          5.537287e+07  3.686858e+07  ...  1.126645e+07  7.784990e+06
10         2.957650e+07  1.156441e+07  ...  9.877157e+06  6.451814e+06
                ...           ...  ...           ...           ...
9988       5.051377e+05  1.692807e+05  ...  1.715821e+05  1.065215e+05
9989       2.889266e+07  1.769398e+07  ...  5.681889e+06  4.869589e+06
9998       7.629662e+06  4.007463e+06  ...  1.982635e+06  1.441142e+06
9999       2.835552e+06  1.258307e+06  ...  8.583928e+05  4.917449e+05
10000      1.513202e+07  3.834462e+06  ...  6.555234e+06  3.667102e+06

[5015 rows x 9 columns]

>>> Projection.max_proj_len()
1141

>>> Projection.seg_id = 'NWL'

>>> timeit.timeit("Projection.result_pv()", globals=globals(), number=1)
5.201247100000003

>>> Projection.result_pv()
             Premiums          Death  ...  Change in AV  Net Cashflow
policy_id                             ...
1          53492000.0  485261.238999  ...  4.465141e+06  1.881696e+06
4          30384000.0  293889.696238  ...  4.840159e+06  4.346383e+06
5          59895000.0  334298.511514  ...  7.238519e+06  7.671440e+06
7          42066000.0  337495.895163  ...  3.513768e+06  1.355059e+06
8           5270000.0   85955.434866  ...  7.032080e+05 -2.137742e+05
              ...            ...  ...           ...           ...
9993        1116000.0    6862.126164  ...  1.498832e+05  6.988384e+04
9994       22050000.0  765048.795780  ...  3.453998e+06  3.159848e+06
9995        3420000.0   24997.118829  ...  6.067274e+05 -4.430946e+04
9996       20675000.0  533581.639585  ...  3.627718e+06  1.951471e+06
9997       66906000.0  429128.288942  ...  8.984202e+06  4.590207e+06

[4985 rows x 9 columns]

>>> Projection.max_proj_len()
1141

To keep the results for both "WL" and "NWL", you can parameterize Projection with seg_id and have Projection['WL'] and Projection['NWL'] as dynamic child spaces of Projection:

>>> Projection.parameters = ("seg_id",)

>>> Projection['WL'].model_point()
          spec_id  age_at_entry sex  ...  surr_charge_id  load_prem_rate  is_wl
policy_id                            ...
2               C            29   M  ...             NaN            0.10   True
3               D            51   F  ...          type_3            0.05   True
6               D            51   F  ...          type_3            0.05   True
9               D            59   F  ...          type_3            0.05   True
10              D            35   F  ...          type_3            0.05   True
          ...           ...  ..  ...             ...             ...    ...
9988            C            32   M  ...             NaN            0.10   True
9989            C            56   M  ...             NaN            0.10   True
9998            D            45   F  ...          type_3            0.05   True
9999            D            39   M  ...          type_3            0.05   True
10000           D            22   F  ...          type_3            0.05   True

[5015 rows x 14 columns]

>>> Projection['WL'].result_pv()
               Premiums         Death  ...  Change in AV  Net Cashflow
policy_id                              ...
2          4.446054e+07  1.356468e+07  ...  1.604824e+07  1.177058e+07
3          1.210841e+08  7.179706e+07  ...  2.796228e+07  2.192224e+07
6          5.051520e+06  3.011482e+06  ...  1.152065e+06  4.933033e+04
9          5.537287e+07  3.686858e+07  ...  1.126645e+07  7.784990e+06
10         2.957650e+07  1.156441e+07  ...  9.877157e+06  6.451814e+06
                ...           ...  ...           ...           ...
9988       5.051377e+05  1.692807e+05  ...  1.715821e+05  1.065215e+05
9989       2.889266e+07  1.769398e+07  ...  5.681889e+06  4.869589e+06
9998       7.629662e+06  4.007463e+06  ...  1.982635e+06  1.441142e+06
9999       2.835552e+06  1.258307e+06  ...  8.583928e+05  4.917449e+05
10000      1.513202e+07  3.834462e+06  ...  6.555234e+06  3.667102e+06

[5015 rows x 9 columns]

>>> Projection['NWL'].model_point()
          spec_id  age_at_entry sex  ...  surr_charge_id  load_prem_rate  is_wl
policy_id                            ...
1               B            47   M  ...          type_1             0.0  False
4               A            32   F  ...             NaN             0.1  False
5               A            28   M  ...             NaN             0.1  False
7               B            45   F  ...          type_1             0.0  False
8               B            47   F  ...          type_1             0.0  False
          ...           ...  ..  ...             ...             ...    ...
9993            B            29   M  ...          type_1             0.0  False
9994            A            52   F  ...             NaN             0.1  False
9995            B            24   M  ...          type_1             0.0  False
9996            B            47   M  ...          type_1             0.0  False
9997            B            30   M  ...          type_1             0.0  False

[4985 rows x 14 columns]

>>> Projection['NWL'].result_pv()
             Premiums          Death  ...  Change in AV  Net Cashflow
policy_id                             ...
1          53492000.0  485261.238999  ...  4.465141e+06  1.881696e+06
4          30384000.0  293889.696238  ...  4.840159e+06  4.346383e+06
5          59895000.0  334298.511514  ...  7.238519e+06  7.671440e+06
7          42066000.0  337495.895163  ...  3.513768e+06  1.355059e+06
8           5270000.0   85955.434866  ...  7.032080e+05 -2.137742e+05
              ...            ...  ...           ...           ...
9993        1116000.0    6862.126164  ...  1.498832e+05  6.988384e+04
9994       22050000.0  765048.795780  ...  3.453998e+06  3.159848e+06
9995        3420000.0   24997.118829  ...  6.067274e+05 -4.430946e+04
9996       20675000.0  533581.639585  ...  3.627718e+06  1.951471e+06
9997       66906000.0  429128.288942  ...  8.984202e+06  4.590207e+06

[4985 rows x 9 columns]

While running the entire model points at once took 34 seconds, running the whole life and limited term model points separately took about 30 seconds in total. The whole life segment is about half the size of the entire model points, and takes 24 seconds, while the entire segment takes 34 seconds, which implies the processing speed per model point improves as the number of model points gets larger.

Formula examples#

Most formulas in the CashValue_ME model are the same as those in CashValue_SE. However, some formulas are updated since they cannot be applied to vector operations without change. For example, below shows how pols_maturity, the number of maturing policies at time t, is defined differently in CashValue_SE and in CashValue_ME.

pols_maturity in CashValue_SE#

def pols_maturity(t):
    if duration_mth(t) == policy_term() * 12:
        return pols_if_at(t, "BEF_MAT")
    else:
        return 0

pols_maturity in CashValue_ME#

 def pols_maturity(t):
     return (duration_mth(t) == policy_term() * 12) * pols_if_at(t, "BEF_MAT")

In CashValue_SE, policy_term() returns an integer, such as 10 indicating a policy term of the selected model point in years, so the if clause checks if the value of duration_mth() is equal to the policy term in month:

In CashValue_SE for model point 1#

>>> policy_term()
10

>>> pols_maturity(120)
65.9357318577613

In contrast, policy_term() in CashValue_ME returns a Series of policy terms of all the model points. If the if clause were defined in the same way as in the CashValue_SE, it would result in an error, because the condition duration_mth(t) == policy_term() * 12 for a certain t returns a Series of boolean values and it is ambiguous for the Series to be in the if condition. Further more, whether the if branch or the else branch should be evaluated needs to be determined element-wise, but the if statement would not allow such element-wise branching. Instead of using the if statement, the formula in CashValue_ME achieves the element-wise conditional operation by multiplication by a Series of boolean values. In the formula in CashValue_ME, pols_if_at(t, "BEF_MAT") returns the numbers of policies at time t for all the model points as a Series. Multiplying it by (duration_mth(t) == policy_term() * 12) replaces the numbers of policies with 0 for model points whose policy terms in month are not equal to t. This operation is effectively an element-wise if operation:

In CashValue_ME at t=120#

>>> policy_term()
poind_id
1    10
2    20
3    95
4    65
dtype: int64


>>> (duration_mth(120) == policy_term() * 12)
poind_id
1     True
2    False
3    False
4    False
dtype: bool


>>> pols_maturity(120)
1    65.935732
2     0.000000
3     0.000000
4     0.000000
dtype: float64

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_ME in the copied folder.

To read the model from Spyder, right-click on the empty space in MxExplorer, and select Read Model. Click the folder icon on the dialog box and select the CashValue_ME folder.

Getting the results#

By default, the model has Cells for outputting projection results as listed in the Results section. result_cf() outputs total cashflows of all the model points, and result_pv() outputs the present values of the cashflows by model points. Both Cells outputs the results as pandas DataFrame.

By following the same steps explained in the Quick Start page using this model, You can get the results in an MxConsole and show the results as tables in MxDataViewer.

Changing the model point#

By default, model_point() returns the entire model_point_table_ext():

>>> Projection.model_point.formula
def model_point():
    return model_point_table_ext()

The calculations in Projection apply to all the model points in model_point_table. To limit the calculation target, change the model_point() formula so that model_point() returns a DataFrame that contains only the target rows. For example, to select only the model point 1:

>>> Projection.model_point.formula
def model_point():
    return model_point_table_ext().loc[1:1]

There are many methods of DataFrame for selecting its rows. See the pandas documentation for details.

When selecting only one model point, make sure that model_point() returns the model point as a DataFrame not as a Series. In the code example above, model_point_table_ext().loc[1:1] is specified instead of model_point_table_ext().loc[1], because model_point_table_ext().loc[1] would return the model point as a Series.

Also, you should be careful not to accidentally update the original DataFrame held as model_point_table.

Model Specifications#

The CashValue_ME 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_ME model.

Projection is the only Space defined in the CashValue_ME 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.)

model_point_table#

All model points as a DataFrame. By default, 4 model points are defined. The DataFrame has an index named point_id.

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

Projection parameters#

This is a new business model and all model points are issued at time 0. The time step of the model is monthly. Cashflows and other time-dependent variables are indexed with t.

Cashflows and other flows that accumulate throughout a period indexed with t denotes the sums of the flows from t til t+1. Balance items indexed with t denotes the amount at t.

`proj_len`()	Projection length in months
`max_proj_len`()	The max of all projection lengths

Model point data#

The model point data is stored in an Excel file named model_point_table.xlsx under the model folder.

`model_point`()	Target model points
`model_point_table_ext`()	Extended model point table
`sex`()	The sex of the model points
`sum_assured`()	The sum assured of the model points
`policy_term`()	The policy term of the model points.
`age`(t)	The attained age at time t.
`age_at_entry`()	The age at entry of the model points
`duration`(t)	Duration of model points at `t` in years
`duration_mth`(t)	Duration of model points at `t` in months
`has_surr_charge`()	Whether surrender charge applies
`is_wl`()	Whether the model point is whole life
`load_prem_rate`()	Rate of premium loading
`surr_charge_id`()	ID of surrender charge pattern
`premium_type`()	Type of premium payment
`av_pp_init`()	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_table_reindexed() returns a mortality table reshaped from mort_table, which is a Series indexed with Age and Duration. mort_rate() looks up mort_table_reindexed() and picks up the annual mortality rates to be applied for all the model points at time t and returns them in a Series. 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().

`mort_table_last_age`()	The last age of mortality tables
`mort_rate`(t)	Mortality rate to be applied at time t
`mort_rate_mth`(t)	Monthly mortality rate to be applied at time t
`mort_table_reindexed`()	MultiIndexed mortality table
`disc_factors`()	Discount factors.
`disc_rate_mth`()	Monthly discount rate
`lapse_rate`(t)	Lapse rate
`expense_acq`()	Acquisition expense per policy
`expense_maint`()	Annual maintenance expense per policy
`inflation_factor`(t)	The inflation factor at time t
`inflation_rate`()	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_pp`(t, kind)	Claim per policy
`premium_pp`(t)	Premium amount per policy
`maint_fee_rate`()	Maintenance fee per account value
`coi_rate`(t)	Cost of insurance rate per account value
`surr_charge_rate`(t)	Surrender charge rate
`surr_charge_table_stacked`()	Stacked surrender charge table
`surr_charge_max_idx`()	maximum index of surrender charge table

Policy decrement#

The policy decrement logic of CashValue_ME is based on that of CashValue_SE. except that each relevant formula operates on the entire model points.

`pols_death`(t)	Number of death
`pols_if`(t)	Number of policies in-force
`pols_if_at`(t, timing)	Number of policies in-force
`pols_if_init`()	Initial number of policies in-force
`pols_lapse`(t)	Number of lapse
`pols_maturity`(t)	Number of maturing policies
`pols_new_biz`(t)	Number of new business policies

Account Value#

The account value logic of CashValue_ME is based on CashValue_SE. except that each relevant formula operates on the entire model points.

`inv_income`(t)	Investment income on account value
`inv_income_pp`(t)	Investment income on account value per policy
`inv_return_mth`(t)	Rate of investment return
`inv_return_table`()	Table of investment return rates
`av_pp_at`(t, timing)	Account value per policy
`net_amt_at_risk`(t)	Net amount at risk per policy
`coi_pp`(t)	Cost of insurance charges per policy
`prem_to_av_pp`(t)	Per-policy premium portion put in the account value
`maint_fee_pp`(t)	Maintenance fee per policy
`av_at`(t, timing)	Account value in-force
`prem_to_av`(t)	Premium portion put in account value
`claims_from_av`(t, kind)	Account value taken out to pay claim
`claims_over_av`(t, kind)	Claim in excess of account value
`coi`(t)	Cost of insurance charges
`maint_fee`(t)	Maintenance fee deducted from account value
`av_change`(t)	Change in account value
`check_av_roll_fwd`()	Check account value roll-forward

Cashflows#

Cashflows are calculated as its per-policy 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.

`surr_charge`(t)	Surrender charge
`claims`(t[, kind])	Claims
`commissions`(t)	Commissions
`premiums`(t)	Premium income
`expenses`(t)	Expenses
`net_cf`(t)	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().

`margin_expense`(t)	Expense margin
`margin_mortality`(t)	Mortality margin
`check_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.

`pv_claims`([kind])	Present value of claims
`pv_commissions`()	Present value of commissions
`pv_expenses`()	Present value of expenses
`pv_net_cf`()	Present value of net cashflows.
`pv_pols_if`()	Present value of policies in-force
`pv_premiums`()	Present value of premiums
`pv_av_change`()	Present value of change in account value
`pv_inv_income`()	Present value of investment income
`check_pv_net_cf`()	Check present value summation

Results#

result_cf() outputs the total cashflows of all the model points as a DataFrame:

>>> result_cf()
          Premiums         Claims      Expenses   Commissions  Net Cashflow
   1.002000e+08  795274.520511  2.016667e+06  5.010000e+06 -1.888257e+06
   1.982432e+05  782659.768793  1.653397e+04  9.912161e+03  1.102781e+05
   1.965019e+05  776554.840116  1.640233e+04  9.825093e+03  1.083123e+05
   1.947758e+05  777512.879071  1.627174e+04  9.738790e+03  1.088861e+05
   1.930649e+05  770406.958695  1.614219e+04  9.653245e+03  1.082080e+05
           ...            ...           ...           ...           ...
0.000000e+00       0.000000  0.000000e+00  0.000000e+00  0.000000e+00
0.000000e+00       0.000000  0.000000e+00  0.000000e+00  0.000000e+00
0.000000e+00       0.000000  0.000000e+00  0.000000e+00  0.000000e+00
0.000000e+00       0.000000  0.000000e+00  0.000000e+00  0.000000e+00
0.000000e+00       0.000000  0.000000e+00  0.000000e+00  0.000000e+00

[1141 rows x 5 columns]

result_pv() outputs the present values of the cashflows by model points:

>>> result_pv()
              Premiums         Death  ...  Change in AV  Net Cashflow
poind_id                              ...
1         5.000000e+07  1.350327e+05  ...  3.771029e+06  4.957050e+06
2         5.000000e+07  1.608984e+06  ...  8.740610e+06  4.241619e+06
3         2.642236e+07  6.107771e+06  ...  1.104837e+07  6.058365e+06
4         2.201418e+07  1.329419e+07  ...  4.763115e+06  2.514042e+06

[4 rows x 9 columns]

`result_cf`()	Result table of cashflows
`result_pv`()	Result table of present value of cashflows
`result_pols`()	Result table of policy decrement