证券实验四 收益与风险与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进行金融建模和分析的教程第一章：Excel基础知识Excel是一种广泛应用于金融领域的电子表格软件，它具备强大的数据处理和分析功能。

在进行金融建模和分析之前，我们首先需要掌握一些Excel的基础知识。

1.1 Excel界面介绍Excel的界面由菜单栏、工具栏、工作区和状态栏组成。

菜单栏和工具栏提供了各种操作选项，工作区用于显示电子表格，而状态栏显示当前的工作状态和一些常用功能选项。

1.2 基本操作技巧在Excel中，我们可以通过鼠标和键盘进行各种操作。

例如，选中单元格、拖动边界调整列宽或行高、插入和删除行列等。

同时，使用快捷键可以更快地完成操作，例如Ctrl+C复制、Ctrl+V 粘贴等。

1.3 数据输入和格式化在Excel中，可以直接在单元格中输入文本、数字和公式。

为了使数据更加易读和易于分析，我们可以对单元格进行格式化，例如设置数字格式、文字格式、日期格式等。

第二章：金融建模2.1 构建现金流量表在金融建模中，现金流量表是一个十分重要的工具。

通过构建现金流量表，我们可以对企业的现金流量进行分析和预测。

2.1.1 建立数据表首先，在Excel中新建一个工作表，将现金流量的各项数据按照时间顺序输入到不同的列中。

例如，第一列为日期，第二列为经营活动的现金流量，第三列为投资活动的现金流量，第四列为筹资活动的现金流量。

2.1.2 计算净现金流量在现金流量表的最后一列，我们可以通过Excel的公式功能计算每个时间段的净现金流量。

净现金流量等于经营活动的现金流量减去投资活动的现金流量再加上筹资活动的现金流量。

2.1.3 绘制现金流量曲线通过选中净现金流量的数据，我们可以使用Excel的绘图功能，绘制出现金流量曲线图。

这有助于我们更直观地了解现金流量的变化趋势和规律。

2.2 构建财务模型财务模型可以帮助我们预测企业的未来财务状况，并进行风险分析和决策支持。

2.2.1 设置输入变量在财务模型中，我们需要设置一些输入变量，例如销售增长率、成本比例、利息费用等。

实验报告证券投资分析锦梅3学院名称理学院专业班级统计学1班提交日期评阅人 ____________评阅分数 ____________证券实验四收益与风险与EXCEL金融计算【实验目的】理解资产组合收益率和风险的计算方法，熟练掌握收益率与风险的计算程序；【实验条件】1、个人计算机一台，；2、Excel软件。

【知识准备】理论知识：课本第三章收益与风险实验参考资料：《金融建模—使用EXCEL和VBA》电子书第三章，3.1,3.2【实验项目容】运用Excel进行股票投资以及投资组合收益率与风险的计算；【实验项目原理】总结证券的收益与风险的计量：简单投资组合的投资收益风险计算：• 投资组合的收益率等于成分资产收益率按其比例加权平均：11221np n n i ii r r r r r ωωωω==+++=∑• 两项资产组成的投资组合，其方差的计算公式是： • 三项资产的投资组合的方差计算公式是：()()()22222cov 2cov 2cov pA AB BC C A BAB A C AC B C BCσωσωσωσωωωωωω=+++++【实验项目步骤与结果】1.单只股票收益率与风险的计算A ．打开数据文件“实验四组合的回报与风险.xls ”选择“1单个股票回报与风险计算实例”()()2222cov pA AB B A B ABσωσωσωω=++子数据表格；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在G7单元格定义=sqrt（F7）D．通过比较中国股市数据和美国股市数据总结收益与风险的关系答：通过比较发现中国股市数据的月度预期收益（1.73%）比美国股市数据（0.65%）大，与此同时月度的方差（1.147%）和标准差也比美国(0.38%)的大，说明中国的总体收益和风险都比美国大。

如何利用Excel进行投资组合分析和风险评估投资组合分析和风险评估是投资者在进行投资决策时必不可少的工具和方法之一。

而Excel作为一款功能强大、操作简便的电子表格软件，可以帮助投资者进行投资组合分析和风险评估。

本文将详细介绍如何利用Excel进行投资组合分析和风险评估。

