An example of a Monte Carlo simulation risk analysis model for determining the aggregate payout an insurer must make to policy holders

This is a more sophisticated model to determine the probability distribution of the estimated amount an insurer will need to pay in aggregate to policy holders of a particular policy over the next year.

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

Technical difficulty: 3

Techniques used: Monte Carlo simulation in Excel, Aggregate loss distribution, Poisson distribution, Lognormal distribution, modeling with Objects

ModelRisk functions used: VoseExpression, VoseLognormalObject, VoseBernoulliObject, VosePoisson, VoseAggregateMC, 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 C18):

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 C9).

The amount that the insurer pays against this loss is based on the following logic:

• There is a deductible of $5,000 and a maximum payout of $50,000.
• For damage up to $15,000, the insurer pays out 90% over the deductible
• For damage between $15,000 and $50,000, the insurer pays out 50% over the deductible
• The insurer will challenge the claim for ny damage exceeding $15,000, with an 8% probability of the challenge failing.

The VoseExpression function converts the above logic into a formula for an individual claim payout:
VoseExpression("IF(#1<5000,0,IF(#1<15000,(#1-5000)*0.9,IF(#1<50000,(#1-5000)*0.5*#2,500000*#2)))",VoseLognormalObject(17500,8600),VoseBernoulliObject(0.8))
In the formula, #1 refers to the first listed variable (a Lognormal(17500,8600), which is the damage size), and #2 refers to the second listed variable (a Bernoulli(0.8), which returns a 1 when the claim fails, and a 0 when it succeeds).

Results

The model takes about 120 seconds to run 5,000 samples. It is set up to directly show four 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 to the insurer:

This shows a loss ranging from $24M to $34M, most likely around $28M.

The second tab shows the same distribution in cumulative form to more easily read off the probabilities:

The third tab shows the distribution of the loss per payout:

The last tab compares the damage and claim amount distributions for a single incident under this policy:
The claim is shifted to the left compared with the damage because of the deductible and the percentage payout. It also has a probability of around 12% of being zero because of the claim being challenged above a certain damage amount.