Transcribed Text
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.
These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction
of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice.
Unethical use is strictly forbidden.