使用Excel进行简单资产分配建模的最佳方法是什么?

来自 Quora用户的答案如何在Excel中进行投资组合优化?

以下是如何使用Excel优化投资组合(您可以使用您想要的任何投资选项):

导入资产返回数据。 通常每月最好,但每天或每年也有效。 请记住,它需要在您的资产中保持一致。 我在我的例子中使用月度数据,我正在做一个三资产组合。 注意我是如何在顶部留出空间的。

接下来,您需要构建一个协方差表 。 它将是三个横向和三个向下,在顶部和侧面有一个边距用于加权。 稍后您还需要在底部使用一条线来表示某些公式。 协方差的命令是= COVAR(,)。 为整个表格构建此范围,每个范围都是正在注意,顶部的权重将映射到侧面的权重。 这对后续步骤很重要。 单元格C1是公式:= A8。

构建协方差表后,您需要构建预期的返回表

您可以在此处选择计算回报的方式。 您可以使用可以说更准确的点对点,也可以使用平均回报。 我在这里使用平均回报。 另一种方法可能是使用平均回报,然后通过波动率 – 阻力补偿器,但现在让我们保持简单。

此外,如果您想使用年度报表,只需添加另一列并将月平均值乘以12即可。

现在我们需要为每个资产添加协方差和权重的sumproduct。 这意味着什么:

基本上,您输入的是列的权重乘以权重列和协方差列的sumproducts。 论坛是= * SUMPRODUCT(,)。

为每列退货执行此操作。

现在, 添加“目标返回”和“标准差”框 。 在“标准差”框中,公式为= SQRT(SUM()。看起来像这样:

与回报不同,方差随着时间的平方根而增加。 因此,要将标准偏差年化,您需要乘以12的平方根。

在此步骤中,您将添加一个提供预期投资组合回报的单元格。 公式为= SUMPRODUCT(,)。 好像:

然后, 在“目标返回”框中,放置一些小的东西。 我们将建立一系列“高效”的投资组合。 然后设置一个空格来复制和粘贴我们的优化结果 ,如下所示:

最后,我们开始优化! 打开Excel的求解器工具。 它通常位于“数据”菜单下。

在“设置目标”框中,选择标准偏差单元格:

很酷,现在选择“min”告诉Solver你希望这个数字很小。

接下来,您将告诉Solver可以更改哪些单元格来完成“min”功能。 选择“权重”列。

这是我们将添加约束的地方。 第一个约束将寻求将投资组合回报与我们的目标回报相匹配。

单击“添加”,然后单击目标返回单元格,然后选择等于,然后选择预期的返回单元格

下一个约束是“无短路”约束。 单击“添加”,选择“权重”范围,选择“> =”,然后键入0.这意味着没有资产权重可以为负数。

我们需要接下来建立“完全投入”的约束。 我之前忘了它,但你需要去衡量权重列。

现在,我们回到Solver,并告诉它确保所有重量加起来达到100%。 单击“添加”,选择权重单元格的总和,选择“=”,然后键入1。

现在您已准备好进行优化! 点击“解决”!

OOPS! 有些不对劲。 好的,不要惊慌失措,这种情况经常发生。 只是意味着我们必须思考为什么Solver无法解决它。

看起来我们的最低回报率为4.0%。 事实证明这是行不通的,因为没有具有如此低回报率的资产。 因此,没有权重可行。 如果我们将“现金”作为资产类别,它就可以运作。

将“目标回报”更改为您看到的最小回报数,在我的示例中看起来像~7.0%。 然后点击“解决”

看起来很有效! Solver告诉我们,获得7%的最有效方法是42%的SPY,12%的GLD和46%的TLT

现在我们将这些权重复制粘贴到投资组合列下,然后将目标返回到略高的数字 。 一遍又一遍地这样做,直到你有一个投资组合的世界!

最终,它看起来像这样。

而且,如果您在x轴上绘制标准偏差并在y轴上返回这些投资组合,您将获得熟悉的有效前沿。

如果我理解正确,您想要比较两个选项:

(a)在标准普尔指数和T-bill ETF /共同基金的某些组合中投资10万美元40年

(b)在一些混合物中投入10万美元3年,然后使用10万美元作为房屋预付款的一部分(或全部)。

为了在相同的条件下进行这些比较,我们必须比较同一时间点的结果 – 从现在起40年。 而且,我们必须做出一些假设,你比我做的更好。 在方案(a)中你打算买房子怎么办? 在方案(a)中你是否将20%的房子放在房子上,而在方案(b)中是20%+ 10万美元(在投资3年后)? 或者,如果您继续进行方案(a),您打算租用吗?

让我们来看看我给出的第一个例子。 好的。 首先设置一些变量(分号表示移动到下一个单元格:

  • Cash0; 100,000美元;时间现金= 0
  • SPweight; 80%; 50%; 每种情景的标准普尔指数基金的目标权重
  • TBweight; 20%; 50%; 每种情景的T-Bill基金的目标权重
  • SPvol; 15%; 年度标准普尔波动
  • SPRATE; 8%; 年度标准普尔预期回报
  • TBvol; 1%; 年度T-Bill波动性
  • TBrate; 2%; 年度T-Bill预期回报
  • MortRate; 4%; 三年内30年固定抵押贷款的年利率,扣除税收减免

这些数字只是(合理的)建议。 你可以而且应该和他们一起玩,或者找到更准确的。 请注意,如果您在两种方案中使用相同的目标权重,那么决定实际上就是:在3年内,我应该在我的房子上额外减少10万美元,或将其投资于标准普尔和国库券基金的混合物?

现在我们需要设置两个表(几乎相同),我们将对选项(a)和(b)的未来值进行建模。 为每列输入以下标题和内容:

  • 时间(41行,从0到40,递增1)
  • S&P $(使用公式设置第一行等于Cash0 * SPweight)
  • TB $(使用公式设置第一行等于Cash0 * TBweight)
  • Mort $(将为0,直到时间= 3,此时它将设置为方案(b)S&P $和TB $的总和乘以-1,表示方案(a)的机会成本)

一旦你有这些设置。 每年你将通过乘以一个随机数生成上述单元格中的美元,该随机数使用正态分布(对数正常会更好)设置,并使用您为上述变量指定的波动率和均值(预期收益)。 例如,公式是= Norminv(rand(),SPrate,SPvol)*(上面的单元格的值)。 Mort $列是一个例外,从time = 4开始,它总是增加5%。 如果你想要做到彻底,那么你应该将S&P $和TB $重新平衡回到目标权重,这样我刚刚提到的“上面的单元格值”实际上是S&P $和TB $的价值从上面的行,加在一起,然后乘以相关的目标权重。

现在您已经将公式填充到时间= 40,您已经生成了一个模拟结果,显示您在40年投资中将获得多少收入,抵消了通过拥有更大的抵押贷款将花费多少。

刷新工作表或更改变量的值,您将获得另一个模拟的结果……以及另一个…等等。

有很多方法可以改进这个模型。 我要做的第一个改进是将S&P和T-Bills的回报设置为两个场景中头几年(当它重要时)相同的随机生成值。 您还可以解决我所做的其他假设和简化。 这只是您如何看待这个问题并建模出一个简单但有些有用的模型的一个例子。

如果您正在努力使用您的传感器,希望按时完成它们。 然后有一个名为fiverr的工作站点。 它只需5美元即可解决您的问题

我会提供优质的产品和服务。 我是专业人士,在数据输入,MS Excel,MS Word,PDF到Excel,PDF到文字,网络研究,名片数据输入,电​​话号码研究工作方面拥有宝贵的经验。我将为公司和个人按时交付工作质量很好。 点击这里

通过描述很难引导,如果通过开发实际模型来完成则更容易,实际模型本身应该很简单。 告诉我你的需求,也许我能够帮助你。