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 simple 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: 1

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

ModelRisk functions used: VoseLognormalObject, VosePoisson, VoseAggregateDeduct, 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 C13):

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 model uses the VoseAggregateDeduct function:
=VoseAggregateDeduct(N,Cost Distribution, Deductible, MaxLimit)
where:
N - the number of claims to sum
Cost Distribution - a non-negative distribution object
Deductible - (optional) the deductible
MaxLimit - (optional) the maximum payout limit
The function transforms the loss amount of the policy holder into the payout amount of the insurer by truncation at (Deductible, Deductible+MaxLimit) followed by a shift to the left to set a minimum of zero. It then randomly samples this payout distribution N times, sums the N payout amounts and returns the sum.

Results

The model takes about 7 seconds to run 5,000 samples. It is set up to directly show two 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 $38M to $43M, most likely around $40.6M.

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