最优资产组合及成图EXCEL制作

1、已知数据 资产期望收益率标准差无风险资产 x10.00035250.01399960.0000744 x2-0.0067550.0384222 x3-0.003210.0169213注注意意：只只能能改改动动红红色色部部分分 x4-0.0027270.0182825 x5-0.0032640.0154838 相关系数 资产x1x2x3x4x5 x110.2916479870.6234434390.5492753870.667789807 x20.29164798710.7051118270.57102770.588960461 x30.6234434390.70511182710.834090

2、0460.889955875 x40.5492753870.57102770.83409004610.865699331 x50.6677898070.5889604610.8899558750.8656993311 中间计算参数 协方差矩阵 资产x1x2x3x4x5 x10.000195989 0.000156876 0.000147688 0.000140586 0.000144755 x20.000156876 0.001476265 0.000458431 0.000401121 0.000350385 x30.000147688 0.0004584310.00028633 0.000

3、258037 0.000233174 x40.000140586 0.000401121 0.0002580370.00033425 0.000245064 x50.000144755 0.000350385 0.000233174 0.000245064 0.000239748 参数a.b.c.d的计算 a=-4.93940738 c=5720.193915 b=0.103976891 d=570.3702341 最优投资组合 期望收益率-0.01944913 标准差0.060324637 资产x1x2x3x4x5 权权重重-4.08701313 0.107565512 1.04721013

4、8 -2.02138716 5.953624645 绘图数据 期望收益率标准差资本配置线cal上的收益率 0% 0.013501748 2% 0.067381472 4% 0.130082126 6%0.19319831 8% 0.256423372 10% 0.319692721 12% 0.382984408 14% 0.446288929 16% 0.509601502 18% 0.572919458 20% 0.636241189 22%0.69956567 24% 0.762892216 26% 0.826220353 28%0.88954974 0.00%0.0000744 6.0

5、3% -0.01944913 20.00% -0.06465381 注注意意：只只能能改改动动红红色色部部分分，其其它它的的都都是是自自动动生生成成的的 10.90.80.70.60.50.40.30.20.10 -10% -5% 0% 5% 10% 15% 20% 25% 30% 系列2 系列1 系列3 注注意意：只只能能改改动动红红色色部部分分 10.90.80.70.60.50.40.30.20.10 -10% -5% 0% 5% 10% 15% 20% 25% 30% 系列2 系列1 系列3 已知数据 资产期望收益率标准差无风险资产 x30.00890.10000.0013 x40.0

6、0660.0095 x50.03070.1004 相关系数 资产x3x4x5 x31.0000-0.37840.9602 x4-0.37841.0000-0.3420 x50.9602-0.34201.0000 中间计算参数 协方差矩阵 资产x3x4x5 x30.01 -0.00035945 0.009640804 x4-0.0003594480.00009025 -0.00032618 x50.009640804 -0.000326180.01008016 参数a.b.c.d的计算 a=87.43167344 c=14049.87861 b=1.15516286 d=8585.600436

7、最优投资组合 期望收益率0.01505782 标准差0.014103466 资产x3x4x5 权重-0.323254453 0.941457561 0.381796892 绘图数据 期望收益率标准差资本配置线cal上的收益率 0%0.01159942 2% 0.019539301 4%0.04402476 6% 0.069308962 8% 0.094754643 10% 0.120259344 12% 0.145792095 14% 0.171340356 16% 0.196898089 18%0.22246203 20%0.24803026 22% 0.273601576 24% 0.299175186 26% 0.324750549 28% 0.350327281 0.00%0.0013 1.41%0.01505782 20.00% 0.196398417 10.90.80.70.60.50.40.30.20.10 -10% -5% 0% 5% 10% 15% 20% 25% 30% 系列2 系列1 系列3 10.90.80.70.60.50.40.30.20.10 -10% -5% 0% 5% 10% 15% 20% 25% 30% 系列2 系列1 系列3 10.90.80.70.60.50.40.30.20.10