Excel在金融模型分析中的应用 (5)

1、方差-协方差矩阵期望收益0.100.03-0.080.058%0.030.200.020.039%-0.080.020.300.2010%0.050.030.200.9011%给定常数c0.1600投资比例x10.0000 x20.0000 x30.0000 x41.00001.0000 -=SUM(C8:C11)组合期望收益11.00% -=MMULT(TRANSPOSE(C8:C11),F2:F5)组合标准差94.87%(Theta)-0.0527 -=(B14-C7)/B15 c组合标准差组合期望收益x1x2x3x4注意：首先要运行规划求解，然后再-0.03520.24%8.70%0.6

2、050.0890.3070.000使用Ctrl+Z快捷键，即可输出执行结果-0.0320.25%8.70%0.6040.0890.3070.000-0.02520.25%8.70%0.6030.0890.3070.000使用VBA函数在有卖空限制的条件下计算有效组合-0.0220.25%8.71%0.6030.0890.3080.000-0.01520.25%8.71%0.6020.0900.3090.000-0.0120.26%8.71%0.6010.0900.3090.000-0.00520.26%8.71%0.5990.0910.3100.000020.27%8.71%0.5980.09

3、10.3110.0000.00520.27%8.71%0.5970.0920.3110.0000.0120.28%8.72%0.5950.0930.3120.0000.01520.29%8.72%0.5930.0930.3130.0000.0220.30%8.72%0.5910.0940.3150.0000.02520.31%8.73%0.5890.0950.3160.0000.0320.32%8.73%0.5860.0970.3180.0000.03520.34%8.74%0.5820.0980.3200.0000.0420.37%8.74%0.5780.1000.3220.0000.045

4、20.41%8.75%0.5730.1020.3250.0000.0520.46%8.76%0.5660.1050.3290.0000.05520.54%8.78%0.5570.1080.3340.0000.0620.67%8.80%0.5450.1130.3420.0000.06520.90%8.82%0.5280.1200.3520.0000.0721.36%8.87%0.4990.1320.3680.0000.07523.27%9.01%0.4270.1630.3860.0250.0831.91%9.46%0.2000.2590.4220.1190.08545.25%10.01%0.00

5、00.2510.4880.2600.0960.76%10.44%0.0000.0000.5560.4440.09574.30%10.70%0.0000.0000.3000.7000.194.87%11.00%0.0000.0000.0001.0000.10594.87%11.00%0.0000.0000.0001.0000.1194.87%11.00%0.0000.0000.0001.00099%89%79%69%59%49%39%29%19%6.0%7.0%8.0%9.0%10.0%11.0%12.0%组合标准差组合期望收益0.11500.94870.11000.00000.00000.00

6、001.00000.12000.94870.11000.00000.00000.00001.00000.12500.94870.11000.00000.00000.00001.00000.13000.94870.11000.00000.00000.00001.00000.13500.94870.11000.00000.00000.00001.00000.14000.94870.11000.00000.00000.00001.00000.14500.94870.11000.00000.00000.00001.00000.15000.94870.11000.00000.00000.00001.00

7、000.15500.94870.11000.00000.00000.00001.00000.16000.94870.11000.00000.00000.00001.00004只证券的期望收益和方差协方差矩阵，计算有卖空限制条件下的有效组合注意：首先要运行规划求解，然后再使用Ctrl+Z快捷键，即可输出执行结果使用VBA函数在有卖空限制的条件下计算有效组合99%89%79%69%59%49%39%29%19%6.0%7.0%8.0%9.0%10.0%11.0%12.0%组合标准差组合期望收益方差-协方差矩阵期望收益0.100.03-0.080.058%在无卖空限制条件下计算有效组合0.030.2

8、00.020.039%-0.080.020.300.2010%0.050.030.200.9011%c1z1xc2R-c2z2y0.001.470563.76%8%0%0.028720.04%0.17017.38%1%0.037125.87%0.811035.17%2%0.060542.19%-0.1454-6.30%3%0.017111.90%xT0.63760.07380.3517-0.0630yT0.20040.25870.42190.1190期望收益（x）8.59%期望收益（y） 9.46%方差（x）3.72%方差（y）10.18%协方差4.10%组合x的投资比例a50.00%组合p的

9、期望收益9.02%组合p的方差5.53%组合p的标准差23.51%不同投资比例下组合的期望收益和标准差组合x的投资比例a标准差期望收益23.51%9.02%-1.871.09%11.03%-1.6266.96%10.87%-1.4462.86%10.71%-1.2658.78%10.56%-1.0854.74%10.40%-0.950.74%10.24%-0.7246.79%10.09%-0.5442.91%9.93%-0.3639.11%9.77%-0.1835.43%9.62%031.91%9.46%0.1828.60%9.30%0.3625.58%9.15%0.5422.97%8.99%

