第08章有无卖空限制下的有效边界(德意志银行Excel金融工程建模)

1、第9章 有无卖空限制下的有效边界模板 a.a. 7 7国国股股票票的的预预期期收收益益率率、标标准准差差和和相相关关系系数数 相关系数矩阵 国别预期收益率（%）标准差（%）美国德国英国日本 美国19.824.210.390.540.23 德国22.123.50.3910.450.37 英国19.224.80.540.4510.39 日本15.321.20.230.370.391 澳大利亚17.127.10.40.30.520.27 加拿大14.9240.70.370.550.28 法国19.426.50.410.620.510.41 b.b.协协方方差差矩矩阵阵 国别美国德国英国日本澳大利亚

2、加拿大法国 美国585.64221.793 324.0864 117.9992262.328406.56262.933 德国221.793552.25262.26184.334191.055208.68386.105 英国324.0864262.26615.04 205.0464 349.4816327.36335.172 日本117.9992184.334 205.0464449.44 155.1204142.464230.338 澳大利亚262.328191.055 349.4816 155.1204734.41377.232 251.3525 加拿大406.56208.68327.361

3、42.464377.232576260.76 法国262.933386.105335.172230.338 251.3525260.76702.25 c.c.等等权权重重组组合合的的协协方方差差矩矩阵阵 国别美国德国英国日本澳大利亚 加拿大 权重0.142857143 0.142857 0.142857 0.142857 0.142857 0.142857 美国0.1428571411.95183673 4.526388 6.614008 2.408147 5.353633 8.297143 德国0.142857144.526387755 11.27041 5.352245 3.761918

4、3.899082 4.258776 英国0.142857146.614008163 5.352245 12.551844.18462 7.132278 6.680816 日本0.142857142.408146939 3.7619184.18462 9.172245 3.165722 2.907429 澳大利亚0.142857145.353632653 3.899082 7.132278 3.165722 14.98796 7.698612 加拿大0.142857148.297142857 4.258776 6.680816 2.907429 7.69861211.7551 法国0.14285

5、7145.365979592 7.879694 6.840245 4.700776 5.129643 5.321633 小计144.51713469 40.94851 49.35605 30.30086 47.36693 46.91951 组合方差308.978592 组合标准差17.5777869 组合预期收益率 18.2571429 d.d.目目标标预预期期收收益益率率等等于于18.26%18.26%时时有有效效边边界界组组合合的的协协方方差差矩矩阵阵（用用solversolver来来求求权权重重） 国别美国德国英国日本澳大利亚 加拿大 权重0.22587657 0.248891 0.02

6、4775 0.367611 0.122619 0.024038 美国0.2258765729.87948442 12.46892 1.813618 9.798036 7.2656462.20747 德国0.2488913512.46891945 34.210181.61717 16.865695.83078 1.248503 英国0.024775011.8136181471.61717 0.377512 1.867475 1.061687 0.194957 日本0.367611259.798036109 16.86569 1.867475 60.73644 6.992237 1.258905

7、澳大利亚0.122619197.2656464975.83078 1.061687 6.99223711.0422 1.111901 加拿大0.024038042.207470489 1.248503 0.194957 1.258905 1.111901 0.332829 法国-0.0138114-0.820264836 -1.32725 -0.11469 -1.16948 -0.42568 -0.08657 小计162.61291027 70.91399 6.81773196.3493 32.87877 6.267993 组合方差272.030716 组合标准差16.4933537 组合预期

8、收益率 18.2600008 e.e.允允许许卖卖空空的的有有效效边边界界和和不不允允许许卖卖空空的的有有效效边边界界 允许卖空的有效边界 各国在有效组合中的权重 预期收益率（%）标准差美国德国英国日本澳大利亚 加拿大 928.3-0.436564447 -0.48467 -0.20506 0.964696 0.019894 1.101504 1026.21-0.365026367 -0.40545 -0.18024 0.900216 0.030988 0.985147 1124.22-0.293488635 -0.32623 -0.15542 0.835736 0.0420810.86879

