投资组合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。
Execl在财经中的应用例题以及答案
1.某公司做设备投资预算，有3个独立方案A、B、C可供选择，设备使用寿命均为8年。
各方案的现金流量如下表所示，贴现率为10%。
一、解题思路描述将已知的数据输入到Excel单元格中，利用净现值的公式分别算出方案A,B,C 的净现值，之后比较净现值的大小，净现值越大，获得的收益就越大。
利用模拟运算表算出不同贴现率下的净现值，利用模拟运算表绘制带平滑线的散点图。
将A1:D1单元格合并，并输入“投资方案决策”，在B2:D2单元格分别输入“方案A”，“方案B”和“方案C”，在A3单元格输入“投资”，在B3:D3单元格分别输入“-80”，“-150”，“-200”，在A4单元格输入“第1年”，并填充至A5:A11单元格，在B4:D12单元格输入已知的各方案的现金流量，在A12单元格输入“净现值”，在A13单元格输入“贴现率”，将B13:D13单元格合并，并输入“10%”，在A14单元格输入“结论”。
二、操作步骤描述与截图（1）通过净现值，选择一种最优投资方案1、在A1:D14单元格中建立模型框架，并输入已知数据，如图1.1所示。
将B4:D11单元格的数字格式设置为“数值”。
2、在B12单元格中输入公式：“=B3+NPV($B$13,B4:B11)”，并将公式填充至C12:D12单元格，计算三种方案的净现值。
3、在B14单元格输入公式：“=IF(MAX(B12:D12)>0,INDEX(B2:D2,MATCH(MAX(B12:D12),B12:D12,0)), "无")”，根据净现值来选取方案，将B14:D14单元格合并单元格。
结果如图1.2所示。
图1.1 框架模型图1.2 计算结果（2）通过模拟运算表，计算贴现率从1%按1%递增到20%时，3个方案的净现值，并绘制带平滑线的散点图，反映净现值随贴现率的变化。
1、在F4:F23单元格中输入贴现率序列1%~20%，在G3单元格输入“=B12”,在H3单元格输入“=C12”,在I3单元格输入“=D12”，如图1.3所示。
实验五：运用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 表格进行资产配置与收益管理的步骤：1. 建立资产类别：在 Excel 表格中，首先需要建立资产类别列表。
可以按照投资者的需求和偏好，将不同资产类别（如股票、债券、房地产等）列举并安排在表格中的一列中。
2. 输入投资资金：在 Excel 表格中的另一列中，输入投资者准备分配给各个资产类别的资金量。
可以根据投资者的实际情况进行输入。
3. 计算权重和比例：在 Excel 表格中，可以使用数学函数将每个资产类别的资金量转化为权重或比例。
根据投资者的需求，可以选择计算资金量占总投资资金的比例或计算资产类别的权重。
4. 输入预期收益和风险：在 Excel 表格中的另两列中，输入各个资产类别的预期收益和风险。
这些数据可以通过研究分析、历史数据或专业机构提供的信息来获取。
5. 计算资产相关性：在 Excel 表格中的另一部分，可以计算各个资产类别之间的相关性。
相关性反映了不同资产之间的联动情况，对于投资组合的风险管理和多样化非常重要。
6. 优化资产配置：利用 Excel 提供的优化函数或插件，可以根据预期收益、风险和相关性等因素，通过数学模型计算出最优的资产配置方案。
投资者可以根据自身需求和投资目标，选择最适合自己的资产配置解决方案。
7. 实时跟踪投资组合收益：利用 Excel 表格中的公式和函数，可以实时计算和跟踪投资组合的收益情况。
计算方差 EXCEL在投资组合理论中的应用
一 计算期望收益率、标准差、协方差矩阵和相关系数;
1.一个简单的两资产组合的例子（表 1） 假 如 有 两 只 股 票 12 个 月 度 的 价 格 数 据 : 股 票 A 和 股 票 B, 资 料 如 下 :
1.1.收益率与期望收益 1）收益率的计算 以股票 A 为例,计算该股票的月收益率.股票 A 在第 t 月的收益率为在第 t 月 月末与第(t-1)月末价格之比的自然对数,计算公式为:
.
本例中的收益率的计算采用连续收益率形式,并忽略股利收入. 具体步骤是:使用 EXCEL 中的 LN 函数计算股票的收益率.调用 Ln 函数的 方法是:单击 EXCEL 工具栏下的 [ f x ] ，或者选择[插入]菜单中的[函数]命令，弹 出[粘贴菜单]对话框，在[函数分类]中选择[数学与三角函数]。在[函数名]中选择 [LN]函数，单击[确定]按钮即可。 2）期望收益的计算 期望收益是指持有股票的投资者在下一个时期所能获得的收益预期。单个 证券的期望收益可以通过计算历史数据的样本均值来估计。 在 EXCEL 中可以通过[统计 ]中的 [AVERAGE]函数实现对期望收益的计算 （见表 1） 。具体操作步骤如下： （1） 股票 A 每月的收益率：单击 C4 单元格，在编辑栏输入=LN （B4/B3） ， 应用自动填充单元格命令即可求出各月收益率对 应的 C4：C15 单元格区域的值。同样可求出股票 B 的月收 益率。 （2） 股票 A 的月期望收益率：选择 C16 单元格，在编辑栏中输 入=AVERAGE（C4：C15） 。股票 B 的月期望收益率：选择 E16 单元格，在编辑栏中输入=AVERAGE（E4：E15） 。 （3） 股票 A 的年期望收益率：选择 C17 单元格，在编辑栏中输 入=12*C16。同样的方法可得股票 B 的年期望收益率。 1.2 方差与标准差 方差与标准差刻画证券收益率变动，是风险的常用度量指标，在 EXCEL 中方差，样本方差，标准差，样本标准差分别用 VAR（计算基于 给定样本的方差） ，VARP（计算基于给定的样本总体的方差） ，STEDV， STDEVP 来表示，公式如下：
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所示。
如何用EXCEL的规划求解功能优化投资组合的阿尔法值（最小二乘估计法）？
如何用EXCEL的规划求解功能优化投资组合的阿尔法值（最小二乘估计法）？文中的计算方法参考了Agnes Paul的“MARKET RISK METRICS –JENSEN’S ALPHA”詹森阿尔法作为一种投资风险衡量指标，衡量的是一项资产或一个投资组合相对于所参考的绩效指标（如标准普尔500指数）的回报表现。
如果阿尔法值等于零，就意味着投资组合的回报率并没有跑赢所参考的业绩指数，而是与大盘涨跌幅一致。
阿尔法值如果是正的意味着投资组合回报率的涨幅高于业绩参考指数，反之，则意味着投资组合回报率的涨幅低于业绩参考指数。
为确定阿尔法的值需要借助回归分析的方法，尤其是最小二乘估计法。
通过最小二乘估计法可以求得资产的实际回报率与预期回报率之间差值的平方的和的最小值。
最小二乘估计法，又称最小平方法，是一种数学优化技术，通过最小化误差的平方和寻找数据的最佳函数匹配。
利用最小二乘估计法可以简便地求得未知的数据，并使得这些求得的数据与实际数据之间误差的平方和为最小。
公式如下：其中，RIt = 资产I在t日的每日回报率Rf =每日的无风险回报率，报价的时候一般是报年化的无风险回报率，为了将年化的无风险回报率折算成每日的无风险回报率，需要借助以下公式(假设一年有252个交易日):每日的无风险回报率=(1+ 年化无风险回报率)1/252-1RMt = 指数M在t日的每日回报率βI=资产I的回报率相对于指数M走势的贝塔值αI= 资产在t日的每日超额回报率I年化的αI= 资产I的每日回报率超过指数M每日涨跌幅的部分的年化值，也就是詹森阿尔法下面用EXCEL的规划求解功能演绎如何优化投资组合的阿尔法值。
1、在雅虎财经网站上下载标准普尔500指数(^GSPC)，卡特彼勒CAT和宝洁公司PG在2019年1月份的每日收盘价，将经调整后的收盘价整理如下：2、用公式LN(当前收盘价/前收盘价)计算股票和指数的每日回报率3、分别计算卡特彼勒和宝洁公司股价回报率与标准普尔500指数走势之间的贝塔值。
使用Excel进行投资组合分析与优化
使用Excel进行投资组合分析与优化第一章：投资组合分析的背景与意义投资组合分析是指通过将不同的资产按照一定的比例组合在一起来降低风险并寻求最大化收益的方法。
这一方法基于投资者的风险偏好和预期收益率，利用统计学方法对不同资产的历史数据进行分析，为投资者提供最佳的投资组合。
第二章：Excel在投资组合分析中的应用2.1 数据的导入和整理使用Excel进行投资组合分析前，首先需要将股票、债券、基金等各类资产的历史价格数据导入到Excel中，并进行整理和调整，以确保数据的准确性和一致性。
2.2 对单一资产的风险和收益分析在Excel中，可以对单一资产的历史价格数据进行计算和分析，包括计算平均收益率、风险（标准差）、夏普比率等指标，以便评估该资产的投资价值和风险程度。
2.3 投资组合的构建与权重确定通过Excel的数据处理和统计分析功能，可以根据投资者的风险偏好和预期收益率，在历史数据的基础上构建出不同的投资组合，并确定每种资产在投资组合中的权重。
第三章：投资组合优化的方法与应用3.1 马科维茨均值-方差模型马科维茨均值-方差模型是投资组合优化的经典方法之一。
通过在Excel中建立数学模型，利用历史数据来计算投资组合的期望收益率和方差，从而在风险和收益之间做出权衡，找到最优的投资组合。
3.2 敏感性分析与风险控制在Excel中，可以通过敏感性分析的方法来评估投资组合在不同风险偏好和收益预期下的表现。
同时，通过设定约束条件来控制风险，例如设置最大损失限制或者最大风险限制。
第四章：Excel在实际案例中的运用4.1 多资产类别投资组合分析以养老金投资组合为例，使用Excel对股票、债券、房地产等多种资产类别进行分析和优化，找到适合养老金投资的最佳组合。
4.2 期权投资组合分析以期货期权投资为例，利用Excel的期权定价模型对不同的期权组合进行分析和计算，以降低投资风险并寻求最大化收益。
第五章：Excel在投资组合管理中的局限与改进尽管Excel在投资组合分析和优化上有着较好的表现，但它也存在一些限制，如计算能力有限、数据处理不够便捷等。
计算方差 excel在投资组合理论中应用
EXCEL在投资组合理论中的应用教学内容:一、计算投资组合的数字特征;二、在没有卖空限制下计算有效前沿组合(1) 计算有效前沿; (2) 绘制资本市场线;(3) 绘制证券市场线;三、不允许卖空条件下计算有效前沿组合,并比较两种条件下的有效前沿组合的区别四、EGP法计算前沿组合在EXCEL中的实现。
一 计算期望收益率、标准差、协方差矩阵和相关系数;1.一个简单的两资产组合的例子（表1）假如有两只股票12个月度的价格数据:股票A 和股票B,资料如下:月份股票Ａ股票Ｂ025.0045.00124.8844.74224.4146.90323.5945.36426.4650.77526.8753.22627.9153.31728.6462.65829.7265.60932.9866.761036.2278.601137.2478.141237.0368.53股票价格1.1.收益率与期望收益 1）收益率的计算以股票A 为例,计算该股票的月收益率.股票A 在第t 月的收益率为在第t 月月末与第(t-1)月末价格之比的自然对数,计算公式为:1ln()AtAt At P r P -=注意:对数收益率是对普通收益率泰勒级数展开得到的，t 期的对数收益率是ln(Pt)-ln(Pt-1)，对数收益率一般适用于时间间隔比较短的时候（因为是一阶泰勒级数逼近的，所以时间间隔大了误差比较大）。
对数收益率的好处是可以直接相加，比如t 期到t+n 期的对数收益率可以由Rt+R(t+1)+R(t+2)+...得到。
(1) 这个公式采用的是连续收益率计算公式,而离散收益率计算公式为,,11A t At A t P r P -=-(2) 如果在第t 月末获得股利收入,记为t Div ,则收益率为,,1lnA t tAt A t P Div r P -+=.(3) 在考虑股利收入下,股票的离散型收益率为,,1,1A t t A t At A t P Div P r P --+-=.本例中的收益率的计算采用连续收益率形式,并忽略股利收入.具体步骤是:使用EXCEL 中的LN 函数计算股票的收益率.调用Ln 函数的方法是:单击EXCEL 工具栏下的[]x f ，或者选择[插入]菜单中的[函数]命令，弹出[粘贴菜单]对话框，在[函数分类]中选择[数学与三角函数]。
投资组合excel实现
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% 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% 1.2%
最优点的CAL CAL CAL 无差异曲线
0.0% 1000.0%
最有资产权重 厌恶细数与CAL的切点
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% 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% 4.0% 0.0074
PA1 1.06% 0.28% 0.08% 0.17% 0.22% 0.30%
PA2 0.28% 1.00% 0.19% 0.28% 0.17% 0.24%
PA3 0.08% 0.19% 0.88% 0.54% 0.33% 0.30%
PA4 0.17% 0.28% 0.54% 1.89% 0.47% 0.29%
- 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 .。