chapter15

PROBABILITY OF END-YEAR PORTFOLIO VALUEMean 20%Sigma 30%Initial investment 100Cutoff 80Probability thatportfolio worth lessthan cutoff 9.12% - =NORMDIST(B5,(1+B2)*B4,B4*B3,TRUE)Data table header: =NORMDIST(B5,(1+B2)*B4,B4*B3,FALSE)Portfolio value 0.00550 0.000010 0.000020 0.000130 0.000140 0.000450 0.000960 0.001870 0.003380 0.005590 0.0081100 0.0106110 0.0126120 0.0133130 0.0126140 0.0106150 0.0081160 0.0055170 0.0033180 0.0018190 0.0009200 0.0004210 0.0001220 0.0001230 0.0000240 0.0000250 0.0000E n d -o f-Y e a r P o rtfo lio V a lu e2 5 02 2 52 0 01 7 51 5 01 2 51 0 07 55 02 500 .0 0 00 .0 0 20 .0 0 40 .0 0 60 .0 0 80 .0 1 00 .0 1 20 .0 1 4A B C123456789101112131415161718192021222324252627282930313233343536PROBABILITY OF END-YEAR PORTFOLIO VALUEE n d -o f-Y e a r P o rtfo lio V a lu e2 5 02 2 52 0 01 7 51 5 01 2 51 0 07 55 02 500 .0 0 00 .0 0 20 .0 0 40 .0 0 60 .0 0 80 .0 1 00 .0 1 20 .0 1 4D123456789101112131415161718192021222324252627282930313233343536投资组合年底价值的概率分布均值 20%标准差 30%初始投资 100截止期价值 80投资组合低于截止期价值的概率 9.12% - =NORMDIST(B5,(1+B2)*B4,B4*B3,TRUE)模拟运算表表头: =NORMDIST(B5,(1+B2)*B4,B4*B3,FALSE)投资组合价值 0.00550 0.000010 0.000020 0.000130 0.000140 0.000450 0.000960 0.001870 0.003380 0.005590 0.0081100 0.0106110 0.0126120 0.0133130 0.0126140 0.0106150 0.0081160 0.0055170 0.0033180 0.0018190 0.0009200 0.0004210 0.0001220 0.0001230 0.0000240 0.0000250 0.0000E n d -o f-Y e a r P o rtfo lio V a lu e2 5 02 2 52 0 01 7 51 5 01 2 51 0 07 55 02 500 .0 0 00 .0 0 20 .0 0 40 .0 0 60 .0 0 80 .0 1 00 .0 1 20 .0 1 4投 资 组 合 年 底 价 值 的 概 率 分 布2 5 02 2 52 0 01 7 51 5 01 2 51 0 07 55 02 500 .0 0 00 .0 0 20 .0 0 40 .0 0 60 .0 0 80 .0 1 00 .0 1 20 .0 1 4A B C123456789101112131415161718192021222324252627282930313233343536投资组合年底价值的概率分布E n d -o f-Y e a r P o rtfo lio V a lu e2 5 02 2 52 0 01 7 51 5 01 2 51 0 07 55 02 500 .0 0 00 .0 0 20 .0 0 40 .0 0 60 .0 0 80 .0 1 00 .0 1 20 .0 1 4投 资 组 合 年 底 价 值 的 概 率 分 布2 5 02 2 52 0 01 7 51 5 01 2 51 0 07 55 02 500 .0 0 00 .0 0 20 .0 0 40 .0 0 60 .0 0 80 .0 1 00 .0 1 20 .0 1 4D123456789101112131415161718192021222324252627282930313233343536PROBABILITY OF END-YEAR PORTFOLIO VALUEMean 20%Sigma 30%Initial investment 100Cutoff 50.210Probability that portfolioworth less than cutoff 1.00% - =NORMDIST(B5,(1+B2)*B4,B4*B3,TRUE)A B C1234567891011121314151617181920投资组合年底价值的概率分布均值 20%标准差 30%初始投资 100截止期价值 50.210投资组合低于截止期价值的概率 1.00% - =NORMDIST(B5,(1+B2)*B4,B4*B3,TRUE)A B C1234567891011121314151617181920212223CALCULATING THE QUANTILES Mean 20%Sigma 30%Initial investment 100Cutoff 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-20-1001015202530354045464748495050.209745253545556575859606162C u m u la tiv e N o rm a l D is trib u tio n(o n ly p a rt-s o w e c a n s e e th e 1 % q u in tile )6 56 05 55 04 54 03 53 02 52 01 51 050- 5- 1 0- 1 5- 2 00 .0 %0 .5 %1 .0 %1 .5 %2 .0 %2 .5 %3 .0 %P o rtfo lio v a lu e (m illio n $ )Probability1 % q u a n t ile p o in tA B C D E1234567891011121314151617181920212223242526272829303132333435363738394041421.00% - =B6 , data table header1.53E-067.34E-063.17E-050.0001230.0002330.0004290.0007710.001350.0023030.003830.006210.0068190.007480.0081980.0089750.0098150.010.0117050.0127630.0139030.015130.0164490.0178640.0193830.0210090.022750.0246110.026598F G123456789101112131415161718192021222324252627282930313233343536373839404142计算分位点均值 20%标准差 30%初始投资 100截止期价值 50.210 - =NORMINV(0.01,(1+B2)*B4,B4*B3)1.00% - =NORMDIST(B5,(1+B2)*B4,B4*B3,TRUE)1.00% 水平下的VaR 49.790 - =B5-B6-20-1001015202530354045464748495050.209745253545556575859606162C u m u la tiv e N o rm a l D is trib u tio n(o n ly p a rt-s o w e c a n s e e th e 1 % q u in tile )6 56 05 55 04 54 03 53 02 52 01 51 050- 5- 1 0- 1 5- 2 00 .0 %0 .5 %1 .0 %1 .5 %2 .0 %2 .5 %3 .0 %P o rtfo lio v a lu e (m illio n $ )Probability1 % q u a n t ile p o in t累 计 正 态 分 布(只 是 一 部 分 - 因 此 我 们 能 看 到 1 % 分 位 点 )6 56 05 55 04 54 03 53 02 52 01 51 050- 5- 1 0- 1 5- 2 00 .0 %0 .5 %1 .0 %1 .5 %2 .0 %2 .5 %3 .0 %投 资 组 合 价 值 (单 位 ： 百 万 美 元 )概率1 % 分 位 点A B C D E1234567891011121314151617181920212223242526272829303132333435363738394041421.00% - =B6 , 模拟运算表表头1.53E-067.34E-063.17E-050.0001230.0002330.0004290.0007710.001350.0023030.003830.006210.0068190.007480.0081980.0089750.0098150.010.0117050.0127630.0139030.015130.0164490.0178640.0193830.0210090.022750.0246110.026598F G123456789101112131415161718192021222324252627282930313233343536373839404142QUANTILES FOR LOGNORMALDISTRIBUTIONInitial value, V0 100Mean, m 10%Sigma, s 30%Time period, T 1 - in yearsParameters of normal distribution of ln(VT)Mean 4.6602 - =LN(B2)+(B3-B42/2)*B5Sigma 0.3000 - =B4*B5Cutoff 52.576 - =LOGINV(0.01,B8,B9)VaR at 1% level 47.424 - =B2-B11A B C123456789101112对数正态分布的分位数初始值, V0 100均值, m 10%标准差, s 30%时间周期, T 1 - 以年计ln(VT)的正态分布的参数均值 4.6602 - =LN(B2)+(B3-B42/2)*B5标准差 0.3000 - =B4*B5截止期价值 52.576 - =LOGINV(0.01,B8,B9)1%水平上的VaR 47.424 - =B2-B11A B C123456789101112VaR FOR 3 ASSET PROBLEMMeanreturns Variance-covariance matrixAsset 1 10% 0.10 0.04 0.03Asset 2 12% 0.04 0.20 -0.04Asset 3 13% 0.03 -0.04 0.60Initial investment 100Mean 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.8500Sigma of investment value 38.4838Cutoff 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-B16Note that the functions in cells B8 and B9 are arrayfunctions: You must press Ctrl+Shift+Enter whenafter you write the function in the cell. The curlybrackets are not written-they appear automatically.A B C D E F G123456789101112131415161718Portfolioproportions0.300.250.45VaR FOR 3 ASSET PROBLEM- =SQRT(MMULT(MMULT(TRANSPOSE(H5:H7),D5:F7),H5:H7)H1234567891011121314151617183资产的VaR问题平均收益 方差-协方差矩阵资产1 10% 0.10 0.04 0.03资产2 12% 0.04 0.20 -0.04资产3 13% 0.03 -0.04 0.60初始投资 100平均收益 0.1185 - =MMULT(TRANSPOSE(B5:B7),H5:H7)投资组合的标准差 0.3848 - =SQRT(MMULT(MMULT(TRANSPOSE(H5:H7),D5:F7),H5:H7)平均投资额 111.8500投资额标准差 38.4838截止期价值 22.3234 - =NORMINV(0.01,(1+B10)*B9,B11*B9)累积PDF 0.01 - =NORMDIST(B16,B13,B14,TRUE)VaR 在1.00%水平上 77.6766 - =B9-B16注意在单元格 B8 和 B9 中是数组函数：你必须按Ctrl+Shift+Enter，当你在单元格中写完这个函数后，花括号 不用输入-它们是自动产生的。A B C D E F G123456789101112131415161718投资组合比例0.300.250.453资产的VaR问题- =SQRT(MMULT(MMULT(TRANSPOSE(H5:H7),D5:F7),H5:H7)H123456789101112131415161718BOOTSTRAPPING DATA-INITIAL POSITIONUnits of Index held 2Bond maturity 五月 8, 2000DateIndexvalueRubleinterestrateRubleexchangerateTotalindexvalueRublebondvalueDollarbondvalue1997/2/10 293 5.30% 3.40 586.00 -84.2166 -286.336=B2*B6=G6*D6=-100*EXP(-(B3-A6)/365*C6)A B C D E F G H1234567891011Portfoliovalue299.66=F6+H6BOOTSTRAPPING DATA-INITIAL POSITIONI1234567891011自助法数据-初始头寸拥有的个数 2债券的到期日 五月 8, 2000日期 指数价值卢布利息率 卢布交易率总指数价值卢布债券价值美元债券价值1997/2/10 293 5.30% 3.40 586.00 -84.2166 -286.336=B2*B6=G6*D6=-100*EXP(-(B3-A6)/365*C6)A B C D E F G H1234567891011投资组合价值299.66=F6+H6自助法数据-初始头寸I1234567891011EXCHANGE RATE AND INDEX DATAUnits of Index held 2Bond maturity 五月 8, 2000Day Index Foreigninterest rate Exchangerate Portfoliovalue1997/1/2 462.71 5.28% 3.50 632.131997/1/3 514.71 5.26% 3.47 738.411997/1/4 456.5 5.23% 3.46 622.491997/1/5 487.39 5.24% 3.45 685.171997/1/6 470.42 5.25% 3.45 651.281997/2/8 467.14 5.31% 3.44 644.751997/2/9 562.06 5.32% 3.41 837.171997/2/10 481.61 5.30% 3.40 676.88A B C D E F12345678910434445Return data (to be bootstrapped) Data with random seriesDaily rates of return over 39 (40?) daysDay Index Interest Exchange Day Index1 12 0.1065 -0.0038 -0.0086 2 0.00353 -0.1200 -0.0057 -0.0029 3 0.00704 0.0655 0.0019 -0.0029 4 -0.01775 -0.0354 0.0019 0.0000 5 0.007138 0.0292 0.0019 0.0058 38 0.000039 0.1850 0.0019 -0.0088 39 -0.003440 -0.1545 -0.0038 -0.0029 40 0.0034G H I J K L M N12345678910434445Data with random series, sortedRandom Interest Random Exchange Random Day Index Random10.6691 -0.0038 0.1375 -0.0086 0.3609 2 -0.0035 0.03540.6747 -0.0057 0.3834 -0.0029 0.7954 3 0.0174 0.04910.7330 0.0019 0.5645 -0.0029 0.5980 4 -0.0069 0.10100.8678 0.0019 0.3780 0.0000 0.5654 5 -0.0069 0.12880.9667 0.0019 0.9766 0.0058 0.0482 38 0.0068 0.92420.4986 0.0019 0.9585 -0.0088 0.8108 39 0.0035 0.92820.4934 -0.0038 0.8703 -0.0029 0.9177 40 0.0000 0.9667Data with random seriesO P Q R S T U V W12345678910434445Bootstrapped dataInterest Random Exchange Random Day Index Interest Exchange1997/2/10 481.6100 0.0530 3.4000-0.0246 0.0981 -0.0029 0.0093 1997/2/11 479.9435 0.0517 3.39010.0019 0.1055 0.0029 0.0243 1997/2/12 488.3636 0.0518 3.3998-0.0019 0.1078 0.0116 0.0292 1997/2/13 485.0071 0.0517 3.43960.0038 0.1244 0.0058 0.0482 1997/2/14 481.6507 0.0519 3.45970.0019 0.9585 0.0059 0.8940 1997/3/19 498.6351 0.0531 3.34160.0000 0.9632 -0.0029 0.9177 1997/3/20 500.3725 0.0531 3.33180.0019 0.9766 -0.0087 0.9280 1997/3/21 500.3725 0.0532 3.3029Data with random series, sortedX Y Z AA AB AC AD AE AF12345678910434445Value676.8835673.1553689.2195679.0115670.7405714.4228718.6860721.1859AG AH12345678910434445包含随机数序列的数据，已排序汇率 随机数 日期 指数 随机数1-0.0086 0.3609 2 -0.0035 0.0354-0.0029 0.7954 3 0.0174 0.0491-0.0029 0.5980 4 -0.0069 0.10100.0000 0.5654 5 -0.0069 0.12880.0058 0.0482 38 0.0068 0.9242-0.0088 0.8108 39 0.0035 0.9282-0.0029 0.9177 40 0.0000 0.9667A B C D E F12345678910434445自助数据利率 随机数 汇率 随机数 日期 指数 利率1997/2/10 #REF! #REF!-0.0246 0.0981 -0.0029 0.0093 1997/2/11 #REF! #REF!0.0019 0.1055 0.0029 0.0243 1997/2/12 #REF! #REF!-0.0019 0.1078 0.0116 0.0292 1997/2/13 #REF! #REF!0.0038 0.1244 0.0058 0.0482 1997/2/14 #REF! #REF!0.0019 0.9585 0.0059 0.8940 1997/3/19 #REF! #REF!0.0000 0.9632 -0.0029 0.9177 1997/3/20 #REF! #REF!0.0019 0.9766 -0.0087 0.9280 1997/3/21 #REF! #REF!G H I J K L M N12345678910434445汇率 投资组合价值#REF! #REF!#REF! #REF!#REF! #REF!#REF! #REF!#REF! #REF!#REF! #REF!#REF! #REF!#REF! #REF!O P Q R S T U V W12345678910434445Exchange40.000040.000040.000040.000040.000040.000040.000040.0000X Y Z AA AB AC AD AE AF12345678910434445Value-4000.0000-4000.0000-4000.0000-4000.0000-4000.0000-4000.0000-4000.0000-4000.0000AG AH12345678910434445BOOTSTRAPPING RETURN DISTRIBUTIONSUnits of Index held 2 Iterations 5,000Bond maturity 五月 8, 2000 Return 0.15Number of data points 40Day IndexIndexrandForeigninterest rateInterestrandExchangerate1997/1/2 615.93 0.0029 5.31% 0.0148 3.401997/1/3 757.02 0.0447 5.24% 0.0179 3.411997/1/4 581.50 0.0452 5.32% 0.0377 3.441997/1/5 651.99 0.0742 5.28% 0.0383 3.421997/1/6 605.37 0.1027 5.28% 0.0634 3.501997/1/7 514.71 0.1455 5.28% 0.0640 3.431997/1/8 640.43 0.1574 5.28% 0.0652 3.481997/1/9 645.50 0.2020 5.25% 0.0789 3.431997/1/10 450.91 0.2049 5.34% 0.0884 3.461997/1/11 475.49 0.2075 5.26% 0.1111 3.461997/1/12 654.17 0.3184 5.36% 0.3611 3.421997/1/13 445.77 0.3308 5.31% 0.3662 3.371997/1/14 669.12 0.3799 5.28% 0.4016 3.441997/1/15 500.71 0.3878 5.31% 0.4112 3.441997/1/16 705.27 0.3951 5.35% 0.4387 3.461997/1/17 533.40 0.4201 5.28% 0.4603 3.461997/1/18 639.95 0.4465 5.32% 0.4751 3.421997/1/19 444.27 0.4551 5.30% 0.4763 3.391997/1/20 670.63 0.4654 5.25% 0.4797 3.451997/1/21 470.42 0.4655 5.25% 0.4952 3.421997/1/22 458.26 0.5114 5.26% 0.5059 3.451997/1/23 466.45 0.5386 5.27% 0.5217 3.471997/1/24 462.71 0.5456 5.27% 0.5596 3.481997/1/25 459.27 0.5682 5.24% 0.5798 3.421997/1/26 740.74 0.6100 5.23% 0.6026 3.411997/1/27 790.82 0.6245 5.28% 0.6068 3.411997/1/28 487.39 0.6405 5.34% 0.6384 3.441997/1/29 456.50 0.6795 5.29% 0.6583 3.521997/1/30 467.14 0.6922 5.24% 0.6601 3.401997/1/31 481.61 0.7349 5.27% 0.6699 3.411997/2/1 544.75 0.7357 5.26% 0.6836 3.421997/2/2 453.69 0.7423 5.24% 0.7388 3.681997/2/3 786.16 0.7436 5.27% 0.7867 3.491997/2/4 561.88 0.7944 5.29% 0.8107 3.411997/2/5 562.06 0.9345 5.32% 0.8328 3.451997/2/6 472.35 0.9353 5.23% 0.8759 3.421997/2/7 636.02 0.9406 5.35% 0.8899 3.421997/2/8 461.79 0.9630 5.30% 0.9238 3.491997/2/9 584.41 0.9688 5.26% 0.9403 3.471997/2/10 687.33 0.9713 5.25% 0.9585 3.41A B C D E F123456789101112131415161718192021222324252627282930313233343536373839404142434445464748A B C D E F495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899A B C D E F100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150A B C D E F151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201A B C D E F202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252A B C D E F253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303A B C D E F304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354A B C D E F355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405A B C D E F406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456A B C D E F457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507A B C D E F508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558A B C D E F559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609A B C D E F610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660A B C D E F661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711A B C D E F712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762A B C D E F763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813A B C D E F814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864A B C D E F8658668678688698708718728738748758768778788798808818828838848858868878888898