第一章：数据准备和导入投资组合分析和风险评估所涉及的数据通常包括资产收益率、协方差矩阵等。

首先，需要将相应的数据准备好并导入Excel中。

可以使用Excel中的数据导入功能，将数据从外部文件（如.csv、.txt）导入到Excel中。

第二章：计算资产收益率和协方差矩阵在Excel中，可以使用函数来计算资产的收益率和协方差矩阵。

假设有n个资产，那么可以使用Excel的AVERAGE函数计算每个资产的平均收益率，使用COVARIANCE.S函数计算资产之间的协方差，使用VAR.S函数计算资产的方差。

通过这些计算，可以得到一个n*n的协方差矩阵。

第三章：计算投资组合的预期收益率和风险在进行投资组合分析时，需要计算投资组合的预期收益率和风险。

预期收益率可以通过资产的权重和各资产的预期收益率的加权平均来计算。

在Excel中，可以使用SUMPRODUCT函数来实现这个计算。

风险可以使用投资组合的方差或标准差来衡量。

在Excel中，可以使用COVAR函数计算投资组合的协方差，使用STDEV函数计算投资组合的标准差。

第四章：计算投资组合的有效边界有效边界是指在给定风险下，可以获得最大预期收益的投资组合。

在Excel中，可以使用Solver插件来求解有效边界。

首先需要在Excel中构建一个目标函数和一系列约束条件，然后通过调整资产的权重来使目标函数最大化。

通过这样的方式，就可以得到有效边界上的一系列投资组合。

第五章：风险评估和资产配置在选择投资组合时，需要进行风险评估和资产配置。

在Excel 中，可以通过计算投资组合的风险收益比、夏普比率等指标来评估风险。

实验四组合收益风险计算指导书一、计算单证券、两证券的收益风险已知股票A和股票B连续12个月的收盘价如图所示，假定在此期间没有支付任何股利，试求每种股票的月收益率、期望收益率、标准差，以及它们之间的协方差和相关系数。

如果某个投资组合由30％的股票A和70％的股票B构成，试求该投资组合的期望收益率和标准差。

（1）在单元格D4中输入公式＝“(B4-B3)/B3, 并将其复制到单元格E4，计算股票A和股票B第1月份的收益率。

（2）选取单元格区域D4：E4，将其往下一直填充复制到单元格区域D15：E15，计算股票A和股票B其他各月的收益率。

（3）在单元格G4中输入公式“＝C9/B9”, 计算A股票的变差系数（4）在单元格G5中输入公式“=AVERAGE(D4:D15)”，并将其复制到单元格H4，得到股票A和股票B月收益率的期望值。

（5）在单元格G6中输入公式“=COVAR（D4:D15,E4:E15）”，计算股票A和股票B收益率之间的协方差。

（6）在单元格G7中输入公式“=CORREL（D4:D15,E4:E15）”或输入公式“=G6/（G5*H5）”，计算股票A和股票B 收益率之间的相关系数。

（7）在单元格G12中输入公式“=SUMPRODUCT（G4:H4,G11:H11）”，计算投资组合的期望收益率。

（8）在单元格G14中输入公式“=SQRT（（G11*G5）∧2+（H11*H5）∧2+2*G11*H11*G6）”，计算投资组合的标准差。

二、计算两种风险资产（risky portfolio）最优组合试计算（例2）:已知A资产的预期收益率为20%，标准差为10%；B资产的预期收益率为25%，标准差为20%；A、B两项资产在投资组合中的比重均为50%。

试分别计算：（a）A、B两项资产之间的相关系数分别为1、0.5、0、-0.5和-1时，投资组合的期望收益率和标准差。

（b）以标准差为X轴，以期望收益率为Y轴，画出A、B两项资产不同投资组合下的期望收益率和标准差之间的关系曲线。

Excel公式在金融投资分析中的应用Excel是一种功能强大且广泛使用的电子表格软件，它不仅可以进行数据的输入和整理，还可以进行复杂的计算和数据分析。

在金融投资分析中，Excel公式的应用十分重要，可以提高投资决策的准确性和效率。

本文将介绍一些常用的Excel公式，并探讨它们在金融投资分析中的具体应用。

