投资组合excel实现
Excel公式和函数 典型案例-多种投资的动态分析模型
Excel公式和函数典型案例－多种投资的动态分析模型投资者在进行多种投资时，往往需要对各种风险资产的期望收益率、标准差，以及它们之间的相关系数进行分析，以便了解对投资组合的影响。
此时，就可以建立一个多种风险资产投资组合的动态计算分析模型。
1．练习要点●使用控件●设置控件格式●使用MMULT函数●使用MINVERSE函数●创建图表●设置图表格式2．操作步骤：（1）分别合并B3至F3和B4至F4单元格区域，并输入标题文字和“已知数据”文字。
然后，在B5至F10单元格区域，创建“已知数据”数据表，并设置其格式，效果如图13-26所示。
图13-26 创建“已知数据”数据表提示设置第3行的【行高】为28.5；第4、5行的【行高】为18.75；第6至10行的【行高】为21。
（2）选择【开发工具】选项卡，单击【控件】组中的【插入】下拉按钮，选择【表单控件】栏中的“数值调节钮”选项，并在D6单元格中绘制该控件，如图13-27所示。
创建数据表图13-27 绘制控件注 意单击Office 按钮，并单击【Excel 选项】按钮，在弹出的对话框中，启用【在功能区显示“开发工具”选项卡】复选框。
（3）右击该控件，执行【设置控件格式】命令，在弹出的对话框中，选择【控制】选项卡，并设置【单元格链接】为$D$6，如图13-28所示。
图13-28 设置控件格式提 示 在【设置控件格式】对话框中，选择【大小】选项卡，设置控件的【高度】为“0.64厘米”；【宽度】为“2.28厘米”。
（4）使用相同的方法，在D 列和F列绘制其他的“数值调节钮”控件，并设置其单元格链接均为它们所在的单元格，如图13-29所示。
图13-29 绘制其他控件提 示 另外，用户也可以复制多个“数值调节钮”控件，并更改其单元格链接。
（5）选择C6单元格，在【编辑栏】中输入“=D6/1000”公式，并按Enter 键，即可建立资产P 的期望收益率与调节按钮的关系，如图13-30所示。
如何用Excel的规划求解功能实现一个投资组合在均值-方差法下的最优化？
如何⽤Excel的规划求解功能实现⼀个投资组合在均值-⽅差法下的最优化？⽂中的计算⽅法参考了Samir Khan的“Mean-Variance Optimization with Transaction Costs”。
⼀般来讲，⼀个投资组合中各项资产的价格变动特征是不⼀样的，⽐如有的资产价格波动率很⾼，但有可能带来更⾼的回报；⽽其他的资产会在⼤盘下跌之时反⽽上涨。
在构建投资组合时通过精⼼挑选具有不同价格波动特点的资产，就可以在确保收益最⼤化的同时实现投资组合的风险最⼩化，⽽均值-⽅差法就可以实现这⼀点。
均值-⽅差法是⼀种⽐较传统的优化投资组合的做法，来源于美国经济学家、1990年诺贝尔经济学奖获得者Harry Markowitz于1950年代创⽴的基于均值-⽅差模型现代组合投资理论。
均值-⽅差模型的理论是解决投资者如何从所有可能的证券组合中选择⼀个最优组合的问题。
投资者的决策⽬标通常有两个：尽可能⾼的收益率和尽可能低的不确定性风险。
即先确⽴⼀个⽬标收益率，然后确定各项资产在投资组合中的权重，使整个投资组合的风险值即整个组合的价格波动的⽅差值最低，最终使这两个相互制约的⽬标达到最佳平衡。
本⽂的主题就是探讨如何⽤Excel的规划求解功能实现⼀个由四只股票构成的投资组合在均值-⽅差法下的最优化，即价格波动风险最低，回报率最⾼。
由于在对现有投资组合中各项资产的⽐率进⾏调整时交易成本会成为⼀个很⼤的影响组合回报率的因素，因此为贴近实际操作，⽂中的案例考虑到了交易成本，并将资产权重每变动1%的交易成本设定为0.1%；四只股票的初始权重均为25%，投资组合的⽉度预期回报率为1%。
1、按以下格式设置Excel表格2、通过雅虎财经⽹站下载美孚⽯油公司XOM、卡特彼勒公司CAT、可⼝可乐公司KO和波⾳公司BA在2018年2⽉1⽇⾄2019年2⽉1⽇这1年间的⽉度收盘价。
3、⽤LN()函数计算4只股票的⽉度回报率，（）内为⽉度收盘价所在的单元格4、⽤AVERAGE()函数计算这四只股票⽉度回报率的均值5、形成协⽅差矩阵。
Excel 财务应用 收益最大化的投资组合问题
Excel 财务应用收益最大化的投资组合问题作为一家企业，没有投资就没有发展，投资是寻找新的盈利机会的唯一途径，也贯穿于企业经营的始终。
投资组合的目的在于分散风险，它是将资金按照一定的比例分别投资于不同种类的项目上，如房地产、债券、股票等。
投资的目的是获得更多的经济利润，因此，收益最大化是企业财务管理的最终目标。
所谓收益最大化，是指企业利润总额和全部资本之比最大，它反映了资本投入与产出之间的比例关系。
这里所说的收益最大化是一种长期的、稳定的、真实的和不损害社会利益的资本收益。
本节就利用规划求解的功能，来分析计算收益最大化的投资组合问题。
例如，某公司计划要投资三种国债，其中，每种债券每年的投资额与净现值如图9-12所示。
已知目前该公司有活动资金30万可供投资，预计1年后，又可以获得20万元，2年后可以获得另外25万元，3年后可以获得10万元。
如何在目前回报率的基础上，确定该公司能够获得最大收益的投资组合？在确定最大收益之前，首先要计算出各债券每的年累计投资额。
若要计算每年累计的投资额，需要使用SUM函数，对各债券进行分别计算，如选择C14单元格，即“国债L06512”债券第3年所需投资额对应的单元格，在【编辑栏】中输入“=SUM(C3:C6)”公式，即可得到该债券第3年的累计投资额。
再分别使用SUM函数，计算其他债券各年的累计投资额，计算结果如图9-13所示。
已知条件计算累计投资图9-12 债券投资所需的资金和净现值图9-13 每年的累计投资额根据已知的投资项目所需的投资资金以及累计投资额，可以在Excel工作表中，创建如图9-14所示的线性规划模型。
创建模型图9-14 线性规划模型由此可以看出，只有合理分配该公司在各债券中的投资比例，才能使公司获得最大收益。
其中，在该模型中，带有“茶色，背景2”填充格式的单元格区域表示该问题所要求的变量，其默认值为0。
接下来使用SUMPRODUCT函数，计算投资总计额以及目标函数值。
使用Excel进行投资组合分析与优化
使用Excel进行投资组合分析与优化在当今的投资领域，有效地管理和优化投资组合是实现长期财务目标的关键。
Excel 作为一款强大的电子表格软件，为投资者提供了便捷且实用的工具，帮助他们进行投资组合的分析与优化。
接下来，让我们深入探讨如何利用 Excel 来实现这一重要的任务。
首先，我们需要明确投资组合的概念。
投资组合简单来说，就是投资者将资金分配到不同的资产类别（如股票、债券、基金、房地产等）中，以达到分散风险和提高收益的目的。
而分析和优化投资组合的目的，就是找到最适合自己的资产配置比例，使得在可接受的风险水平下，获得最大的收益。
在 Excel 中，我们可以通过输入和整理投资数据来开始我们的分析之旅。
这些数据包括各种投资产品的历史价格、收益率、波动率等。
为了获取这些数据，我们可以从金融网站、数据库或者相关的财经报告中收集。
假设我们有以下几种投资产品：股票 A、股票 B、债券 C 和基金 D。
我们将它们的历史价格数据输入到 Excel 表格中，然后通过简单的函数计算，就可以得出它们的平均收益率和波动率。
收益率的计算可以使用“平均函数（AVERAGE）”，而波动率则可以通过计算收益率的标准差来得到，在 Excel 中可以使用“STDEV 函数”。
有了这些基础数据，我们就可以构建投资组合了。
在 Excel 中，我们可以通过假设不同的资产配置比例，来计算组合的预期收益率和风险。
例如，我们假设股票 A 占投资组合的 30%，股票 B 占 20%，债券C 占 30%，基金D 占 20%。
然后，我们使用“SUMPRODUCT 函数”来计算组合的预期收益率。
这个函数可以将每种资产的收益率乘以其在组合中的权重，然后将结果相加。
对于组合的风险（波动率），由于投资组合中不同资产之间的相关性会影响整体风险，所以计算会相对复杂一些。
但在 Excel 中，我们可以通过使用“协方差函数（COVAR）”和“方差函数（VAR）”来进行计算。
使用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实现
标准差
10.3% 10.0% 9.4% 13.7% 9.1% 10.3% 11.00% 10.45%
9.91%
每种资产
CAL描点 的期望收 最有风险 无差异曲线描 组合期望
处
益率
组合
点处
收益率
0.00% 0.10% 0.20%
0.7% 1.2% 1.3% 2.4% 2.1% 1.6%
-10.9% 14.6% -5.4% 24.6% 59.2% 17.9%
9.39% 50
国债
PA4
2.44% 50
13.74% 50
0.00% 0.00%
PA5
2.08% 50
9.09% 50
PA6
1.56% 50
10.34% 50
相关系数矩阵
PA1
PA2
PA3
PA4
PA5
PA6
PA1
100.0% 26.8%
7.8% 12.0% 23.6% 28.5%
PA2
26.8% 100.0%
100% 100% 100% 100% 100% 100% 100%
最优完全
组合权重
51.2%
512
-5.3%
7.1%
-2.6%
12.0%
28.9%
8.7%
8.2% 0.0% 82.1% 0.0% 0.5% 1.0% 1.5% 2.0% 2.5% 3.0% 3.5% 4.0% 4.5% 5.0% 6.0% 7.0% 8.0% 9.0% 10.0% 11.0% 12.0% 13.0% 14.0% 15.0%
0.30% 0.40% 0.50% 0.60% 0.70% 0.80% 0.90% 1.00% 1.10% 1.20% 1.30% 1.40% 1.50% 1.60% 1.70% 1.80% 1.90% 2.00% 2.10% 2.20% 2.30% 2.40% 2.50% 2.60% 2.70% 2.80% 2.90% 3.00%
实验五_运用Excel规划求解进行最优投资组合的求解
实验报告证券投资学院名称专业班级提交日期评阅人 ____________评阅分数 ____________实验五：运用Excel规划求解进行最优投资组合的求解【实验目的】1、理解资产组合收益率和风险的计算方法.熟练掌握收益率与风险的计算程序；2、进一步理解最优投资组合模型.并据此构建多项资产的最优投资组合；【实验条件】1、个人计算机一台.预装Windows操作系统和浏览器；2、计算机通过局域网形式接入互联网；3、matlab或者Excel软件。
【知识准备】理论知识：课本第三章收益与风险.第四章投资组合模型.第五章 CAPM实验参考资料：《金融建模—使用EXCEL和VBA》电子书第三章.第四章.第五章【实验项目内容】请打开参考《金融建模—使用EXCEL和VBA》电子书第四章相关章节（4.3）完成以下实验A．打开“实验五组合优化.xls”.翻到“用规划求解计算最优组合”子数据表；B．调用规划求解功能进行求解。
点击“工具”在下拉菜单点击“规划求解”.如没有此选项说明需要加载规划求解后才能使用.如何加载见实验补充文档“EXCEL规划求解功能的安装”。
C．D．在规划求解选项卡里面选择“选项”.再选择“非负”再运行一次.比较两次返回的投资比例值的正负。
在实验报告中记录两次得到的最优投资组合.并说明投资比例是负值说明什么？E．（选做）借助连续调用规划求解的VBA过程生成有效组合以及资本市场线。
参考实验参考电子书《金融建模—使用EXCEL和VBA》电子书第四章P83F．对比可卖空和不可卖空的有效前沿图试对比说明其不同？【实验项目步骤与结果】A.B．使用规划求解C．投资比例为负值说明该证券风险远远大于其收益率.已经不适合投资。
F．对比可卖空和不可卖空的有效前沿图试对比说明其不同？通过可卖空和不可卖空有效前沿图的对比可以看到.在相同风险的时候可卖空的情况下期望回报要比不可卖空的情况要高.并且随着风险的增加可卖空曲线的期望回报增加程度明显比不可卖空曲线要大。
通过Excel进行投资组合分析的基本方法
通过Excel进行投资组合分析的基本方法第一章：引言投资组合分析是投资者评估和管理其投资组合的过程。
在金融市场中，投资者可以选择多种不同类型的资产进行投资，如股票、债券、房地产等，以实现其投资目标。
而利用Excel进行投资组合分析是一种常见且有效的方法。
本文将介绍利用Excel进行投资组合分析的基本方法。
第二章：数据获取与整理在进行投资组合分析之前，我们首先需要获取和整理相关的数据。
对于股票投资组合分析，我们可以通过财经网站或金融数据提供商获取每只股票的历史股价数据。
对于债券或其他资产，我们可以获取其历史价格和收益率数据。
在Excel中，我们可以使用数据导入功能将获取的数据导入到工作表中或使用宏来自动化这个过程。
导入数据后，我们可以对其进行清洗和整理，包括删除重复数据、处理缺失值和异常值等。
第三章：计算资产回报率计算资产回报率是投资组合分析的重要一步。
回报率是评估投资绩效的基本指标之一。
在Excel中，我们可以使用以下公式计算资产的回报率：回报率 = (当前价格 - 初始价格) / 初始价格对于股票投资组合，我们可以逐个计算每只股票的回报率，并使用加权平均法计算整个投资组合的回报率。
在Excel中，我们可以使用SUMPRODUCT函数和相关权重数据来计算加权平均回报率。
第四章：计算投资组合风险投资组合的风险是投资者关注的另一个重要指标。
在Excel中，我们可以使用一些常见的风险指标来衡量投资组合的风险，如标准差和Beta系数。
标准差是衡量资产收益率波动性的指标。
在Excel中，我们可以使用STDEV.P函数或STDEV.S函数计算资产的标准差。
使用加权平均法，我们可以计算整个投资组合的标准差。
Beta系数衡量了一个资产与整个市场的相关性。
通过回归分析，我们可以使用Excel内置的相关函数计算出资产与市场的相关系数，并进而计算出Beta系数。
第五章：计算投资组合的最优权重计算投资组合的最优权重是投资组合分析的重要一环。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
9.39% 50
国债
PA4
2.44% 50
13.74% 50
0.00% 0.00%
PA5
2.08% 50
9.09% 50
P4% 50
相关系数矩阵
PA1
PA2
PA3
PA4
PA5
PA6
PA1
100.0% 26.8%
7.8% 12.0% 23.6% 28.5%
PA2
26.8% 100.0%
组合优 化
多种 风险 资产
资产类型
A池
B池
C池
1 3.00%
风险厌恶系数
2.8 28
预期收益率
国债
0.30% 50
PA1
0.70% 50
1
标准差 0.00%
10.31% 50
平均收益率
2.50% 2.00% 1.50% 1.00%
PA2
1.17% 50
10.00% 50
0.50%
PA3
1.26% 50
100% 100% 100% 100% 100% 100% 100%
最优完全
组合权重
51.2%
512
-5.3%
7.1%
-2.6%
12.0%
28.9%
8.7%
0.30% 0.29% 0.36% 1.07%
PA6 101.6%
Merton Model A B C
Delta Gamma
261.3979949 264.9038101 268.4783787
5.7E+00 0.365846857
国债 PA1 PA2 PA3 PA4 PA5 组PA合6 优化的有 效边际曲线描
20.0% 20.2% 19.3% 22.9%
PA3
7.8% 20.0%
100.0% 41.8% 38.5% 31.2%
PA4
12.0% 20.2%
41.8% 100.0% 37.3% 20.4%
PA5
23.6% 19.3%
38.5% 37.3% 100.0% 38.7%
Portfolio Weights
最优点的CAL CAL CAL 无差异曲线
0.0% 1000.0%
9.39% 8.89% 8.41% 7.97% 7.56% 7.19% 6.87% 6.60% 6.40% 6.26% 6.19% 6.20% 6.28% 6.43% 6.65% 6.92% 7.25% 7.63% 8.05% 8.50% 8.98% 9.48% 10.00% 10.54% 11.10% 11.67% 12.25% 12.84%
2.1% 0.3% 18.7%
0.74% 0.75% 0.77% 0.81% 0.86% 0.92% 1.00% 1.09% 1.19% 1.31% 1.44% 1.75% 2.12% 2.54% 3.01% 3.54% 4.13% 4.78% 5.48% 6.23% 7.04%
最有资产权重
4.0%
8.2% 0.0% 82.1% 0.0% 0.5% 1.0% 1.5% 2.0% 2.5% 3.0% 3.5% 4.0% 4.5% 5.0% 6.0% 7.0% 8.0% 9.0% 10.0% 11.0% 12.0% 13.0% 14.0% 15.0%
0.30% 0.40% 0.50% 0.60% 0.70% 0.80% 0.90% 1.00% 1.10% 1.20% 1.30% 1.40% 1.50% 1.60% 1.70% 1.80% 1.90% 2.00% 2.10% 2.20% 2.30% 2.40% 2.50% 2.60% 2.70% 2.80% 2.90% 3.00%
厌恶细数与 CAL的切点 0.0074
1.2%
PA4 最优P点A5的CAL
最有资产权重
PA6 PA3
PA2
PA1
10.00% 各资产组合权重
15.00%
国债
PA1
PA2
PA3
PA4
PA5
PA6
Optimal Risky Portfolio
Optimal Complete Portfolio
Ones
PA3
0.08% 0.19%
0.88% 0.54% 0.33% 0.30%
PA3 101.3%
PA4
0.17% 0.28%
0.54% 1.89% 0.47% 0.29%
PA4 102.4%
PA5
0.22% 0.17%
0.33% 0.47% 0.83% 0.36%
PA5 102.1%
PA6
0.30% 0.24%
标准差
10.3% 10.0% 9.4% 13.7% 9.1% 10.3% 11.00% 10.45%
9.91%
每种资产
CAL描点 的期望收 最有风险 无差异曲线描 组合期望
处
益率
组合
点处
收益率
0.00% 0.10% 0.20%
0.7% 1.2% 1.3% 2.4% 2.1% 1.6%
-10.9% 14.6% -5.4% 24.6% 59.2% 17.9%
最有资产权重
5.00%
标准差
各资产组合权重 70% 60% 50% 40% 30% 20% 10%
0%
PA6
28.5% 22.9%
31.2% 20.4% 38.7% 100.0%
国债
10
-10%
10
10
10
10
10
-20%
10
10
10
10
10
10
10
10
10
组合的标准差
PA1
PA2
10.3% 10.0%
PA3
PA4
PA5
PA6
9.4% 13.7% 9.1% 10.3%
协方差矩阵
PA1
PA2
PA3
PA4
PA5
PA6
国债 100.3%
PA1
1.06% 0.28%
0.08% 0.17% 0.22% 0.30%
PA1 100.7%
PA2
0.28% 1.00%
0.19% 0.28% 0.17% 0.24%
PA2 101.2%