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

《实验四-收益与风险与EXCEL金融计算.pdf》由会员分享，可在线阅读，更多相关《实验四-收益与风险与EXCEL金融计算.pdf（8页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、.1/8 实验四 收益与风险与 EXCEL 金融计算 实验四容：运用 Excel 进行股票投资以与投资组合收益率与风险的计算；知识准备 理论知识：课本第三章 收益与风险，第四章 投资组合模型，第五章 CAPM 实验参考资料：金融建模使用 EXCEL 和 VBA电子书 第三章，第四章，第五章 实验项目容 请打开参考金融建模使用 EXCEL 和 VBA电子书 第三章相关章节（3.3,3.4,3.5）完成以下实验 1.单只股票收益率与风险的计算 A打开数据文件“实验四组合的回报与风险.xls”选择“1 单个股票回报与风险计算实例”子数据表格；B期望收益的计算（完成表格中黄色标记的单元格的计算）；(1

2、)计算股票的每月收益率,在 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).2/8（3）计算年度股票的年收益率方差与标准差，在 G6 单元格

3、定义=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 单元格定义=D74*D71+E74*E71 在 D80 单元格

4、定义=D742*D72+2*D74*E74*D77+E742*E72 在 D81 单元格定义=D81(1/2).3/8 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.组合协方差矩阵的计算 先选定协方差矩阵单元格区域（如以下图一）

5、，然后定义为=D142:I147*TRANSPOSE(C136:H136)*C136:H136，（如以下图二），输入完需同按 Ctrl+Shift+Enter（如以下图三）.4/8 3.组合收益率样本均值与样本方差的计算 定义单元格 D157 为=MMULT(C134:H134,J150:J155)输入完需同按 Ctrl+Shift+Enter 定义单元格 D158 为=MMULT(MMULT(TRANSPOSE(J150:J155),D150:I155),J150:J155)输.5/8 入完需同按 Ctrl+Shift+Enter 定义单元格 D159 为=SQRT(D158)4.组合投资分

6、散风险的作用是不是十分明显？5.(选作)参考前面实验已完成的“实验四组合的回报与风险.xls”，尝试自己收集你感兴趣的 3-6 只股票的原始数据完成：A单只股票收益与风险的计算；B多支股票组合收益与风险的计算。实验项目原理 参考资料实验参考材料的 Doc 文件 实验项目步骤与结果 1.单只股票收益率与风险的计算 A打开数据文件“实验四组合的回报与风险.xls”选择“1 单个股票回报与风险计算实例”子数据表格；B期望收益的计算（完成表格中黄色标记的单元格的计算）；(1)计算股票的每月收益率,在 C3 单元格定义=(B3-B4)/B4 如以下图 （2）计算股票的月期望收益率，在 F5 单元格定义=

7、AVERAGE($C$3:$C$62)（3）计算股票的年收益率，在 G5 单元格定义=F5*12 C、方差与标准差的计算 在 EXCELL 中方差，样本方差，标准差，样本标准差分别用 VAR、STEDV。可以通过.6/8 EXCELL 中的工具栏fx/统计。(1)计算月度股票收益率的方差,在 F6 单元格定义=VAR($C$3:$C$62)（2）计算月度股票收益率的标准差，在 F7 单元格定义=SQRT(F6)（3）计算年度股票的年收益率方差与标准差，在 G6 单元格定义=F6*12*12 在 G7 单元格定义=F7*12 D通过比较中国股市数据和美国股市数据总结收益与风险的关系 中国股市预期

8、收益和方差都比美国股市高，说明中国股市收益高，风险大，美国股市收益低，风险小。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 单元格定义=D742*D72+2*D74*E

9、74*D77+E742*E72 在 D82 单元格定义=D81(1/2).7/8 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.组合协方差矩阵的计算

10、 先选定协方差矩阵单元格区域，然后定义为=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 .8/8 定义单元格 D159 为=SQRT(D158)4.组合投资分散风险的作用是不是十分明显？由以上数据可知组合投资分散风险的作用十分明显。实验项目结论与心得 通过实验，我学会了通过计算收益率，方差，标准差以与风险系数来研究股票的价值与风险。通常收益越大，风险越大。组合投资可以分散风险。教师评语与评分