一、基本数学和统计公式1. SUM函数：SUM函数是最基本的数学公式之一，用于计算一组数值的总和。

在金融投资分析中，我们经常需要计算投资组合的总价值或者某个时间段内的收益总和，SUM函数可以很方便地完成这样的计算。

2. AVERAGE函数：AVERAGE函数用于计算一组数值的平均值。

在金融投资分析中，我们可以使用AVERAGE函数计算某个证券的历史平均收益率，以评估其长期表现。

3. MAX和MIN函数：MAX函数用于找出一组数值中的最大值，而MIN函数用于找出最小值。

在金融投资分析中，我们可以使用MAX和MIN函数来确定股票价格的历史最高点和最低点，以及寻找最佳买入和卖出时机。

4. STDEV函数：STDEV函数用于计算一组数据的标准差。

在金融投资分析中，标准差可以衡量市场波动的程度，帮助投资者评估风险和预测未来收益的可能范围。

二、利率和复利公式1. 简单利息计算：在金融投资分析中，我们经常需要计算简单利息的收入。

可以使用以下公式计算简单利息：简单利息 = 本金 ×年利率 ×投资期限2. 复利计算：复利是指按照一定的利率将利息再投资，从而实现利息的复利增长。

在金融投资分析中，复利是一种常用的投资增长模型。

以下是复利计算的常用公式：复利收益 = 本金 × (1 + 年利率)^投资期限 - 本金三、现值和未来值公式1. 投资现值计算：现值是指未来收益在当前时间的价值，可以通过使用Excel的PV函数进行计算。

以下是计算投资现值的公式：现值 = 未来收益 / (1 + 年利率)^投资期限2. 未来值计算：未来值是指当前投资在未来某个时间点的价值，可以通过使用Excel的FV函数进行计算。

EXCEL金融计算实验指导《金融学》实验指导手册EXCEL 金融计算南京审计学院金融学院前言本实验指导手册为金融学院《金融学》、《证券投资学》课程配套书。

该实验指导手册侧重于培养学生应用《金融学》、《证券投资学》课程所学的基本原理，利用EXCEL软件为计算工具，分析各种金融工具的风险与收益能力。

全手册共分三部分。

第一部分复习现值、终值、年金等财务概念，了解EXCEL工具环境与常用财务函数的使用，关于债券、股票等原生工具的定价，利用久期模型分析债券利率风险。

第二部分关于现代投资理论构造资产组合，通过计算加深对有效前沿、资本市场线和证券市场线等概念的理解。

第三部分关于衍生工具的定价方法，二项式期权模型定价、布莱克-斯科尔斯期权定价模型的EXCEL实现与VBA编程计算。

实验一更有效地使用EXCEL实验目的与要求：熟悉EXECL的运行环境，掌握数据导入方法，掌握图表与数据透视表的使用，了解常用函数的功能与使用方法。

实验指导：一、概念释义：1、数据输入与运算在Excel环境下进行计算．所需要的数据大致有3种来源：手工输入、自动生成和从外部导入。

当数据量很小而且又没有规律的情况下,一般采用手工输入的方法。

而对于那些有规律的数据，如连续的数字或字符序列可以用Excel的填充命令自动生成。

当数据量比较大或有现成的数据来源．如网络资源、数据库资源等可供应用，可以采用导入的方法。

Excel可以读入不同来源、不同格式的多种数据文件。

执行Excel上的“文件” ”打开”命令，就可以直接读人数据库文件、Web文件、XML文件、文本文件以及其他格式的电子表格文件等。

对于连接在网络《局域网、广域网或Web)上的计算机，这些文件可以保存在网络上的任何位置——只要使用者具有访问权限都可以接读入Excel。

在读入非Excel格式文件时，Excel会自动将文件转换成为工作表格式。

对于某些格式的数据，在进行这种格式转换时，可能需要用户做出一些选择，如在读入文本文件(．txt、．rtf)时。

实验报告证券投资分析李锦梅3学院名称理学院专业班级统计学1班提交日期评阅人____________评阅分数____________证券实验四收益与风险与EXCEL金融计算【实验目的】理解资产组合收益率和风险的计算方法，熟练掌握收益率与风险的计算程序；【实验条件】1、个人计算机一台，；2、Excel软件。

