证券投资 EXCEL工具 第12章 有无卖空限制下的有效边界

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.41B.B.协协方方差差矩矩阵阵国别美国德国英国日本澳大利亚 加拿大法国美国585.6

2、4221.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.36142.464377.23257626

3、0.76法国262.933386.105335.172230.338 251.3525260.76702.25C.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 3.899082 4.258776英国0.142

4、857146.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.142857145.365979592 7.879694 6.840

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

6、258765729.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澳大利亚0.122619197.2656464975.83078 1.061687

7、 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组合预期收益率 18.2600008E.E.允允许许卖卖空空的的有有效效边边界界和和不不允允许许卖卖空

8、空的的有有效效边边界界允许卖空的有效边界各国在有效组合中的权重预期收益率（%）标准差美国德国英国日本澳大利亚 加拿大928.3-0.436564447 -0.48467 -0.20506 0.964696 0.019894 1.1015041026.21-0.365026367 -0.40545 -0.18024 0.900216 0.030988 0.9851471124.22-0.293488635 -0.32623 -0.15542 0.835736 0.0420810.868791222.36-0.221950718 -0.24702-0.1306 0.771256 0.053175

9、0.7524331320.67-0.150412802-0.1678 -0.10578 0.706776 0.064268 0.6360761419.19-0.078874914 -0.08858 -0.08096 0.642296 0.075361 0.5197191517.98-0.007337003 -0.00936 -0.05614 0.577816 0.086455 0.4033621617.080.064200804 0.069858 -0.03132 0.513336 0.097548 0.2870051716.570.135738708 0.149076-0.0065 0.44

10、8856 0.108641 0.1706481816.450.207276618 0.228295 0.018322 0.384376 0.119735 0.0542911916.750.278814484 0.307513 0.043142 0.319896 0.130828 -0.062072017.450.350352398 0.386731 0.067962 0.255416 0.141922 -0.178422118.50.4218903280.46595 0.092782 0.190936 0.153015 -0.294782219.840.493428173 0.545168 0

11、.117602 0.126456 0.164109 -0.411142321.420.56496612 0.624386 0.142423 0.061976 0.175202 -0.527492423.190.636503962 0.703604 0.167243-0.0025 0.186295 -0.643852525.110.708041906 0.782823 0.192063 -0.06698 0.197389 -0.760212627.150.77957982 0.862041 0.216883 -0.13146 0.208482 -0.87656不允许卖空的有效边界各国在有效组合中

12、的权重预期收益率（%）标准差美国德国英国日本澳大利亚 加拿大13 不可行不可行不可行不可行不可行不可行不可行14.9240000011520.140000.2500.751617.10.05319802 0.0609370 0.509073 0.089523 0.2872691716.570.132748424 0.1439730 0.446621 0.106489 0.1701681816.460.206701296 0.222617 0.016025 0.382018 0.119367 0.0532721916.810.249468091 0.328809 0.040558 0.27932

13、4 0.10184102017.840.268352916 0.459865 0.065763 0.145544 0.06047502119.480.287237770.59092 0.090968 0.0117640.0191102222.910.043478242 0.956522000022.123.501000026 不可行不可行不可行不可行不可行不可行不可行允许和不允许卖空下的有效边界302520151050051015202530标准差预期收益率允许卖空的有效边界不允许卖空的有效边界美国德国英国日本澳大利亚加拿大法国等权重组合澳大利亚 加拿大法国0.40.70.410.30.370

14、.620.520.550.510.270.280.4110.580.350.5810.410.350.411法国0.1428575.365987.8796946.8402454.7007765.1296435.32163314.3316349.5696法国-0.01381-0.82026-1.32725-0.11469-1.16948-0.42568-0.08657D.D.目目标标预预期期收收益益率率等等于于18.26%18.26%时时有有效效边边界界组组合合的的协协方方差差矩矩阵阵（用用SolverSolver来来求求权权重重）0.133958-3.80997法国小计0.040210.034

15、36810.02853510.02270210.01686910.01103610.0052041-0.000631-0.006461-0.012291-0.018131-0.023961-0.029791-0.035631-0.041461-0.047291-0.053121-0.058961法国小计不可行不可行01010101010101010101不可行不可行1992年4月10日至2001年8月14日上海证交所8只股票的每日收盘价 收收 盘盘 价价交交易易日日期期 60060160060160060260060260060360060360060460060460065160065160

