Lab exercise: Monte Carlo Simulation of an Auto Insurance Pool
Insurance exploits the statistical properties of pooled independent risks. Let’s assume the typical driver has a 1% chance of having a car accident in any given year, and each driver’s risk of an accident is completely independent of any other driver’s risk. The distribution of each individual accident is exponential with a mean of $50,000.
Create 1000 simulations of an accident year with 1000 drivers. For each simulation, simulate the number of accidents, then simulate the total claims cost for the year. You may find it useful to know that the sum of independent exponential random variables has a gamma distribution.
Use the AVERAGE and STDEV.S functions to calculate the mean and standard deviation of the total claims cost, total number of claims, average claims size, and average cost per policyholder. Additionally, create a histogram of the total claims size. Comment on the shape.
The probability of sufficiency is the probability that the premiums collected for one year is sufficient to cover the total claims cost for that year. Find the APRA regulated probability of sufficiency for general insurers in Australia. From the simulations, determine the minimum premium that has the required probability of sufficiency, you may find the PERCENTILE function useful here.
Make sure you can easily recreate the scenario with different parameters. i.e. it should be easy to adjust the: probability of a driver having an accident, mean claim size, and the number of drivers. All calculations must automatically update if any of these parameters are changed. The spreadsheet should have professional, and consistent formatting.
Illustration:
As way of illustration, suppose the probability of sufficiency is 60%. You would need to find the premium charged such that the total premium exceeds the total claims cost 60% of the time based off your 1000 simulations.
1