投资组合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在金融工程中的运用 投资组合篇
金融工程实验课习题二
1. 收集任意2个股票收盘价数据，计算日、月、年收益率，及其对应的日、月、年标准差和相关系数。
2. 你正考虑投资两种证券。
证券1的预期收益率和风险分别为8%和18%。
证券2的预期收益率和风险分别为16%和26%。
两种证券的预期收益率的相关系数为0.25，国库券收益率为4%。
1）假定不允许卖空，两种证券在最小方差组合中的权重为多少？
2）假定不允许卖空，两种证券在最优风险组合中(单位风险报酬率最大)的权重为多少？
4）如果你的资金只能在两种风险资产之间配置，为了实现13%的预期收益率，你将如何培植你的资金。
请将结果与上题比较。
excel在金融工程中的运用 组合收益
金融工程实验一习题一布置时间：第一周
上交时间：第二周
1. 用excel计算
3400263
2374356 32521
3265848 24073
4373262
7410903
⎡⎤⎡⎤
⎢⎥⎢⎥
⎢⎥⎢⎥
⎡⎤
⎢⎥⎢⎥
⎢⎥
⎢⎥⎢⎥
⎣⎦
⎢⎥⎢⎥
⎢⎥⎢⎥
⎣⎦⎣⎦。
2. 已知：A、B两种证券构成证券投资组合。
A证券的预期收益率10%，方差是
0.0144，投资比重为80%；B证券的预期收益率为18%，方差是0.04，投资比重
为20%。
回答：
（1）当A证券收益率与B证券收益率的协方差是0.0048时，计算下列指标：
①该证券投资组合的预期收益率；②A证券的标准差；③B证券的标准差；④A
证券与B证券的相关系数；⑤该证券投资组合的标准差。
（2）当A证券与B证券的相关系数为0.6时，结合（1）的计算结果回答以下问题：①相关系数的大小对投资组合收益率有没有影响？②相关系数的大小对投资组合风险有什么样的影响？
答：①相关系数大小对投资组合收益率没有影响
②相关系数越大，投资组合风险越大，反之亦然。
要求：习题文件名格式：学号+姓名；交给课代表，由课代表整理压缩后提交。
预习：投资组合收益率、方差、协方差，两证券模型。
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的基本功能和常用公式。
Excel是一款功能强大的电子表格软件，可以帮助我们进行各种计算和数据分析。
在这一章节中，我们将介绍Excel 的基本功能，如单元格操作、数据输入和格式化等，并列举一些常用的公式，如计算均值、标准差和相关系数等。
第三章：股票数据的获取和整理在进行股票投资组合分析之前，我们需要获取和整理相关的股票数据。
在这一章节中，我们将介绍如何使用Excel从网络上获取股票数据，并演示如何将数据整理成适合分析的格式。
同时，我们也会介绍一些常用的数据处理技巧，如数据筛选、排序和透视表等。
第四章：股票的风险和收益评估股票的风险和收益是股票投资组合分析的核心内容。
在这一章节中，我们将介绍如何使用Excel对股票的风险和收益进行评估。
我们将介绍常用的风险衡量指标，如标准差、贝塔系数和夏普比率，并演示如何使用Excel进行计算和分析。
第五章：股票组合构建和优化在进行股票投资组合分析时，我们通常会选择多个股票构建一个投资组合。
在这一章节中，我们将介绍如何使用Excel进行股票组合的构建和优化。
我们将介绍常用的组合优化方法，如马科维茨模型和均值-方差模型，并演示如何使用Excel进行计算和优化。
第六章：风险控制和资产配置在进行股票投资组合分析时，风险控制和资产配置是非常重要的。
在这一章节中，我们将介绍如何使用Excel进行风险控制和资产配置。
我们将介绍常用的风险控制方法，如保险策略和动态平衡策略，并演示如何使用Excel进行计算和分析。
用EXCEL实现多个资产的投资组合优化
用EXCEL实现多个资产的投资组合优化
用EXCEL实现多个资产的投资组合优化【摘要】我们可以用excel 来构建多个资产的投资组合，实现收益最大化或者风险最小化，并计算达到目标收益的概率。
【关键词】投资组合；最优
一、风险资产数据
假设我们要构建含五个风险资产的投资组合。
根据统计以往10年的五个资产的历史数据，我们得到以下数据
相关系数（correlation）风险资产1 风险资产2 风险资产3 风险资产4 风险资产5
风险资产1 1 0.51 0.49 0.27 0.47
风险资产2 0.51 1 0.98 0.5 0.94
风险资产3 0.49 0.98 1 0.48 0.9
风险资产4 0.27 0.5 0.48 1 0.46
风险资产5 0.47 0.94 0.9 0.46 1
预期收益（e（r））0.085 0.13 0.135 0.13 0.11
收益标准差（）0.091 0.206 0.212 0.19 0.12
占组合最大百分比（%）100 40 80 30 10
占组合最小百分比（%）0 10 0 0 0
二、假设
为了简化计算过程，我们做了一下假设：
1.根据中心极限理论，我们假设五个资产的收益分布为正态分布。
实验五_运用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 Solver构建最优投资组合(王世臻)
运用Excel Solver构建最优投资组合王世臻（20121563）黄燕宁（20121941）王爽（20125204）汪雅娴（20121336）杨瑞(20121799)潘晓玉（20123384）本文运用马科维茨投资组合优化程序来说明股票市场的分散化投资，借助Excel Solver构建最优投资组合。
我们从Resset金融研究数据库中从电子信息行业选取启明星辰等40只股票2010年至2013年的月收益率以及对应的无风险收益率等数据。
来源于Resset金融研究数据库二、模型设定我们可以设第i 只股票的期望风险溢价为i (r )E ，第i 只股票的权重为i w ，整体的期望风险溢价为p (r )E ，标准差为p σ，夏普比率为p S ，因此我们可以得到组合的期望风险溢价为：11224040()()()()()p i i E r w E r w E r w E r w E r =+++++(1)整体的标准差为：124040[(,)]11i j i j p w w Cov r r i j σ=∑∑==(2) 夏普比率为： p (r )p pE S σ= (3)三、构建组合我们分卖空和未卖空两种情况分别进行讨论： （一）允许进行卖空在这种情况下，为了找出最小的方差组合，我们以(2)式为目标函数，以4011i i w ==∑为约束条件运用Excel solver 求解可以得到最小的标准差为0.04127，此时的风险溢价为0.03901 ，夏普比率为0.94525，同时可以得到此时的风险组合如表。
为了画出风险组合的有效边界，我们以(2)式为目标函数，通过改变(1)式的值利用Excel solver 画出下图1：图1 有效边界与资本配置线图选取边界上夏普比率最高的组合，即有效边界上的最优的风险组合。
我们标准差风险溢价以(3)式为目标函数，以4011i i w ==∑为约束条件运用Excel solver 求解可以得到最优风险组合的标准差为0.0446，此时的风险溢价为0.0477 ，夏普比率为1.069507，得到图1。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
请用Excel软件完成以下要求：1.请利用Exhibit4的数据求出这12类资产的方差－协方差矩阵。
2.请以Exhibit5中预期收益为6.5%的那一列为例：（1）演示如何在已知资产权重和Exhibit4数据的情况下，计算出投资组合的期望收益（本例为6.5%）和标准差（本例为7.83%）；（2）演示如何在约束条件下（本表中Constraints 部分），得到要求收益率（本例为6.5%）下的最优的组合权重（即标准差最小的投资组合）。
3.Exhibit8中的Policy组合，请去掉Inflation-Indexed和Cash后，把原来的权重重新标准化（即剩余的10种资产的相对比例不变），重新构造一个新组合（称为RiskyP1），然后计算TIPS（即表中的Inflation-Indexed）和这个新组合RiskyP1的相关系数。
4.参考Exhibit TN-4，画出由RiskyP1和TIPS构造的“有效前沿”（注意：严格来说并不能算是均值－方差有效前沿），然后再画出从CASH出发的上述“前沿”的切线（注意：虽然前面Exhibit4里CASH的S.D.不为0，但这里假设它为0，即无风险）。
201-053 -9-E x h i b i t 4A s s u m e d R e a l E x p e c t e d R e t u r n s , V o l a t i l i t i e s , a n d C o r r e l a t i o n sC o r r e l a t i o n sE x p e c t e d R e a l R e t u r n (%) S .D . (%) D o m e s t i c E q u i t yF o r e i g n E q u i t y E m e r g i n g M a r k e t s P r i v a t e E q u i t y A b s o l u t e R e t u r n H i g h Y i e l d C o m m o d i t i e s R e a l E s t a t e D o m e s t i c B o n d s F o r e i g n B o n d s I n f l -I n d e x e d B o n d s C a s h 1D o m e s t i cE q u i t y 6.5 16.0 1.00 0.50 0.40 0.40 0.60 0.55 (0.05) 0.20 0.40 0.15 0.10 0.10 2F o r e i g n E q u i t y 6.5 17.0 0.50 1.00 0.35 0.30 0.50 0.35 (0.05) 0.15 0.25 0.40 (0.05) 0.05 3E m e r g i n g M a r k e t s 8.5 20.0 0.40 0.35 1.00 0.25 0.30 0.35 0.00 0.15 0.15 0.10 0.00 0.00 4P r i v a t e E q u i t y 9.5 22.0 0.40 0.30 0.25 1.00 0.30 0.20 (0.10) 0.15 0.20 0.10 0.10 0.05 5A b s o l u t e R e t u r n 5.5 12.0 0.60 0.50 0.30 0.30 1.00 0.40 0.00 0.15 0.30 0.20 0.20 0.10 6H i g h Y i e l d 5.5 12.0 0.55 0.35 0.35 0.20 0.40 1.00 0.10 0.10 0.45 0.15 0.30 0.10 7C o m m o d i t i e s 4.5 12.0 (0.05) (0.05) 0.00 (0.10) 0.00 0.10 1.00 0.00 (0.15) (0.10) 0.20 (0.05)8R e a l E s t a t e 5.5 12.0 0.20 0.15 0.15 0.15 0.15 0.10 0.00 1.00 0.20 0.10 0.20 0.15 9D o m e s t i c B o n d s 4.3 7.0 0.40 0.25 0.15 0.20 0.30 0.45 (0.15) 0.20 1.00 0.40 0.50 0.15 10F o r e i g n B o n d s 4.3 8.0 0.15 0.40 0.10 0.10 0.20 0.15 (0.10) 0.10 0.40 1.00 0.10 0.10 11I n f l -I n d e x e d B o n d s 4.0 3.0 0.10 (0.05) 0.00 0.10 0.20 0.30 0.20 0.20 0.50 0.10 1.00 (0.10) 12C a s h 3.5 1.0 0.10 0.05 0.00 0.05 0.10 0.10 (0.05) 0.15 0.15 0.10 (0.10) 1.00S o u r c e : H a r v a r d M a n a g e m e n t C o m p a n y .201-053 -10-E x h i b i t 5P o r t f o l i o O p t i m i z a t i o n B a s e d o n C a p i t a l M a r k e t A s s u m p t i o n s a n d 0% C o n s t r a i n t (C a s h t o -50%)(A l l N u m b e r s i n %)C o n s t r a i n t s P o r t f o l i o A l l o c a t i o n L o w e r U p p e rD o m e s t i cE q u i t y 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.00 1.00F o r e i g n E q u i t y 3.5 3.8 4.3 4.7 4.8 4.8 4.2 0.00 1.00 E m e r g i n g M a r k e t s 13.1 14.8 15.9 17.3 19.1 20.8 22.3 0.00 1.00 P r i v a t e E q u i t y 14.1 15.9 17.3 18.8 21.1 23.4 25.7 0.00 1.00 A b s o l u t e R e t u r n 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.00 1.00 H i g h Y i e l d 0.0 0.0 0.5 0.3 1.1 1.9 3.4 0.00 1.00 C o m m o d i t i e s 11.2 12.5 13.6 14.7 16.1 17.6 19.7 0.00 1.00 R e a l E s t a t e10.6 11.5 13.1 14.2 15.9 17.6 19.7 0.00 1.00 D o m e s t i c B o n d s 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.00 1.00 F o r e i g n B o n d s 7.5 8.4 8.6 9.7 9.4 9.5 11.5 0.00 1.00 I n f l a t i o n -I n d e x e d B o n d s 52.3 54.1 63.1 70.3 62.6 54.5 43.5 0.00 1.00 C a s h (12.2) (21.0) (36.4) (50.0) (50.0) (50.0) (50.0) (0.50) 1.00 100.0 100.0 100.0 100.0 100.0 100.0 100.0 E x p e c t e d R e a l R e t u r n 5.75 6.00 6.25 6.50 6.75 7.00 7.25 S t a n d a r d D e v i a t i o n 5.87 6.52 7.17 7.83 8.49 9.18 9.88S h a r p e R a t i o 0.380.38 0.38 0.38 0.38 0.38 0.38S o u r c e : H a r v a r d M a n a g e m e n t C o m p a n y .The Harvard Management Company and Inflation–Protected Bonds 201-05313Exhibit 8Proposed Policy Portfolio (All numbers are percentages except for Sharpe Ratio).Minimum Policy Maximum Benchmark1.Domestic Equity12 22 40 80% S&P 500; 16% S&P Mid Cap; 4% Russell20002. Foreign Equity 10 15 20 93% EAFE; 7% Salomon Extended Market Index(excluding US and EAFE overlap)3. Emerging Markets 3 9 13 IFC Global Index and EMBI +4. Private Equity 10 15 20 Cambridge Associates Weighted Composite5. Absolute Return 0 5 10 LIBOR + 5%6. High Yield 0 3 5 Salomon High Yield and Bankrupt7. Commodities 3 6 9 60% GSCI; 40% NCREIF Timber Index8. Real Estate 4 7 10 NCREIF Property Index9. Domestic Bonds 2 7 12 Lehman 5+ Year Treasury Index 10. Foreign Bonds 0 4 10 J.P. Morgan Non U.S. 11. Inflation-Indexed 2 7 12 Salomon TIPS 12. Cash -5 0 10 3 month LIBOR 100 Expected Real Return 6.44 Standard Deviation 9.30 Sharpe Ratio 0.32Source: Harvard Management Company .。