实验报告
证券投资分析
李锦梅
201130980113
学院名称理学院
专业班级统计学1班
提交日期
评阅人____________
评阅分数____________
证券实验四收益与风险与EXCEL金融计算
【实验目的】
理解资产组合收益率和风险的计算方法，熟练掌握收益率与风险的计算程序；【实验条件】
1、个人计算机一台，；
2、Excel软件。
【知识准备】
理论知识：课本第三章收益与风险
实验参考资料：《金融建模—使用EXCEL和VBA》电子书第三章，3.1,3.2
【实验项目内容】
运用Excel进行股票投资以及投资组合收益率与风险的计算；
【实验项目原理】
总结证券的收益与风险的计量：
简单投资组合的投资收益风险计算：
• 投资组合的收益率等于成分资产收益率按其比例加权平均：
• 两项资产组成的投资组合，其方差的计算公式是： • 三项资产的投资组合的方差计算公式是：
【实验项目步骤与结果】
1.单只股票收益率与风险的计算
A ．打开数据文件“实验四组合的回报与风险.xls ”选择“1单个股票回报与风险计算实例”
11221
n
p n n i i
i r r r r r ωωωω==++
+=∑()()()2
2
2
22cov 2cov 2cov p A A B B C C A B
AB A C AC B C BC
σωσωσωσωω
ωωωω=+++
++()()2
2
2
2cov p
A A
B B A B AB
σ
ωσωσωω=++
子数据表格；
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
在G7单元格定义=sqrt（F7）
D．通过比较中国股市数据和美国股市数据总结收益与风险的关系
答：通过比较发现中国股市数据的月度预期收益（1.73%）比美国股市数据（0.65%）大，与此同时月度的方差（1.147%）和标准差也比美国(0.38%)的大，说明中国的总体收益和风险都比美国大。
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.注意比较两组样本的相关系数
F.试总结组合相关系数与投资组合分担风险的关系
答：组合1（JDEUX与FFNOX）相关性为0.99，接近1，由其构成的组合几乎没有风险分散效应。
组合2（麦当劳与美国银行）股票回报的相关性（0.3）较小，组合的风险分散效应明显。
3.多资产股票收益率与风险的计算
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.组合投资分散风险的作用是不是十分明显？
答：组合投资分散风险的作用很明显，单个资产的收益率最高为6.04%，而投资组合的预期回报为44.5552%。
以组合形式投资，被称为分散化。
分散化可以降低风险，因为组合中各项资产价格的变动，在方向上往往不一致，即使方向一致，涨跌幅也会不同，这种差别会产生对冲效应，从而降低组合回报相对于单项资产回报的波动率。
【实验项目步骤与结果】
实验步骤见实验内容
【实验项目结论与心得】
1、巩固了资产组合收益率和风险的计算方法；
2、学会了用excel进行股票投资以及投资组合收益率与风险的计算；
3、学习了资产组合相关系数与分担风险的关系。
【教师评语与评分】。