An example of a Monte Carlo simulation risk analysis model for the banking and insurance industry

Technical difficulty: 3

Techniques used: Monte Carlo simulation in Excel

ModelRisk functions used: VoseAggregateMC,VoseAggregateFFT,VoseIntegrate

Model description

Credit risk is the risk of loss due to a debtor's failure or partial failure to repay a loan or other credit instrument (bonds). We need three components to assess the credit risk of an individual obligor:

1. Default probability
2. Exposure distribution
3. Loss given default as a fraction of the exposure

Components 2 and 3 can sometimes be replaced by a single distribution of loss give default. This example describes how to fit probability distributions to data which are used to estimate 2 and 3 above. We can also estimate the binomial probability needed for the probability of default. There are a number of methods we can use to determine the aggregate distribution.

Single portfolio example

The figure below shows a credit risk model for a single portfolio of independent, Lognormally distributed random individual losses where there are 2 135 debtors each with the same 8.3% probability of default.



In this model the VoseAggregateMC function in Cell C20 is randomly sampling n Lognormal(55,12) distributions and summing them together, where n is itself a Binomial(2135, 8.3%) distribution. This is the 'brute force' Monte Carlo approach to summing random variables, but with the advantage that the simulation is all done within a single ModelRisk function (which is very fast).

Since the model of the figure above is estimating a distribution of loss, the Value at Risk at the 95th confidence level is simply the 95th percentile of the output distribution, which is returned directly into the spreadsheet at Cell G20 at the end of a simulation. In this example, running 1 000 000 iterations produces a VaR of 10 943.62.

We can take a different approach by constructing the aggregate distribution using Fast Fourier Transforms (using the VoseAggregateFFT function). Cell F22 defines this function as a distribution object, Cell C23 then generates random values from this distribution object, whilst Cell E23 directly returns the 95th percentile of the aggregate distribution without any simulation. The ModelRisk screen capture of the AggregateFFT interface shown below illustrates this:



In Cell G23 the formula:

      =VoseIntegrate("#*VoseAggregateFFTProb(#,C16,C13,,0)",E23,15000,1)/(1-C17)

calculates the integral to determine the Expected Shortfall


where E23 is the defined threshold, x is the value of the aggregate loss distribution, f(x) is the density function for the aggregate loss distribution (determined by the function VoseAggregateFFTProb( ...) ), and 15 000 is a sufficiently large value to be used as a maximum for the integral, as shown in the screen capture below:



The ModelRisk Integrate window to determine the Expected Shortfall of a credit portfolio. The Steps parameter is an optional integer used to determine how many sub-intervals are made within each interval approximation as the function iterates to optimized precision.