UNIVERSITY OF ILLINOIS AT URBANA-CHAMPAIGNCollege of Business – Department of FinanceFINANCE 432: Managing Financial Risk for InsurersProfessor Stephen D’ArcyHomework #3 (10 points)Lectures 1-8Due February 18, 2008(3 points)1. On January 1, 2008, Crazy Cookie Company issued a 10 year floating rate bond under which the interest rate resets annually based on the then-current LIBOR. The company is considering 3 different interest rate strategies: an interest rate collar with a floor of 6% and ceiling of 10%, an interest rate corridor buying a 9%cap and selling an 11% cap, and a 70% participating cap at 8%. Assume that LIBOR follows the values listed below. Plot Crazy Cookie Company’s net interest expense for each of the 3 options over the next 10 years on the same graph (use different colors or a different marking to differentiate the lines!!!). Thex-axis should be the year and the y-axis should be the net interest rate for that year.Date LIBOR1/1/08 6.071/1/09 7.621/1/10 5.841/1/11 5.021/1/12 5.891/1/13 9.731/1/14 10.621/1/15 12.191/1/16 11.031/1/17 6.15(1 point)2. The chief actuary in your company, who does not understand derivatives, has just read on Wikipedia that the notional value of credit derivatives as of June 2007 was $42.6 trillion, which is larger than the entire international bond market. She knows that you took a course on financial risk management, so she asks you to explain how the credit derivative market can be larger than the bond market on which the derivatives are based. How would you explain this to her?(2 points)3. You are working for a medium sized mutual life insurance company. The Chairman of the Board of your company, whose background is in marketing, recently read the article, “The Bond Transformers” (WSJ – 1/20/08) and is concerned to find that your company is also involved in the credit derivative market. A. Explain, in terms that someone who does not have a background in finance could understand, how it could benefit your company to be involved in the credit derivative market.B. Explain what the bond insurers did wrong in their use of credit derivatives that led to the problems listed in this article.(4 points)4. Aggregate Loss Spreadsheet – See class websiteThis program is designed as a learning tool for risk metrics. The goal is to show you how common risk metrics are calculated and to demonstrate how much these values can vary based on different sets of experience or different model parameters. This program uses aggregate losses as the key variable. (Since losses are negative assets, we are focusing on the right hand side of the distribution, which shows the largest losses, rather than the left hand side of the distribution as we did for assets in lecture 2 and class exercise #3.) The losses are generated by determining loss frequency (from one of two distributions) and loss severity (from one of four distributions) independently. This program is in the early stages of development, and you are encouraged to suggest or develop improvements for future sessions.The Setup page allows the user to select the frequency and severity distributions (red cells) and to modify the input the appropriate mean and standard deviation parameters(blue cells). After establishing the parameters, and hitting ‘F9’, one hundred aggregate losses are displayed on the AggLoss worksheet. (Each time you hit ‘F9’ another 100 aggregate losses are calculated.) The column headed Rand() lists the random number generated from a uniform distribution (from 0 to 1). The column headed Loss Frequency Number of Losses lists the number of losses associated with the generated random number. This can be verified by checking the random number against the cumulative distribution function (cdf) of the Negbi worksheet if frequency is based on the Negative Binomial distribution, or the Poisson worksheet if the frequency is based on the Poisson distribution (check the Setup page to see which applies). The next 200 columns list the loss severity for each of the losses that occurred (Number of Losses). All the remaining columns beyond the loss frequency value are 0 (losses that did not occur). The Aggregate Losses column is the sum of all the individual losses. As Excel has inverse functions that can generate a loss size based on the Normal, Lognormal and Gamma distributions, the only severity distribution requiring a separate lookup sheet (to determine the number of losses based on the cumulative distribution function and the random number determined) is the Pareto distribution, which is worksheet 3. You are going to use the AggLoss worksheet to calculate some risk metrics.In order to have an unchanging example of how to do this, we have a worksheet labeled Fixed Example. This is pre-set to have loss frequency based on a Poisson distribution and loss severity on a Lognormal distribution. The values do not change when you hit ‘F9’, so we can discuss particular values that everyone will see. For this worksheet, 100 iterations have already been run and sorted based on the size of the aggregate losses. The risk metrics of VaR, Tail VaR and Tail Conditional Expectation and Maximum Loss have all been calculated. In row 8, the column A lists the number 33. This indicates that the 33rd iteration generated the largest aggregate losses of the 100 iterations. (The columns have been sorted based on the aggregate losses.) This aggregate loss level is 202,243,569 (column B). The random number generated for this iteration was0.26587 (column D) which converts to 8 losses (look at the cdf on the Poisson distributionworksheet and see that the cdf is .23 for 8 losses and .34 for 9 losses). The next 8 columnsare the individual losses. (We did not store the random numbers that were generated that produced these values.) Loss 4 (194,609,765) is the reason the aggregate losses are sohigh. The second highest aggregate losses are displayed on row 9. This was iteration 100,which had 14 losses (greater loss frequency than average) with loss 13 (112,715,052) being the major contributor.Risk metrics are shown both numerically and graphically. The 95% VaR is listed at the top of the page. This is 22,669,138, the value in row 13, column B. In 95% of the cases, losses do not exceed this value. The Tail VaR (and the Tail Conditional Expectation) is the average loss in top 5%, which is calculated by averaging the values in rows 8 through 12, column B. The next