10、0.7220.93%8.83%0.919.64%8.68%1.0819.23%8.52%1.2619.78%8.36%1.4421.20%8.20%1.6223.34%8.05%1.826.02%7.89%31.91%9.46%31.91%9.46%31.91%9.46%31.91%9.46%31.91%9.46%31.91%9.46%31.91%9.46%31.91%9.46%在无卖空限制条件下计算有效组合99%89%79%69%59%49%39%29%19%6.0%7.0%8.0%9.0%10.0%11.0%12.0%组组合合标标准准差差组组合合期期望望收收益益有卖空限制无卖空限制方差-协

11、方差矩阵公司A公司B公司C公司D公司E公司F期望收益公司A0.12670.04380.1971 0.0541 0.06250.108829.24%公司B0.04380.07130.0492 0.0068 0.03840.029620.68%公司C0.19710.04920.3667 0.0853 0.09750.163625.02%公司D0.05410.00680.0853 0.1136 0.02100.065731.64%公司E0.06250.03840.0975 0.0210 0.06540.026715.34%公司F0.10880.02960.1636 0.0657 0.02670.18

12、1943.87%00常数c0.10公司A0.7203 -=MMULT(MINVERSE(B3:G8),I3:I8-B11)公司B0.1104 /SUM(MMULT(MINVERSE(B3:G8),I3:I8-B11)公司C-0.554公司D0.3074公司E0.014公司F0.4023组合期望收益0.37067 -=MMULT(TRANSPOSE(E11:E16),I3:I8)组合标准差0.26006 -=SQRT(MMULT(MMULT(TRANSPOSE(E11:E16), B3:G8),E11:E16)c标准差 期望收益0.26010.3707-0.050.20390.2999-0.04

13、0.20520.3023-0.030.20680.3048-0.020.20840.3074-0.010.21030.310400.21240.31350.010.21480.31700.020.21750.32070.030.22050.32480.040.22400.32940.050.22790.33440.060.23250.34000.070.23780.34630.080.24400.35340.090.25130.36140.10.26010.37070.110.27050.38140.120.28330.39390.130.29900.40890.140.31880.4269常

14、数c0.40投资比例xAxBxCxDxExF0.000.000.000.00000.001.00001.00组合期望收益 0.4387组合标准差0.4265值0.0908注意：首先要运行规划求解，然后再常数c标准差 期望收益公司A公司B公司C公司D公司E公司F使用Ctrl+X快捷键，即可输出执行结果-0.18000.21630.27040.0000 0.3979 0.00000.31580.13100.1553-0.16000.21740.27280.0000 0.3988 0.00000.31800.12110.1621-0.14000.21880.27540.0000 0.3998 0.0

15、0000.32040.11000.1698-0.12000.22040.27830.0000 0.4010 0.00000.32300.09770.1783-0.10000.22220.28160.0000 0.4023 0.00000.32600.08380.1878-0.08000.22440.28530.0000 0.4038 0.00000.32940.06820.1987-0.06000.22710.28960.0000 0.4054 0.00000.33330.05030.2110-0.04000.23040.29450.0000 0.4074 0.00000.33770.0298

16、0.2252-0.02000.23440.30010.0000 0.4096 0.00000.34290.00580.24170.00000.23770.30450.0000 0.3976 0.00000.34320.00000.25920.02000.24130.30890.0000 0.3794 0.00000.34170.00000.27890.04000.24590.31420.0000 0.3576 0.00000.34000.00000.30240.06000.25180.32060.0000 0.3313 0.00000.33800.00000.33080.08000.25970

17、.32840.0000 0.2988 0.00000.33550.00000.36570.10000.27040.33830.0000 0.2579 0.00000.33230.00000.40970.12000.28570.35110.0000 0.2047 0.00000.32820.00000.46710.14000.30820.36850.0000 0.1327 0.00000.32260.00000.54470.16000.34340.39340.0000 0.0296 0.00000.31460.00000.65580.18000.36080.40490.0000 0.0000 0.00000.27660.00000.72340.20000.37240.41180.0000 0.0000 0.00000.21990.00000.78010.220.389153 0.420860000.1460200.853980.240.414223 0.433130000.0457500.954250.260.426459 0.438720000010.280.426459 0.438720000010.30.426459 0.438720000010.320.426459 0.438720000010.340.426459 0.438720000010.360.426