Simon Benninga. Financial Modelling 3-rd edition

Подождите немного. Документ загружается.

398 Chapter 15

1. What is the distribution of the end-of-year portfolio value?

2. What is the probability of a loss of more than $20 million by year-end

(i.e., what is the probability that the end-of-year value is less than $80

million)?

3. With 1 percent probability what is the maximum loss at the end of the

year? This is the VaR at 1 percent.

The probability that the end-of-year portfolio value is less than $80

million is about 9 percent. (“Million” is omitted in the example.)

AB C D

Mean 20%

Sigma 30%

Initial investment 100

Cutoff 80

Probability that

portfolio worth less

than cutof

9.12% <

=NORMDIST(B5,(1+B2)*B4,B4*B3,TRUE)

Portfolio value 0.0055

0 0.0000

10 0.0000

20 0.0001

30 0.0001

40 0.0004

50 0.0009

60 0.0018

70 0.0033

80 0.0055

90 0.0081

100 0.0106

110 0.0126

120 0.0133

130 0.0126

140 0.0106

150 0.0081

160 0.0055

170 0.0033

180 0.0018

190 0.0009

200 0.0004

210 0.0001

220 0.0001

230 0.0000

240 0.0000

250 0.0000

Data table header: =NORMDIST(B5,(1+B2)*B4,B4*B3,FALSE)

PROBABILITY OF END-OF-YEAR PORTFOLIO VALUE

End-of-Year Portfolio Value

0.000

0.002

0.004

0.006

0.008

0.010

0.012

0.014

0 25 50 75 100 125 150 175 200 225 250

399 Value at Risk

Excel’s NormDist function can return both the cumulative distribution

and the probability mass function. Here’s the way the screen looks when

we apply the NormDist function in cell B6:

The spreadsheet uses two versions of NormDist: First we use the func-

tion in cell B6 to determine the probability that the end-year value of

the portfolio is less than 80. In this version of the function we use the

value TRUE for the last entry in NormDist; when we write =NORM

DIST(B5,(1+B2)*B4,B4*B3,TRUE), NormDist returns values of the

cumulative normal distribution. In the data table we set this value to

FALSE to plot the probability mass function of the end-year portfolio

value.

15.3 Deﬁ ning Quantiles in Excel

By using Excel’s Solver, we can determine that with a probability of 1

percent, the end-of-year portfolio value will be less than 50.209. Recall

that the value at risk is the worst expected loss under normal market

conditions over a speciﬁ c time interval at a given conﬁ dence level. There-

fore, the value 50.210 means that the VaR of the portfolio at the 1 percent

level is 100 − 50.210 = 49.790.

400 Chapter 15

The cutoff is known as the quantile of the distribution. In Excel it can

be determined by using Solver, as illustrated here. For two distributions

we use—the normal and the lognormal distribution—Excel has built-in

functions that ﬁ nd the quintile. These functions—Norminv, Normsinv,

and Loginv—ﬁ nd the inverse for the normal, standard normal, and log-

normal distributions.

Here’s an example for the numbers that we’ve been using; this time

we have written the function =NORMINV(0.01,(1+B3)*B5,B5*B4) in

cell B6. This function ﬁ nds the cutoff point for which the normal distribu-

tion with a mean of 120 and a standard deviation of 30 has probability

of 1 percent. You can see this point on the following graph, which shows

part of the cumulative distribution:

AB C

Mean 20%

Sigma 30%

Initial investment 100

Cutoff 50.210

Probability that portfolio

worth less than cutoff

1.00% <

=NORMDIST(B5,(1+B2)*B4,B4*B3,TRUE)

PROBABILITY OF END-OF-YEAR PORTFOLIO VALUE

401 Value at Risk

15.3.1 The Lognormal Distribution

The lognormal distribution is a more reasonable distribution for many

asset prices (which cannot become negative) than the normal distribu-

tion. Suppose that the return [return = log(price relative)] on the port-

folio is normally distributed with annual mean μ and annual standard

deviation σ. Furthermore, suppose that the current value of the portfolio

is given by V

. Then it follows (see Hull 2006, Chapter 11) that the loga-

rithm of the portfolio value at time T, V

