Experiments

The Value-at-Risk (VAR) approach to measuring the risk of a financial portfolio is discussed in Box 16.5 (pp. 634-5) of the text. The calculations for Box 16.5 were performed using the spreadsheet in this file (see tab VAR_TEST). You can use this spreadsheet to run some simple experiments and learn a few things about VAR. Just a few more words of introduction. The objective of Box 16.5 is to discover the riskiness of a portfolio of five foreign currencies: the Canadian dollar, British pound, Euro, Japanese yen and Swiss franc. Monthly data on these five currencies are in cells B9:F132 of the spreadsheet. Log returns are in cells K10:O132. Summary statistics like average monthly return are in cells K136:O136 and the standard deviation of returns are in cells K136:O136. The correlation matrix of returns is in cells K141:O145. You may want to print out this information, and then browse around the spreadsheet to see if everything is clear. Once things are clear, then proceed to the first question. 1. The base case is set to analyze a portfolio with $100,000 in positions spread across the five currencies. The $ value of the position in each currency is shown in cells K138:O138. The base case measures a 95% VAR using both Gross VAR and Net VAR measures for the portfolio. The 95% level (entered in cell E147) is a figure that we can change. (a) What is the estimate of the Gross VAR and Net VAR for this portfolio? (b) Briefly, why is the Net VAR so much smaller than the Gross VAR? (c) Which is better measure of risk in this case, the Gross or Net measure? 2. Try the following experiments: (a) Change the probability level in cell E147 to 99% and recalculate Gross and Net VAR. What are the values? How do they compare with the base case? What’s the intuition behind this result? (b) Change the probability level in cell E147 to 90% and recalculate Gross and Net VAR. What are the values? How do they compare with the base case? What’s the intuition behind this result? 3. Try the following experiment: Return the probability level in cell E147 to 95% and double the size of each position, so that the total of all positions is $200,000. (a) What are the values of Gross and Net VAR in this case? How do they compare with the base case? What’s the intuition behind this result? 4. Try the next experiment: Keep the probability level in cell E147 at 95% and change the size of the British pound position to +$20,000. (a) What are the values of Gross and Net VAR in this case? How do they compare with the base case? What’s the intuition behind this result? 5. Try these experiments: Keeping the probability level in cell E147 at 95% suppose that you wanted to keep you Net VAR at a maximum of $2,000. (a) How could you accomplish this objective by altering only your Euro position? How many Euros would you buy or sell? (b) How could you accomplish this objective by altering only your Yen (¥) position? How many Yen would you buy or sell? (c) Why is there a difference between your answers to (a) and (b)? 6. What other experiments can you think of using this spreadsheet? Suggest another experiment and explain why you think it could be of interest. Calculation of Gross and Net VAR (Box 16.3) Returns are ln(St/St-1) over 123 months, January 1990 - April 2000 COUNTRY CODE COUNTRY NAME 156 112 134 158 146 United Kingdom Germany Japan Canada Germany 156..AE.ZF. United Kingdom 112..AG.ZF. 134..AE.ZF.. Japan 158..AE.ZF. Switzerland Canada 146..AE.ZF.. SERIES CODE .. .. . .. . Augmented by Euro Area 163..AE.ZF.. OFFICIAL DESCRIPTOR MARKET RATE MARKET RATE MARKET RATE MARKET RATE RATE MGNITUD_EN 1990M1 1990M2 1990M3 1990M4 1990M5 1990M6 1990M7 1990M8 1990M9 1990M10 1990M11 1990M12 1991M1 1991M2 1991M3 1991M4 1991M5 1991M6 1991M7 1991M8 1991M9 1991M10 1991M11 1991M12 US $ per (unit $ per (unit $ per (unit n.c.) (unit $ per (unit n.c.) US n.c.) US n.c.) US $ per US n.c.) 0.843170 1.682800 0.594318 0.006937 0.67024 0.838785 1.684700 0.591086 0.006739 0.67227 -0.00521 0.854555 1.642800 0.590179 0.006361 0.66823 0.018626 0.858369 1.636000 0.595132 0.006275 0.68611 0.004454 0.851716 1.682000 0.591366 0.006592 0.70299 -0.00778 0.857559 1.741800 0.598265 0.006540 0.70547 0.006837 0.867453 1.852700 0.626566 0.006787 0.73828 0.011471 0.866101 1.900500 0.640123 0.006932 0.77369 -0.00156 0.864678 1.873500 0.639345 0.007257 0.76982 -0.00164 0.856604 1.945500 0.658285 0.007731 0.77670 -0.00938 0.858001 1.937200 0.664452 0.007499 0.77942 0.001629 0.861846 1.928000 0.669344 0.007440 0.77190 0.004472 0.860067 1.961500 0.671141 0.007622 0.79020 -0.00207 0.869641 1.918500 0.657808 0.007576 0.75930 0.01107 0.863036 1.735500 0.582411 0.007092 0.68493 -0.00762 0.868508 1.709000 0.577201 0.007278 0.68329 0.00632 0.873210 1.710500 0.579811 0.007252 0.68027 0.005399 0.875657 1.621500 0.551876 0.007252 0.64103 0.002798 0.868282 1.684500 0.572803 0.007257 0.65630 -0.00846 0.875657 1.687000 0.574449 0.007291 0.65681 0.008458 0.883704 1.752500 0.601287 0.007527 0.68989 0.009149 0.890551 1.741500 0.597693 0.007639 0.67981 0.007718 0.881368 1.760000 0.612820 0.007689 0.69541 -0.01037 0.865351 1.870700 0.659631 0.007987 0.73774 -0.01834 0.001128434 -0.0251854 -0.00414786 0.027729323 0.0349355 0.061724973 0.025472975 -0.01430867 0.037710674 -0.00427538 -0.00476044 0.01722629 -0.02216586 -0.10024807 -0.01538715 0.000877321 -0.05343408 0.038117137 0.00148302 0.038091536 -0.00629653 0.010566998 0.060998883 -0.005453 -0.001536 0.008356 -0.006348 0.011599 0.046221 0.021405 -0.001215 0.029193 0.009325 0.007336 0.002681 -0.020066 -0.121737 -0.008987 0.004512 -0.049378 0.037218 0.002868 0.045661 -0.005995 0.024994 0.073608 -0.02906 -0.05761 -0.01358 0.049198 -0.00788 0.036973 0.021263 0.045745 0.063281 -0.03046 -0.00784 0.024098 -0.00608 -0.06596 0.025864 -0.00363 0 0.000725 0.004728 0.031855 0.014787 0.006515 0.038007 D:\BOOK\DRAFT-3\VAR_TEST.XLS 1992M1 1992M2 1992M3 1992M4 1992M5 1992M6 1992M7 1992M8 1992M9 1992M10 1992M11 1992M12 1993M1 1993M2 1993M3 1993M4 1993M5 1993M6 1993M7 1993M8 1993M9 1993M10 1993M11 1993M12 1994M1 1994M2 1994M3 1994M4 1994M5 1994M6 1994M7 1994M8 1994M9 1994M10 1994M11 1994M12 1995M1 0.851209 0.846095 0.840407 0.836190 0.830082 0.835492 0.844452 0.836680 0.801282 0.806452 0.777726 0.786720 0.787340 0.797703 0.795418 0.787402 0.786968 0.779849 0.778756 0.757117 0.748615 0.756716 0.748111 0.755287 0.753409 0.739645 0.722700 0.723851 0.722178 0.723432 0.722961 0.729341 0.745823 0.739372 0.727167 0.712860 0.709925 Calculation of Gross and Net VAR (Box 16.3) Returns are ln(St/St-1) over 123 months, January 1990 - April 2000 1.784200 1.760000 1.739200 1.767200 1.820500 1.898000 1.919300 1.982600 1.782700 1.569000 1.504800 1.512000 1.502200 1.426200 1.503500 1.574500 1.558700 1.508700 1.480700 1.497300 1.510700 1.486700 1.486500 1.481200 1.498100 1.487000 1.482200 1.509500 1.510200 1.540200 1.526300 1.534000 1.579700 1.622000 1.562500 1.562500 1.593000 0.619694 0.610575 0.608754 0.602482 0.620040 0.654879 0.676133 0.709371 0.709572 0.650618 0.624415 0.619579 0.627549 0.608643 0.619464 0.632831 0.627353 0.592347 0.574812 0.599413 0.617322 0.596908 0.584454 0.579274 0.574251 0.583567 0.598086 0.600962 0.609310 0.626802 0.626645 0.631712 0.645870 0.661376 0.636618 0.645661 0.662778 0.007952 0.007735 0.007508 0.007491 0.007797 0.007968 0.007862 0.008137 0.008389 0.008117 0.008019 0.008016 0.008026 0.008496 0.008595 0.008997 0.009390 0.009368 0.009443 0.009597 0.009510 0.009242 0.009179 0.008941 0.009099 0.009602 0.009695 0.009756 0.009572 0.010096 0.010025 0.010045 0.010157 0.010269 0.010109 0.010026 0.010147 0.69711 0.67204 0.66733 0.65595 0.68329 0.72595 0.75873 0.79428 0.81334 0.72913 0.69156 0.68681 0.68050 0.65643 0.66890 0.70067 0.70102 0.66247 0.65595 0.68166 0.70547 0.67590 0.67024 0.67590 0.68329 0.69955 0.70922 0.70711 0.71480 0.74377 0.73883 0.75019 0.77760 0.79283 0.75188 0.76249 0.78419 -0.01648 -0.00603 -0.00675 -0.00503 -0.00733 0.006496 0.010667 -0.00925 -0.04323 0.006431 -0.03627 0.011498 0.000787 0.013076 -0.00287 -0.01013 -0.00055 -0.00909 -0.0014 -0.02818 -0.01129 0.010763 -0.01144 0.009546 -0.00249 -0.01844 -0.02318 0.001591 -0.00231 0.001735 -0.00065 0.008786 0.022347 -0.00869 -0.01664 -0.01987 -0.00413 -0.04734256 -0.01365633 -0.01188857 0.015971136 0.029714816 0.041689512 0.011159836 0.032448578 -0.10628005 -0.1276906 -0.04177847 0.004773279 -0.00650258 -0.05191714 0.052782159 0.046142038 -0.01008562 -0.03260379 -0.0187334 0.011148537 0.008909633 -0.01601422 -0.00013454 -0.00357179 0.011345068 -0.00743697 -0.0032332 0.018251 0.000463622 0.019670186 -0.00906577 0.005032197 0.029356253 0.026425 -0.03737285 0 0.019331928 -0.062454 -0.014824 -0.002987 -0.010356 0.028725 0.054667 0.031939 0.047989 0.000284 -0.086739 -0.041108 -0.007775 0.012783 -0.030591 0.017622 0.02135 -0.008695 -0.057416 -0.03005 0.041908 0.029441 -0.033628 -0.021086 -0.008902 -0.008709 0.016093 0.024576 0.004796 0.013797 0.028303 -0.000251 0.008053 0.022164 0.023724 -0.038152 0.014105 0.026166 -0.00438 -0.02768 -0.02987 -0.00225 0.04012 0.021676 -0.01345 0.03439 0.030568 -0.03301 -0.0121 -0.0004 0.001203 0.05697 0.011536 0.045722 0.042764 -0.00237 0.007994 0.016183 -0.00908 -0.02859 -0.00691 -0.02627 0.017588 0.053739 0.009648 0.006321 -0.01904 0.053275 -0.00704 0.002007 0.011111 0.010928 -0.01569 -0.00826 0.012003 D:\BOOK\DRAFT-3\VAR_TEST.XLS 1995M2 1995M3 1995M4 1995M5 1995M6 1995M7 1995M8 1995M9 1995M10 1995M11 1995M12 1996M1 1996M2 1996M3 1996M4 1996M5 1996M6 1996M7 1996M8 1996M9 1996M10 1996M11 1996M12 1997M1 1997M2 1997M3 1997M4 1997M5 1997M6 1997M7 1997M8 1997M9 1997M10 1997M11 1997M12 1998M1 1998M2 0.717618 0.714796 0.735510 0.730140 0.728120 0.730087 0.744602 0.744158 0.746547 0.736160 0.732493 0.727643 0.728757 0.733568 0.734538 0.729767 0.732547 0.727379 0.730780 0.734107 0.745823 0.741400 0.730140 0.742225 0.731797 0.722387 0.715820 0.724165 0.724061 0.723903 0.720202 0.723589 0.709622 0.702149 0.699741 0.686672 0.702494 Calculation of Gross and Net VAR (Box 16.3) Returns are ln(St/St-1) over 123 months, January 1990 - April 2000 1.579000 1.613000 1.612500 1.604000 1.594000 1.602500 1.552500 1.580000 1.574600 1.534500 1.550000 1.506700 1.533000 1.525400 1.508100 1.538300 1.548500 1.558000 1.557200 1.562200 1.628200 1.682500 1.698000 1.602000 1.629800 1.629300 1.629400 1.639300 1.663300 1.636400 1.622800 1.613200 1.673300 1.676900 1.653800 1.638200 1.644400 0.684416 0.722700 0.724008 0.720098 0.722700 0.724375 0.681896 0.704821 0.707514 0.696040 0.697593 0.670331 0.681803 0.677645 0.653851 0.651848 0.657073 0.680087 0.675265 0.654965 0.661113 0.651721 0.643170 0.612145 0.591296 0.596019 0.578905 0.588235 0.573362 0.545703 0.557414 0.566412 0.580383 0.566990 0.558005 0.547495 0.552120 0.010304 0.011192 0.011940 0.012019 0.011820 0.011308 0.010091 0.010173 0.009833 0.009847 0.009725 0.009324 0.009551 0.009409 0.009542 0.009242 0.009139 0.009266 0.009222 0.009011 0.008787 0.008790 0.008621 0.008197 0.008280 0.008061 0.007883 0.008587 0.008741 0.008457 0.008379 0.008264 0.008337 0.007840 0.007695 0.007880 0.007859 0.80665 0.87789 0.87719 0.87329 0.86881 0.87252 0.83091 0.87428 0.87619 0.85778 0.86919 0.82136 0.83591 0.84019 0.80743 0.79384 0.79904 0.83998 0.833541719 0.795608243 0.796241739 0.77000077 0.742721331 0.705318098 0.677002234 0.689084895 0.680272109 0.708817692 0.685494927 0.660196739 0.674399784 0.688705234 0.714643036 0.701213099 0.687143544 0.679393981 0.681245316 0.010778 -0.00394 0.028567 -0.00733 -0.00277 0.002698 0.019686 -0.0006 0.003205 -0.01401 -0.00499 -0.00664 0.001529 0.00658 0.001321 -0.00652 0.003802 -0.00708 0.004666 0.004541 0.015835 -0.00595 -0.0153 0.016416 -0.01415 -0.01294 -0.00913 0.011592 -0.00014 -0.00022 -0.00513 0.004692 -0.01949 -0.01059 -0.00343 -0.01885 0.02278 -0.0088273 0.021304064 -0.00031003 -0.00528526 -0.00625393 0.005318329 -0.03169837 0.017558312 -0.00342358 -0.02579668 0.010050336 -0.0283331 0.017304771 -0.00496993 -0.01140609 0.01982733 0.00660881 0.006116227 -0.00051361 0.003205747 0.041380026 0.032805672 0.009170305 -0.05819824 0.017204459 -0.00030683 6.13742E-05 0.006057473 0.014534259 -0.01630487 -0.00834565 -0.00593327 0.036577941 0.002149126 -0.01387118 -0.00947759 0.003777498 0.032125 0.054429 0.001808 -0.005415 0.003607 0.002315 -0.060433 0.033067 0.003813 -0.016351 0.00223 -0.039864 0.016968 -0.006117 -0.035743 -0.003068 0.007984 0.034425 -0.007116 -0.030524 0.009344 -0.014309 -0.013207 -0.049439 -0.034652 0.007955 -0.029134 0.015989 -0.02561 -0.049442 0.021233 0.016014 0.024367 -0.023347 -0.015974 -0.019013 0.008412 0.015338 0.082665 0.064725 0.006589 -0.01669 -0.04428 -0.11392 0.008105 -0.034 0.001476 -0.01253 -0.04209 0.024063 -0.01498 0.014023 -0.03193 -0.01121 0.013803 -0.00481 -0.02306 -0.02518 0.000264 -0.01941 -0.05043 0.01005 -0.02671 -0.02232 0.085543 0.017761 -0.0331 -0.00926 -0.01373 0.008716 -0.06143 -0.01864 0.02375 -0.00275 D:\BOOK\DRAFT-3\VAR_TEST.XLS 1998M3 1998M4 1998M5 1998M6 1998M7 1998M8 1998M9 1998M10 1998M11 1998M12 1999M1 1999M2 1999M3 1999M4 1999M5 1999M6 1999M7 1999M8 1999M9 1999M10 1999M11 1999M12 2000M1 2000M2 2000M3 2000M4 Count 0.705916 0.699203 0.686295 0.679532 0.661770 0.636051 0.655351 0.648298 0.656297 0.653381 0.663394 0.663394 0.662603 0.686059 0.677048 0.679348 0.663878 0.668539 0.680272 0.679810 0.678979 0.692857 0.689085 0.690226 0.687994 0.675174 124.00 Calculation of Gross and Net VAR (Box 16.3) Returns are ln(St/St-1) over 123 months, January 1990 - April 2000 1.680100 1.670600 1.630200 1.662700 1.637800 1.652100 1.695500 1.677300 1.652100 1.663500 1.647400 1.602100 1.611500 1.612500 1.604200 1.574900 1.619000 1.603700 1.646500 1.634000 1.594500 1.616400 1.623700 1.592700 1.595100 1.568100 124.00 0.541477 0.557165 0.561073 0.552883 0.561956 0.564239 0.596694 0.605767 0.587544 0.597729 0.582055 0.563341 0.549230 0.541816 0.534607 0.528062 0.546775 0.540589 0.545293 0.534300 0.516252 0.513644 0.500604 0.496670 0.488438 0.464507 124.00 0.007573 0.007559 0.007192 0.007100 0.006959 0.007069 0.007394 0.008591 0.008087 0.008651 0.008606 0.008375 0.008306 0.008380 0.008236 0.008258 0.008681 0.009024 0.009359 0.009537 0.009756 0.009785 0.009359 0.009076 0.009447 0.009385 124.00 0.657418973 0.66728947 0.674991563 0.656469507 0.669523299 0.686153424 0.720824623 0.742997251 0.711541198 0.726480203 0.705517144 0.692856648 0.672404519 0.657462196 0.656081879 0.644122383 0.668762121 0.658804928 0.667779633 0.652315721 0.628140704 0.625156289 0.609384522 0.606501698 0.599232982 0.577133953 124.00 0.004859 -0.00955 -0.01863 -0.0099 -0.02649 -0.03964 0.029892 -0.01082 0.012263 -0.00445 0.015208 0 -0.00119 0.034788 -0.01322 0.003391 -0.02303 0.006995 0.017399 -0.00068 -0.00122 0.020233 -0.00546 0.001655 -0.00324 -0.01881 0.021477739 -0.00567047 -0.02448014 0.01974008 -0.01508891 0.008693328 0.025930477 -0.01079232 -0.01513815 0.006876611 -0.00972553 -0.02788302 0.005850154 0.000620347 -0.00516058 -0.01843341 0.027616896 -0.00949522 0.026338363 -0.00762083 -0.02447079 0.013641247 0.004506041 -0.01927681 0.001505741 -0.01707173 -0.019465 0.028561 0.006989 -0.014704 0.016276 0.004054 0.055928 0.01509 -0.030544 0.017186 -0.026573 -0.032679 -0.025368 -0.01359 -0.013396 -0.012317 0.034824 -0.011379 0.008663 -0.020365 -0.034363 -0.005064 -0.025715 -0.00789 -0.016713 -0.050236 -0.03703 -0.00189 -0.04976 -0.01286 -0.02003 0.015711 0.044892 0.150092 -0.06042 0.067319 -0.00518 -0.02717 -0.00834 0.008927 -0.01736 0.002639 0.049947 0.038762 0.036481 0.018895 0.022668 0.002931 -0.04449 -0.03069 0.040092 -0.00659 Data and Calculations Box 16.5 on page 634 Count Average Return Std Dev of Return $$ Position % Weights Correlation Matrix Canada United Kingdom Germany 123 123 123 123 -0.00181 -0.00057394 -0.002004 0.002457 0.013142 0.028498349 0.02969 0.034668 60,000 (20,000) 25,000 25,000 60.00% -20.00% 25.00% 25.00% Canada United Kingdom Germany Japan 1 0.090725661 -0.027327 -0.06446 0.090726 1 0.711286 0.323539 -0.02733 0.711285647 1 0.47462 D:\BOOK\DRAFT-3\VAR_TEST.XLS Calculation of Gross and Net VAR (Box 16.3) Returns are ln(St/St-1) over 123 months, January 1990 - April 2000 Japan Switzerland -0.06446 0.323539209 -0.0717 0.654762046 1,297.05 Probability => Z-value => 0.95 Gross VAR 1.64 Net VAR Canada United Kingdom Germany Japan Switzerland 937.51 0.47462 1 0.932926 0.512417 1,220.88 1,425.59 Japan -4.4E-06 -1.6E-05 3.05E-05 7.51E-05 1.47E-05 Portfolio Portfolio Net VAR Canada United Kingdom Germany 6.22E-05 -4.0776E-06 -1.6E-06 -4.1E-06 3.24862E-05 -3.01E-05 -1.6E-06 -3.0091E-05 5.51E-05 -4.4E-06 -1.5982E-05 3.05E-05 -1.9E-06 -1.2381E-05 2.3E-05 Formula 16.13 =====> D:\BOOK\DRAFT-3\VAR_TEST.XLS Calculation of Gross and Net VAR (Box 16.3) Returns are ln(St/St-1) over 123 months, January 1990 - April 2000 Switzerland 0.003021 -0.00603 0.026406 0.024307 0.003521 0.045462 0.046854 -0.00502 0.008892 0.003501 -0.0097 0.023429 -0.03989 -0.10308 -0.00239 -0.00443 -0.05942 0.023544 0.000788 0.049135 -0.01472 0.022689 0.059083 D:\BOOK\DRAFT-3\VAR_TEST.XLS -0.05665 -0.03662 -0.00703 -0.0172 0.040836 0.060561 0.044155 0.045798 0.02371 -0.1093 -0.0529 -0.00689 -0.00923 -0.03602 0.018818 0.046412 0.000491 -0.05655 -0.00989 0.038447 0.034325 -0.04281 -0.00841 0.008413 0.010873 0.023506 0.013735 -0.00297 0.010806 0.039735 -0.00667 0.015262 0.035895 0.019394 -0.05304 0.014007 0.028068 Calculation of Gross and Net VAR (Box 16.3) Returns are ln(St/St-1) over 123 months, January 1990 - April 2000 D:\BOOK\DRAFT-3\VAR_TEST.XLS 0.028234 0.084631 -0.00079 -0.00446 -0.00514 0.004266 -0.04887 0.050878 0.002188 -0.02124 0.013211 -0.0566 0.017566 0.005112 -0.03978 -0.01697 0.006531 0.049969 -0.0077 -0.04658 0.000796 -0.03351 -0.03607 -0.05167 -0.04097 0.01769 -0.01287 0.041105 -0.03346 -0.0376 0.021285 0.02099 0.03697 -0.01897 -0.02027 -0.01134 0.002721 Calculation of Gross and Net VAR (Box 16.3) Returns are ln(St/St-1) over 123 months, January 1990 - April 2000 D:\BOOK\DRAFT-3\VAR_TEST.XLS -0.0356 0.014902 0.011476 -0.02782 0.01969 0.024535 0.049295 0.030296 -0.04326 0.020778 -0.02928 -0.01811 -0.02996 -0.02247 -0.0021 -0.0184 0.03754 -0.015 0.013531 -0.02343 -0.03776 -0.00476 -0.02555 -0.00474 -0.01206 -0.03758 Calculation of Gross and Net VAR (Box 16.3) Returns are ln(St/St-1) over 123 months, January 1990 - April 2000 123 -0.00122 0.033175 10,000 100,000 10.00% SwitzerlandPortfolio -0.0717 0.654762 0.932926 D:\BOOK\DRAFT-3\VAR_TEST.XLS 0.512417 1 545.67 5,426.71 Calculation of Gross and Net VAR (Box 16.3) Returns are ln(St/St-1) over 123 months, January 1990 - April 2000 Switzerland -1.9E-06 -1.2E-05 2.3E-05 1.47E-05 1.1E-05 Variance 0.000232 Std Dev 0.015216 2,502.84 46.12% D:\BOOK\DRAFT-3\VAR_TEST.XLS