Life-Insurance-Pricing-and-Reserves-Model

Table of contents

• Project overview
• Data
• Technologies
• Features
• Model architecture
• Limitations
• Process
• Results
• What I learned
• How can it be improved
• Running the project

Project overview

This project computes net single premiums (NSP), annuities, reserves, and other actuarial metrics for different life insurance products (Term, Whole Life, and Endowment) using simulated policy data in Excel. It also includes the valuation of reserves as of a selected date.

Data

The dataset "Data" contains 62,484 simulated life insurance policies with the following variables:

Variable	Definition
IDPOL	Policy ID
SEXO	Client's gender
STATUS	Smoking status (F = Smoker, NF = Non-smoker)
PROD	Product type (V = Whole Life, T = Term, D = Endowment)
SAF	Sum assured
SAD	Paid-up insurance
ECON	Issue age
E_DESC	Adjusted age (If SEXO = F then ECON = ECON - 3. If STATUS = NF then ECON = ECON - 2)
COB	Pending charges
PAG	Payments made
T_P	Agreed interest rate
INIVIG	Policy start date (D/M/Y)
DIV	Dividends
MON	Currency

The mortality table used, "CNSFM-2013" corresponds to the "CNSFM 2013" mixed mortality table (men and women), applied to life insurance policies covering the risk of death for individuals without disability. The table and its documentation can be found in the file "CNSFM-2013" or in .pdf format at the original source.

Technologies

• Excel
• Visual Basic (VBA)

Features

This project provides the following functionalities:

• Net Single Premium (NSP) calculation: Computes NSP for Term, Whole Life, and Endowment insurance products.

• Actuarial metrics computation: Calculates annuities, gross premiums (PT), annual gross premiums (PT anual), risk premiums (PR), unearned premium reserves (RRC), earning factor (Factor de Dev), earned risk premium (PRD), and dividends.

• NSP valuation at a selected date: Computes the valuation of NSP as of a specified date.

• Summary table generation: Produces a summary table with results for each policy.

Model architecture

The model architecture is structured as follows:

Limitations

The main limitations of this project are:

• Mortality is assumed to follow the "CNSFM-2013" table without selection effects.
• Interest rates are assumed constant over time.
• Expenses and profit margins are deterministic and fixed across policies.
• Policyholder behavior is not modeled.

Process

First, the simulated policies from the file "Data" (62485 rows and 13 columns) were loaded into the sheet "Insumos", and the mortality table from "CNSFM-2013" (113 rows and 2 columns) was loaded into the sheet "Tabla de Mortalidad". The survival probability $p_{x}$ was then computed, and the sheet "Calculadora Seguros de Vida" was created, where a specific policy can be selected and its information displayed.

Next, a set of columns was constructed to compute variables such as $k$, $x + k$, $q_{x + k}$, $_{k}p_{x}$, as shown in the next figure:

These values were then used to compute NSP for Term, Whole Life, and Endowment insurance, as well as annuities, gross premiums, annual gross premiums, and risk premiums, assuming specific values for acquisition costs, administrative expenses, and profit margins. This sheet allows the user to select a particular policy using the variable IDPOL and compute all corresponding actuarial values based on its characteristics. The results obtained, depending on the type of policy, are displayed in the corresponding table as follows:

After that, the sheet "Calculadora Reservas" was created to compute reserves for each type of insurance and perform valuation at a selected date. Similar to the "Calculadora Seguros de Vida", a policy can be selected to perform the calculations, to achieve this, the following columns were introduced:

Variable	Definition
FINVIG	End of policy term (D/M/Y))
FECHAVAL	Valuation date (D/M/Y)
AÑOPOL	Current policy year
NPAGOS	Number of payments
COBROS	Number of charges made

After that, NSP values for Term, Whole Life, and Endowment insurance were computed with valuation at the selected date, along with annuities, unearned premium reserves, earning factors, earned risk premiums, and dividends. As in the "Calculadora Seguros de Vida" sheet, the results are displayed in a summary table as follows:

Finally, a VBA macro was developed to iterate through all policies in either of the two calculators, compute the corresponding actuarial metrics, and copy and paste the results into the "Insumos" sheet. The macro can be downloaded from the file "Copy-Macro.bas" or reviewed in the file "Copy-Macro.txt".

Results

NSP values for Term, Whole Life, and Endowment insurance (with and without valuation at a selected date), as well as annuities, gross premiums, annual gross premiums, risk premiums, unearned premium reserves, earning factor, earned risk premium, and dividends, were computed for 62,484 insurance policies in a matter of seconds.

Final recommendation

As a final recommendation, adjust the values of acquisition costs, administrative expenses, and profit margins according to your specific needs, and modify the macro to copy the desired set of results.

What I learned

The most important thing I learned from this project is that it is possible to compute a large number of actuarial metrics for insurance policies efficiently using tools such as Excel.

How can it be improved

• Add or modify discounts applied to the issue age ECON.
• Expand the set of computed results by including additional actuarial metrics.
• Improve the formatting and structure of the model.
• Use alternative mortality tables and different assumptions for administrative costs, acquisition expenses, and profit margins.

Running the project

To use the project, open the Excel file "Life-Insurance-Pricing-and-Reserves-Model.xlsx" and change the value in cell A3 to select a specific policy and compute its corresponding values:

To copy the results into another sheet, run the macro and adjust its parameters to select the desired range: