证券实验四 收益与风险与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进行金融建模和分析的教程
使用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 设置输入变量在财务模型中，我们需要设置一些输入变量，例如销售增长率、成本比例、利息费用等。
证券实验四收益与风险与EXCEL金融计算
实验报告证券投资分析锦梅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中。
可以使用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金融计算
2010-9-20
金融学院
4
一、概念释义： 1、数据输入与运算 2、图表与数据透视表 3、内置函数和自定义函数 4、假设分析工具
2010-9-20
金融学院
5
二、操作示例
2010-9-20
金融学院
6
实验二 货币的时间价值
实验目的与要求： 货币的时间价值是一切金融计算的基础， 在本实验中关键的概念是复利和贴现。通 过实验要求学生掌握EXCEL常用财务函 数使用。
2010-9-20
金融学院
7
一、概念释义： 1．现值 2.净现值 3.终值 4.年金
2010-9-20
金融学院
8
实验三
债券利率风险评估
实验目的与要求： 债券的主要风险为利率风险，久期和凸性是 刻画债券的两个重要特征。本实验要求熟悉 债券的久期概念，掌握久期的计算。（市场 风险、信用风险）
2010-9-20
EXCEL金融计算
金融学院
2010-9-20
1
课程简介
本实验课程重于培养学生应用《金融学》、《证券投资 学》课程所学的基本原理，利用EXCEL软件为计算工具， 分析各种金融工具的风险与收益能力。 本实验课分三部分。第一部分复习现值、终值、年 金等财务概念，了解EXCEL工具环境与常用财务函数的 使用，关于债券、股票等原生工具的定价，利用久期模 型分析债券利率风险。第二部分关于现代投资理论构造 资产组合，通过计算加深对有效前沿、资本市场线和证 券市场线等概念的理解。第三部分关于衍生工具的定价 方法，二项式期权模型定价、布莱克-斯科尔斯期权定 价模型的EXCEL实现与VBA编程计算、债券的价值与收益 2、债券的久期 3、修正久期
2010-9-20
《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成为数据分析工具，成为精通金融 建模所需的技巧。
Excel公式在金融投资分析中的应用
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函数进行计算。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
实验报告
证券投资分析
李锦梅
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、学习了资产组合相关系数与分担风险的关系。
【教师评语与评分】。