Payout distribution for an insurance policy

An example of a risk analysis model for determining the potential size of a bank’s loss from fraud

Banks must reserve capital to cover unexpected operational risk events, where operational risks are those that occur from running the business rather than potential losses from trades and loans.

This model illustrates how one can use distributions of frequency and magnitude fitted to data to determine the probability distribution of largest loss that might occur due to fraud over the next year. It calculates the capital charge, the amount to be held in reserve, to be 99% sure of covering losses from this risk.

The model uses tools for fitting distributions to frequency and loss severity data, and automatically selecting the best-fitting distributions. It also uses a special ModelRisk function for determining the largest loss that may occur.

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 uses a dataset of 375 individual historic losses from fraud events over 21 bank-years. The frequency with which these losses have occurred in each year are fitted to a Poisson distribution, which is common practice but assumes that the fraud events occur independently. The frequency data are also fit to Polya distribution, which assumes that the expected frequency randomly varies between years (i.e. that some years there is a greater propensity to commit fraud than others), and to a Delaporte distribution (which assumes that some part of the expected frequency is stable, and another part random):

Cell B12 finds the best fitting distribution using the VoseOptimalFit function, using the Akaike Information Criterion as the measure of goodness of fit to rank against. Selecting this cell and clicking the View Function icon in the ModelRisk ribbon will show the three fitted distributions:

The Polya is the best fitting of the three. Similarly, for the loss size, the spliced combination of Lognormal and Pareto distribution is the best fitting (Cell D13):

The model then combines these results using the VoseLargest function (Cell G6) to generate values for the largest loss that might occur, given that an individual loss follows the fitted spliced distribution, and that the number of losses is a sample from the fitted Polya distribution:


The model takes about 31 seconds to run 50,000 samples, a large number that is necessary to give a precise value for the 99.9th percentile. 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 largest loss amount:
This shows a loss ranging from about $2M to $50M, most likely around $7M.

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

Finally, the third tab presents the embedded report from the spreadsheet, showing the loss estimate at different confidence levels:

Download model

Click on the button below to download the Excel model. ModelRisk needs to be installed in order for the model to work.

Download model


For Microsoft Excel

Download your free copy of ModelRisk Basic today. Professional quality risk modeling software and no catches

Download ModelRisk Basic now


For Primavera & Microsoft Project

Download your free copy of Tamara Basic today. Professional quality project risk software and no catches.

Download Tamara Basic now