The examples will begin to be illustrated through more financially related Model Builders and eventually in later chapters, the concepts will be entirely financially based with supporting Model Builder examples. The most immediate ap- plications relate to default, options, and commodity and interest rate projection. For each of these topics we have learned that there are more complex interactions at work than just generating a random number and assuming a certain distribu- tion.
Correlation between price movements or default status is perhaps the most important interaction that we need to be aware of. The financial crisis that began in has shown us the importance of estimating correlation correctly; one of the key failures in risk management and rating agency methodology going into the crisis was an underestimation of correlation within mortgage and structured debt default risk.
Census data in the United States typically shows that more educated people earn more money. Look at the data set plotted in Figure 3.
The first thing to realize is that a scatter plot best shows correlation visually, between two sets of variables. Here we can see that there is a positive correlation, that is, one where as the value of one variable increases there is an increase in the value of another variable. The correlation is actually quite strong in this case mainly because I made up the data set. When we work through Model Builder 3. Another way of thinking about what this means is to use extreme cases. If the correlation is equal to 1, the two variables are completely linked, and for every unit of increase in one of the variables there will be a proportional increase in the other variable.
The formula for correlation usually represented by r is shown in equation 3. It is crucial to keep in mind that correlation measures only the linear relation- ship between two variables. If so, performing regressions of one variable against the root of another may be appropriate.
See a Problem?
This Model Builder examines the fundamental correlation calculations using mathematics on the sheet and prebuilt Excel functions. It will be useful since the correlation calculations here are the underpinnings for correlation calculated using matrix mathematics and eventually in the corporate default simulation. Next we need to import data from the complete Model Builder file located on the website. Paste the values of this data over the same range on your newly created sheet.
To understand the underlying meaning of the correlation coefficient calculation we will first calculate the coefficient using basic math functions. Next we should calculate the deviations and square them for each variable. Similarly we should do the same for the other variable.
[PDF] Corporate Valuation Modeling: A Step-by-Step Guide (Wiley Finance) Full Online
So far the sheet should look like Figure 3. The denominators need to be multiplied and the square root taken of that product. Finally, the correlation coefficient is calculated by dividing the numerator by the equation calculated in step 7.
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 com- pany that services Asian markets and Asian markets are in decline, we might expect our individual invested company to experience decline because of the re- gional correlation. 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. Copy the data from B6:D16 on the MB3. This data is shown in Figure 3. 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 com- panies. The main nationally recognized statistical rating organizations typically produce annual default studies that show the updated default probabilities of various company and security types.
Corporate Valuation Modeling : A Step-by-Step Guide
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. 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. So what exactly is the noncorrelated component of a corporate default simulation?
- Financial modeling - Wikipedia;
- Corporate Valuation Modeling: A Step-by-Step Guide;
- Wiley Finance Series 金融书籍 【本】_百度文库.
- Turbulent Mixing in Nonreactive and Reactive Flows;
- Corporate Valuation Modeling - eBook - albopima.ga.
- [PDF] Corporate Valuation Modeling: A Step-by-Step Guide (Wiley Finance) Full Online;
- The Grey King (The Dark is Rising Sequence);
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 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. Notice that we created the same normal random number for each exposure. Now we are going to implement correlation.
Prior to doing this we should put in an assumed correlation coefficient. 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. Our default test in L7 is now ready. Copy and paste this formula over the range L7:L In L17 we should sum the total number of defaults for all exposures. Thus far the sheet should be developing like Figure 3. Note that the actual numbers could be different from the Excel work you are developing.
Financial Valuation Workbook - PDF Free Download
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. Enter the values 1 to 10 in CL In BB enter 1 to to label each row that represents an iteration. 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. 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. 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 CL Next we should sum up the number of defaults in each iteration or row.