, is normally distributed:

ln( ) ~ ln( )VVTT

Normal ,

+−

⎛

⎝

⎜

⎞

⎠

⎟

⎡

⎣

⎢

⎤

⎦

⎥

Suppose, for example, that V

= 100, μ = 10 percent, and σ = 30 percent.

Thus the end-of-year log of the portfolio value is distributed normally:

ln( ) ~ ln( ) .

..V

100 0 10

03 466Normal , Normal+−

⎛

⎝

⎜

⎞

⎠

⎟

⎡

⎣

⎢

⎤

⎦

⎥

= 66017 0 3,.

[]

AB C

Mean 20%

Sigma 30%

Initial investment 100

Cutoff 50.210 <

=NORMINV(0.01,(1+B2)*B4,B4*B3)

1.00% <

=NORMDIST(B5,(1+B2)*B4,B4*B3,TRUE)

VaR at 1.00% level 49.790 <

=B5-B6

CALCULATING THE QUANTILES

Cumulative Normal Distribution

(only part--so we can see the 1% quintile)

0.0

0.5

1.0

1.5

2.0

2.5

3.0

-20-15-10-5 0 5 101520253035404550556065

Portfolio value (million $)

Probability (%)

1% quantile point

402 Chapter 15

Thus a portfolio whose initial value is $100 million and whose annual

returns are lognormally distributed with parameters μ = 10 percent and

σ = 30 percent has an annual VaR equal to $47.42 million at 1 percent:

AB C

Initial value, V

100

Mean,

10%

Sigma,

30%

Time period, T 1 <

in years

Parameters of normal distribution of ln(V

)

Mean 4.6602 <

=LN(B2)+(B3-B4^2/2)*B5

Sigma 0.3000 <

=B4*B5

Cutoff 52.576 <

=LOGINV(0.01,B8,B9)

VaR at 1% level 47.424 <

=B2-B11

QUANTILES FOR LOGNORMAL

DISTRIBUTION

Most VaR calculations are not concerned with annual value at risk. The

main regulatory and management concern is with loss of portfolio value

over a much shorter time period (typically several days or perhaps

weeks). It is clear that the distribution formula

ln( ) ~ ln( )VVTT

Normal ,

+−

⎛

⎝

⎜

⎞

⎠

⎟

⎡

⎣

⎢

⎤

⎦

⎥

can be used to calculate the VaR over any horizon. Recall that T is mea-

sured in annual terms; if there are 250 business days in a year, then the

daily VaR corresponds to T = 1/250 (for many ﬁ xed-income instruments

one should use 1/360, 1/365, or 1/365.25 depending on the market

convention).

15.4 A Three-Asset Problem: The Importance of the Variance-Covariance

Matrix

As can be seen from the preceding examples, VaR is not—in principle,

at least—a very complicated concept. In the implementation of VaR,

403 Value at Risk

however, there are two big practical problems (both problems are

discussed in much greater detail in the material available on the

RiskMetrics Web site cited in footnote 2):

1. The ﬁ rst problem is the estimation of the parameters of asset return

distributions. In “real-world” applications of VaR, it is necessary to

estimate means, variances, and correlations of returns. This is a not-

inconsiderable problem! In this section we illustrate the importance of

the correlations between asset returns. In the following section we give

a highly simpliﬁ ed example of the estimation of return distributions from

market data. For example, you can imagine that a long position in euros

and a short position in U.S. dollars is less risky than a position in only

one of the currencies, because of a high probability that proﬁ ts of one

position will be mainly offset by losses of another.

2. The second problem is the actual calculation of position sizes. A large

ﬁ nancial institution may have thousands of loans outstanding. The data-

base of these loans may not classify them by their riskiness, nor even by

their term to maturity. Or—to give a second example—a bank may have

offsetting positions in foreign currencies at different branches in differ-

ent locations. A long position in Deutschmarks in New York may be

offset by a short position in Deutschmarks in Geneva; the bank’s risk—

which we intend to measure by VaR—is based on the net position.

We start with the problem of correlations between asset returns. We

continue the previous example, but assume that there are three risky

assets. As before, the parameters of the distributions of the asset returns

are known: all the means, μ

, μ

