An example of a Monte Carlo simulation risk analysis model for Insurance payout

The exposure to the claim amounts from an insurance policy are often shared between insurance companies through tranches, whereby each insurer covers a part of the individual claim amount.

The model uses one of several special insurance risk modeling tools available in the Complete Edition of ModelRisk.

Technical difficulty: 2

Techniques used: Monte Carlo simulation in Excel, Aggregate loss distribution, Insurance tranching, Lognormal distribution, Poisson distribution

ModelRisk functions used: VoseLognormalObject, VosePoisson, VoseAggregateTranche, VoseOutput

Model description

The risk analysis model is built using Excel with our Monte Carlo simulation add-in called ModelRisk. It uses the following formatting convention:

This example allows one to input the number of policy holders (n) and the expected annual number of claims each policy holder would make in a year (λ). Assuming independence between claims, this leads to the result that the actual number of claims will follow a Poisson(nλ) distribution (Cell C11):

The magnitude of a random financial loss to a policy holder is assumed for this model to follow a Lognormal distribution, though one could use any other distribution. ModelRisk has twenty or so distributions that may be suitable, and which can all be fit to data. For unusually distributed data one could also use one of ModelRisk’s non-parametric distributions. The distribution is entered as an Object (Cell C10).

The model uses the VoseAggregateTranche array function:
=VoseAggregateTranche(N,Cost Distribution, {Tranche Minima}, , {Tranche Maxima})
where:
N - the number of claims to sum
Cost Distribution - a non-negative distribution object
{Tranche Minima} – an array specifying the lower limit of the tranche for an individual loss
{Tranche Maxima} – an array specifying the upper limit of the tranche for an individual loss
The function transforms the loss amount of the policy holder into the payout amount of the insurer by splitting the claim size distribution into tranches. In this example, the claim size distribution is Lognormal(6000,7000) and is split into:

• Tranche 1: from $3,000 to $8,000
• Tranche 2: from $8,000 to $15,000
• Tranche 3: from $15,000 upwards (the function recognizes the designation “+infinity” in the model)

Notes

The main additional value provided by VoseAggregateTranche is that the correlation between exposures in each tranche is retained. Thus, as in the example model, an insurer can correctly gauge the exposure of covering more than one tranche, or fractions of several tranches.

It is not required that the {Min} and {Max} arrays are non-overlapping, but the user should use caution in this situation since summing any of the overlapping parts of the output array will double count the exposure.

It is also not required that the {Min} and {Max} arrays cover the entire domain of the severity distribution. For example, an insurer may only be interested in two non-contiguous tranches it proposes to cover. However, the sum of the output array will then no longer be the aggregate exposure to all insurers. In this example, no allocation is made for claim amounts below $3,000 – perhaps because this is covered by another insurer.

Results

The model takes about 7 seconds to run 5,000 samples. It is set up to directly show three reports within the ModelRisk ResultsViewer at the end of the simulation, which are described below.

The first tab shows a histogram plot of the probability distribution of the aggregate loss amount across all tranches:

This shows a loss ranging from around $175M to $200M, most likely around $186M.

The second tab shows the loss distributions across for individual tranche and across all tranches in cumulative form to more easily read off the probabilities:

Finally, the third tab shows these same distributions in box-plot form: