A B C D E F G H I J K

1 Table 7A: Spreadsheet Model for International Diversification

2
3 A.1 Country Index Statistics and Forecasts or Risk Premiums
4
Correlation with the
Standard Deviation U.S. Averages Forecast
5 Country 1991-2000 2001-2005 1991-2000 2001-2005 1991-2000 2001-2005 2006
6 US 0.1295 0.1495 1 1 0.1108 -0.0148 0.0600
7 UK 0.1466 0.1493 0.64 0.83 0.0536 0.0094 0.0530
8 France 0.1741 0.2008 0.54 0.83 0.0837 0.0247 0.0700
9 Germany 0.1538 0.2270 0.53 0.85 0.0473 0.0209 0.0800
10 Australia 0.1808 0.1617 0.52 0.81 0.0468 0.1225 0.0580
11 Japan 0.2432 0.1878 0.41 0.43 -0.0177 0.0398 0.0450
12 Canada 0.1687 0.1727 0.72 0.79 0.0727 0.1009 0.0590
13
14 A.2 The Bordered Covariance Matrix
15

16 Portfolio
Weights 1.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000
17 US UK France Germany Australia Japan Canada
18 1.0000 US 0.0224 0.0184 0.0250 0.0288 0.0195 0.0121 0.0205
19 0.0000 UK 0.0184 0.0223 0.0275 0.0299 0.0204 0.0124 0.0206
20 0.0000 France 0.0250 0.0275 0.0403 0.0438 0.0259 0.0177 0.0273
21 0.0000 Germany 0.0288 0.0299 0.0438 0.0515 0.0301 0.0183 0.0305
22 0.0000 Australia 0.0195 0.0204 0.0259 0.0301 0.0261 0.0147 0.0234
23 0.0000 Japan 0.0121 0.0124 0.0177 0.0183 0.0147 0.0353 0.0158
24 0.0000 Canada 0.0205 0.0206 0.0273 0.0305 0.0234 0.0158 0.0298
25 1.0000 0.0224 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000
26 0.0600 Mean
27 0.1495 SD
28 0.4013 Slope
29
30 Cell A18 - A24 A18 is set arbitrarily to 1 while A19 to A24 are set to 0
31 Formula in cell C16 =A18 … Formula in cell I16 =A24
32 Formula in cell A25 =SUM(A18:A24)
33 Formula in cell C25 =C16*SUMPRODUCT($A$18:$A$24,C18:C24)
34 Formula in cell D25-I2 Copied from C25 (note the absolute addresses)
35 Formula in cell A26 =SUMPRODUCT($A$18:$A$24,H6:H12)
36 Formula in cell A27 =SUM(C25:I25)^0.5
37 Formula in cell A28 =A26/A27
38
39 A.3 The Efficient Frontier
40
41 Cell to store constraint on risk premium 0.0400
42
43 Min Var Optimum
44 Mean 0.0383 0.0400 0.0450 0.0500 0.0550 0.0564 0.0575 0.0600 0.0700
45 SD 0.1 0.1132 0.1135 0.1168 0.1238 0.1340 0.1374 0.1401 0.1466 0.1771
46 Slope 0.3386 0.3525 0.3853 0.4037 0.4104 0.4107 0.4106 0.4092 0.3953
47 US 0.6112 0.6195 0.6446 0.6696 0.6947 0.7018 0.7073 0.7198 0.7699
48 UK 0.8778 0.8083 0.5992 0.3900 0.1809 0.1214 0.0758 -0.0283 -0.4465
 A B C D E F G H I J K
49 France -0.2140 -0.2029 -0.1693 -0.1357 -0.1021 -0.0926 -0.0852 -0.0685 -0.0014
50 Germany -0.5097 -0.4610 -0.3144 -0.1679 -0.0213 0.0205 0.0524 0.1253 0.4185
51 Australia 0.0695 0.0748 0.0907 0.1067 0.1226 0.1271 0.1306 0.1385 0.1704
52 Japan 0.2055 0.1987 0.1781 0.1575 0.1369 0.1311 0.1266 0.1164 0.0752
53 Canada -0.0402 -0.0374 -0.0288 -0.0203 -0.0118 -0.0093 -0.0075 -0.0032 0.0139
54 CAL* 0.0411 0.0465 0.0466 0.0480 0.0509 0.0550 0.0564 0.0575 0.0602 0.0727
55 *Risk premium on CAL = SD * slope
56
 A B C D E F G H I J K L M
1 Table 7.B1: Scenario Analysis for Bonds Table 7.B2 Scenario Analysis for Bonds and Stocks
2 Scenario rates of return Scenario rates of return Portfolio return
3 Scenario Probability rD(i) rD(i)+0.03 0.4*rD(i) Scenario Probability rD(i) rE(i) 0.4*rD(i)+0.6*rE(i)
4 1 0.14 -0.10 -0.07 -0.040 1 0.14 -0.10 -0.35 -0.2500
5 2 0.36 0.00 0.03 0.000 2 0.36 0.00 0.20 0.1200
6 3 0.30 0.10 0.13 0.040 3 0.30 0.10 0.45 0.3100
7 4 0.20 0.32 0.35 0.128 4 0.20 0.32 -0.19 0.0140
8 Mean 0.080 0.110 0.032 Mean 0.08 0.12 0.1040
9 Cell C8 =SUMPRODUCT($B$4:$B$7,C4:C7) Cell M4 =0.4*J4+0.6*K4
10 Cell M8 =SUMPRODUCT($I$4:$I$7,L4:L7)
11
12
13 Table 7.B3: Scenario Analysis for Bonds Table 7.B4 Scenario Analysis for Bonds and Stocks
14 Scenario rates of return Scenario rates of return
15 Scenario Probability rD(i) rD(i)+0.03 0.4*rD(i) Scenario Probability rD(i) rE(i)
16 1 0.14 -0.10 -0.07 -0.040 1 0.14 -0.10 -0.35
17 2 0.36 0.00 0.03 0.000 2 0.36 0.00 0.20
18 3 0.30 0.10 0.13 0.040 3 0.30 0.10 0.45
19 4 0.20 0.32 0.35 0.128 4 0.20 0.32 -0.19
20 Mean 0.0800 0.1100 0.0240 Mean 0.08 0.12
21 Variance 0.0185 0.0185 0.0034 SD 0.1359 0.2918
22 SD 0.1359 0.1359 0.0584 Covariance -0.0034
23 Cell C21 =SUMPRODUCT($B$16:$B$19,C16:C19,C16:C19)-C20^2 Correlation -0.0847
24 Cell C22 =C21^0.5 Cell I22 =SUMPRODUCT(I16:I19,J16:J19,K16:K19)-J20*K20
25 Cell I23 =J22/(J21*K21)
26
27 Table 7.B5 Scenario Analysis for Bonds and Stocks
28 Scenario rates of retur Portfolio return
29 Scenario Probability rD(i) rE(i) 0.4*rD(i)+0.6rE(i)
30 1 0.14 -0.10 -0.35 -0.25
31 2 0.36 0.00 0.20 0.12
32 3 0.30 0.10 0.45 0.31
33 4 0.20 0.32 -0.19 0.014
34 Mean 0.08 0.12 0.1040
35 SD 0.1359 0.2918 0.1788
36 Covariance -0.0034 0.1788
37 Correlation -0.0847
38 Cell E35 =(SUMPRODUCT(B30:B33,E30:E33,E30:E33)-E34^2)^0.5
39 Cell E36 =((0.4*C35)^2+(0.6*D35)^2+2*0.4*0.6*C36)^0.5