9、 1222.36-0.221950718 -0.24702-0.1306 0.771256 0.053175 0.752433 1320.67-0.150412802-0.1678 -0.10578 0.706776 0.064268 0.636076 1419.19-0.078874914 -0.08858 -0.08096 0.642296 0.075361 0.519719 1517.98-0.007337003 -0.00936 -0.05614 0.577816 0.086455 0.403362 1617.080.064200804 0.069858 -0.03132 0.5133

10、36 0.097548 0.287005 1716.570.135738708 0.149076-0.0065 0.448856 0.108641 0.170648 1816.450.207276618 0.228295 0.018322 0.384376 0.119735 0.054291 1916.750.278814484 0.307513 0.043142 0.319896 0.130828 -0.06207 2017.450.350352398 0.386731 0.067962 0.255416 0.141922 -0.17842 2118.50.4218903280.46595

11、0.092782 0.190936 0.153015 -0.29478 2219.840.493428173 0.545168 0.117602 0.126456 0.164109 -0.41114 2321.420.56496612 0.624386 0.142423 0.061976 0.175202 -0.52749 2423.190.636503962 0.703604 0.167243-0.0025 0.186295 -0.64385 2525.110.708041906 0.782823 0.192063 -0.06698 0.197389 -0.76021 2627.150.77

12、957982 0.862041 0.216883 -0.13146 0.208482 -0.87656 不允许卖空的有效边界 各国在有效组合中的权重 预期收益率（%）标准差美国德国英国日本澳大利亚 加拿大 13 不可行不可行不可行不可行不可行不可行不可行 14.924000001 1520.140000.2500.75 1617.10.05319802 0.0609370 0.509073 0.089523 0.287269 1716.570.132748424 0.1439730 0.446621 0.106489 0.170168 1816.460.206701296 0.222617 0

13、.016025 0.382018 0.119367 0.053272 1916.810.249468091 0.328809 0.040558 0.279324 0.1018410 2017.840.268352916 0.459865 0.065763 0.145544 0.0604750 2119.480.287237770.59092 0.090968 0.0117640.019110 2222.910.043478242 0.9565220000 22.123.5010000 26 不可行不可行不可行不可行不可行不可行不可行 允许和不允许卖空下的有效边界 302520151050 0

14、5 10 15 20 25 30 标准差 预期收益率 允许卖空的 有效边界 不允许卖空 的有效边界 美国 德国 英国 日本 澳大利亚 加拿大 法国 等权重组合 澳大利亚 加拿大法国 0.40.70.41 0.30.370.62 0.520.550.51 0.270.280.41 10.580.35 0.5810.41 0.350.411 法国 0.142857 5.36598 7.879694 6.840245 4.700776 5.129643 5.321633 14.33163 49.5696 法国 -0.01381 -0.82026 -1.32725 -0.11469 -1.16948

15、-0.42568 -0.08657 d.d.目目标标预预期期收收益益率率等等于于18.26%18.26%时时有有效效边边界界组组合合的的协协方方差差矩矩阵阵（用用solversolver来来求求权权重重） 0.133958 -3.80997 法国小计 0.04021 0.0343681 0.0285351 0.0227021 0.0168691 0.0110361 0.0052041 -0.000631 -0.006461 -0.012291 -0.018131 -0.023961 -0.029791 -0.035631 -0.041461 -0.047291 -0.053121 -0.058

16、961 法国小计 不可行不可行 01 01 01 01 01 01 01 01 01 01 不可行不可行 允许和不允许卖空下的有效边界 302520151050 0 5 10 15 20 25 30 标准差 预期收益率 允许卖空的 有效边界 不允许卖空 的有效边界 美国 德国 英国 日本 澳大利亚 加拿大 法国 等权重组合 1992年4月10日至2001年8月14日上海证交所8只股票的每日收盘价 收收 盘盘 价价 交交易易日日期期 6006016006016006026006026006036006036006046006046006516006516006526006526006536006

17、53600654600654 199204101.884.171.722.020.930.430.564.33 199204132.014.381.722.020.980.430.564.47 199204141.994.61.722.021.030.440.574.85 199204151.924.831.722.021.080.440.584.93 199204161.825.081.722.041.130.450.584.8 199204171.775.331.722.041.070.450.594.58 199204201.465.591.722.041.020.450.594.13

