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:

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. 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

Default probabilities can perhaps be considered constant over a short period, but over a longer period or where the market is very volatile it should also be modelled as a function of the condition of the economy and, for corporate credit risk, as a function of the state of the regional business sector too. The same may apply for the loss given default variable as the debt holder may recover a smaller fraction of the exposure in more stressing times. This means that we must construct credit portfolios that are disaggregated at an appropriate level and sum their cash flows. Failure to model these correlations will underestimate the risk of losses. For example, we might produce a model that varies the probability of default (PD) as a function of changes in GDP growth (GDP), interest rate (IR) and inflation (I) using an equation of the form:



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.