CORRELATION IN A FINANCIAL CONTEXT

We have just looked at how correlation is calculated using an easy to understand example, provided historical data, but what is important to simulation is how correlation is used for future projections. If we believe there is a correlation between items that we are simulating, we will have to account for it in our simulation; otherwise the end results can be very different.

The two most common areas that correlation enters into financial simulations are industry- and regional-based correlations. For instance, if we are invested in a telecommunications company and the telecommunications industry as a whole is in decline, we would expect a correlation in the performance of our individual telecommunication company investment. Likewise, if we are invested in a company that services Asian markets and Asian markets are in decline, we might expect our individual invested company to experience decline because of the regional correlation. Let's take a look at a simple simulation of 10 corporate loans with a single correlation factor and see how the correlation affects the expected default of the basket of loans.

MODEL BUILDER 3.2: Introducing Correlation in a Basic Corporate Default Model

1. This Model Builder expands off of our basic correlation coefficient calculation and shows how correlation can affect the results of a simulation. In this example there are 10 assumed exposures that are from different companies, but all maintain the same risk profile in regards to par amount, tenor, and risk rating. In the same workbook, MB_3_User.xls, insert a new worksheet and name it “MB 3.2”.
2. We can save time by importing assumption data from the completed MB_3_Complete.xls workbook on the website. Copy the data from B6:D16 on the MB3.2 sheet of MB_3_Complete.xls and paste it into the same location on your workbook. This data is shown in Figure 3.4 and shows the 10 separate investments, the identical par amounts, and the default probabilities. We will discuss default probabilities in further detail later in Chapter 5, but for now we should understand that these percentages are generated from historical default data of similar companies. The main nationally recognized statistical rating organizations typically produce annual default studies that show the updated default probabilities of various company and security types.
3. Ultimately a default probability is a percentage between 0 and 1. As we saw from the previous section on distributions, if we assume a normal distribution we cannot simply work with the default probability. In fact we do not want the probability, but the inverse of the distribution it is related to. This provides us a number that can be thought of as the default threshold. If we imagined a normal distribution this would be the z value or the actual point on the normal distribution. When we create normal random numbers in the upcoming simulation, we will test whether we went below the default threshold, indicating default. Convert the default probabilities to thresholds by entering the following formula in F7:

   FIGURE 3.4 In this example we will take a theoretical basket of 10 exposures.

   

   =NORMSINV(D7)

   Copy and paste this formula over the range F7:F16. You should also label F6 with the title “Default Threshold”.

4. Before we run multiple iterations to see the aggregate results, let's look at what the default test might look like for just one iteration. To do this, we already should be able to set up the first part, which is the noncorrelated component. This is done by generating a normal random variable and testing whether it is less than the default threshold. Notice that for each of the ten exposures this is a unique figure and requires the formula NORMSINV(RAND()) for each company. So what exactly is the noncorrelated component of a corporate default simulation? The intent is to try to integrate factors that could affect default that are unique to each company. This could be concepts such as management capability, fraud, or other unique circumstances that we would not expect to affect another company if it affected one of the companies in our basket.

   The opposite of the noncorrelated component to corporate defaults are concepts that might affect the other companies if it occurred in one of our ten exposures. Earlier we mentioned examples that could include industry or regional problems. Keep in mind there can always be other correlations beyond these two. To account for this, we create one normal random variable that is the same for each of the 10 entities.

   The way we induce correlation then is to use a certain percentage of the noncorrelated normal random variable versus the correlated normal random variable, depending on the assumed level of correlation. This can be implemented using the following formula:

   =(Correlation Coefficient)*Correlated Normal Random+((1-Correlation

   Coefficient^2))^0.5*Non-Correlated Normal Random

   Let's piece these concepts together by first entering the following labels in the corresponding cells:

   I6: “Non-Corr Component” K6: “Combined Test Value”

   J6: “Corr Component” L6: “Default Test”

5. In I7 enter the following formula:

   =NORMSINV(RAND())

   Copy and paste this across the range I7:I16. This is the noncorrelated component, which is demonstrated by the fact that every time the sheet is calculated there will be a unique normal random number for each exposure.

6. In J7 enter the following formula to create the correlated component:

   =NORMSINV(RAND())

   Rather than copying this formula down over a similar range as in step 5, enter the following formula in J8:

   =J7

   Now copy this formula over the range J8:J16. Notice that we created the same normal random number for each exposure.

7. Now we are going to implement correlation. Prior to doing this we should put in an assumed correlation coefficient. Label B4 “Assumed Correlation Coefficient” and enter the value “20%” in E4 for now.
8. Go back over to the area we were working in during step 6 and enter the following formula in K7:

   =$E$4*J7+((1-$E$4^2)^0.5)*I 7

   Copy and paste this formula over the range K7:K16. Notice that this formula takes the correlated component and multiplies it by the correlation coefficient and adds to it the noncorrelated component multiplied by the square root of one minus the correlation coefficient squared (the non-correlated factor).

9. Our default test in L7 is now ready. Enter the following formula in L7:

   =IF(K7<F7,1,0)

   This simulates whether a default occurred against the distribution formed by the exposure's default probability by entering a 1 if a default occurred and a 0 if it did not. Copy and paste this formula over the range L7:L16.