18、199204211.355.561.722.060.970.450.64.13 199204221.275.291.722.060.920.450.64.13 199204231.285.541.742.080.880.450.614.13 199204241.395.761.742.10.830.440.614.12 199204271.436.051.742.120.870.440.624.11 199204281.516.351.742.120.910.440.634.06 199204291.46.631.742.140.870.450.634.01 199204301.426.71.

19、742.160.870.450.643.86 199205041.426.961.742.160.910.450.653.83 199205051.356.751.742.270.90.480.683.67 199205061.346.741.822.270.890.50.713.54 199205071.366.751.912.380.890.530.753.47 199205081.396.962.012.50.90.550.783.5 199205111.487.32.112.630.950.580.823.98 199205121.427.672.222.760.940.60.873.

20、91 199205131.397.732.332.90.90.580.913.9 199205141.387.772.443.050.920.550.953.91 199205151.418.162.563.20.920.5814.01 199205181.468.572.693.360.970.611.054.26 199205191.6192.823.531.020.641.14.55 199205201.599.192.973.70.980.611.164.65 199205211.5710.235.9315.261.020.631.544.43 199205221.710.56.191

21、6.141.040.631.994.98 199205252.0111.217.4216.661.160.712.096.01 199205261.8410.396.9415.361.060.741.892.92 199205272.019.346.6115.361.020.741.825.32 199205281.89.256.0813.20.970.711.75.1 199205291.879.916.5614.161.020.721.655.29 199206011.89.746.4413.210.691.115.15 199206021.769.586.1712.550.990.71.

22、065.14 199206031.638.855.9811.720.910.650.974.77 199206041.079.096.1712.50.950.681.013.21 199206051.079.166.2812.520.980.671.043.11 199206081.019.236.2512.390.950.671.042.86 199206091.059.526.5412.340.970.691.093.03 199206101.099.656.7213.460.950.711.123.08 199206111.079.76.7113.430.970.731.153.01 1

23、99206121.079.656.6613.430.970.781.152.97 199206151.079.586.7713.381.020.891.22.99 199206171.059.386.8513.591.060.831.212.96 199206181.069.366.8313.661.050.851.212.98 199206191.079.436.7913.571.10.911.232.99 199206221.089.456.8313.541.120.891.163.02 199206231.089.366.7913.381.10.891.142.99 199206241.

24、079.266.7513.241.120.91.162.96 199206251.079.346.7913.331.140.921.22.96 199206261.079.336.8513.361.210.961.223.02 199206291.199.36.8613.281.391.061.233.1 199206301.249.356.9513.281.421.11.243.34 199207011.199.58713.431.451.091.223.26 199207021.189.716.9613.281.561.141.243.27 199207031.219.71713.111.

25、581.181.283.4 199207061.229.927.9113.161.461.141.653.5 199207071.239.88.2613.31.511.21.763.49 199207081.239.837.8813.071.461.181.683.34 199207091.219.757.5813.231.461.191.613.19 199207101.229.577.9113.481.481.191.693.21 199207131.188.947.8513.031.491.21.733.06 199207141.148.697.7712.651.51.291.732.9

26、9 199207151.168.458.0612.451.581.411.772.99 199207161.168.948.0612.521.641.411.753.01 199207171.168.868.112.551.631.431.773.02 199207201.158.58.0212.411.61.421.792.89 199207211.178.447.9112.031.61.411.772.86 199207221.137.917.7511.451.551.41.72.88 199207231.097.597.5611.481.51.341.662.68 199207241.1

27、37.737.6211.591.531.361.682.77 199207271.168.4812.391.61.391.732.87 199207281.148.067.8312.161.561.371.692.93 199207291.148.047.8111.981.571.361.692.98 199207301.138.017.7711.721.571.361.672.98 199207311.147.987.7911.821.561.371.682.9 199208031.127.987.7111.451.541.341.652.91 199208041.137.917.7311.

28、661.551.331.652.91 199208051.117.987.6211.31.541.321.632.85 199208061.17.897.511.281.531.311.612.82 199208071.097.887.411.221.51.281.572.83 199208101.027.536.7210.521.281.141.42.67 199208110.96.725.888.891.1611.252.33 199208120.835.665.397.761.060.961.152.11 199208130.886.195.778.71.1411.222.26 1992

