# Credit Risk - Single portfolio example with separate exposure and loss given default distributions

An example of a **Monte Carlo simulation risk analysis model** for **cost modeling**

**Technical difficulty**: 2

**Techniques used**: Monte Carlo simulation in Excel

**ModelRisk functions used**: **ModelRisk functions used**: VoseSumProduct,VoseSimulate,VoseAggregateProduct,VosePoisson,VoseLognormal,VoseBeta

# 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:

- Default probability
- Exposure distribution
- 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. If they are separate, one needs to determine the probability distribution of the sum:

Although this is easily done with Monte Carlo simulation by modelling each default in a separate row, it is a cumbersome model because the number of defaults is a random variable and therefore one is simulating a different number of rows. The model also needs rewriting to match the maximum number of defaults one might come across.

Alternatively, with ModelRisk you can use the VoseSumProduct function to return the aggregate distribution with one cell formula. Both methods are shown in the example model below:

The VoseSimulate function simply generates random values from its object distribution parameter, which allows the user to keep the distribution in one place in the spreadsheet rather than many. The VoseSumProduct(n,a,b,c...) adds n variables together where each variable is a*b*c*... where a,b,c, etc are distribution objects:

The loss given default distribution can be constructed using FFT calculations. The ModelRisk function VoseAggregateProduct performs this routine, so we can write:

=VoseAggregateProduct(VosePoissonObject(1000), VoseLognormalObject(100,10), VoseBetaObject(11,35))

# Default probability as a variable

where a, b and c are constants, t is some time in the future, σ is a residual volatility and X0 means the value of the variable X now.

# Download model

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