, as well as the variance-covariance

matrix of the returns:

S =

⎛

⎝

⎜

⎞

⎠

⎟

σσσ

11 12 13

21 22 23

31 32 33

The matrix S is of course symmetric; σ

is the variance of the ith asset’s

return, and σ

is the covariance of the returns of assets i and j (if i = j,

is the variance of asset i’s return).

Suppose that the total portfolio value today is $100 million, with $30

million invested in asset 1, $25 million in asset 2, and $45 million in asset

3. Then the return distribution of the portfolio is given by

404 Chapter 15

Mean return , ,

Varian

⎧

⎨

⎪

⎩

⎪

⎫

⎬

⎪

⎭

⎪

=++{}xxx x x x

123

11 22 33

μμμ

cce of return =

{}{}

xxxSxxx

123 123

,, ,,

where x = {x

, x

} = {0.3, 0.25, 0.45} is the vector of proportions invested

in each of the three assets. Assuming that the returns are normally

distributed (meaning that prices are lognormally distributed), we may

calculate the VaR as in the following spreadsheet fragment:

ABCDEFG

Mean

returns

Portfolio

ortions

Asset 1 10% 0.10 0.04 0.03 0.30

Asset 2 12% 0.04 0.20 -0.04 0.25

Asset 3 13% 0.03 -0.04 0.60 0.45

Initial investment 100

Mean return 0.1185 <

{=MMULT(TRANSPOSE(B5:B7),H5:H7)}

Portfolio sigma 0.3848 <

{=SQRT(MMULT(MMULT(TRANSPOSE(H5:H7),D5:F7),H5:H7))}

Mean investment value 111.8500

Sigma of investment value 38.4838

Cutoff 22.3234 <

=NORMINV(0.01,(1+B10)*B9,B11*B9)

Cumulative PDF 0.01 <

=NORMDIST(B16,B13,B14,TRUE)

VaR at 1.00% level 77.6766 <

=B9-B16

VaR FOR THREE-ASSET PROBLEM

Variance-covariance matrix

Note that the functions in cells B8 and B9 are array

functions: You must press [Ctrl]+[Shift]+[Enter]

after you write the function in the cell. The curly

brackets {} are not written--they appear automatically.

15.5 Simulating Data—Bootstrapping

Sometimes it helps to simulate data. In this section we give an example.

We suppose that the current date is 10 February, 1997, and we consider

a ﬁ rm that has an investment in two assets:

•

It is long two units of an index fund. The fund’s current market price

is 293, so that the investment in the index fund is worth 2

293 = 586.

•

It is short a foreign bond denominated in rubles. The bond is a zero-

coupon bond (i.e., pays no interest), has face value of 100 rubles and

maturity of 8 May, 2000. If the current ruble interest rate is 5.30 percent,

then the 10 February, 1997, ruble value of the bond is

405 Value at Risk

−∗ − ∗ − =−100 5 30 8 2000 10 1997 365 84 21exp[ . ( , . , )/ ] . percent May Feb 666

In dollars, the value of the bond is −84.2166

3.40 = −286.3365, so that

the net portfolio value is 586 − 286.3365 = 299.66.

This example is illustrated in the following display:

ABCDEFGH

Units of Index held 2

Bond maturity May 8, 2000

Date

Index

value

Ruble

interest

rate

Ruble

exchange

rate

Total

index

value

Ruble

bond

value

Dollar

bond

value

Portfolio

value

2/10/1997 293 5.30% 3.40 586.00 -84.2166 -286.3365 299.66

=B2*B6

=G6*D6 =F6+H6

=-100*EXP(-(B3-A6)/365*C6)

BOOTSTRAPPING DATA--INITIAL POSITION

Now suppose we have exchange-rate and index data. We illustrate data

for 40 days (the middle of the data has been hidden, but you will see that

the rows go from 8 to 45):

ABCDE

Units of Index held 2

Bond maturity May 8, 2000

Day Index

Foreign

interest rate

Exchange

rate

Portfolio

value

1/2/1997 462.71 5.28% 3.50 632.13

1/3/1997 514.71 5.26% 3.47 738.41

1/4/1997 456.5 5.23% 3.46 622.49

