RISK MANAGEMENT PROJECT Overview This project presents hypothetical (and very unrealistic) financial institution The first worksheet the Excel file contains the balance sheet of this bank as of "today", the last of 500 business days represented the Data worksheet. The task the project is to calculate Market Valu at-Risk and Credit Value- Risk for this bank based nthe price value data the assets and liabilities on the balance sheet, compute Economic Capital required the bank, compare that accounting Capital (Net Worth) from balance sheet, decide whether current capital sufficient, and what measures totake itis not sufficient. Balance Sheet The assets of the bank shown the balance sheet is: approximately $1 ,000,000 ina money market ("cash") holding 3,000,000 principal value of a 2-year Baa bond holding 30,000 shares common stock; liabilities begin at about $5,000,000 deposits; net worth $1,008,238. The bank also has a short position 5-month call option on same bond the bank holds The details the option listed footnote tothe balance sheet. Since this derivative position it is not asset liability does not appear the balance sheet proper. However, the value this derivative can fluctuate; relevant measurement of the bank': risk (You can also interpret the combination the bond and the related option as it were bond with an embedded option, such asa callable bond) Instructions Market Risk We will first work on Market Risk. Later we will also examine Credit Risk and Operational Risk for this "bank" The tab "Data' contains prices and values for the three assets and the liability. These are fictional prices, generated from stochastic process they are not the prices o any actua financial instruments. This covers 500 days, years trading days. Day (row 12 Data spreadsheet) is the first day of the 2-year period; Day 500 is "today". Thisi where we will do most our work for Market Risk. We will compute Market Value -Risk first using data from the entire 500 day period. A. The first stepi: find the daily price changes for each of the assets and liabilities¹ In each of columns headed P.P, (column E for shares, column bonds, column P for cash, column for deposits) enter the formula for first differences and copy that down the column (e.g. for shares cell E13 enter =C13-C12 and copy that down; do the same for bonds The "price' for the Deposits not really aprice labelled "Increment", the deposits during Think itas though its constant $5 million whose value changes. This convenier for our purposes. column for cash column P, for also calculate the first differences Net Worth (Capital) ir column headed Ves Recalculate the spreadsheet and save B. Now the next spreadsheet "Market Risk The exposures for each class of asset and liability are already entered cells B13 (Deposits entered negative number since they are liability.) Tofind the Average of 1 -day price changes each item, make the using= 13:E511)for shares in cell C10, similarly for bonds, cash and deposits; find the standard deviations the same way using =STDEV(Data!E13:E511) and forth. Toget from heret VaR requires few more steps on this spreadsheet First, you need the price correlations between each pair assets and liabilities: shares- bonds, -deposits, bonds-cash, boods deposits, -deposits Fillin the Correlation Matrix tofind correlation between the 1st differences shares and bond, in cell H10 enter =CORREL(Data!E13:E511 Data!K13:K5: 11), and so forth (The correlation of each item withitself- 1.000 already entered.) Now that you have the standard deviations of each asset liability and the correlations among them, you can calculate the standard deviation the entire bank, using aversion of the formula you've used before the class: Enter this with the correct cell references (be careful about the cell references and parentheses) cell D17. (To confirm your calculation find the standard deviation of daily changes in Net Worth. entering that function cell D18. It should be the same as what you get D17.) Using this standard deviation for "the bank". calculate Market Value at Risk cells D25 to F29. You can choose the z-value and confidence level you want, but specify what you will you and give a reasor for the choice. C. How deal with option In the Market Risk Option tab, all the parameters for evaluating t the option are cells D15 to D19. Since the bond an asset, your risk for the price and value of the bond t fall. you are using a 99% confidence level VaR, calculate price the bond falls 2.326 standard deviations from "today price in ell 1511 on the Data tab Enter that price incell D26. Now that price option tree diagram Beginning with that and using DIS calculate premium the value option. [Remember this isa 6-month option careful of the exponent you use discountin the option value.] How much would the value the option change compared "today's' premium and 4.60529 156? Enter change D36. Think your position (maybe recheck the footnote the balance sheet): will this change be loss for you Based on your answer to that adjust your Market VaR from Market Risk Ito take into account the option, and soput your final Market VaR cell D42. Instructions for Credit Risk 1. First figure the probability of default for the bond your portfolio (PD, or 0) from market vield levels. Your bond's yieldis on the Data tab for dav # 500 (cell 1511). The risk-free Treasury bond vield given on the Credit Risk tab. Using these you can calculate the market's implied PD using the standard formula: (The recovery rate fis not specified pick your own). Enter this probability of default on the Credit Risk tab cellE14. (this formula strictly valid only for 1-year zero-coupor bonds, but use anyway the difference from two year coupon bondi negligible.) 2. Next take the PD from the Moody's table. Remember this bond rated Baa. Enter this in cell E16 the Credit Risk tab. 3. Choose the probability of default vou want to use either the market -derived PD. or the Moodv's PD, another AND DESCRIBE WHY YOU CHOSE THE PD YOU CHOSE. 4. Using the recovery rate you chose. calculate Loss Given Default (LGD)in cell E19 of the Credit Risk tab. 5. Using this LGD and the PDyou chose. calculate expected credit loss E(C) n cell E2 of the Credit Risk tab. 6. Calculate the standard deviation of the credit loss SD(CL) using the standard formulas and enter the Credit Risk tab. SD(CL) =LGD(PD:(1-PD) 7. Figure the Unexpected Credit Loss. Worst Credit Loss and the Credit Value at Risk CVaR- for the confidence level you have chosen Enter these in E31, E33, E35. 3 Economic Capital and Evaluation and Action Those tabs explain themselves. Ithink. If not, send me an email or bring questions upi class.