Calculating Unexpected Losses (UL) & Economic Capital Buffer (ECAP) under Basel with Excel example - YouTube

Every bank seeks protection against future loan losses

Capital buffers absorb this risk. The Basel bank regulation defines two types of losses

expected losses (E L) and unexpected losses (U L)

We can estimate the unexpected losses by multiplying the following elements for each loan or loan segment

One, the Exposure at Default or E A D

Two, the Probability of Default or P D

And last, the Loss Given Default or L G D

The product tells us the total of the expected losses

Banks keep loan loss reserve to protect themselves against these anticipated losses

Banks maintain a one-year horizon for this buffer

We understand that these future losses are estimates

The real losses can be much higher, or lower

Loan loss reserves equal to the expected losses would fall short in half of the real-life cases

Banks also need to protect themselves against Unexpected Losses (U L)

Loan loss provisions are a liability

Protection against unexpected losses comes from the equity

The question is how to calculate this extra equity capital needed?

To calculate this equity buffer we need three elements

First the bank needs to determine up to what confidence level it seeks protection

Our intuition tells is us that a hundred percent protection is not possible

Still we face the choice of a protection level in line with the bank’s risk appetite

A good way of thinking of this is through a Monte Carlo simulation

Say, our bank could live next year’s life not one, but a thousand times

In real life this is not possible, but in a simulation, we can repeat the scenario many times

During the thousand virtual lives the bank will suffer small and big losses

Some years the loan loss provisions will suffice, other years they fall short

Our starting point is the expected losses which equal forty in our model

The uncertainty surrounding the P D and L G D numbers drive the variations in the result

This uncertainty is measured as the standard deviation

To keep things simple, we focus on the probability of default and ignore the L G D

Now we can model each of the probabilities of default with a normal distribution

The mean and standard deviation define this distribution. We do this for all P D's

We run the simulation for a thousand trials. Every trial the simulation generates random values for the P D's

Random, but defined by the normal distribution

Thousand trials produce a thousand possible amounts for the expected losses

We can put them in a histogram

It is no surprise that the mean of our results is close to the original forty of the expected losses.

The surface of our graph represents the thousand trials. So, the thousand simulated lives of our bank

We could ask the question how much capital the bank would need to stay safe in 95% of the cases

In other words, what are the expected losses at the 95th percentile?

We see this amount equals 49.60. So, with buffers of 49.60 we would survive in 95% of the cases

This amount is covered for forty units through the loan loss provisions

This implies we need an additional 9.60 to reach the 95% certainty level. This is the answer to our question

The extra risk capital buffer is usually called economic capital and comes from the bank’s equity

To sum this up, we have the loan loss reserves to protect the bank against expected losses

We have the economic capital to protect the bank against unexpected losses

We do this up to a degree of 95%. The remaining five percent in the right-hand tail is the uncovered risk

Okay, now we understand the concept and let’s see whether we can model this in Excel

Also, we would like to add another dimension: correlations

The way to approach this in Excel is to create a covariance matrix

As we will see, the covariance matrix will produce the standard deviation of the expected losses

Once we know the standard deviation, we can determine how much extra buffer capital we need

A matrix is a number table with rows and columns. We can create a covariance matrix using the standard deviations and correlations in our portfolio

Let’s start with the standard deviations

We take our loan portfolio and list the loans A, B, and C along the horizontal and vertical axis of our matrix

On the diagonal axis we fill out the standard deviations for each of the loans. We do this in monetary values

For the remaining cells we fill in zero. It is smart to assign a name to the matrix data. We call this matrix “sigma”

Next, we have the correlation matrix. Along the diagonal axis we fill in a hundred percent since every loan is fully correlated to itself

For the time being we fill out zeros for the remaining cells. We name this matrix “rho”

With the standard deviation matrix and the correlation matrix we can create the covariance matrix.

We can do this matrix multiplication using the Excel MMULT function

In older Excel version this matrix multiplication can be a bit bothersome

First selecting the output range, entering the formula in the top-left-cell of the output range, and then pressing CTRL+shift+ENTER to confirm it

A nice feature of the covariance matrix is that the sum of the cells produces the variance

We can do this with the Excel SUM function

We remember from school that the variance equals the standard deviation squared

That implies that the square root of the variance produces the portfolio’s standard deviation

The Excel function SQRT gives us a hand

The standard deviation equals 5.70, which is close to what we saw earlier in our simulation

Again, we would like to find the cut-off point at the 95th percentile

This point equals the sum of the E L and U L at that confidence level

We keep things simple and assume the losses follow a normal distribution

We know the distribution’s mean and the standard deviation

The Excel function NORMINV helps us to find the cut-off point at the 95th percentile

We find a value of 49.38.

Again, close to what we found with the simulation

With a total capital buffer of 49.38 we cover 95 percent of the future losses

We already buffered forty in the loan loss provisions. This buffer covers the expected losses

Hence, we need an additional economic capital buffer of 9.38 to reach the 95% certainty level

We can make a small table and see what the capital needs are at the various confidence levels

As we want to cover more of the risk, our capital needs grow

The reverse is also true. Higher capital levels assure more protection against future losses. Hence, the banks itself receives a better credit rating

The model we created provides a couple of interesting insides in the mechanics of loan portfolio management

We will touch upon three themes

First, the portfolio benefit

Second, correlations and in the third place the concept of contribution to variance

We all know that a portfolio of loans presents less risk than one big loan

Diversification is the main pillar of risk management

Our model allows for calculating this portfolio effect

We saw that the standard deviation of our portfolio equals 5.70

In our sigma matrix we find along the diagonal axis the individual standard deviations for each of the loans

If we take the sum of the matrix, we get a risk level of 9.10

The portfolio’s sigma is just 5.70

We conclude that the portfolio benefit reduced the risk with 37%

For our bank’s credit risk management, it is interesting to quantify this benefit

Next topic is correlation. We understand that positive correlations between loan losses reduce the portfolio effect

The correlation matrix rho lists the correlations between the portfolio elements

If we fill out a hundred percent correlation for all the cells the portfolio benefit vanishes

You can test various scenarios

Assume that the correlations between all loans is 90%

Make sure to fill out the corresponding values in both the matrix upper and lower triangle

We notice that there is only a meagre 3% risk reduction left

In practice, most loans and market segments are positively correlated. Still, there is some portfolio benefit

The third concept pertains to contribution to variance or risk

Our portfolio has a certain risk level that is expressed in a standard deviation or variance

We saw that they represent the same thing. The standard deviation is the square root of the variance

The covariance matrix of our matrix multiplication exercise represents the total portfolio variance

We took the sum of the matrix to find this variance

If the matrix represents all variance then the individual lines in the matrix represent the part of that loan

Thus, we can make an overview of the contribution to variance