Question

Suppose you have purchased Home Owners Insurance. This insurance policy has a $5,000 deductible, so that if you have a claim and the damage is less than $5,000 you pay for it out of pocket. However, if the damage is greater than $5,000, you pay the first $5,000 and the insurance pays the rest. In the current year, there is a probability of 0.085 that you will have a claim. If you have a claim for damages on your home, the damage amount in normal distribution with mean of $7,000 and standard deviation of $2,000. Build a Monte Carlo simulation model to show your out of pocket expense in this scenario. Use a data table to run 5000 iterations of the model. Analyze the results of the 5000 iterations to find how often a claim was filed, and how often the claim met or exceeded the deductible amount. Show both as a percentage of the 5000 iterations. Must use excel sheet.

Answer 1

This is a good exercise to understand how to simulate (a) Binomial and (b) Normal distributions. The data comes really nicely and gives some tangible results about the number of times a claim is actually made, i.e. the amount exceeds $5000 in order to get an insurance value, the percentage of these claims as the total of all incidence when a damage was made, the average insurance amount etc.

So the work is actually excel based and cannot be pasted here. I am giving a walktrough of the process followed to simulate the data so you can understand how it works. I shall also share the excel.

First, we use the Binomial Inverse distribution function on excel, to generate 5000 incidence with the parameter p = 0.085. The exact input command is =BINOM.INV(1,0.085,RAND()). This generates 5000 values of 0 and 1, with proportions of 1's being very close to 0.085.

Second, we do a similar simulation of the Normal distribution, again 5000 times, with the command =NORM.INV(RAND(),7000,2000). We get a nicely laid out set of values.

The third step is to multiply, along each row, the value obtained from above two distributions. What that does is, give us a sample output of the number of times a claim is expected to be made, with those values following the mean and standard deviation as defined earlier.

Now we shall have certain proportion of those 5000 rows having non-zero values. This is an important data, because it tells us how many times some damage was borne by the person, irrespective of whether a claim ensued or jot.

Lastly what we now need to do is look at only those values in this column which exceed 5000, and find their value post deductible. So we do the command =MAX(0,D3-5000) on the damage made, where D3 is the cell reference for the damage.

Voila!! You are all set to do summary statistics on the values obtained. Here is a sample output of the simulation

Summary Statistic	Value
No of Claim Incidence	441
No of Claims Made	363
Claims Made as % of Incidence	82.31%
Average Amount of Claim Post Deductible	2479.431

These values are random on the excel, so on the file you download, they will change every time you click anywhere on the sheet. In case you need to report a fixed set of values, just copy the entire sheet, go to a new tab and use Paste with Values only.

Excel Link: https://drive.google.com/file/d/18kJOstZXqg_7lyEEqNdRW0JVNhx9t4nd/view?usp=sharing

PS: I have done the summary statistics on the base of number of times damages were done. You can just change the base to 5000, to report the final values as demanded by the problem.