29、08140.896.216.19.031.171.021.242.37 199208170.986.936.759.681.311.141.372.63 199208180.937.366.469.221.271.071.332.52 199208190.977.256.569.531.281.111.362.54 199208200.957.36.549.291.281.11.342.52 199208210.947.336.369.081.231.11.322.5 199208240.97.196.118.771.191.081.262.35 199208250.876.435.938.2

30、71.161.021.242.3 199208260.96.636.158.591.21.041.272.43 199208270.896.576.028.331.171.031.242.35 199208280.896.415.948.281.161.021.232.31 199208310.846.115.868.131.1211.212.23 199209010.856.15.898.171.121.011.212.25 199209020.815.815.667.811.070.971.162.13 199209030.775.655.57.651.060.951.152.07 199

31、209040.785.645.47.641.030.951.132.05 199209070.725.474.957.130.930.871.031.85 199209080.654.674.446.430.890.770.941.71 199209090.664.64.396.390.90.760.951.7 199209100.735.294.8270.970.861.041.89 199209110.85.675.347.551.070.931.132.11 199209140.755.45.037.150.950.851.071.8 199209150.745.295.057.190.

32、980.871.11.92 199209160.785.585.277.471.040.931.172.01 199209170.785.585.267.341.040.931.162.01 199209180.775.675.237.261.020.931.161.97 199209210.755.175.17.080.990.911.131.92 199209220.744.995.026.990.990.891.111.88 199209230.734.774.926.830.960.871.091.85 199209240.754.895.087.0410.91.111.93 1992

33、09250.764.915.17.0710.91.131.93 199209280.754.855.0470.990.891.121.91 199209290.734.714.976.840.970.871.11.87 199209300.734.644.96.760.960.861.091.85 199210050.714.374.716.50.930.811.061.76 199210060.714.214.996.460.940.821.041.76 199210070.694.114.96.410.940.811.021.74 199210080.683.844.786.220.90.

34、7911.7 199210090.73.994.916.410.940.821.041.75 199210120.724.285.226.981.030.881.111.81 199210130.694.195.096.8210.841.071.74 199210140.683.995.096.871.010.831.051.71 199210150.693.955.16.881.030.831.071.72 199210160.693.955.136.891.040.831.071.73 199210190.653.654.996.5610.81.031.65 199210200.623.5

35、14.926.4610.81.021.61 199210210.593.274.826.290.970.770.981.51 199210220.522.994.5360.930.70.91.44 199210230.553.044.76.230.960.710.921.51 199210260.492.644.055.590.850.620.821.37 199210270.452.153.44.730.730.510.711.26 199210280.452.233.474.790.740.520.721.28 199210290.472.323.615.080.810.560.761.3

36、4 199210300.492.473.885.340.890.610.811.38 199211020.462.283.544.950.820.580.751.27 199211030.462.223.524.840.810.570.741.26 199211040.452.183.464.740.790.550.731.24 199211050.452.173.44.670.770.540.731.23 199211060.442.193.384.610.760.540.731.21 199211090.422.123.14.110.730.510.691.12 199211100.422

37、.022.843.690.660.470.621.11 199211110.422.12.853.770.670.470.621.11 199211120.422.162.913.830.690.490.641.13 199211130.422.182.923.810.690.50.641.12 199211160.422.112.83.630.660.470.621.1 199211170.412.082.723.510.650.470.611.08 199211180.412.162.83.640.670.490.621.09 199211190.42.112.743.540.660.49

38、0.611.07 199211200.42.122.743.60.660.490.621.07 199211230.412.282.83.70.690.520.651.09 199211240.442.613.054.010.740.580.721.19 199211250.593.533.955.470.910.670.881.54 199211260.553.843.825.620.860.740.91.51 199211270.644.314.456.090.950.80.981.67 199211300.735.424.737.291.040.871.11.95 199212010.7

39、15.274.657.191.020.831.051.91 199212020.715.144.787.131.020.821.031.92 199212030.715.674.927.911.050.871.081.98 199212040.85.345.017.911.10.911.112.22 199212070.835.454.978.071.090.91.12.24 199212080.795.224.737.551.030.861.062.15 199212090.694.514.246.770.920.790.951.96 199212100.664.243.96.30.890.