16、0652600652600653600653600654600654199204101.884.171.722.020.930.430.564.33199204132.014.381.722.020.980.430.564.47199204141.994.61.722.021.030.440.574.85199204151.924.831.722.021.080.440.584.93199204161.825.081.722.041.130.450.584.8199204171.775.331.722.041.070.450.594.58199204201.465.591.722.041.02

17、0.450.594355.561.722.060.970.450.64275.291.722.060.920.450.64285.541.742.080.880.450.614395.761.742.10.830.440.614.12199204271.436.051.742.120.870.440.624.11199204281.516.351.742.120.910.440.634.06199204291.46.631.742.140.870.450.634.01199204301.42

18、6.71.742.160.870.450.643.86199205041.426.961.742.160.910.450.653.83199205051.356.751.742.270.90.480.683.67199205061.346.741.822.270.890.50.713.54199205071.366.751.912.380.890.530.753.47199205081.396.962.012.50.90.550.783.5199205111.487.32.112.630.950.580.823.98199205121.427.672.222.760.940.60.873.91

19、199205131.397.732.332.90.90.580.913.9199205141.387.772.443.050.920.550.953.91199205151.418.162.563.20.920.5814.01199205181.468.572.693.360.970.611.054.26199205191.6192.823.531.020.641.14.55199205201.599.192.973.70.980.611.164.65199205211.5710.235.9315.261.020.631.544.43199205221.710.56.1916.141.040.

20、631.994.98199205252.0111.217.4216.661.160.712.096.01199205261.8410.396.9415.361.060.741.892.92199205272.019.346.6115.361.020.741.825.32199205281.89.256.0813.20.970.711.75.1199205291.879.916.5614.161.020.721.655.29199206011.89.746.4413.210.691.115769.586.1712.550.990.71.0656

21、38.855.9811.720.910.650.974.77199206041.079.096.1712.50.950.681.013.21199206051.079.166.2812.520.980.671.043.11199206081.019.236.2512.390.950.671.042.86199206091.059.526.5412.340.970.691.093.03199206101.099.656.7213.460.950.711.123.08199206111.079.76.7113.430.970.731.153.01199206121.079.656.6613.430

22、.970.781.152.97199206151.079.586.7713.381.020.891.22.99199206171.059.386.8513.591.060.831.212.96199206181.069.366.8313.661.050.851.212.98199206191.079.436.7913.571.10.911.232.99199206221.089.456.8313.541.120.891.163.02199206231.089.366.7913.381.10.891.142.99199206241.079.266.7513.241.120.91.162.9619

23、9206251.079.346.7913.331.140.921.22.96199206261.079.336.8513.361.210.961.223.02199206291.199.36.8613.281.391.061.233.1199206301.249.356.9513.281.421.11.243.34199207011.199.58713.431.451.091.223.26199207021.189.716.9613.281.561.141.243.27199207031.219.71713.111.581.181.283.4199207061.229.927.9113.161

24、.461.141.653.5199207071.239.88.2613.31.511.21.763.49199207081.239.837.8813.071.461.181.683.34199207091.219.757.5813.231.461.191.613.19199207101.229.577.9113.481.481.191.693.21199207131.188.947.8513.031.491.21.733.06199207141.148.697.7712.651.51.291.732.99199207151.168.458.0612.451.581.411.772.991992

25、07161.168.948.0612.521.641.411.753.01199207171.168.868.112.551.631.431.773.02199207201.158.58.0212.411.61.421.792.89199207211.178.447.9112.031.61.411.772.86199207221.137.917.7511.451.551.41.72.88199207231.097.597.5611.481.51.341.662.68199207241.137.737.6211.591.531.361.682.77199207271.168.4812.391.6

26、1.391.732.87199207281.148.067.8312.161.561.371.692.93199207291.148.047.8111.981.571.361.692.98199207301.138.017.7711.721.571.361.672.98199207311.147.987.7911.821.561.371.682.9199208031.127.987.7111.451.541.341.652.91199208041.137.917.7311.661.551.331.652.91199208051.117.987.6211.31.541.321.632.85199

