实验四 收益与风险与EXCEL金融计算
使用Excel进行投资组合分析和风险管理
使用Excel进行投资组合分析和风险管理1. 引言投资组合分析和风险管理是金融领域中重要的主题之一。
在投资过程中，投资者需要选择合适的资产组合，通过分析和管理风险来提高收益和降低风险。
Excel是一种功能强大的工具，可以帮助投资者进行投资组合分析和风险管理。
2. 投资组合建立在使用Excel进行投资组合分析之前，首先需要建立一个投资组合。
投资者可以通过Excel创建一个包含各种不同资产的投资组合。
首先，列出不同的资产，并给出它们的预期收益率和风险水平。
然后，通过在Excel中创建一个投资组合工作表，将各种资产组合起来，赋予它们不同的配比。
最后，计算出整个投资组合的预期收益率和风险水平。
3. 投资组合分析使用Excel可以进行多种投资组合分析。
首先，可以通过计算投资组合的期望收益率和方差来评估投资组合的风险和回报。
通过Excel的函数，例如AVERAGE和VAR，可以轻松计算这些指标。
其次，可以使用散点图和线性回归分析来进行风险和回报之间的关联性分析。
通过Excel的数据分析工具包，可以方便地进行这些分析。
4. 风险管理在投资组合分析过程中，风险管理起着至关重要的作用。
Excel 可以帮助投资者进行风险度量和风险控制。
首先，使用Excel的函数，例如STDEV和CORREL，可以计算出资产的标准差和相关系数，从而量化资产的风险。
其次，可以使用Excel的条件格式和图表功能来进行风险可视化，以便更直观地理解和管理风险。
最后，可以使用Excel的内置的求解器工具来进行投资组合的最优化，以在给定风险水平下获得最大收益或最小风险。
5. 实例分析为了更好地理解如何使用Excel进行投资组合分析和风险管理，下面将通过一个实例来说明。
假设投资者有三类资产：股票、债券和黄金。
通过Excel的数据处理和分析函数，他们可以计算出每种资产的预期收益率和标准差，并通过线性回归分析衡量它们之间的相关性。
然后，他们可以通过Excel的条件格式和图表功能，将风险可视化，并使用Excel的求解器工具计算出最优的资产配置。
Excel金融计算专业教程(ppt 33页)
– 从菜单中选取“插入→函数……”命令； – 点击编辑区左侧的“fx”标志； – 点击工具栏上的自动求和标志∑； – 按[Shift]+[F3]。
在某些情况下，可能需要将某函数作为另一函数的参数使 用，这称为嵌套函数。公式可包含多达七级的嵌套函数。
BESSELJ(贝塞尔函数), HEX2BIN(16进制到2进制), IMAGINARY(复数的虚部系数), …… NPV(净现值), FV(净现值), INTRATE(利率), YIELD(收益), ……
-8-
1.1.2 数据运算与引用
Excel中引用单元格的方法分为：A1引用和R1C1引用。前 者是用字母数字组合标识引用单元格的绝对位置；后者表示对 当前选中单元格相对位置的引用。
Excel 金融计算专业教程
第1章 计算工具EXCEL
——更有效地使用电子表软件
本章总结了Excel中一些很重要但又容易为人们所忽视的 使用技巧和运算工具，特别是强调了本书后续内容里经 常用到的一些方法。
1.1 数据输入与运算
• 1.1 数据输入与运算
– 1.1.1 数据的导入 – 1.1.2 数据运算与引用 – 1.1.3 排序与筛选
当需要引用位于其他工作表或其他文件中的数据时，就要 使用三维引用。三维引用的基本格式是：
[spreadsheet]sheet!A1 其中，前面的方括号“[ ]”内是要引用的文件名，如果源数 据在同一文件内则可省略；跟在文件名后面的是工作表的名称， 如果同时引用多个工作表相同位置的数据，则可以使用冒号“:” 标识工作表范围，如Sheet1:Sheet3，注意工作表名后面要跟有 叹号“!”；最后是引用的单元格或单元格区域的名称，如A1、 B2或C3:D6等等。
基于excel的金融学原理
基于excel的金融学原理Excel是一款功能强大的电子表格软件，可以用于进行各种金融学计算和分析。
下面将介绍一些基于Excel的金融学原理，涉及到投资组合理论、资本资产定价模型和衍生品定价等内容。
1.投资组合理论投资组合理论是金融学中的一个基本原理，用于确定在给定风险下的最佳投资组合。
这个理论的核心概念是通过资产之间的相关性来实现风险的分散，从而使投资组合风险最小化。
Excel可以用来计算投资组合的风险和预期收益，并通过求解最小方差组合、有效边界等方法来确定最佳投资组合。
2.资本资产定价模型资本资产定价模型（CAPM）是用于计算资产预期回报的一个模型。
这个模型表明资产的回报是由市场回报和个别风险之间的正相关关系决定的。
Excel可以用来计算资产的预期回报，并通过β系数来度量个别风险对总风险的贡献。
通过CAPM模型，可以评估一个投资项目的风险和回报，并决定是否值得进行投资。
3.衍生品定价衍生品是一种基于基础资产的金融工具，其价值是从基础资产中派生出来的。
Excel可以用来计算各种衍生品的定价，包括期权、期货和利率互换等。
在衍生品定价中，Black-Scholes模型和Binomial模型是最常用的定价模型。
Excel提供了一系列的函数和工具，可以帮助进行衍生品的定价计算。
4.统计分析在金融学中，统计分析是一个重要的工具。
Excel提供了各种统计函数和工具，可以用于计算和分析金融数据的统计指标，如均值、标准差、相关系数等。
通过统计分析，可以帮助了解金融市场的特征和行为。
5.金融建模金融建模是基于数学和统计的金融学方法，用于对金融市场和金融产品进行建模和分析。
Excel可以用来构建各种金融模型，如股票价格模型、利率模型和风险模型等，并进行模拟和分析。
通过金融建模，可以帮助预测金融市场的走势和评估金融产品的风险。
总结起来，Excel作为一种功能强大的工具，可以用于实施各种金融学原理，包括投资组合理论、资本资产定价模型和衍生品定价等。
实验四收益与风险与EXCEL金融计算
实验四收益与风险与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*12C、方差与标准差的计算在EXCELL中方差，样本方差，标准差，样本标准差分别用VAR、STEDV。
可以通过EXCELL中的工具栏[fx]/[统计]。
(1)计算月度股票收益率的方差,在F6单元格定义=VAR($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)*12C.组合相关系数的计算，在D78单元格定义=CORREL(E5:E64,F5:F64)D.组合收益率样本均值与样本方差的计算在D78单元格定义=(D73+E73)/2在D79单元格定义=D74*D71+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．多支股票组合收益与风险的计算。
投资组合管理资产配置与收益 Excel 表格
投资组合管理资产配置与收益 Excel 表格投资组合管理是投资者根据自身风险偏好和收益目标，将投资资金分配给不同资产类别的过程。
在投资组合管理中，资产配置被认为是最重要的决策之一，它决定了投资组合中不同资产类别的权重分配。
为了更好地进行资产配置和实时跟踪投资组合的收益情况，Excel 表格成为了金融领域中常用的工具之一。
Excel 表格可以有效地帮助投资者管理投资组合并进行资产配置。
通过使用 Excel 表格，投资者可以将投资资金分配给不同的资产类别，并根据资产的预期收益、风险和相关性来计算和优化资产配置。
以下是利用 Excel 表格进行资产配置与收益管理的步骤：1. 建立资产类别：在 Excel 表格中，首先需要建立资产类别列表。
可以按照投资者的需求和偏好，将不同资产类别（如股票、债券、房地产等）列举并安排在表格中的一列中。
2. 输入投资资金：在 Excel 表格中的另一列中，输入投资者准备分配给各个资产类别的资金量。
可以根据投资者的实际情况进行输入。
3. 计算权重和比例：在 Excel 表格中，可以使用数学函数将每个资产类别的资金量转化为权重或比例。
根据投资者的需求，可以选择计算资金量占总投资资金的比例或计算资产类别的权重。
4. 输入预期收益和风险：在 Excel 表格中的另两列中，输入各个资产类别的预期收益和风险。
这些数据可以通过研究分析、历史数据或专业机构提供的信息来获取。
5. 计算资产相关性：在 Excel 表格中的另一部分，可以计算各个资产类别之间的相关性。
相关性反映了不同资产之间的联动情况，对于投资组合的风险管理和多样化非常重要。
6. 优化资产配置：利用 Excel 提供的优化函数或插件，可以根据预期收益、风险和相关性等因素，通过数学模型计算出最优的资产配置方案。
投资者可以根据自身需求和投资目标，选择最适合自己的资产配置解决方案。
7. 实时跟踪投资组合收益：利用 Excel 表格中的公式和函数，可以实时计算和跟踪投资组合的收益情况。
在Excel中进行金融建模和分析的方法
在Excel中进行金融建模和分析的方法第一章：Excel的基本功能1.1. 单元格和公式在Excel中，单元格是最基本的组成单位，用于存储数据。
我们可以通过在单元格中输入公式实现各种数学运算、逻辑判断和文本处理等功能。
公式可以使用基本的四则运算符、函数和引用其他单元格的内容。
1.2. 数据筛选和排序Excel提供了强大的筛选和排序功能，可以帮助我们快速定位和筛选需要的数据。
通过设置筛选条件，可以快速找到符合要求的记录。
同时，我们还可以对数据进行升序或降序排序，以便更好地进行分析和比较。
第二章：金融模型的建立2.1. 基本的财务模型财务模型是金融分析的基础，主要用于预测和分析企业的财务状况和经营能力。
在Excel中，我们可以通过建立各种财务模型，如财务报表模型、财务比率模型等，来对企业进行全面的财务分析。
2.2. 资产定价模型资产定价模型是金融建模的重要内容，用于估计资产的内在价值。
在Excel中，我们可以使用CAPM（Capital Asset Pricing Model）等模型，计算资产的预期收益率，并进行风险评估和投资组合优化。
2.3. 期权定价模型期权是金融衍生品中常见的一种，其价值受到多种因素的影响。
通过使用Black-Scholes期权定价模型，我们可以在Excel中计算期权的理论价格，为投资者和交易员提供相应的参考和决策依据。
第三章：金融数据分析3.1. 数据导入和清洗金融数据通常来自各种来源，如证券交易所、财经新闻网站等。
在Excel中，我们可以通过导入外部数据功能，将这些数据导入到工作表中进行分析。
同时，为了确保数据的准确性和一致性，我们还需要进行数据清洗、去重和格式化处理等。
3.2. 数据可视化数据可视化是金融数据分析的重要手段之一，能够直观地展示数据的变化趋势和关联关系。
在Excel中，我们可以使用各种图表类型，如折线图、柱状图和散点图等，对金融数据进行可视化展示，帮助我们更好地理解和解释数据。
《Excel金融操作 PPT课件》
固定收益工具
如何仅使用Excel估值固定收 益工具的复合价格和收益率
股票估值模型
如何使用Excel估值股票，并 使用不同的分析获取预测市 场价格。
Excel金融操作的技巧和注意事项
1 熟悉快捷键
2 注意数据源
在Excel金融中，快捷键 可大幅提高生产效率， 戳此查看快捷键
输入多单元格时，经常 容易出现手误。一定要 核对数据源，以免造成 严重后果。
汇率计算
汇率波动是金融中的一个重要主题。在Excel里 使用金融函数, 能够帮助你了解汇率的变化对各 种投资产品带来的影响。
Excel在金融行业的应用场景
数据清洗和整理
大量数据是金融工作者最常面对的挑战之一。 Excel 强大的数据清洗和整理工具，能够帮 助你轻松应对。
计算收益和回报
Excel的金融函数使计算复杂的收益和回报 变得简单。它能够估计投资的风险和回报， 为投资者制定计划提供可靠的帮助。
1
了解金融计算
学会如何处理投资组合、风险及回报
掌握数据处理的技巧
2
等金融计算。
学会如何使用Excel处理大量的金融数
据。
3ቤተ መጻሕፍቲ ባይዱ
掌握金融建模技巧
学会如何使用Excel金融模型对市场趋 势进行分析和预测。
Excel金融操作课程的内容
常用金融函数
学习各种函数并能够熟练应用，包括Net Present Value, Internal Rate of Return和现值计 算等。
管理投资组合
学习如何评估投资组合的性能、回报和风险。
资产估值
学习如何对股票、债券等资产进行估值。
统计数据分析
学习使Excel成为数据分析工具，成为精通金融 建模所需的技巧。
金融风险实验
实验一 隐含波动率的计算1.实验目的利用Black-Scholes 期权定价公式模型的Excel计算模板，计算隐含波动率。
2.基本原理隐含波动率是根据观察的期权市场价格，通过B-S 期权定价模型计算出波动率.B-S 模型定价模型下，看涨期权的定价公式如下: 12()()rt CSN d XeN d其中 21ln (0.5)Sr tX d t, 21d d t 。
式中：C 为看涨期权的价值；S 为标的资产的当前价格；X 为期权的执行价格；t 为距期权到期日的时间；r 为无风险利率；2为以连续复利计算的标的资产年收益对数的方差。
1()N d 、2()N d 为在正态分布下，随机变量小于1d 、2d 的累计概率。
看跌期权的定价公式为： 12()()rt PSN d Xe N d利用B-S 期权定价模型确定期权价值的步骤如下： （1）计算1d 和2d ；（2）计算1()N d 、2()N d 或1()N d 、2()N d ；（3）计算看涨期权或看跌期权的价值。
B-S 期权定价公式中六个变量，它们彼此关联，只要知道其中五个就可以计算出剩余的一个。
在已知期权价值的情况下，要计算其它几个变量中的某个变量，可以利用单变量求解工具或规划求解工具。
3.实验数据与内容已知目前的股票价格为40元，年收益率的标准差为35%，年无风险利率为8%，期权的执行价格为35元，还有6个月到期，要求： （1）建立看涨期权、看跌期权的价值计算模板；（2）假设股票价格为20，年无风险收益不变，期权的执行价格为25元，剩余时间不变，期权的目标价值为4元，计算期权的隐含波动率。
4.操作步骤与结果（1）建立期权看涨期权、看跌期权的价值计算模板。
（1.1）右键点击窗口上端空白处，选中“窗体”，在出现的窗体中选择“组合框”窗体控件，在单元格B8位置上插入一个“组合框”控件。
点击右键，出现下拉菜单后选择“设置控件格式”，在“控件”对话框中，进行设置。
读书笔记：EXCEL计算资产收益与风险（一）
读书笔记：EXCEL计算资产收益与风险（一）5.4.1 EXCEL计算资产收益与风险01之前我们学习了收益与风险之间的关系。
我们讲了单个资产的收益率和风险怎么测量，两个资产组合在一起，它的收益和风险有什么样的关系。
还把风险和收益的关系扩展到n种资产，在这种情况下，如何去寻求最优化的投资组合。
最后得到了非常有名的马科维茨的资产组合理论。
但同时也强调，这个理论在当初刚提出的时候运用起来是非常麻烦的，因为随着资产数的增加，所需要估计的参数是以平方级的比例增加的。
不过目前好在技术的发展，这种运算量已经不能算得上是限制。
这节课,我们就来讲一讲怎么用Excel来实现单个资产收益率和方差的计算。
这里需要额外说明的是，Excel 可能是一种比较简便和直观展示的软件，但它运用起来其实还不太方便,尤其是当你需要批量处理的时候。
所以如果大家感兴趣, 一方面，可以去学一学Excel的编程VBA，或者去学习一些其他金融机构用的比较多的软件，如Matlab、Python、 R 等等。
这门课的操作中我尽量使用手动操作，尽可能不涉及过于复杂的编程技巧。
在开始本次课之前，同学们需要做的一个准备,就是去下载相关的资产的收益率数据。
尽管大家可以直接下载本次课所用的数据，但我还是建议大家自己手动去下载数据。
鉴于时间关系，我这里就不具体讲如何下载数据了。
稍微提一下，可以考虑的数据来源渠道有WIND、CSMAR、各类炒股软件、雅虎财经等。
本次课下载了被市场上普遍认为是白马股的几个公司，它们分别是格力电器、恒瑞医药、贵州茅台、伊利股份这4只股票。
数据区间是从2005年1月至2019年8月的月度数据。
下面来讲如何计算单个资产的期望收益和标准。
(一)期望收益率的计算首先计算4个股票的期望收益率。
之前讲过，资产的期望收益率等于未来不同状态下的加权平均。
这意味着得去预测未来不同经济状态资产收益率，以及发生的概率。
显然这是比较困难的。
实际上还有一种方式可以选择，那就是可以通过某种资产历史收益率来估计未来的期望收益率。
Excel金融计算精品教程
g’n = ln(an+1/an) 例如，要根据股票价格逐日计算其利得收益率(利得是由于 股票价格变动而产生的收益)就可以采用后一种方法(当然也可以 采用前一种方法)
• 5个最基本的函数：
– PV()函数——现值 – FV()函数——终值 – RATE()函数——利率 – PMT()函数——每期现金流量 – NPER()函数——期数，它们分布在时间线上
• 使用这些各函数时要注意其参数：
– pmt参数作为每期发生的现金流量，在整个年金期间其值保持不变； – type = 0或省略表示各期现金流量发生在期末，即普通年金；type
-6-
2.2 多重现金流量
• 2.1 终值和现值
• 2.4 年金的深入讨论
– 2.1.1 复利与终值
– 2.4.1 年金计算的代数原理
– 2.1.2 贴现与现值
– 2.4.2 递增年金
– 2.1.3 在Excel中计算复利和贴现 • 2.5 计息期与利率
• 2.2 多重现金流量
– 2.5.1 名义利率与有效利率
– 2.3.1 普通年金
– 2.3.2 预付年金
– 2.3.3 永续年金
– 2.3.4 Excel中的年金计算函数
-23-
2.5.1 名义利率与有效利率
对于给定的年利率，只要在1年内计算复利，就必须考虑实 际利率的差别。这时，给定的年利率称为名义利率(rnom，或 APR ) ，用名义利率除以每年内的计息次数得到的是期利率(rper)， 而根据实际的利息与本金之比计算的利率称为有效利率(EAR)。
-9-
2.3 年金的计算
EXCEL金融计算实验指导
EXCEL金融计算实验指导《金融学》实验指导手册EXCEL 金融计算南京审计学院金融学院前言本实验指导手册为金融学院《金融学》、《证券投资学》课程配套书。
该实验指导手册侧重于培养学生应用《金融学》、《证券投资学》课程所学的基本原理，利用EXCEL软件为计算工具，分析各种金融工具的风险与收益能力。
全手册共分三部分。
第一部分复习现值、终值、年金等财务概念，了解EXCEL工具环境与常用财务函数的使用，关于债券、股票等原生工具的定价，利用久期模型分析债券利率风险。
第二部分关于现代投资理论构造资产组合，通过计算加深对有效前沿、资本市场线和证券市场线等概念的理解。
第三部分关于衍生工具的定价方法，二项式期权模型定价、布莱克-斯科尔斯期权定价模型的EXCEL实现与VBA编程计算。
实验一更有效地使用EXCEL实验目的与要求：熟悉EXECL的运行环境，掌握数据导入方法，掌握图表与数据透视表的使用，了解常用函数的功能与使用方法。
实验指导：一、概念释义：1、数据输入与运算在Excel环境下进行计算．所需要的数据大致有3种来源：手工输入、自动生成和从外部导入。
当数据量很小而且又没有规律的情况下,一般采用手工输入的方法。
而对于那些有规律的数据，如连续的数字或字符序列可以用Excel的填充命令自动生成。
当数据量比较大或有现成的数据来源．如网络资源、数据库资源等可供应用，可以采用导入的方法。
Excel可以读入不同来源、不同格式的多种数据文件。
执行Excel上的“文件” ”打开”命令，就可以直接读人数据库文件、Web文件、XML文件、文本文件以及其他格式的电子表格文件等。
对于连接在网络《局域网、广域网或Web)上的计算机，这些文件可以保存在网络上的任何位置——只要使用者具有访问权限都可以接读入Excel。
在读入非Excel格式文件时，Excel会自动将文件转换成为工作表格式。
对于某些格式的数据，在进行这种格式转换时，可能需要用户做出一些选择，如在读入文本文件(．txt、．rtf)时。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
实验四收益与风险与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单元格定义=D74*D71+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.组合投资分散风险的作用是不是十分明显？
由以上数据可知组合投资分散风险的作用十分明显。
【实验项目结论与心得】
通过实验，我学会了通过计算收益率，方差，标准差以及风险系数来研究股票的价值与风险。
通常收益越大，风险越大。
组合投资可以分散风险。
【教师评语与评分】。