40、760.921.85 199212110.784.794.537.031.020.861.112.17 199212140.754.654.537.020.990.871.092.07 199212150.724.474.526.720.970.811.062.04 199212160.724.514.436.770.960.811.052.03 199212170.764.74.517.030.990.881.082.18 199212180.754.594.436.820.990.891.062.1 199212210.84.984.547.2310.941.092.13 19921222

41、0.794.894.537.1910.941.092.08 199212230.84.974.547.291.010.951.092.08 199212240.825.344.667.691.050.971.122.12 199212250.815.344.87.761.061.011.152.09 199212280.85.234.717.771.0611.22.06 199212290.815.314.977.941.111.011.212.07 199212300.815.244.997.951.120.991.192.06 199212310.825.254.977.971.11.01

42、1.192.07 199301040.835.35.118.251.121.021.212.11 199301050.885.455.38.681.151.041.242.19 199301060.875.415.218.511.131.021.222.2 199301070.95.585.238.91.151.041.252.33 199301080.95.535.569.191.261.121.322.33 199301110.946.055.7310.311.31.131.342.44 199301120.956.045.7810.821.31.151.352.45 199301130.

43、996.215.8111.981.31.151.372.5 199301141.036.76.1112.441.341.181.462.68 199301151.026.615.8811.981.381.171.472.6 199301181.036.556.1311.861.421.21.512.57 199301190.916.125.8211.251.351.151.452.39 199301201.016.39611.821.391.181.52.52 199301211.036.596.1312.111.41.211.542.61 199301221.046.76.3612.291.

44、451.271.62.64 199301271.077.236.9413.171.611.361.682.74 199301281.057.416.8312.991.581.341.652.68 199301291.128.097.0313.431.621.371.682.91 199302011.188.357.3514.631.691.421.753.12 199302021.218.767.3714.831.691.441.773.3 199302031.38.947.29151.681.451.763.46 199302041.429.577.8915.571.791.661.933.

45、74 199302051.379.277.7714.841.781.771.913.58 199302081.419.498.1815.471.91.942.093.63 199302091.489.968.7716.32.112.282.333.8 199302101.439.478.615.722.022.182.263.78 199302111.369.038.3314.861.912.052.163.56 199302121.49.478.5215.352.062.172.333.7 199302151.449.658.8115.722.132.162.513.74 199302161

46、.429.368.6615.422.072.12.423.65 199302171.399.118.8515.262.092.082.413.59 199302181.419.298.716.042.052.082.373.63 199302191.439.658.6216.142.0622.323.69 199302221.389.238.2915.261.951.982.263.56 199302231.318.917.6714.251.841.882.173.34 199302241.188.186.9213.021.711.732.043.06 199302251.328.947.98

47、14.371.941.962.273.34 199302261.248.487.6313.461.831.862.163.12 199303011.117.226.911.921.621.651.962.74 199303021.127.427.1512.121.691.732.022.79 199303031.117.397.1111.821.661.692.012.73 199303041.026.796.7310.881.561.61.912.52 199303051.016.76.3810.671.511.621.922.5 199303081.117.287.1311.31.852.

48、182.382.76 199303091.016.677.6410.471.892.082.212.53 199303101.087.067.8711.031.972.22.442.65 199303111.137.558.1811.912.042.342.592.82 199303121.097.518.0711.662.042.342.562.74 199303151.076.887.4810.931.851.221.642.53 199303161.137.297.8511.381.891.281.762.7 199303171.127.157.7310.981.851.231.712.

49、63 199303181.097.047.6710.731.791.211.652.63 199303191.096.977.5710.611.691.141.612.61 199303220.895.416.549.111.050.981.42.24 199303230.854.995.988.610.980.931.352.05 199303240.844.785.788.60.960.921.351.97 199303250.854.675.788.790.920.91.381.98 199303260.955.216.279.671.020.981.552.2 199303290.87