10. We may want to visualize this in a more obvious way with the following formula in M7:

    =IF(L7=1,“Default”,“No Default”)

    Copy and paste this formula over the range M7:M16.

11. In L17 we should sum the total number of defaults for all exposures. Enter the formula =SUM(L7:L16) in L17. Thus far the sheet should be developing like Figure 3.5. Note that the actual numbers could be different from the Excel work you are developing.
12. So far we have completed one iteration. If we push F9 to calculate the sheet, the random numbers will be regenerated and we should see a different default pattern among the 10 (there is the chance that the default pattern could be identical since it is random, but this will occur very infrequently). We should next expand our analysis to multiple iterations. Ideally this is done in VBA for efficiency, but we can show how it can be done directly on the sheet. All we need to do is consolidate the prior calculations into a single row; each row will then represent an iteration. Start this by creating the labels “Exposure Number” in B32 and “Iterations” in B33. Enter the values 1 to 10 in C32:L32. These will label the area for each exposure's test that we will soon create.
13. In B34:B5033 enter 1 to 5000 to label each row that represents an iteration.

    FIGURE 3.5 A single iteration default test for 10 entities.

    

14. M33 should be labeled “Corr Component”, with M34 containing the following formula:

    =NORMSINV(RAND())

    Copy and paste this over the range M33:M5033. This is the correlated component that will be the same for all 10 exposures during a single iteration. Notice that it will change for each iteration though.

15. Next enter the following formula into C34:

    =IF((($E$4)*$M34+((1-$E$4^2))^0.5*NORMSINV(RAND()))

    <OFFSET($F$6,C$32,0),1,0)

    While this formula looks complicated it is actually a condensed version of the calculations we did in steps 1 to 9. For each exposure this formula takes the correlation coefficient and multiplies it by the same normal random number (representing the correlated component) and adds this to the product of the square root of one minus the correlation coefficient squared multiplied by a unique normal random number. The sum of these two calculations is then tested against the default thresholds from the tabled data starting in F7. Note that an OFFSET function is used to look up the default threshold for each exposure. If the calculation is less than the default threshold a 1 is returned; otherwise a zero is returned. Copy and paste this over the range C34:L34.

16. Next we should sum up the number of defaults in each iteration or row. Do this by entering the following formula in O34:

    =SUM(C34:L34)

    Copy and paste this formula over the range O34:O5033.

    FIGURE 3.6 With a low correlation coefficient, the distribution from the simulation should look relatively normal.

    

17. We now need a method of visualizing the large data set that we have created. In Chapter 2 we introduced the idea of a histogram as a useful tool for visualizing simulation results. We can implement this again by counting the number of times a specific number of exposures defaulted out of the 10. In fact, we can do this for each of the 11 possibilities (there is a possibility that 0 out of the 10 exposures default). To do this enter a series of values from 0 to 10, incremented by 1 in B20:L20.
18. In B21 enter the following formula to count the number of times each case occurred:

    =COUNTIF($O$34:$O$5033,B20)

    Copy and paste this formula over the range B21:L21. Notice that this tells us how many times we saw 0 exposures out of 10 default, 1 exposure out of 10 default, and so on. We can then create a column chart to visualize the data as shown in Figure 3.6.

Interpretation of Model Builder 3.2 and How Correlation Affects a Simulation

Now that the actual construction of this Model Builder is complete we should take some time to interpret what we have done. Notice that the histogram distribution of results is a normal distribution centered on 4.5. This makes sense since we assumed a low correlation, a normal distribution, and all of our companies have a default probability of 45 percent. We can see the distribution looks like a bell curve with the most common scenarios having 4–5 defaults, but there are scenarios with 0 defaults and with 10 defaults.

Change the correlation coefficient in E4 to 0 percent. Make sure the sheet is calculated by pressing F9 if you have Excel set to manual mode (which is a good idea when creating so many formulas on the sheet!). You should see the histogram distribution remain centered on 4.5, but there are fewer scenarios with 0 or 10 defaults, as shown in Figure 3.7.

FIGURE 3.7 With a low correlation coefficient and a normal distribution assumption, we can see a very symmetrical normally distributed result.

The interesting effect, though, is when we change the correlation coefficient the other way. Change the correlation coefficient in E4 to 90 percent and make sure the sheet is calculated. The histogram distribution should now change to an extreme barbell distribution with peaks at 0 and 10, as seen in Figure 3.8.

This is because with a high correlation there is a very high probability of doing very well with no defaults or a very high probability of doing very poorly with nearly the entire basket defaulting. This is an effect similar to what we saw in 2007 when the correlation among mortgage-backed securities was revised to a much higher percentage. Rather than having a diverse array of default probabilities the underlying mortgage securities were extremely correlated and many pools of loans started to go into delinquency and default at the same time.

Another interesting characteristic that should be noted about the simulation is that the individual asset default rates among all iterations are close to the assumed default probability, even though the correlation among the assets could be very high. This can be checked by summing up a row of defaults for a single asset and dividing that sum by the total number of iterations. For example, the sum of C34:C5033 divided by 5000 should be approximately 45 percent (i.e., the original default probability that was assumed).

FIGURE 3.8 A high correlation coefficient greatly affects the outcome of a simulation, even if the distribution was originally normal.