27、208061.17.897.511.281.531.311.612.82199208071.097.887.411.221.51.281.572.83199208101.027.536.7210.521.281.141.42.67199208110.96.725.888.891.1611.252.33199208120.835.665.397.761.060.961.152.11199208130.886.195.778.71.1411.222.26199208140.896.216.19.031.171.021.242.37199208170.986.936.759.681.311.141.

28、372.63199208180.937.366.469.221.271.071.332.52199208190.977.256.569.531.281.111.362.54199208200.957.36.549.291.281.11.342.52199208210.947.336.369.081.231.11.322.5199208240.97.196.118.771.191.081.262.35199208250.876.435.938.271.161.021.242.3199208260.96.636.158.591.21.041.272.43199208270.896.576.028.

29、331.171.031.242.35199208280.896.415.948.281.161.021.232.31199208310.846.115.868.131.1211.212.23199209010.856.15.898.171.121.011.212.25199209020.815.815.667.811.070.971.162775.655.57.651.060.951.152.07199209040.785.645.47.641.030.951.132.05199209070.725.474.957.130.930.871.031.8519920908

30、0.654.674.446.430.890.770.941.71199209090.664.64.396.390.90.760.951.7199209100.735.294.8270.970.861.041.89199209110.85.675.347.551.070.931.132.11199209140.755.45.037.150.950.851.071.8199209150.745.295.057.190.980.871.11.92199209160.785.585.277.471.040.931.172.01199209170.785.585.267.341.040.931.162.

31、01199209180.775.675.237.261.020.931.161.97199209210.755.175.17.080.990.911.131.92199209220.744.995.026.990.990.891.111.88199209230.734.774.926.830.960.871.091.85199209240.754.895.087.0410.91.111.93199209250.764.915.17.0710.91.131.93199209280.754.855.0470.990.891.121.91199209290.734.714.976.840.970.8

32、71.11.87199209300.734.644.96.760.960.861.091.85199210050.714.374.716.50.930.811.061.76199210060.714.214.996.460.940.821.041.76199210070.694.114.96.410.940.811.021.74199210080.683.844.786.220.90.7911.7199210090.73.994.916.410.940.821.041.75199210120.724.285.226.981.030.881.111.81199210130.694.195.096

33、.8210.841.071.74199210140.683.995.096.871.010.831.051.71199210150.693.955.16.881.030.831.071.72199210160.693.955.136.891.040.831.071.73199210190.653.654.996.5610.81.031.65199210200.623.514.926.4610.81.021.61199210210.593.274.826.290.970.770.981.51199210220.522.994.5360.930.70.91.44199210230.553.044.

34、76.230.960.710.921.51199210260.492.644.055.590.850.620.821.37199210270.452.153.44.730.730.510.711.26199210280.452.233.474.790.740.520.721.28199210290.472.323.615.080.810.560.761.34199210300.492.473.885.340.890.610.811.38199211020.462.283.544.950.820.580.751.27199211030.462.223.524.840.810.570.741.26

35、199211040.452.183.464.740.790.550.731.24199211050.452.173.44.670.770.540.731.23199211060.442.193.384.610.760.540.731.21199211090.422.123.14.110.730.510.691.12199211100.422.022.843.690.660.470.621.11199211110.422.12.853.770.670.470.621.11199211120.422.162.913.830.690.490.641422.182.923.8

36、10.690.50.641.12199211160.422.112.83.630.660.470.621.1199211170.412.082.723.510.650.470.611.08199211180.412.162.83.640.670.490.621.09199211190.42.112.743.540.660.490.611.07199211200.42.122.743.60.660.490.621.07199211230.412.282.83.70.690.520.651.09199211240.442.613.054.010.740.580.721.19199211250.59

37、3.533.955.470.910.670.881.54199211260.553.843.825.620.860.740.91.51199211270.644.314.456.090.950.80.981.67199211300.735.424.737.291.040.871.11.95199212010.715.274.657.191.020.831.051.91199212020.715.144.787.131.020.821.031.92199212030.715.674.927.911.050.871.081.98199212040.85.345.017.911.10.911.112

