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

1、实验四收益与风险与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寸& Ciu弓曰号盜一怙：的1九5；.亠“ Kxjriipgiibiity Mode亠 Mkrowft EmcIgSep-19afi帕1Q Tr4-Allf-DB-2fiS7 ；P月 jaW6 295net5JuIS-SCfR国1. 7： ：2.E：r- =J-F102.15?r：ji*5i0uB豔tiJun-9lJL40*i方箜1.117h心鉛.的038=伸im is6 2T*t輪苓：c T.*-旳

3、*,9155a环&.16HnJipr-OS1J77 57S3：9069B-WK92373 21is g吐0327s.-10Fcb-DS2Cs2 sS生=ttgEC.71LJan-S乂工屿Jari-OS91.4SlC.Slfc=nDKdmo siCHft-iM115PV-OBIHTl ISB 2SNcv-CflUS 77175 B 79Qct-Mm ia7.671615Sepdt| -川口曲氐JH歯呼曲-!_ I A5v.irtty Warn ng Scnfi 聶 1% .Ionian*- FZ - Mr 和帖：底二_J p* I*randniomi timie CvV FbTfies - Af

4、tiie SilylFLDrta-r! - J*虫i武电- _3 F百叭Jrt -第気丹骑 it & nd1 b FUri SElKt -Eflfeinoi|i中mim2 吋瞪3 戸愛卷4 ixj 却熄6 淀尢尿也方蚤拒圧ah虹制逗肓笊n回拆血金毗i担询闻RHL#7X12343呦蛋帼ri护苦辽辱申”呈三一Jen鼻亦湛6*1；ZRCMQ20C56ax04t0阳Q 3-6CJl C-fiii CAS C.3S&54IOR皿 T-53H站mi%-!自谥事逖甘曲回唱屯皿甜 3*強产妣音曲用W回型刽LM 1 Rffl萨3耳妙胪3. 组合收益率样本均值与样本方差的计算定义单元格 D157为=MMULT(C

5、134: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文件【实验项目步骤与

6、结果】1.单只股票收益率与风险的计算A 打开数据文件“实验四组合的回报与风险.xls ”选择“ 1单个股票回报与风险计算实例”子数据表格；B 期望收益的计算(完成表格中黄色标记的单元格的计算)；(1) 计算股票的每月收益率，在C3单元格定义=(B3-B4)/B4如下图(2) 计算股票的月期望收益率，在F5单元格定义=AVERAGE($C$3:$C$62)(3) 计算股票的年收益率，在G5单元格定义=F5*12C、方差与标准差的计算在EXCELL中方差，样本方差，标准差，样本标准差分别用VAR STEDV可以通过EXCELL中的工具栏fx/ 统计计算月度股票收益率的方差,在F6单元格定义=VAR

7、($C$3:$C$62)(2)计算月度股票收益率的标准差，在F7单元格定义=SQRT(F6)(3)计算年度股票的年收益率方差与标准差，在G6单元格定义=F6*12*12在G7单元格定义=F7*12D. 通过比较中国股市数据和美国股市数据总结收益与风险的关系中国股市预期收益和方差都比美国股市高，说明中国股市收益高，风险大，美国股市收 益低，风险小。2两资产股票收益率与风险的计算A 打开数据文件“实验二之一组合的回报与风险.xls”选择“ 2两资产组合回报与风险计算实例”子数据表格；B.组合协方差的计算，在 D77单元格定义=COVAR(E5:E64,F5:F64)*12乙计算两资产组合的預期回报

8、和凤险D组合2方法1组合1JDEUX二 FNQX表岂劳羌国银行预期回抿2-69%3.5S?-：17.953.9996*方差2.55%2.GS：3.24%35.37 K标谁差15.97%17 99%5 9.各後产占比C5.&C.5C.5组合1组合2资产协芳差2.1823.21F彊产拥关黒数95. CU詆g賢产风险平均1S.2O54-3B.73%组合预期回抿5.1311.0%组合方差1.19-组合标谁差15.1633.6C.组合相关系数的计算，在 D78单元格定义=CORREL(E5:E64,F5:F64)D.组合收益率样本均值与样本方差的计算在D79单元格定义=(D73+E73)/2在 D80单

9、元格定义=D74*D71+E74*E71在 D81 单元格定义=D74A2*D72+2*D74*E74*D77+E74A2*E72在D82单元格定义=D81A(1/2)E. 注意比较两组样本的相关系数组合1的相关系数大，组合2的相关系数小F. 试总结组合相关系数与投资组合分担风险的关系相关系数大，投资组合分担分险小2多资产股票收益率与风险的计算A 打开数据文件“实验二之一组合的回报与风险.xls”选择“ 3多资产组合回报与风险计算实例”子数据表格；B. 组合相关系矩阵数的计算D142单元格定义为=CORREL(INDEX($C$68:$H$127,0,$B142),INDEX($C$68:$H

10、$127,0,D$140)其他类似不需自己实验C. 组合协方差矩阵的计算先选定协方差矩阵单元格区域，然后定义为=D142:I147*TRANSPOSE(C136:H136)*C136:H136,输入完需同按Ctrl+Shift+Enter轴更占皱.距匱2i5浦/银行中唁订押三mif吒爪1a 20270呻M2CI-5SPMil 濡D.5210 590:49C.46asi3申至貢全C 271D 2CC彗c悶4三一安工D.430 49Q2Q1ass50 52C旳0肚10 5360-13旨协万年护环浦冷辗行中知寿e至黄全MfT海鼻朮胡雅戈讯吐；札芟握疔0,250370.178050.110430.15

11、3410坪聘012-4270,167牛信al券：.173C50己6哼此0 327420 23732C：9692C 273360.167中樂貝金D.L1K30J2742D.EK&O.L1I55D.Ls 呂二SD.22.SB50.167丄一莹工0.237320.11SS0+97S20.237 6B0.1S337C.167C.述藝0伍30淖書西O1376B0血40亡血聲C.167D.L2+270.73 詛1:。亠订C.心眄C.lb/组台方差组佥凤船堀裁点f3. 组合收益率样本均值与样本方差的计算定义单元格 D157为=MMULT(C134:H134,J150:J155)输入完需同按 Ctrl+Shi

12、ft+Enter定义单元格 D158 为=MMULT(MMULT(TRANSPOSE(J150:J155),D150:I155),J150:J155)输入完需同按 Ctrl+Shift+Enter定义单元格 D159为=SQRT(D158)浦发银行中信证券中金黄金三一重工海蜩水泥雅戈邙1浦发银行10.520.27C.430.620.382中馆证券0 52C.59C %C.480.613中金黄金0.270.591C.2C0.390.434三一重工0.43c.-gC.2C10占右0.355海螺水泥0.52C.-SC.39C.561C.536雅戈尔C.36061C.430.35D.531方差矩阵浦发银行申值证券中金黄金三一重工海螺水泥雅丈尔权重浦lff0.25037C.17BC5D.LLC43O._53ilD.1B4&90.12427C.167中信证券O.178OS0.46946032742023732C.156920273360.167中金黄金0110430327420.646D90411550.1S8190.228630.167三一重工0.153410.237320.111550.-37920.237&8C.163370.167海據水泥0184890.196930.3594J0,20554Q.167雅戈尔0.1227C 273360.2