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 156 112 134 158 146
COUNTRY
NAME Canada 112..AG.ZF. 134..AE.ZF.. Japan
Germany
156..AE.ZF. United Kingdom 158..AE.ZF. Switzerland Canada
146..AE.ZF.. Germany
United Kingdom Japan
SERIES CODE .. .. .
Augmented .. .
by Euro
Area
163..AE.ZF..
OFFICIAL
DESCRIPTOR MARKET RATE MARKET RATE MARKET RATE MARKET RATE RATE
MGNITUD_EN US n.c.) US n.c.) US $ per US n.c.)
US $ per (unit $ per (unit $ per (unit n.c.) (unit $ per (unit n.c.)
1990M1 0.843170 1.682800 0.594318 0.006937 0.67024
1990M2 0.838785 1.684700 0.591086 0.006739 0.67227 -0.00521 0.001128434 -0.005453 -0.02906
1990M3 0.854555 1.642800 0.590179 0.006361 0.66823 0.018626 -0.0251854 -0.001536 -0.05761
1990M4 0.858369 1.636000 0.595132 0.006275 0.68611 0.004454 -0.00414786 0.008356 -0.01358
1990M5 0.851716 1.682000 0.591366 0.006592 0.70299 -0.00778 0.027729323 -0.006348 0.049198
1990M6 0.857559 1.741800 0.598265 0.006540 0.70547 0.006837 0.0349355 0.011599 -0.00788
1990M7 0.867453 1.852700 0.626566 0.006787 0.73828 0.011471 0.061724973 0.046221 0.036973
1990M8 0.866101 1.900500 0.640123 0.006932 0.77369 -0.00156 0.025472975 0.021405 0.021263
1990M9 0.864678 1.873500 0.639345 0.007257 0.76982 -0.00164 -0.01430867 -0.001215 0.045745
1990M10 0.856604 1.945500 0.658285 0.007731 0.77670 -0.00938 0.037710674 0.029193 0.063281
1990M11 0.858001 1.937200 0.664452 0.007499 0.77942 0.001629 -0.00427538 0.009325 -0.03046
1990M12 0.861846 1.928000 0.669344 0.007440 0.77190 0.004472 -0.00476044 0.007336 -0.00784
1991M1 0.860067 1.961500 0.671141 0.007622 0.79020 -0.00207 0.01722629 0.002681 0.024098
1991M2 0.869641 1.918500 0.657808 0.007576 0.75930 0.01107 -0.02216586 -0.020066 -0.00608
1991M3 0.863036 1.735500 0.582411 0.007092 0.68493 -0.00762 -0.10024807 -0.121737 -0.06596
1991M4 0.868508 1.709000 0.577201 0.007278 0.68329 0.00632 -0.01538715 -0.008987 0.025864
1991M5 0.873210 1.710500 0.579811 0.007252 0.68027 0.005399 0.000877321 0.004512 -0.00363
1991M6 0.875657 1.621500 0.551876 0.007252 0.64103 0.002798 -0.05343408 -0.049378 0
1991M7 0.868282 1.684500 0.572803 0.007257 0.65630 -0.00846 0.038117137 0.037218 0.000725
1991M8 0.875657 1.687000 0.574449 0.007291 0.65681 0.008458 0.00148302 0.002868 0.004728
1991M9 0.883704 1.752500 0.601287 0.007527 0.68989 0.009149 0.038091536 0.045661 0.031855
1991M10 0.890551 1.741500 0.597693 0.007639 0.67981 0.007718 -0.00629653 -0.005995 0.014787
1991M11 0.881368 1.760000 0.612820 0.007689 0.69541 -0.01037 0.010566998 0.024994 0.006515
1991M12 0.865351 1.870700 0.659631 0.007987 0.73774 -0.01834 0.060998883 0.073608 0.038007
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
1992M1 0.851209 1.784200 0.619694 0.007952 0.69711 -0.01648 -0.04734256 -0.062454 -0.00438
1992M2 0.846095 1.760000 0.610575 0.007735 0.67204 -0.00603 -0.01365633 -0.014824 -0.02768
1992M3 0.840407 1.739200 0.608754 0.007508 0.66733 -0.00675 -0.01188857 -0.002987 -0.02987
1992M4 0.836190 1.767200 0.602482 0.007491 0.65595 -0.00503 0.015971136 -0.010356 -0.00225
1992M5 0.830082 1.820500 0.620040 0.007797 0.68329 -0.00733 0.029714816 0.028725 0.04012
1992M6 0.835492 1.898000 0.654879 0.007968 0.72595 0.006496 0.041689512 0.054667 0.021676
1992M7 0.844452 1.919300 0.676133 0.007862 0.75873 0.010667 0.011159836 0.031939 -0.01345
1992M8 0.836680 1.982600 0.709371 0.008137 0.79428 -0.00925 0.032448578 0.047989 0.03439
1992M9 0.801282 1.782700 0.709572 0.008389 0.81334 -0.04323 -0.10628005 0.000284 0.030568
1992M10 0.806452 1.569000 0.650618 0.008117 0.72913 0.006431 -0.1276906 -0.086739 -0.03301
1992M11 0.777726 1.504800 0.624415 0.008019 0.69156 -0.03627 -0.04177847 -0.041108 -0.0121
1992M12 0.786720 1.512000 0.619579 0.008016 0.68681 0.011498 0.004773279 -0.007775 -0.0004
1993M1 0.787340 1.502200 0.627549 0.008026 0.68050 0.000787 -0.00650258 0.012783 0.001203
1993M2 0.797703 1.426200 0.608643 0.008496 0.65643 0.013076 -0.05191714 -0.030591 0.05697
1993M3 0.795418 1.503500 0.619464 0.008595 0.66890 -0.00287 0.052782159 0.017622 0.011536
1993M4 0.787402 1.574500 0.632831 0.008997 0.70067 -0.01013 0.046142038 0.02135 0.045722
1993M5 0.786968 1.558700 0.627353 0.009390 0.70102 -0.00055 -0.01008562 -0.008695 0.042764
1993M6 0.779849 1.508700 0.592347 0.009368 0.66247 -0.00909 -0.03260379 -0.057416 -0.00237
1993M7 0.778756 1.480700 0.574812 0.009443 0.65595 -0.0014 -0.0187334 -0.03005 0.007994
1993M8 0.757117 1.497300 0.599413 0.009597 0.68166 -0.02818 0.011148537 0.041908 0.016183
1993M9 0.748615 1.510700 0.617322 0.009510 0.70547 -0.01129 0.008909633 0.029441 -0.00908
1993M10 0.756716 1.486700 0.596908 0.009242 0.67590 0.010763 -0.01601422 -0.033628 -0.02859
1993M11 0.748111 1.486500 0.584454 0.009179 0.67024 -0.01144 -0.00013454 -0.021086 -0.00691
1993M12 0.755287 1.481200 0.579274 0.008941 0.67590 0.009546 -0.00357179 -0.008902 -0.02627
1994M1 0.753409 1.498100 0.574251 0.009099 0.68329 -0.00249 0.011345068 -0.008709 0.017588
1994M2 0.739645 1.487000 0.583567 0.009602 0.69955 -0.01844 -0.00743697 0.016093 0.053739
1994M3 0.722700 1.482200 0.598086 0.009695 0.70922 -0.02318 -0.0032332 0.024576 0.009648
1994M4 0.723851 1.509500 0.600962 0.009756 0.70711 0.001591 0.018251 0.004796 0.006321
1994M5 0.722178 1.510200 0.609310 0.009572 0.71480 -0.00231 0.000463622 0.013797 -0.01904
1994M6 0.723432 1.540200 0.626802 0.010096 0.74377 0.001735 0.019670186 0.028303 0.053275
1994M7 0.722961 1.526300 0.626645 0.010025 0.73883 -0.00065 -0.00906577 -0.000251 -0.00704
1994M8 0.729341 1.534000 0.631712 0.010045 0.75019 0.008786 0.005032197 0.008053 0.002007
1994M9 0.745823 1.579700 0.645870 0.010157 0.77760 0.022347 0.029356253 0.022164 0.011111
1994M10 0.739372 1.622000 0.661376 0.010269 0.79283 -0.00869 0.026425 0.023724 0.010928
1994M11 0.727167 1.562500 0.636618 0.010109 0.75188 -0.01664 -0.03737285 -0.038152 -0.01569
1994M12 0.712860 1.562500 0.645661 0.010026 0.76249 -0.01987 0 0.014105 -0.00826
1995M1 0.709925 1.593000 0.662778 0.010147 0.78419 -0.00413 0.019331928 0.026166 0.012003
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
1995M2 0.717618 1.579000 0.684416 0.010304 0.80665 0.010778 -0.0088273 0.032125 0.015338
1995M3 0.714796 1.613000 0.722700 0.011192 0.87789 -0.00394 0.021304064 0.054429 0.082665
1995M4 0.735510 1.612500 0.724008 0.011940 0.87719 0.028567 -0.00031003 0.001808 0.064725
1995M5 0.730140 1.604000 0.720098 0.012019 0.87329 -0.00733 -0.00528526 -0.005415 0.006589
1995M6 0.728120 1.594000 0.722700 0.011820 0.86881 -0.00277 -0.00625393 0.003607 -0.01669
1995M7 0.730087 1.602500 0.724375 0.011308 0.87252 0.002698 0.005318329 0.002315 -0.04428
1995M8 0.744602 1.552500 0.681896 0.010091 0.83091 0.019686 -0.03169837 -0.060433 -0.11392
1995M9 0.744158 1.580000 0.704821 0.010173 0.87428 -0.0006 0.017558312 0.033067 0.008105
1995M10 0.746547 1.574600 0.707514 0.009833 0.87619 0.003205 -0.00342358 0.003813 -0.034
1995M11 0.736160 1.534500 0.696040 0.009847 0.85778 -0.01401 -0.02579668 -0.016351 0.001476
1995M12 0.732493 1.550000 0.697593 0.009725 0.86919 -0.00499 0.010050336 0.00223 -0.01253
1996M1 0.727643 1.506700 0.670331 0.009324 0.82136 -0.00664 -0.0283331 -0.039864 -0.04209
1996M2 0.728757 1.533000 0.681803 0.009551 0.83591 0.001529 0.017304771 0.016968 0.024063
1996M3 0.733568 1.525400 0.677645 0.009409 0.84019 0.00658 -0.00496993 -0.006117 -0.01498
1996M4 0.734538 1.508100 0.653851 0.009542 0.80743 0.001321 -0.01140609 -0.035743 0.014023
1996M5 0.729767 1.538300 0.651848 0.009242 0.79384 -0.00652 0.01982733 -0.003068 -0.03193
1996M6 0.732547 1.548500 0.657073 0.009139 0.79904 0.003802 0.00660881 0.007984 -0.01121
1996M7 0.727379 1.558000 0.680087 0.009266 0.83998 -0.00708 0.006116227 0.034425 0.013803
1996M8 0.730780 1.557200 0.675265 0.009222 0.833541719 0.004666 -0.00051361 -0.007116 -0.00481
1996M9 0.734107 1.562200 0.654965 0.009011 0.795608243 0.004541 0.003205747 -0.030524 -0.02306
1996M10 0.745823 1.628200 0.661113 0.008787 0.796241739 0.015835 0.041380026 0.009344 -0.02518
1996M11 0.741400 1.682500 0.651721 0.008790 0.77000077 -0.00595 0.032805672 -0.014309 0.000264
1996M12 0.730140 1.698000 0.643170 0.008621 0.742721331 -0.0153 0.009170305 -0.013207 -0.01941
1997M1 0.742225 1.602000 0.612145 0.008197 0.705318098 0.016416 -0.05819824 -0.049439 -0.05043
1997M2 0.731797 1.629800 0.591296 0.008280 0.677002234 -0.01415 0.017204459 -0.034652 0.01005
1997M3 0.722387 1.629300 0.596019 0.008061 0.689084895 -0.01294 -0.00030683 0.007955 -0.02671
1997M4 0.715820 1.629400 0.578905 0.007883 0.680272109 -0.00913 6.13742E-05 -0.029134 -0.02232
1997M5 0.724165 1.639300 0.588235 0.008587 0.708817692 0.011592 0.006057473 0.015989 0.085543
1997M6 0.724061 1.663300 0.573362 0.008741 0.685494927 -0.00014 0.014534259 -0.02561 0.017761
1997M7 0.723903 1.636400 0.545703 0.008457 0.660196739 -0.00022 -0.01630487 -0.049442 -0.0331
1997M8 0.720202 1.622800 0.557414 0.008379 0.674399784 -0.00513 -0.00834565 0.021233 -0.00926
1997M9 0.723589 1.613200 0.566412 0.008264 0.688705234 0.004692 -0.00593327 0.016014 -0.01373
1997M10 0.709622 1.673300 0.580383 0.008337 0.714643036 -0.01949 0.036577941 0.024367 0.008716
1997M11 0.702149 1.676900 0.566990 0.007840 0.701213099 -0.01059 0.002149126 -0.023347 -0.06143
1997M12 0.699741 1.653800 0.558005 0.007695 0.687143544 -0.00343 -0.01387118 -0.015974 -0.01864
1998M1 0.686672 1.638200 0.547495 0.007880 0.679393981 -0.01885 -0.00947759 -0.019013 0.02375
1998M2 0.702494 1.644400 0.552120 0.007859 0.681245316 0.02278 0.003777498 0.008412 -0.00275
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
1998M3 0.705916 1.680100 0.541477 0.007573 0.657418973 0.004859 0.021477739 -0.019465 -0.03703
1998M4 0.699203 1.670600 0.557165 0.007559 0.66728947 -0.00955 -0.00567047 0.028561 -0.00189
1998M5 0.686295 1.630200 0.561073 0.007192 0.674991563 -0.01863 -0.02448014 0.006989 -0.04976
1998M6 0.679532 1.662700 0.552883 0.007100 0.656469507 -0.0099 0.01974008 -0.014704 -0.01286
1998M7 0.661770 1.637800 0.561956 0.006959 0.669523299 -0.02649 -0.01508891 0.016276 -0.02003
1998M8 0.636051 1.652100 0.564239 0.007069 0.686153424 -0.03964 0.008693328 0.004054 0.015711
1998M9 0.655351 1.695500 0.596694 0.007394 0.720824623 0.029892 0.025930477 0.055928 0.044892
1998M10 0.648298 1.677300 0.605767 0.008591 0.742997251 -0.01082 -0.01079232 0.01509 0.150092
1998M11 0.656297 1.652100 0.587544 0.008087 0.711541198 0.012263 -0.01513815 -0.030544 -0.06042
1998M12 0.653381 1.663500 0.597729 0.008651 0.726480203 -0.00445 0.006876611 0.017186 0.067319
1999M1 0.663394 1.647400 0.582055 0.008606 0.705517144 0.015208 -0.00972553 -0.026573 -0.00518
1999M2 0.663394 1.602100 0.563341 0.008375 0.692856648 0 -0.02788302 -0.032679 -0.02717
1999M3 0.662603 1.611500 0.549230 0.008306 0.672404519 -0.00119 0.005850154 -0.025368 -0.00834
1999M4 0.686059 1.612500 0.541816 0.008380 0.657462196 0.034788 0.000620347 -0.01359 0.008927
1999M5 0.677048 1.604200 0.534607 0.008236 0.656081879 -0.01322 -0.00516058 -0.013396 -0.01736
1999M6 0.679348 1.574900 0.528062 0.008258 0.644122383 0.003391 -0.01843341 -0.012317 0.002639
1999M7 0.663878 1.619000 0.546775 0.008681 0.668762121 -0.02303 0.027616896 0.034824 0.049947
1999M8 0.668539 1.603700 0.540589 0.009024 0.658804928 0.006995 -0.00949522 -0.011379 0.038762
1999M9 0.680272 1.646500 0.545293 0.009359 0.667779633 0.017399 0.026338363 0.008663 0.036481
1999M10 0.679810 1.634000 0.534300 0.009537 0.652315721 -0.00068 -0.00762083 -0.020365 0.018895
1999M11 0.678979 1.594500 0.516252 0.009756 0.628140704 -0.00122 -0.02447079 -0.034363 0.022668
1999M12 0.692857 1.616400 0.513644 0.009785 0.625156289 0.020233 0.013641247 -0.005064 0.002931
2000M1 0.689085 1.623700 0.500604 0.009359 0.609384522 -0.00546 0.004506041 -0.025715 -0.04449
2000M2 0.690226 1.592700 0.496670 0.009076 0.606501698 0.001655 -0.01927681 -0.00789 -0.03069
2000M3 0.687994 1.595100 0.488438 0.009447 0.599232982 -0.00324 0.001505741 -0.016713 0.040092
2000M4 0.675174 1.568100 0.464507 0.009385 0.577133953 -0.01881 -0.01707173 -0.050236 -0.00659
Count 124.00 124.00 124.00 124.00 124.00
Data and Calculations Count 123 123 123 123
Box 16.5 on page 634 Average Return -0.00181 -0.00057394 -0.002004 0.002457
Std Dev of Return 0.013142 0.028498349 0.02969 0.034668
$$ Position 60,000 (20,000) 25,000 25,000
% Weights 60.00% -20.00% 25.00% 25.00%
Correlation Matrix Canada United Kingdom Germany Japan
Canada 1 0.090725661 -0.027327 -0.06446
United Kingdom 0.090726 1 0.711286 0.323539
Germany -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 -0.06446 0.323539209 0.47462 1
Switzerland -0.0717 0.654762046 0.932926 0.512417
Probability => 0.95 Gross VAR 1,297.05 937.51 1,220.88 1,425.59
Z-value => 1.64
Net VAR Canada United Kingdom Germany Japan
Canada 6.22E-05 -4.0776E-06 -1.6E-06 -4.4E-06
United Kingdom -4.1E-06 3.24862E-05 -3.01E-05 -1.6E-05
Germany -1.6E-06 -3.0091E-05 5.51E-05 3.05E-05
Japan -4.4E-06 -1.5982E-05 3.05E-05 7.51E-05
Switzerland -1.9E-06 -1.2381E-05 2.3E-05 1.47E-05
Formula 16.13 =====> Portfolio
Portfolio
Net VAR
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
Calculation of Gross and Net VAR (Box 16.3)
Returns are ln(St/St-1) over 123 months, January 1990 - April 2000
-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
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
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
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
-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
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
Calculation of Gross and Net VAR (Box 16.3)
Returns are ln(St/St-1) over 123 months, January 1990 - April 2000
0.512417
1
545.67 5,426.71
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