38、.22199212070.835.454.978.071.090.91.12.24199212080.795.224.737.551.030.861.062694.514.246.770.920.790.951.96199212100.664.243.96.30.890.760.921.85199212110.784.794.537.031.020.861.112754.654.537.020.990.871.092.07199212150.724.474.526.720.970.811.062.04199212160.724.514.436

39、.770.960.811.052.03199212170.764.74.517.030.990.881.082754.594.436.820.990.891.062.1199212210.84.984.547.2310.941.092794.894.537.1910.941.092.08199212230.84.974.547.291.010.951.092.08199212240.825.344.667.691.050.971.122.12199212250.815.344.87.761.061.011.152.09199212280.85

40、.234.717.771.0611.22.06199212290.815.314.977.941.111.011.212.07199212300.815.244.997.951.120.991.192.06199212310.825.254.977.971.11.011.192.07199301040.835.35.118.251.121.021.212.11199301050.885.455.38.681.151.041.242.19199301060.875.415.218.511.131.021.222.2199301070.95.585.238.91.151.041.252.33199

41、301080.95.535.569.191.261.121.322.33199301110.946.055.7310.311.31.131.342.44199301120.956.045.7810.821.31.151.352.45199301130.996.215.8111.981.31.151.372.5199301141.036.76.1112.441.341.181.462.68199301151.026.615.8811.981.381.171.472.6199301181.036.556.1311.861.421.21.512.57199301190.916.125.8211.25

42、1.351.151.452.39199301201.016.39611.821.391.181.52.52199301211.036.596.1312.111.41.211.542.61199301221.046.76.3612.291.451.271.62.64199301271.077.236.9413.171.611.361.682.74199301281.057.416.8312.991.581.341.652.68199301291.128.097.0313.431.621.371.682.91199302011.188.357.3514.631.691.421.753.121993

43、02021.218.767.3714.831.691.441.773.3199302031.38.947.29151.681.451.763.46199302041.429.577.8915.571.791.661.933.74199302051.379.277.7714.841.781.771.913.58199302081.419.498.1815.471.91.942.093.63199302091.489.968.7716.32.112.282.333.8199302101.439.478.615.722.022.182.263.78199302111.369.038.3314.861

44、.912.052.163.56199302121.49.478.5215.352.062.172.333.7199302151.449.658.8115.722.132.162.513.74199302161.429.368.6615.422.072.12.423.65199302171.399.118.8515.262.092.082.413.59199302181.419.298.716.042.052.082.373.63199302191.439.658.6216.142.0622.323.69199302221.389.238.2915.261.951.982.263.5619930

45、2231.318.917.6714.251.841.882.173.34199302241.188.186.9213.021.711.732.043.06199302251.328.947.9814.371.941.962.273.34199302261.248.487.6313.461.831.862.163.12199303011.117.226.911.921.621.651.962.74199303021.127.427.1512.121.691.732.022.79199303031.117.397.1111.821.661.692.012.73199303041.026.796.7

46、310.881.561.61.912.52199303051.016.76.3810.671.511.621.922.5199303081.117.287.1311.31.852.182.382.76199303091.016.677.6410.471.892.082.212.53199303101.087.067.8711.031.972.22.442.65199303111.137.558.1811.912.042.342.592.82199303121.097.518.0711.662.042.342.562.74199303151.076.887.4810.931.851.221.64

47、2.53199303161.137.297.8511.381.891.281.762.7199303171.127.157.7310.981.851.231.712.63199303181.097.047.6710.731.791.211.652.63199303191.096.977.5710.611.691.141.612.61199303220.895.416.549.111.050.981.42.24199303230.854.995.988.610.980.931.352.05199303240.844.785.788.60.960.921.351.97199303250.854.6

48、75.788.790.920.91.381.98199303260.955.216.279.671.020.981.552.2199303290.873.755.398.850.960.931.452.01199303300.873.715.138.770.930.881.42199303310.863.74.678.490.890.881.351.93199304010.893.784.868.80.930.911.411.99199304020.893.764.738.830.920.91.412199304050.913.843.729.020.990.921.442.021993040

49、61.074.524.4410.471.161.071.612.28199304071.114.754.7611.041.351.161.72.39199304081.295.235.2612.341.471.291.862.85199304091.235.155.0912.081.471.31.862.75199304121.164.854.9111.451.361.251.82.57199304131.275.25.3612.481.461.351.942.79199304141.25.035.1912.181.41.321.912.66199304151.2755.112.241.391

