相关系数、比例与投资组合的收益率和标准差.xls

月份价格 025连续型离散型 124.12-3.58%-3.52% 223.37-3.16%-3.11% 324.755.74%5.91% 426.627.28%7.56% 526.5-0.45%-0.45% 6285.51%5.66% 728.883.09%3.14% 829.752.97%3.01% 931.385.33%5.48% 1036.2514.43%15.52% 1137.132.40%2.43% 1236.88-0.68%-0.67% 月均3.24%3.41% 月方差(总）0.23%0.25% 月方差(样）0.25%0.28% 月标准差(总）4.78%5.02% 月标准差(样）5.00%5.25% 5.00% 收益率 变量值变量值离差离差 1500150012001200 200200-100-100 -800-800-1100-1100 平均平均3003000 0合计合计 概率概率收益率收益率期望值期望值 概率政府债券公司债 0.20.2-7%-7%-1.4%-1.4% 0.28%12% 0.515%15%7.5%7.5%0.58%9% 0.330%30%9.0%9.0%0.38%7% 合计合计1 115.1%15.1% 期望值8.0%9.0% 方差方差3.92%3.92% 0.0%0.0% 标准差标准差19.81%19.81% AB组合组合目标目标 收益率收益率30%30%10%10%25%25% 权数权数75%75%25%25% 25.0%25.0% 股票X股票Y -6%-7% 12%15% 25%30% 12.3%15.1% 0.0%0.0% 方案方案 投资额投资额 年度年度收益额收益额收益率收益率收益额收益额收益率收益率收益额收益额收益率收益率收益率收益率 年度年度 RARBRPWAWBRPxiXyiY 200 1202040%40%202040%40%4040%40%50%50%50%50%40%40%25%25%25%25% 200 2-5-5-10%-10%-5-5-10%-10%-10-10%-10%50%50%50%50%-10%-10%-25%-25%-25%-25% 200 317.517.535%35%17.517.535%35%3535%35%50%50%50%50%35%35%20%20%20%20% 200 4-2.5-2.5-5%-5%-2.5-2.5-5%-5%-5-5%-5%50%50%50%50%-5%-5%-20%-20%-20%-20% 200 57.57.515%15%7.57.515%15%1515%15%50%50%50%50%15%15%0%0%0%0% 平均数平均数15%15%15%15%50%50%50%50%15%15%求和求和 标准差标准差22.64%22.64%22.64%22.64%22.64%22.64%22.64%22.64% 协方差协方差4.10%4.10%4.10%4.10% 相关系数相关系数1 11 1 比重比重 组合P组合PA+B A+B 100100 收益率的平均值和标准差收益率的平均值和标准差 A AB B 50505050 （xiX）（）（yiY）（xiX）2（yiY）2 6.2500%6.2500%0.06250.06250.06250.0625 6.2500%6.2500%0.06250.06250.06250.0625 4.0000%4.0000%0.040.040.040.04 4.0000%4.0000%0.040.040.040.04 0.0000%0.0000%0 00 0 20.5000%20.5000%20.5000%20.5000%20.5000%20.5000% 求算术平方根求算术平方根0.4527692570.4527692570.4527692570.452769257 方案方案 投资额投资额 年度年度收益额收益额收益率收益率收益额收益额收益率收益率收益额收益额收益率收益率收益率收益率 年度年度 RARBWAWBRPxiX yiY 200 1202040%40%-5-5-10%-10%50%50%50%50%15%15%25%25%-25%-25% 200 2-5-5-10%-10%202040%40%50%50%50%50%15%15%-25%-25%25%25% 200 317.517.535%35%-2.5-2.5-5%-5%50%50%50%50%15%15%20%20%-20%-20% 200 4-2.5-2.5-5%-5%17.517.535%35%50%50%50%50%15%15%-20%-20%20%20% 200 57.57.515%15%7.57.515%15%50%50%50%50%15%15%0%0%0%0% 平均数平均数15%15%15%15%50%50%50%50%15%15%求和求和 标准差标准差22.64%22.64%22.64%22.64%0.00%0.00%0%0% 协方差协方差-4.10%-4.10%-4.10%-4.10% 相关系数相关系数-1-1-1-1 比重比重 100100 组合P组合PC+D C+D 收益率的平均值和标准差收益率的平均值和标准差 C CD D 50505050 （xiX）（）（yiY）（xiX）2（yiY）2 -6.2500%-6.2500%0.06250.06250.06250.0625 -6.2500%-6.2500%0.06250.06250.06250.0625 -4.0000%-4.0000%0.040.040.040.04 -4.0000%-4.0000%0.040.040.040.04 0.0000%0.0000%0 00 0 -20.5000%-20.5000%20.5000%20.5000%20.5000%20.5000% 求算术平方根求算术平方根0.4527692570.4527692570.4527692570.452769257 股票股票 期望收期望收 益率益率 标准标准 差差 与股票X的相与股票X的相 关系数关系数 X X10%10%12%12%1 1 Y Y1 114%14%18%18%-1-1 Y Y2 214%14%18%18%-0.25-0.25 Y Y3 314%14%18%18%0.250.25 Y Y4 414%14%18%18%1 1 W Wyi yi W WX X P（rXY1=-P（rXY1=- 1)1) P（rP（rXY2 XY2=- =- 0.25）0.25） P（rP（rXY3 XY3=0.25） =0.25） 0%0%-1-1-0.25-0.250.250.25 1 1100%100%0%0%0.1800.1800.1800.1800.1800.180 2 290%90%10%10%0.1500.1500.1590.1590.1650.165 3 380%80%20%20%0.1200.1200.1400.1400.1520.152 4 470%70%30%30%0.0900.0900.1220.1220.1390.139 5 560%60%40%40%0.0600.0600.1070.1070.1290.129 6 650%50%50%50%0.0300.0300.0950.0950.1200.120 7 740%40%60%60%0.0000.0000.0880.0880.1140.114 8 830%30%70%70%0.0300.0300.0880.0880.1110.111 9 920%20%80%80%0.0600.0600.0940.0940.1110.111 101010%10%90%90%0.0900.0900.1050.1050.1140.114 11110%0%100%100%0.1200.1200.1200.1200.1200.120 最小方最小方 差组合差组合 34%34%66%66%0.0170.0170.0870.0870.1120.112 优于组优于组 合合1 的的 组合组合 X和YX和Yi i股票的相关资料股票的相关资料 投资比重投资比重 X和Yi股票组成的投资组合X和Yi股票组成的投资组合 不同相关系数下投资组合的标准差不同相关系数下投资组合的标准差 期望收益率 8.0% 10.0% 12.0% 14.0% 16.0% 0.000.050.100.150.20 期望收益率 风险 （标准差） X和Yi股票组成的投资组合机会集 r=-1 r=-0.25 r=0.25 r=1 期望收益率期望收益率 P P （r（rXY4 XY4=1） =1） R RP P 1 1 0.1800.18014.00%14.00% 0.1740.17413.60%13.60% 0.1680.16813.20%13.20% 0.1620.16212.80%12.80% 0.1560.15612.40%12.40% 0.1500.15012.00%12.00% 0.1440.14411.60%11.60% 0.1380.13811.20%11.20% 0.1320.13210.80%10.80% 0.1260.12610.40%10.40% 0.1200.12010.00%10.00% 0.1410.141 不同相关系数下投资组合的标准差不同相关系数下投资组合的标准差 r=-1 r=-0.25 r=0.25 r=1 KA 10%10% A 12%12% KB 15%15% B 20%20% rA , B-0.5-0.5-0.5-0.5 组合组合 WAWBKP P P3 P 1 1100%100%0%0%10.00%10.00%12.00%12.00%12.00%12.00%12.00%12.00%12.00%12.00% 2 280%80%20%20%11.00%11.00%8.35%8.35%8.35%8.35%8.35%8.35%8.35%8.35% 3 360%60%40%40%12.00%12.00%7.63%7.63%7.63%7.63%7.63%7.63%7.63%7.63% 4 450%50%50%50%12.50%12.50%8.72%8.72%8.72%8.72%8.72%8.72%8.72%8.72% 5 540%40%60%60%13.00%13.00%10.46%10.46%10.46%10.46%10.46%10.46%10.46%10.46% 6 620%20%80%80%14.00%14.00%14.95%14.95%14.95%14.95%14.95%14.95%14.95%14.95% 7 70%0%100%100%15.00%15.00%20.00%20.00%20.00%20.00%20.00%20.00%20.00%20.00% 8 845%45% 0.550.5512.75%12.75%9.53%9.53%0.095270.09527 0.095270.09527 0.095270.09527 最小方最小方 差组合差组合 79%79%21%21%11.07%11.07%8.18%8.18%8.18%8.18%8.18%8.18%8.18%8.18% 优于组优于组 合合1 的的 组合组合 12% 14% 16% 收益率 8% 10% 0%5%10%15%20%25% 风险 （标准差） 机会集 -0.5-0.51 P P P 12.00%12.00%12.00%12.00%12.00%12.00% 8.35%8.35%8.35%8.35%13.60%13.60% 7.63%7.63%7.63%7.63%15.20%15.20% 8.72%8.72%8.72%8.72%16.00%16.00% 10.46%10.46%10.46%10.46%16.80%16.80% 14.95%14.95%14.95%14.95%18.40%18.40% 20.00%20.00%20.00%20.00%20.00%20.00% 0.095270.09527 0.095270.095270.1640.164 8.18%8.18%8.18%8.18%13.71%13.71% r=-1 r=-0.5 r=-0.2 r=0 r=0.2 r=0.5 r=1 A、B间连线 三项资产的风险资产组合 E(rE(rA)=)=10.00% A=20.00%covcovAB=-0.03AB=-0.5000 E(rE(rB)=)=25.00% B=30.00%covcovBC=0.01BC=0.0667 E(rE(rC)=)=40.00% C=50.00%covcovAC=-0.02AC=-0.2000 MVA59%MVB32%MVC9% E(rE(rMV)=)=17.50% MV=11.09% MA48%MB36%MC16% E(rE(rM)=)=20.20% M=12.24% RPRP15.20% SHARPESHARPE1.2414 A30%B30%C40% covcovPM=2.10% beta=beta=1.4034 E(rE(rP)=)=26.33% E(rE(rP)=)=26.50% ABCE(r)E(r) 100%0%0%10.00%20.00% 90%10%0%11.50%16.70% 80%20%0%13.00%14.00% 70%30%0%14.50%12.29% 60%40%0%16.00%12.00% 50%50%0%17.50%13.23% 40%60%0%19.00%15.62% 30%70%0%20.50%18.73% 20%80%0%22.00%22.27% 10%90%0%23.50%26.06% 0%100%0%25.00%30.00% 90%0%10%13.00%17.69% 80%10%10%14.50%14.56% 70%20%10%16.00%12.21% 60%30%10%17.50%11.14% 50%40%10%19.00%11.70% 40%50%10%20.50%13.71% 30%60%10%22.00%16.64% 20%70%10%23.50%20.10% 10%80%10%25.00%23.85% 0%90%10%26.50%27.78% 80%0%20%16.00%17.09% 70%10%20%17.50%14.53% 60%20%20%19.00%12.96% 任意组合： 可能组合： r rF= 最小方差组合： 市场组合： 50%30%20%20.50%12.77% 40%40%20%22.00%14.00% 30%50%20%23.50%16.34% 20%60%20%25.00%19.39% 10%70%20%26.50%22.87% 0%80%20%28.00%26.61% 70%0%30%19.00%18.36% 60%10%30%20.50%16.61% 50%20%30%22.00%15.91% 40%30%30%23.50%16.37% 30%40%30%25.00%17.92% 20%50%30%26.50%20.30% 10%60%30%28.00%23.26% 0%70%30%29.50%26.61% 60%0%40%22.00%21.17% 50%10%40%23.50%20.17% 40%20%40%25.00%20.10% 30%30%40%26.50%20.95% 20%40%40%28.00%22.63% 10%50%40%29.50%24.96% 0%60%40%31.00%27.78% 50%0%50%25.00%25.00% 40%10%50%26.50%24.58% 30%20%50%28.00%24.92% 20%30%50%29.50%26.00% 10%40%50%31.00%27.73% 0%50%50%32.50%30.00% 40%0%60%28.00%29.46% 30%10%60%29.50%29.44% 20%20%60%31.00%30.07% 10%30%60%32.50%31.29% 0%40%60%34.00%33.05% 30%0%70%31.00%34.31% 20%10%70%32.50%34.58% 10%20%70%34.00%35.40% 0%30%70%35.50%36.72% 20%0%80%34.00%39.40% 10%10%80%35.50%39.89% 0%20%80%37.00%40.84% 10%0%90%37.00%44.64% 0%10%90%38.50%45.30% 0%0%100%40.00%50.00% 40% 50% 0% 10% 20% 30% 40% 0%10%20%30%40%50%60% 系列1 5.00% 资产平均收益的方差50% 两资产的平均协方差10% 资产个数组合的标准差 171% 255% 348% 445% 542% 641% 740% 839% 938% 1037% 1137% 1237% 1336% 1436% 1536% 1635% 1735% 1835% 1935% 2035% 2135% 2234% 2334% 2434% 2534% 10032% 0% 10% 20% 30% 40% 50% 60% 70% 80% 01020304050 风险 （标准差） 投资组合中资产项数与风险的关系 60708090100 110 资产项数 投资组合中资产项数与风险的关系 年份 市场组合 个股SUMMARY OUTPUT 199817.69%29.96% 1999-15.62%-1.36%回归统计 2000-25.12% -32.71%Multiple R 200138.45%17.10%R Square 200226.35%39.44%Adjusted R Square 2003-5.21%0.55%标准误差 观测值 方差分析 回归分析 残差 总计 Intercept X Variable 1 年份XySUMMARY OUTPUT 11.50%1.80% 21.00%-0.50%回归统计 30.00%2.00%Multiple R 4-2.00%-2.00%R Square 54.00%5.00%Adjusted R Square 63.00%5.00%标准误差 观测值 方差分析 回归分析 残差 总计 y = 0.867x + 0.0355 R2 = 0.7102 -40% -30% -20% -10% 0% 10% 20% 30% 40% 50% -50% -40% -30% -20% -10%0%10%20%30%40%50% Intercept X Variable 1 y = 1.1836x + 0.004 R = 0