1/5/1997 487.39 5.24% 3.45 685.17

1/6/1997 470.42 5.25% 3.45 651.28

2/8/1997 467.14 5.31% 3.44 644.75

2/9/1997 562.06 5.32% 3.41 837.17

2/10/1997 481.61 5.30% 3.40 676.88

EXCHANGE-RATE AND INDEX DATA

We want to use these data as a basis for generating “random” return

data. We illustrate one technique for doing so, called bootstrapping: This

406 Chapter 15

term refers to random reshufﬂ ings of the data. For each iteration, we

reorder the series of index prices, interest rates, and exchange rates and

calculate the return on the portfolio.

ABCDEFG

Units of Index held 2 Iterations 5,000 Start time 11:45:50

Bond maturity May 8, 2000 Return 0.15 Elapsed 0:16:41

Number of data points 40

=H46/H7-1

Day Index

Index

rand

Foreign

interest rate

Interest

rand

Exchange

rate

Exchange

rand

Portfolio

value

1/2/1997 615.93 0.0029 5.31% 0.0148 3.40 0.0202 947.24

1/3/1997 757.02 0.0447 5.24% 0.0179 3.41 0.0456 1,227.87

1/4/1997 581.50 0.0452 5.32% 0.0377 3.44 0.0620 875.04

1/5/1997 651.99 0.0742 5.28% 0.0383 3.42 0.0846 1,017.27

1/6/1997 605.37 0.1027 5.28% 0.0634 3.50 0.1070 917.28

1/7/1997 514.71 0.1455 5.28% 0.0640 3.43 0.1321 741.79

1/8/1997 640.43 0.1574 5.28% 0.0652 3.48 0.1522 988.99

1/9/1997 645.50 0.2020 5.25% 0.0789 3.43 0.1532 1,003.00

1/10/1997 450.91 0.2049 5.34% 0.0884 3.46 0.1994 612.12

1/11/1997 475.49 0.2075 5.26% 0.1111 3.46 0.2074 660.47

1/12/1997 654.17 0.3184 5.36% 0.3611 3.42 0.2156 1,022.10

1/13/1997 445.77 0.3308 5.31% 0.3662 3.37 0.2309 608.98

1/14/1997 669.12 0.3799 5.28% 0.4016 3.44 0.2428 1,049.48

1/15/1997 500.71 0.3878 5.31% 0.4112 3.44 0.2469 712.65

1/16/1997 705.27 0.3951 5.35% 0.4387 3.46 0.2963 1,120.69

1/17/1997 533.40 0.4201 5.28% 0.4603 3.46 0.3266 776.23

1/18/1997 639.95 0.4465 5.32% 0.4751 3.42 0.3454 993.03

1/19/1997 444.27 0.4551 5.30% 0.4763 3.39 0.4183 603.96

1/20/1997 670.63 0.4654 5.25% 0.4797 3.45 0.5154 1,051.12

1/21/1997 470.42 0.4655 5.25% 0.4952 3.42 0.5357 653.18

1/22/1997 458.26 0.5114 5.26% 0.5059 3.45 0.5883 626.39

1/23/1997 466.45 0.5386 5.27% 0.5217 3.47 0.6197 641.14

1/24/1997 462.71 0.5456 5.27% 0.5596 3.48 0.6813 632.78

1/25/1997 459.27 0.5682 5.24% 0.5798 3.42 0.7240 630.62

1/26/1997 740.74 0.6100 5.23% 0.6026 3.41 0.7321 1,194.27

1/27/1997 790.82 0.6245 5.28% 0.6068 3.41 0.7507 1,294.86

1/28/1997 487.39 0.6405 5.34% 0.6384 3.44 0.7987 686.00

1/29/1997 456.50 0.6795 5.29% 0.6583 3.52 0.8033 616.98

1/30/1997 467.14 0.6922 5.24% 0.6601 3.40 0.8047 647.84

1/31/1997 481.61 0.7349 5.27% 0.6699 3.41 0.8337 676.18

2/1/1997 544.75 0.7357 5.26% 0.6836 3.42 0.8501 801.48

2/2/1997 453.69 0.7423 5.24% 0.7388 3.68 0.8512 597.22