50、.291.892.75199304161.214.684.8511.451.341.211.782.63199304191.254.774.9911.721.361.231.832.67199304201.264.975.0311.851.371.251.862.74199304211.234.894.9211.761.351.241.842.73199304221.264.995.1212.231.371.281.922.81199304231.245.015.0312.031.391.291.922.92199304261.285.235.8912.641.441.3423.0419930

51、4271.375.936.2713.121.51.4523.12199304281.335.86.2413.381.491.4223.29199304291.285.566.0713.031.451.391.953335.646.4212.981.461.381.953.21199305031.295.566.5913.021.671.452.053.21199305041.175.055.9811.891.531.311.893.21199305051.184.955.9911.751.551.331.93.21199305061.14.555.5110.781.4

52、41.221.783.21199305071.144.845.7711.31.551.261.823.21199305101.074.65.3910.571.481.171.692.65199305111.094.655.6210.691.511.191.712.73199305121.14.645.5910.991.491.21.72.7199305131.134.755.7212.131.521.231.742.79199305141.114.75.5611.641.481.221.722.73199305171.14.625.4111.091.431.21.672.34199305181

53、.094.65.3411.171.421.181.672.3199305191.094.615.3611.381.481.191.682.32199305201.114.655.5411.591.531.241.762.33199305211.084.565.411.351.51.21.732.251993052414.084.7910.271.341.071.562.1199305250.9844.669.891.31.051.542.07199305260.964.044.599.871.261.031.512.05199305270.943.974.499.471.210.991.482

54、199305280.954.014.579.591.211.482.02199305310.933.954.419.351.130.971.431.92199306010.963.924.489.371.191.061.552199306021.094.55.2610.671.361.21.722.43199306030.994.054.799.631.261.11.572.22199306040.974.034.6410.111.241.071.542.09199306071.034.234.910.31.351.151.622.31199306081.054.24.8910.411.361

55、.151.612.46199306091.054.294.8510.311.321.131.612.4199306101.024.134.7310.211.271.11.552.31199306111.014.134.7210.281.271.091.552.28199306140.963.934.469.891.221.051.52.2199306150.973.954.479.871.211.031.492.21199306160.9844.499.941.211.041.492.21199306170.9844.519.911.21.031.482.21199306180.983.954

56、.519.891.21.031.492.21199306210.963.924.379.331.171.021.452963.944.49.271.171.021.462973.944.429.231.171.031.472.16199306240.963.894.378.991.151.021.462.12199306250.983.954.419.161.181.031.482023.994.449.451.211.051.542.27199306291.054.094.519.741.231.071.572.3

57、8199306301.033.984.49.611.211.051.552.3199307011.044.014.399.61.211.041.552.32199307021.033.954.349.421.181.041.542.271993070513.884.239.231.150.991.492.19199307060.963.644.058.721.090.941.412.1199307070.973.674.038.691.090.941.412973.694.018.651.090.941.422943.573.828.321.

58、050.931.382.07199307120.923.53.688.071.020.911.362.06199307130.953.563.768.211.040.921.362.1199307140.923.463.587.950.980.881.252.02199307150.923.493.617.940.990.91.242.03199307160.923.493.597.880.980.881.222.03199307190.93.473.517.690.950.881.162199307200.873.373.357.440.930.861.091.89199307210.893

59、.43.387.460.940.861.11.9199307220.893.43.517.460.940.861.111.92199307230.893.433.467.470.950.861.121.9199307260.873.343.347.290.920.841.111.86199307270.873.353.467.330.940.861.121.89199307280.873.433.437.410.940.871.161.9199307290.883.453.487.510.970.871.191.9199307300.973.73.868.321.060.941.312.081

60、99308020.923.513.587.7810.921.241.97199308030.93.53.587.7610.911.231.94199308040.913.583.637.911.020.941.261.98199308050.913.513.647.911.020.931.261.97199308060.923.543.687.911.050.941.271.98199308090.953.653.918.281.10.971.312.08199308100.953.73.817.991.060.971.272.02199308110.963.873.858.321.090.9