【知识准备】理论知识：课本第三章收益与风险实验参考资料：《金融建模—使用EXCEL和VBA》电子书第三章，3.1,3.2【实验项目内容】运用Excel进行股票投资以及投资组合收益率与风险的计算；【实验项目原理】总结证券的收益与风险的计量：简单投资组合的投资收益风险计算：•投资组合的收益率等于成分资产收益率按其比例加权平均：11221np n n i ii r r r r r ωωωω==+++=∑• 两项资产组成的投资组合，其方差的计算公式是： •三项资产的投资组合的方差计算公式是：()()()22222cov 2cov 2cov pA AB BC C A BAB A C AC B C BCσωσωσωσωωωωωω=+++++【实验项目步骤与结果】1.单只股票收益率与风险的计算A ．打开数据文件“实验四组合的回报与风险.xls ”选择“1单个股票回报与风险计算实例”()()2222cov p A A B B A B ABσωσωσωω=++子数据表格；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在G7单元格定义=sqrt（F7）D．通过比较中国股市数据和美国股市数据总结收益与风险的关系答：通过比较发现中国股市数据的月度预期收益（1.73%）比美国股市数据（0.65%）大，与此同时月度的方差（1.147%）和标准差也比美国(0.38%)的大，说明中国的总体收益和风险都比美国大。

实验报告证券投资分析学院名称专业班级提交日期2013年12月5评阅人____________评阅分数____________证券实验二 证券投资组合，CAPM 检验与EXCEL 金融计算 【实验目的】1、理解资产组合收益率和风险的计算方法，熟练掌握收益率与风险的计算程序；2、进一步理解最优投资组合模型，并据此构建多项资产的最优投资组合；3、进一步理解资本资产定价模型（CAPM ）的基本思想以及在股票分析中的应用。

【实验条件】1、个人计算机一台，预装Windows 操作系统和浏览器；2、计算机通过局域网形式接入互联网；3、统计软件或者Excel 软件。

第一部分实验内容：运用Excel 进行股票投资以及投资组合收益率与风险的计算；【知识准备】理论知识：课本第三章 收益与风险，第四章 投资组合模型，第五章 CAPM 实验参考资料：《金融建模—使用EXCEL 和VBA 》电子书 第三章，第四章，第五章【实验项目内容】1.单只股票收益率与风险的计算2.两资产股票收益率与风险的计算3.组合收益率样本均值与样本方差的计算4.组合投资分散风险的作用是不是十分明显？第二部分实验内容：运用Excel 进行最优投资组合的求解； 【知识准备】理论知识：课本第三章 收益与风险，第四章 投资组合模型，第五章 CAPM 实验参考资料：《金融建模—使用EXCEL 和VBA 》电子书 第三章，第四章，第五章 【实验项目内容】1．.打开2组合优化模型。

Xls 文档，翻到“用规划求解计算最优组合”子数据表；2．调用规划求解功能进行求解。

点击“工具”在下拉菜单点击“规划求解”，如没有此选项说明需要加载规划求解后才能使用，如何加载见实验补充文档“EXCEL 规划求解功能的安装”。

【实验项目原理】投资组合是指将资金配置在一种以上的证券上，获取在风险可控下最大化回报的投资方式。

1、两种证券组合的收益和风险假设有两种证券A 、B ，投资者将资金按照A X 、B X 的比例构建证券组合，则该证券组合的收益率可以表示为：P A A B BR X R X R =+，其中A X +B X =12、两种证券组合的预期收益率：()()()P A A B B E R X E R X E R =+两种证券组合的预期收益率是两种证券预期收益率的加权平均，权数是投资在该证券上的资金比例。

读书笔记：EXCEL计算资产收益与风险（一）5.4.1 EXCEL计算资产收益与风险01之前我们学习了收益与风险之间的关系。

我们讲了单个资产的收益率和风险怎么测量，两个资产组合在一起，它的收益和风险有什么样的关系。

还把风险和收益的关系扩展到n种资产，在这种情况下，如何去寻求最优化的投资组合。

最后得到了非常有名的马科维茨的资产组合理论。

但同时也强调，这个理论在当初刚提出的时候运用起来是非常麻烦的，因为随着资产数的增加，所需要估计的参数是以平方级的比例增加的。