2/3/1997 786.16 0.7436 5.27% 0.7867 3.49 0.8553 1,278.42

2/4/1997 561.88 0.7944 5.29% 0.8107 3.41 0.8797 836.74

2/5/1997 562.06 0.9345 5.32% 0.8328 3.45 0.8811 833.97

2/6/1997 472.35 0.9353 5.23% 0.8759 3.42 0.9045 656.19

2/7/1997 636.02 0.9406 5.35% 0.8899 3.42 0.9336 984.61

2/8/1997 461.79 0.9630 5.30% 0.9238 3.49 0.9662 629.75

2/9/1997 584.41 0.9688 5.26% 0.9403 3.47 0.9878 876.25

2/10/1997 687.33 0.9713 5.25% 0.9585 3.41 0.9990 1,087.02

BOOTSTRAPPING RETURN DISTRIBUTIONS

The distribution of the bootstrapped return data looks like the

following.

3. The bootstrapping technique is illustrated in the appendix to this chapter.

407 Value at Risk

The graph on the right indicates the return distribution, which is far

from normal. From columns L, M, and N, you can tell that the 1 percent

VaR is about −50 percent, meaning that with a probability of 1 percent,

the ﬁ rm could lose 50 percent of its investment.

IJKLMNOPQRSTU

Max return 1.16940 <

=MAX(J:J) =K3

Min return -0.53931 <

=MIN(J:J)

=($K$2-$K$3)/50+L7

Output

Bins

Cumulative

percent

-0.3950 -0.5393 1 0.0%

-0.1775 -0.5051 53 1.1%

-0.2292 -0.4710 96 1.9%

-0.2688 -0.4368 87 1.7%

-0.0166 -0.4026 121 2.4%

0.0276 -0.3684 217 4.3%

0.5282 -0.3343 199 4.0%

-0.1496 -0.3001 160 3.2%

0.8918 -0.2659 130 2.6%

-0.4159 -0.2317 162 3.2%

-0.4101 -0.1976 164 3.3%

0.0182 -0.1634 135 2.7%

0.0449 -0.1292 158 3.2%

-0.3506 -0.0950 149 3.0%

-0.2166 -0.0609 203 4.1%

-0.3239 -0.0267 275 5.5%

-0.2028 0.0075 228 4.6%

0.2835 0.0417 244 4.9%

1.0692 0.0758 212 4.2%

0.0790 0.1100 128 2.6%

0.6386 0.1442 135 2.7%

-0.2984 0.1783 92 1.8%

0.2447 0.2125 137 2.7%

-0.1735 0.2467 98 2.0%

0.0521 0.2809 100 2.0%

-0.0751 0.3150 109 2.2%

-0.0110 0.3492 94 1.9%

-0.0717 0.3834 73 1.5%

0.1361 0.4176 73 1.5%

-0.3946 0.4517 65 1.3%

0.8810 0.4859 87 1.7%

0.0918 0.5201 59 1.2%

-0.0068 0.5543 98 2.0%

-0.2152 0.5884 101 2.0%

0.0085 0.6226 75 1.5%

0.2460 0.6568 73 1.5%

-0.3402 0.6910 51 1.0%

-0.4153 0.7251 54 1.1%

0.8356 0.7593 33 0.7%

-0.3696 0.7935 35 0.7%

-0.3039 0.8277 33 0.7%

-0.1435 0.8618 22 0.4%

-0.4194 0.8960 37 0.7%

-0.3564 0.9302 21 0.4%

-0.1352 0.9644 25 0.5%

0.6275 0.9985 35 0.7%

-0.3857 1.0327 19 0.4%

0.2709 1.0669 22 0.4%

-0.4091 1.1010 15 0.3%

-0.3238 1.1352 6 0.1%

-0.3540 1.1694 7 0.1%

-0.3286

-0.0724

-0.3396

0.0087

-0.3552

Bootstrapped Return Frequency Distribution

5,000 Iterations

Bootstrapped Return Frequency Distribution

5,000 Iterations

100

150

200

250

300

-1.0000 -0.5000 0.0000 0.5000 1.0000 1.5000

Return

Frequency

Much more date: In this

case 5,000 data points.