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

已知数据

资产期望收益率标准差无风险资产

X10.00035250.01399960.0000744

X2-0.0067550.0384222

注意：只能改动X3-0.003210.0169213

X4-0.0027270.0182825

X5-0.0032640.0154838

相关系数

资产

X2

X3

X4

X5

中间计算参数

协方差矩阵

资产X1X2X3X4X5

X10.000195990.000156880.000147690.000140590.00014475

X20.000156880.001476270.000458430.000401120.00035039

X30.000147690.000458430.000286330.000258040.00023317

X40.000140590.000401120.000258040.000334250.00024506

X50.000144750.000350390.000233170.000245060.00023975

参数A.B.C.D的计算

A=-4.9394074C=5720.19391

B=0.10397689D=570.370234

最优投资组合

期望收益率-0.0194491标准差0.06032464

资产X1X2X3X4X5

权重-4.08701310.10756551 1.04721014-2.0213872 5.95362465

绘图数据

期望收益率标准差资本配置线CAL上的收益率

0%0.01350175

2%0.06738147

能改动红色部分

4%0.13008213

6%0.19319831

8%0.25642337

10%0.31969272

12%0.38298441

14%0.44628893

16%0.5096015

18%0.57291946

20%0.63624119

22%0.69956567

24%0.76289222

26%0.82622035

28%0.88954974

0.00%0.0000744

6.03%-0.0194491

20.00%-0.0646538

注意：只能改动红色部分，其它的都是自动生成的