不过目前好在技术的发展，这种运算量已经不能算得上是限制。

这节课,我们就来讲一讲怎么用Excel来实现单个资产收益率和方差的计算。

这里需要额外说明的是，Excel 可能是一种比较简便和直观展示的软件，但它运用起来其实还不太方便,尤其是当你需要批量处理的时候。

所以如果大家感兴趣, 一方面，可以去学一学Excel的编程VBA，或者去学习一些其他金融机构用的比较多的软件，如Matlab、Python、 R 等等。

这门课的操作中我尽量使用手动操作，尽可能不涉及过于复杂的编程技巧。

在开始本次课之前，同学们需要做的一个准备,就是去下载相关的资产的收益率数据。

尽管大家可以直接下载本次课所用的数据，但我还是建议大家自己手动去下载数据。

鉴于时间关系，我这里就不具体讲如何下载数据了。

稍微提一下，可以考虑的数据来源渠道有WIND、CSMAR、各类炒股软件、雅虎财经等。

本次课下载了被市场上普遍认为是白马股的几个公司，它们分别是格力电器、恒瑞医药、贵州茅台、伊利股份这4只股票。

数据区间是从2005年1月至2019年8月的月度数据。

下面来讲如何计算单个资产的期望收益和标准。

(一)期望收益率的计算首先计算4个股票的期望收益率。

之前讲过，资产的期望收益率等于未来不同状态下的加权平均。

这意味着得去预测未来不同经济状态资产收益率，以及发生的概率。

显然这是比较困难的。

实际上还有一种方式可以选择，那就是可以通过某种资产历史收益率来估计未来的期望收益率。

开发经验６１Excel在证券投资决策中的应用潍坊学院证券投资是企业投资的重要组成部分，科学地进行证券投资管理，能增加企业收益，降低风险，有利于财务管理目标的实现。

张敏次幂计算。

Ｅｘｃｅｌ函数ＰＶ（ｒａｔｅ，ｎｐｅｒ，ｐｍｔ，ｆｖ，ｔｙｐｅ）返回固定期间间隔的等额收付款项的现值。

ｒａｔｅ为各期利率，ｎｐｅｒ为收付款期数，ｐｍｔ为各期应收付的固定金额，ｆｖ为未来值，ｔｙｐｅ为０（可省略）或１，用以指定各期的付款时间是在期初还是期末。

如果按复利计息，则债券估价一般公式为“ＰＶ（市场利率，期数，面值＊票面利率，面值，０）”。

当折价发行对债券估计时，将ＰＶ中的参数ｒａｔｅ设为０。

当利随本清且按单利计息时，将ＰＶ中的参数ｐｍｔ设为０，ｆｖ为“面值＊ｆｒｅｑｕ－ｅｎｃｙ，ｂａｓｉｓ）用于计算定期付息债券的收益率，其中ｓｅｔｔｌｅｍｅｎｔ是在发行日之后，购买债券的日期，ｍａｔｕｒｉｔｙ为债券的到期日。

ｒａｔｅ为债券年息票利率，ｐｒ为面值￥１００的债券的价格，ｒｅｄｅｍｐｔｉｏｎ为面值￥１００的债券清偿价格，ｆｒｅｑｕｅｎｃｙ为年付息次数，ｂａｓｉｓ为日计数类型，一般为１。

如果首期付息日或末期付息日不固定，则可以使用ＯＤＤＦＹＩＥＬＤ、ＯＤＤＬＹＩＥＬＤ函数计算收益率。

例如：某公司２００１年２月１日以９２４１计算证券投资收益率１．１短期证券收益率短期证券期限短，一般不考虑时间价值因素，按单利计息，收益率公式是：收益率＝（售价－购价＋收益额）／购价。

在Ｅｘｃｅｌ中只要输入相关数据及公式即可求得。

例如，某人于２００５年２月１日以９００元购买一张面值为１０００元的债券，票面（１＋票面利率＊期数）”。

因此，债券估价可每年２月１日付息一次，债券分为两类：计复利的债券估价和计单利的元购入一张面值１０００元，票面利率６％，利率为９％，按面值收回本金，债券估价。

每年付息一次的债券，并于２００６年２月１到期日２００６年１月３１，日以９５０元的市价售出。