实验四-收益与风险与EXCEL金融计算.doc

实验四 收益与风险与EXCEL金融计算
实验四内容:运用Excel进行股票投资以及投资组合收益率与风险得计算;
【知识准备】
理论知识:课本第三章 收益与风险,第四章 投资组合模型,第五章 CAPM
实验参考资料:《金融建模—使用EXCEL与VBA》电子书 第三章,第四章,第五章
【实验项目内容】
请打开参考《金融建模—使用EXCEL与VBA》电子书 第三章相关章节(3、3,3、4,3、5)完成以下实验
1、单只股票收益率与风险得计算
A.打开数据文件“实验四组合得回报与风险、xls”选择“1单个股票回报与风险计算实例”子数据表格;
B.期望收益得计算(完成表格中黄色标记得单元格得计算);
(1)计算股票得每月收益率, 在C3单元格定义=(B3-B4)/B4如下图
(2)计算股票得月期望收益率,在F5单元格定义=AVERAGE($C$3:$C$62)
(3)计算股票得年收益率,在G5单元格定义=F5*12

C、方差与标准差得计算
在EXCELL中方差,样本方差,标准差,样本标准差分别用VAR、STEDV。可以通过EXCELL中得工具栏[fx]/[统计]。
(1)计算月度股票收益率得方差, 在F6单元格定义=VAR($C$3:$C$62)
(2)计算月度股票收益率得标准差,在F7单元格定义=SQRT(F6)
(3)计算年度股票得年收益率方差与标准差,在G6单元格定义=F6*12*12
在G7单元格定义=F7*12
D.通过比较中国股市数据与美国股市数据总结收益与风险得关系
2、两资产股票收益率与风险得计算
A.打开数据文件“实验二之一组合得回报与风险、xls”选择“2两资产组合回报与风险计算实例”子数据表格;
B、组合协方差得计算,在D77单元格定义=COVAR(E5:E64,F5:F64)*12

C、组合相关系数得计算,在D78单元格定义=CORREL(E5:E64,F5:F64)
D、组合收益率样本均值与样本方差得计算
在D78单元格定义=(D73+E73)/2
在D79单元格定义=D74D71+E74*E71
在D80单元格定义=D74^2*D72+2*D74*E74*D77+E74^2*E72
在D81单元格定义=D81^(1/2)
E、注意比较两组样本得相关系数
F、试总结组合相关系数与投资组合分担风险得关系
2、多资产股票收益率与风险得计算
A.打开数据文件“实验二之一组合得回报与风险、xls”选择“3多资产组合回报与风险计算实例”子数据表格;
B.组合相关系矩阵数得计算
D142单元格定义为
=CORREL(INDEX($C$68:$H$127,0,$B142),INDEX($C$68:$H$127,0,D$140))
其她类似不需自己实验
C组合协方差矩阵得计算
先选定协方差矩阵单元格区域(如下图一),
然后定义为=D142:I147*TRANSPOSE(C136:H136)*C136:H136,(如下图二),
输入完需同按Ctrl+Shift+Enter(如下图三)

3、组合收益率样本均值与样本方差得计算
定义单元格D157为=MMULT(C134:H134,J150:J155) 输入完需同按Ctrl+Shift+Enter
定义单元格D158为=MMULT(MMULT(TRANSPOSE(J150:J155),D150:I155),J150:J155) 输入完需同按Ctrl+Shift+Enter
定义单元格D159为=SQRT(D158)
4、组合投资分散风险得作用就是不就是十分明显？
5、(选作)参考前面实验已完成得“实验四组合得回报与风险、xls”,尝试自己收集您感兴趣得3-6只股票得原始数据完成:
A.单只股票收益与风险得计算;
B.多支股票组合收益与风险得计算。
【实验项目原理】
参考资料实验参考材料得Doc文件
【实验项目步骤与结果】
1、单只股票收益率与风险得计算
A.打开数据文件“实验四组合得回报与风险、xls”选择“1单个股票回报与风险计算实例”子数据表格;
B.期望收益得计算(完成表格中黄色标记得单元格得计算);
(1)计算股票得每月收益率, 在C3单元格定义=(B3-B4)/B4如下图
(2)计算股票得月期望收益率,在F5单元格定义=AVERAGE($C$3:$C$62)
(3)计算股票得年收益率,在G5单元格定义=F5*12

C、方差与标准差得计算
在EXCELL中方差,样本方差,标准差,样本标准差分别用VAR、STEDV。可以通过EXCELL中得工具栏[fx]/[统计]。
(1)计算月度股票收益率得方差, 在F6单元格定义=VAR($C$3:$C$62)
(2)计算月度股票收益率得标准差,在F7单元格定义=SQRT(F6)
(3)计算年度股票得年收益率方差与标准差,在G6单元格定义=F6*12*12
在G7单元格定义=F7*12
D.通过比较中国股市数据与美国股市数据总结收益与风险得关系
中国股市预期收益与方差都比美国股市高,说明中国股市收益高,风险大,美国股市收益低,风险小。
2、两资产股票收益率与风险得计算
A.打开数据文件“实验二之一组合得回报与风险、xls”选择“2两资产组合回报与风险计算实例”子数据表格;
B、组合协方差得计算,在D77单元格定义=COVAR(E5:E64,F5:F64)*12

C、组合相关系数得计算,在D78单元格定义=CORREL(E5:E64,F5:F64)
D、组合收益率样本均值与样本方差得计算
在D79单元格定义=(D73+E73)/2
在D80单元格定义=D74*D71+E74*E71
在D81单元格定义=D74^2*D72+2*D74*E74*D77+E74^2*E72
在D82单元格定义=D81^(1/2)
E、注意比较两组样本得相关系数
组合1得相关系数大,组合2得相关系数小
F、试总结组合相关系数与投资组合分担风险得关系
相关系数大,投资组合分担分险小
2、多资产股票收益率与风险得计算
A.打开数据文件“实验二之一组合得回报与风险、xls”选择“3多资产组合回报与风险计算实例”子数据表格;
B.组合相关系矩阵数得计算
D142单元格定义为
=CORREL(INDEX($C$68:$H$127,0,$B142),INDEX($C$68:$H$127,0,D$140))
其她类似不需自己实验
C、组合协方差矩阵得计算
先选定协方差矩阵单元格区域,
然后定义为=D142:I147*TRANSPOSE(C136:H136)*C136:H136,
输入完需同按Ctrl+Shift+Enter

3、组合收益率样本均值与样本方差得计算
定义单元格D157为=MMULT(C134:H134,J150:J155) 输入完需同按Ctrl+Shift+Enter
定义单元格D158为=MMULT(MMULT(TRANSPOSE(J150:J155),D150:I155),J150:J155) 输入完需同按Ctrl+Shift+Enter
定义单元格D159为=SQRT(D158)

4、组合投资分散风险得作用就是不就是十分明显？
由以上数据可知组合投资分散风险得作用十分明显。
【实验项目结论与心得】
通过实验,我学会了通过计算收益率,方差,标准差以及风险系数来研究股票得价值与风险。通常收益越大,风险越大。组合投资可以分散风险。
【教师评语与评分】