无风险资产与两种风险资产投资组合的期望收益率与标准差(ECXEL图表绘制)
利用Excel建立项目投资动态盈亏平衡分析模型
利用Ｅｘｃｅｌ建立项目投资动态盈亏平衡分析模型河北科技师范学院杜茂宝威兆川王晓勤一、投资项目动态盈亏平衡分析原理投资项目的盈亏平衡分析是指根据投资项目产销量、成本和利润之间的关系，计算出投资项目不亏损不盈利的产销量及相关指标，从而判断项目经营风险大小的方法。
根据是否考虑资金的时间价值因素，可以将盈亏平衡分析分为静态分析和动态分析两种。
所谓动态盈亏平衡分析是指在考虑资金时间价值因素的条件下进行的盈亏平衡分析。
动态盈亏平衡点的产销量实际上也就是使投资项目的净现值或净年值为零时的产销量。
在投资项目寿命期内各年的产销量相等、固定资产按直线法计提折旧的情况下，动态盈亏平衡点的年销售量Q 的计算公式为：式中，P为产品单价，V为产品单位变动成本，F为付现固定成本，I为初始投资，Z为投资项目预计残值收入，C为固定资产折旧，T为所得税税率。
下面的公式中符号的经济含义与上述相同。
盈亏平衡点的销售额的计算公式为(S)：S=q·P安全边际量(M Q)：M Q=Q0-Q，式中，Q0为计划或现有产销量。
安全边际额(M S)：M S=S0-S，式中，S0为计划或现有产销额。
安全边际率(M R)：M R=M Q/Q0=M S/S0盈亏平衡点的产销量和销售额越低，或者安全边际量、安全边际额、安全边际率越高，说明投资项目的经营风险越小，经营上的安全程度越高，反之亦然。
二、项目投资动态盈亏平衡分析模型的建立与应用项目投资决策具有投资额大、期限长、风险高等特点。
企业为降低项目投资的经营风险，提高决策的科学性，一般要进行风险性分析，而动态盈亏平衡分析是有效方法之一。
进行盈亏平衡分析，需要进行综合考虑、反复计算，依靠人工测算不仅繁琐，而且重复计算，这将严重影响动态盈亏分析法的应用，但利用计算机，通过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的求解器工具计算出最优的资产配置。
风险的市场价格
ERp
CML
M
O
Rf
p
资本市场线
资本市场线（CML） 2.资本市场线方程
m 市场平均的风险价格（Price of Risk） 风险溢价 借助资本市场线方程，我们可以对所有有 效组合的期望收益率进行计算，即，在无 风险利率基础上加上组合的风险乘以市场 平均风险价格得到的组合风险溢价。
M
T
N
O
图4.7引入无风险借贷后的最佳组合
分离定理 1.最优风险组合 T点是一个纯粹由风险资产构成的投资组合， 既没有无风险资产也没有运用无风险借贷 的资金，也就是说引入无风险借贷前后没 有发生任何变化的一个组合，由此可见该 组合与有效边界上的其他组合相比具有一 定的特殊性，体现在两个方面： 第一是不变性。引入无风险借贷前后没有 发生任何变化的一个组合。 第二是唯一性。由切点的唯一性决定 。
无风险贷出---有效集发生变化
ERp
A T
B O
引入无风险贷出后的可行集
无风险贷出---有效集发生变化 由于无风险资产的引 入，原来在风险资产 ER 有效边界上的有效投 资组合都可以与无风 险资产重新构建组， 因此，使得投资组合 的选择区域也发生了 相应变化。 ER CAL线段的斜率？ 夏普指数
1/2
p ( X X 2 X1 X 21,2 ) X1 1
2 2 1 1 2 2 2 2
2 0,1,2 0
包含无风险资产的两种资产组合 o点表示收益为的无风险 资产，A点风险资产。这 两个资产构成的组合分布 在OA直线上，比如B点， 它的期望收益是两资产期 望收益的加权平均，但是 标准差却只是风险资产标 准差乘以其对应的权重。
如何利用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 中，可以通过计算投资组合的风险收益比、夏普比率等指标来评估风险。
风险资产与无风险资产PPT课件
[E(rP ) rf
P
]
C
由该式得到图6- 4可行投资组合(investment opportunity set)
3-6
记险风资险产收收益益率率为为rrPf。，P的期望收益率为E(rP)，标准差为P。无风
在风。下险面收的益数率字rf=例7%子。中因，此我，们风假险定资，产E的(rP风) =险1溢5%价，为PE=(rP2)-2r%f=，8%无 由y份风险资产与（ 1-y）份无风险资产组成的整个资产组合，
3-16
表 无差异曲线的电子数据表计算
3-17
图 无差异曲线 for
U = .05 and U = .09 with A = 2 and A = 4
3-18
图 用无差异曲线寻找最优的投资组合
3-19
风险爱好者的CAL
E(r)
与借方相比，贷方具有更大 的风险厌恶系数。
P
借方
7% 贷方
p = 22%
3-1
第3章
风险资产与无风险资产 之间的资本配置
3-2
风险与无风险资产组合的资本配置
控制资产组合风险的方法：
- 部分投资于无风险资产，部分投资于风险资产 - 无风险资产：代表委托书；国库券 - 风险资产：股票(或股票组合)
- 记风险资产组合为P，无风险资产组合为F，风险资产在整个 组合中的比重为y，则我们可通过y的调整来调整组合风险
3-7
重新整理,
因此，资产组合的期望收益作为其标准 差的函数是一条直线，截距为rf，斜率如 下：
3-8
图 风险资产与无风险资产的
可行投资组合
3-9
投资机会集合（ the investment opportunity set），即由不同y 值产生的所有资产组合的可能期望收益与标准方差配对的 集合。其图形是由rf点引出，穿过P点的直线。
投资组合管理资产配置与收益 Excel 表格
投资组合管理资产配置与收益 Excel 表格投资组合管理是投资者根据自身风险偏好和收益目标，将投资资金分配给不同资产类别的过程。
在投资组合管理中，资产配置被认为是最重要的决策之一，它决定了投资组合中不同资产类别的权重分配。
为了更好地进行资产配置和实时跟踪投资组合的收益情况，Excel 表格成为了金融领域中常用的工具之一。
Excel 表格可以有效地帮助投资者管理投资组合并进行资产配置。
通过使用 Excel 表格，投资者可以将投资资金分配给不同的资产类别，并根据资产的预期收益、风险和相关性来计算和优化资产配置。
以下是利用 Excel 表格进行资产配置与收益管理的步骤：1. 建立资产类别：在 Excel 表格中，首先需要建立资产类别列表。
可以按照投资者的需求和偏好，将不同资产类别（如股票、债券、房地产等）列举并安排在表格中的一列中。
2. 输入投资资金：在 Excel 表格中的另一列中，输入投资者准备分配给各个资产类别的资金量。
可以根据投资者的实际情况进行输入。
3. 计算权重和比例：在 Excel 表格中，可以使用数学函数将每个资产类别的资金量转化为权重或比例。
根据投资者的需求，可以选择计算资金量占总投资资金的比例或计算资产类别的权重。
4. 输入预期收益和风险：在 Excel 表格中的另两列中，输入各个资产类别的预期收益和风险。
这些数据可以通过研究分析、历史数据或专业机构提供的信息来获取。
5. 计算资产相关性：在 Excel 表格中的另一部分，可以计算各个资产类别之间的相关性。
相关性反映了不同资产之间的联动情况，对于投资组合的风险管理和多样化非常重要。
6. 优化资产配置：利用 Excel 提供的优化函数或插件，可以根据预期收益、风险和相关性等因素，通过数学模型计算出最优的资产配置方案。
投资者可以根据自身需求和投资目标，选择最适合自己的资产配置解决方案。
7. 实时跟踪投资组合收益：利用 Excel 表格中的公式和函数，可以实时计算和跟踪投资组合的收益情况。
Excel公式和函数 典型案例—多种风险资产的最优投资组合
Excel公式和函数典型案例—多种风险资产的最优投资组合Excel公式和函数典型案例—多种风险资产的最优投资组合在进行投资的过程中，可以根据投资组合中各项资产的投资比重，计算出所对应的投资组合的期望收益率，而根据不同投资组合的期望收益率，又可以计算出对应投资组合的标准差，将这些结果绘制成图形，即可得到多种风险资产构成的投资组合的关系曲线。
本例将利用Excel中的MMULT函数和图表功能，制作多种风险资产的最优投资组合图表。
1．练习要点● 协方差矩阵 ● 数组公式 ● 定义名称 ● 设置图表格式 2．操作步骤：（1）合并B2至N2单元格区域，输入标题文字，并设置其【字体】为“微软雅黑”；【字号】为18；【填充颜色】为“橙色，强调文字颜色6，淡色40％”如图13-65所示。
图13-65 设置标题格式（2）在B3至D9单元格区域中，创建“已知数据”数据表。
然后，选择C5至D9单元格区域，设置其【数字格式】为“百分比”；【小数位数】为1，如图13-66所示。
图13-66 设置数字格式提 示 设置B3至D4单元格区域的【填充颜色】为“水绿色，强调文字颜色5，淡色60％”。
然后，为该数据表添加边框效果。
（3）在B11至G16单元格区域中，输入各资产之间的相关系数数据，并设置B11至B16、C11至G11单元格区域的【填充颜色】为“水绿色，强调文字颜色5，淡色60％”，如图13-67所示。
设置效果显示 效果显示设置效果显示图13-67 相关系数（4）分别合并I3至N3、I4至N4单元格区域，输入相应的数据内容，如图13-68所示。
创建数据表图13-68 协方差矩阵（5）选择J6单元格，在【编辑栏】中输入“=C12*$D$5*D5”公式，并按Enter键，如图13-69所示。
输入效果显示图13-69 资产A与资产A之间的参数（6）选择K6单元格，在【编辑栏】中输入“=D12*$D$6*D5”公式，并按Enter键，如图13-70所示。
无风险资产与两种风险资产投资组合的期望收益率与标准差(ECXEL图表绘制)
简称
2010-04-21 万向钱
2010-04-22
万向钱 潮
2010-04-23 万向钱
2010-04-26 万向钱
2010-04-27 万向钱
2010-04-28 万向钱
2010-04-29 万向钱
2010-04-30 万向钱
2010-05-04 万向钱
2010-05-05 万向钱
2010-05-06 万向钱
60 -0.99% 61.12 2.19% 61.99 1.11% 60.9 -1.69% 61.66 1.48% 59.88 -1.84% 57.17 -3.59% 57.7 1.84% 59.18 3.26% 58.1 -0.99% 59.18 1.98% 62.15 4.82% 66.2 7.52% 66.5 1.11% 67.2 1.83% 66.88 -0.33% 65.63 -1.75% 65.47 -0.80%
52.36 51.2 53.9 53.5
49.77 53
54.9 55.09 56.15 57.76 57.38
55.2 55.99 56.63
57.5 57.73
58.3 57.3
58 61.4 59.99 55.89 52.99 54.44 54.93 54.2
54 52.65
50.7
47.8 -3.88% 48.41 2.78% 47.91 0.02% 50.15 6.07% 48.5 -3.44% 47.68 -0.04% 49.69 6.45% 54.66 11.82% 57.5 4.75% 56.05 0.18% 50.45 -9.23% 49.98 -1.03% 47.6 -2.96% 47.6 0.00% 52.36 6.86% 52.2 -0.31% 54.12 5.70% 53.5 -0.74% 51.39 -3.94% 53.24 6.97% 55.4 4.53% 55.38 0.87% 56.48 2.52% 57.82 2.97% 57.78 0.03%
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
66 65.45 68.09
48.79 3.81% 46.82 -2.66% 47.2 0.00% 46.22 -0.56% 47.98 3.90% 50.5 5.21% 49.7 -1.19% 50.44 0.92% 50.21 -0.57% 49.3 -0.70% 50.22 1.45%
11.21 11.05 -1.43% 登海种
11.28 10.66 -5.50% 登海种
10.66 10.85 1.78% 登海种
10.64 11.1 4.32% 登海种
10.94 11.43 4.48% 登海种
11.48 11.3 -1.57% 登海种
11 10.72 -2.55% 登海种
10.8 10.06 -6.85% 登海种
49 -0.55% 45.5 -6.13% 46.55 3.65% 48.32 3.76% 47.8 -0.42%
48 0.82% 52.59 8.86% 52.71 0.23% 52.45 -0.21% 55.4 6.33% 55.36 0.29% 55.65 1.00% 56.29 2.14% 54.51 -3.16% 55.68 2.39% 55.95 -0.27% 59.19 5.58% 62.48 4.34% 61.7 -1.12% 60.81 -1.28%
47 48.1 47.2 46.48 46.18
48 50.3 49.98 50.5 49.65 49.5 49.27 48.47 44.91 46.57
48 47.61 48.31 52.59 52.56
52.1 55.2 55.1 55.11 56.29 54.38 56.1 56.06 59.88 62.4 61.6 60.6 59.81 61.31 61.95 60.76
2010-05-24 万向钱
2010-05-25 万向钱
2010-05-26 万向钱
2010-05-27 万向钱
2010-05-28 万向钱
2010-05-31 万向钱
2010-06-01 万向钱
2010-06-02 万向钱
2010-06-03 万向钱
2010-06-04 万向钱
2010-06-07 万向钱
52.36 51.2 53.9 53.5
49.77 53
54.9 55.09 56.15 57.76 57.38
55.2 55.99 56.63
57.5 57.73
58.3 57.3
58 61.4 59.99 55.89 52.99 54.44 54.93 54.2
54 52.65
50.7
47.8 -3.88% 48.41 2.78% 47.91 0.02% 50.15 6.07% 48.5 -3.44% 47.68 -0.04% 49.69 6.45% 54.66 11.82% 57.5 4.75% 56.05 0.18% 50.45 -9.23% 49.98 -1.03% 47.6 -2.96% 47.6 0.00% 52.36 6.86% 52.2 -0.31% 54.12 5.70% 53.5 -0.74% 51.39 -3.94% 53.24 6.97% 55.4 4.53% 55.38 0.87% 56.48 2.52% 57.82 2.97% 57.78 0.03%
2010-06-28 万向钱
2010-06-29 万向钱
开盘价 (元)
收盘 价 (元)
持有期 收益率 简称 r1
11.3 12.35 9.29% 登海种
12.05 12.19
1.16%
登海种 业
12.05 11.53 -4.32% 登海种
11.57 11.65 0.69% 登海种
11.65 11.29 -3.09% 登海种
10.71 10.36 -3.27% 登海种 10.4 10.15 -2.40% 登海种 10.12 10.18 0.59% 登海种
10 10.05 0.50% 登海种 10.01 10.51 5.00% 登海种 10.56 10.93 3.50% 登海种
11 10.8 -1.82% 登海种 10.89 10.98 0.83% 登海种 11.05 11.21 1.45% 登海种 11.2 10.99 -1.87% 登海种 10.96 10.88 -0.73% 登海种 10.86 10.39 -4.33% 登海种 10.3 10.37 0.68% 登海种 10.35 10.62 2.61% 登海种 10.66 11 3.19% 登海种 10.95 11 0.46% 登海种 11.01 11.59 5.27% 登海种 11.6 11.49 -0.95% 登海种 11.5 11.6 0.87% 登海种 11.51 11.25 -2.26% 登海种 11.25 11.56 2.76% 登海种 11.56 11.94 3.29% 登海种 11.88 11.76 -1.01% 登海种 11.71 12.08 3.16% 登海种 12.03 11.65 -3.16% 登海种 11.66 11.86 1.72% 登海种 11.88 11.7 -1.52% 登海种 11.7 12.87 10.00% 登海种 12.94 12.87 -0.54% 登海种 12.81 12.21 -4.68% 登海种 12.11 12.9 6.52% 登海种 12.81 13.03 1.72% 登海种 12.95 13.13 1.39% 登海种 13.15 13.52 2.81% 登海种 13.55 13.93 2.80% 登海种 13.96 13.82 -1.00% 登海种 13.85 13.91 0.43% 登海种 13.85 13.35 -3.61% 登海种 13.3 13.42 0.90% 登海种 13.38 14.3 6.88% 登海种 14.18 14.15 -0.21% 登海种 14.27 14.6 2.31% 登海种 14.52 14.67 1.03% 登海种 14.79 15.26 3.18% 登海种 15.11 15.15 0.26% 登海种 15.11 14.66 -2.98% 登海种 14.81 15.11 2.03% 登海种 15.13 15.24 0.73% 登海种 15.03 14.53 -3.33% 登海种 14.56 14.76 1.37% 登海种 14.63 14.96 2.26% 登海种
10.73 10.78 0.47% 登海种
开盘价 (元)
收盘价 (元)
持有期 收益率 r2
46.9 48.35 3.09%
47.39 49.62 4.71%
49.73 47.1 47.9
47.28 50.23
47.7 46.68 48.88 54.89 55.95 55.58
50.5 49.05
47.6 49
2010-06-08 万向钱
2010-06-09 万向钱
2010-06-10 万向钱
2010-06-11 万向钱
2010-06-17 万向钱
2010-06-18 万向钱
2010-06-21 万向钱
2010-06-22 万向钱
2010-06-23 万向钱
2010-06-24 万向钱
2010-06-25 万向钱
2010-06-30 万向钱 2010-07-01 万向钱 2010-07-02 万向钱 2010-07-05 万向钱 2010-07-06 万向钱 2010-07-07 万向钱 2010-07-08 万向钱 2010-07-09 万向钱 2010-07-12 万向钱 2010-07-13 万向钱 2010-07-14 万向钱 2010-07-15 万向钱 2010-07-16 万向钱 2010-07-19 万向钱 2010-07-20 万向钱 2010-07-21 万向钱 2010-07-22 万向钱 2010-07-23 万向钱 2010-07-26 万向钱 2010-07-27 万向钱 2010-07-28 万向钱 2010-07-29 万向钱 2010-07-30 万向钱 2010-08-02 万向钱 2010-08-03 万向钱 2010-08-04 万向钱 2010-08-05 万向钱 2010-08-06 万向钱 2010-08-09 万向钱 2010-08-10 万向钱 2010-08-11 万向钱 2010-08-12 万向钱 2010-08-13 万向钱 2010-08-16 万向钱 2010-08-17 万向钱 2010-08-18 万向钱 2010-08-19 万向钱 2010-08-20 万向钱 2010-08-23 万向钱 2010-08-24 万向钱 2010-08-25 万向钱 2010-08-26 万向钱 2010-08-27 万向钱 2010-08-30 万向钱 2010-08-31 万向钱 2010-09-01 万向钱 2010-09-02 万向钱 2010-09-03 万向钱 2010-09-06 万向钱 2010-09-07 万向钱 2010-09-08 万向钱
57 -0.66% 57 3.26% 55.85 -0.25% 58.6 3.48% 58.7 2.09% 58.65 1.59% 57.3 -1.72% 58.01 1.24% 60.86 4.93% 59.65 -2.85% 55.9 -6.82% 53.5 -4.28% 54.6 3.04% 54.94 0.92% 54.31 -1.13% 54.22 0.04% 52.8 -2.22% 50.95 -3.23% 48 -5.33%
60 -0.99% 61.12 2.19% 61.99 1.11% 60.9 -1.69% 61.66 1.48% 59.88 -1.84% 57.17 -3.59% 57.7 1.84% 59.18 3.26% 58.1 -0.99% 59.18 1.98% 62.15 4.82% 66.2 7.52% 66.5 1.11% 67.2 1.83% 66.88 -0.33% 65.63 -1.75% 65.47 -0.80%