50、3.755.398.850.960.931.452.01 199303300.873.715.138.770.930.881.42 199303310.863.74.678.490.890.881.351.93 199304010.893.784.868.80.930.911.411.99 199304020.893.764.738.830.920.91.412 199304050.913.843.729.020.990.921.442.02 199304061.074.524.4410.471.161.071.612.28 199304071.114.754.7611.041.351.161

51、.72.39 199304081.295.235.2612.341.471.291.862.85 199304091.235.155.0912.081.471.31.862.75 199304121.164.854.9111.451.361.251.82.57 199304131.275.25.3612.481.461.351.942.79 199304141.25.035.1912.181.41.321.912.66 199304151.2755.112.241.391.291.892.75 199304161.214.684.8511.451.341.211.782.63 19930419

52、1.254.774.9911.721.361.231.832.67 199304201.264.975.0311.851.371.251.862.74 199304211.234.894.9211.761.351.241.842.73 199304221.264.995.1212.231.371.281.922.81 199304231.245.015.0312.031.391.291.922.92 199304261.285.235.8912.641.441.3423.04 199304271.375.936.2713.121.51.4523.12 199304281.335.86.2413

53、.381.491.4223.29 199304291.285.566.0713.031.451.391.953.15 199304301.335.646.4212.981.461.381.953.21 199305031.295.566.5913.021.671.452.053.21 199305041.175.055.9811.891.531.311.893.21 199305051.184.955.9911.751.551.331.93.21 199305061.14.555.5110.781.441.221.783.21 199305071.144.845.7711.31.551.261

54、.823.21 199305101.074.65.3910.571.481.171.692.65 199305111.094.655.6210.691.511.191.712.73 199305121.14.645.5910.991.491.21.72.7 199305131.134.755.7212.131.521.231.742.79 199305141.114.75.5611.641.481.221.722.73 199305171.14.625.4111.091.431.21.672.34 199305181.094.65.3411.171.421.181.672.3 19930519

55、1.094.615.3611.381.481.191.682.32 199305201.114.655.5411.591.531.241.762.33 199305211.084.565.411.351.51.21.732.25 1993052414.084.7910.271.341.071.562.1 199305250.9844.669.891.31.051.542.07 199305260.964.044.599.871.261.031.512.05 199305270.943.974.499.471.210.991.482 199305280.954.014.579.591.211.4

56、82.02 199305310.933.954.419.351.130.971.431.92 199306010.963.924.489.371.191.061.552 199306021.094.55.2610.671.361.21.722.43 199306030.994.054.799.631.261.11.572.22 199306040.974.034.6410.111.241.071.542.09 199306071.034.234.910.31.351.151.622.31 199306081.054.24.8910.411.361.151.612.46 199306091.05

57、4.294.8510.311.321.131.612.4 199306101.024.134.7310.211.271.11.552.31 199306111.014.134.7210.281.271.091.552.28 199306140.963.934.469.891.221.051.52.2 199306150.973.954.479.871.211.031.492.21 199306160.9844.499.941.211.041.492.21 199306170.9844.519.911.21.031.482.21 199306180.983.954.519.891.21.031.

58、492.21 199306210.963.924.379.331.171.021.452.14 199306220.963.944.49.271.171.021.462.15 199306230.973.944.429.231.171.031.472.16 199306240.963.894.378.991.151.021.462.12 199306250.983.954.419.161.181.031.482.17 199306281.023.994.449.451.211.051.542.27 199306291.054.094.519.741.231.071.572.38 1993063

59、01.033.984.49.611.211.051.552.3 199307011.044.014.399.61.211.041.552.32 199307021.033.954.349.421.181.041.542.27 1993070513.884.239.231.150.991.492.19 199307060.963.644.058.721.090.941.412.1 199307070.973.674.038.691.090.941.412.14 199307080.973.694.018.651.090.941.422.14 199307090.943.573.828.321.0

60、50.931.382.07 199307120.923.53.688.071.020.911.362.06 199307130.953.563.768.211.040.921.362.1 199307140.923.463.587.950.980.881.252.02 199307150.923.493.617.940.990.91.242.03 199307160.923.493.597.880.980.881.222.03 199307190.93.473.517.690.950.881.162 199307200.873.373.357.440.930.861.091.89 199307