Excel中的IRR计算（内部收益率）

该教程显示了如何通过公式和目标寻求功能计算Excel中项目的IRR。您还将学习如何创建内部返回模板以自动执行所有IRR计算。

当您知道拟议投资的内部回报率时，您可能会认为自己需要评估它 - IRR越大，IRR越好。实际上，这并不是那么简单。 Microsoft Excel提供了三种不同的功能来找到内部收益率，真正了解您实际上使用IRR计算的内容将非常有帮助。

• 什么是内部收益率（IRR）？
• Excel中的手动IRR计算
• 如何用Excel公式计算IRR
  • IRR公式计算内部收益率
  • xirr公式以找到不平等时机现金流的IRR公式
  • MIRR配方进行修改IRR
• 内部收益计算器 /模板的速率
• 如何与目标寻求IRR

什么是IRR？

内部收益率（IRR）是一个常用的度量标准，可以估计潜在投资的盈利能力。有时，它也称为折扣现金流量的回报率或经济回报率。

从技术上讲，IRR是使所有现金流量（流入和流出）的净现值的折现率从等于零的一定投资。

“内部”一词表示仅考虑内部因素；诸如通货膨胀，资本成本和各种财务风险之类的外部因素被排除在计算之外。

IRR揭示了什么？

在资本预算中，IRR被广泛用于评估预期投资的盈利能力并对多个项目进行排名。一般原则与此一样简单：内部收益率越高，项目越有吸引力。

当估计一个项目时，财务分析师通常将IRR与公司的加权平均资本或障碍率进行比较，这是公司可以接受的投资的最低收益率。在假设的情况下，当IRR是做出决定的唯一标准时，如果IRR大于障碍率，则将项目视为一项良好的投资。如果IRR低于资本成本，则应拒绝该项目。实际上，还有许多其他因素会影响决定，例如净现值（NPV），投资回收期，绝对退货价值等。

IRR限制

尽管IRR是评估资本项目的一种非常流行的方法，但它确实存在许多固有的缺陷，可能会导致次优的决策。 IRR的主要问题是：

• 相对度量。 IRR认为百分比而不是绝对价值，结果，它可以偏爱一个具有很高回报率的项目，但美元价值很小。实际上，公司可能更喜欢一个大型项目，而IRR较低的项目比具有较高IRR的小型项目更喜欢。在这方面，NPV是一个更好的指标，因为它考虑了通过进行项目获得或损失的实际金额。
• 相同的再投资率。 IRR假设项目产生的所有现金流都以等于IRR本身的速率进行再投资，这是一个非常不现实的情况。 MIRR可以解决此问题，该问题允许指定不同的财务和再投资率。
• 多个结果。对于交替现金流量和负现金流量的项目，可以找到多个IRR。该问题还可以在MIRR中解决，后者旨在仅产生一个速率。

尽管存在这些缺陷，但IRR仍然是对资本预算的重要衡量标准，至少，在做出投资决定之前，您应该对其进行怀疑。

Excel中的IRR计算

由于内部收益率是给定现金流量等于零的净现值的折现率，因此IRR计算基于传统的NPV公式：

如果您对总和表示法不太熟悉，则IRR公式的扩展形式可能更容易理解：

在哪里：

• CF _0-初始投资（由负数表示）
• cf ₁ ，cf ₂ …cf _n-现金流量
• 我 - 周期编号
• n-总数总计
• IRR-内部回报率

公式的性质使得没有分析方法来计算IRR。我们必须使用“猜测和检查”方法来找到它。为了更好地理解内部收益率的概念，让我们在一个非常简单的示例上执行IRR计算。

示例：您现在投资1000美元，在未来2年内拿回500美元和660美元。什么折现率使净现值零？

作为我们的第一个猜测，让我们尝试8％的速率：

• 现在：PV = - $ 1,000
• 1年：PV = $ 500 /（1 0.08） ¹ = $ 462.96
• 2年：PV = $ 660 /（1 0.08） ² = $ 565.84

添加这些，我们的NPV等于$ 28.81：

哦，甚至没有接近0。也许更好的猜测，例如10％，可以改变事物吗？

• 现在：PV = - $ 1,000
• 1年：PV = $ 500 /（1 0.1） ¹ = $ 454.55
• 2年：PV = $ 660 /（1 0.1） ² = $ 545.45
• NPV：-1000 $ 454.55 $ 545.45 = $ 0.00

就是这样！以10％的折现率，NPV正好为0。因此，这项投资的IRN为10％：

这就是您手动计算内部收益率的方式。 Microsoft Excel，其他软件程序和各种在线IRR计算器也依赖此试验和错误方法。但是与人类不同，计算机可以很快进行多次迭代。

如何用公式在Excel中计算IRR

Microsoft Excel提供了3个功能来查找内部收益率：

• IRR-最常用的功能，用于计算定期发生的一系列现金流量的内部收益率。
• XIRR - 找到一系列以不规则时间间隔发生的现金流量的IRR。由于它考虑了付款的确切日期，因此此功能提供了更好的计算准确性。
• MIRR -返回修改后的内部收益率，这是IRR的变体，它考虑了借贷成本和在重新投资正现金流量时获得的复合利息。

在下面，您将找到所有这些功能的示例。为了一致性，我们将在所有公式中使用相同的数据集。

IRR公式计算内部收益率

假设您正在考虑使用B2：B7中的现金流量进行5年的投资。要确定IRR，请使用此简单的公式：

=IRR(B2:B7)

笔记。为了使IRR公式正常工作，请确保您的现金流量至少具有一个负（流出）和一个正值（流入），并且所有值都按时间顺序列出。

有关更多信息，请参阅Excel IRR功能。

XIRR公式以寻找不规则现金流的IRR

如果有不平等时机的现金流量，使用IRR功能可能会冒险，因为它假设所有付款都发生在一个时期结束时，并且所有时间段是相等的。在这种情况下，XIRR将是一个更明智的选择。

随着B2：B7中的现金流量及其在C2：C7中的日期，该公式将如下：

=XIRR(B2:B7,C2:C7)

笔记：

• 尽管XIRR功能不一定按时间顺序排列，但第一个现金流（初始投资）的日期应首先在阵列中。
• 必须以有效的excel日期提供日期；提供文本格式的日期会使Excel有误解它们的风险。
• Excel XIRR函数使用不同的公式来得出结果。 XIRR公式根据365天的年份折扣后续付款，结果，Xirr始终返回年度内部收益率。

有关更多详细信息，请参阅Excel XIRR功能。

MIRR公式可以制定改良的IRR

为了处理更现实的情况，当项目资金以更接近公司资本成本的速度进行再投资时，您可以通过使用MIRR公式来计算修改后的内部回报率：

=MIRR(B2:B7,E1,E2)

其中b2：b7是现金流量，E1是融资率（借款的成本），而E2是再投资利率（收益再投资时获得的利息）。

笔记。由于Excel MIRR函数计算利润中的复合兴趣，因此其结果可能与IRR和XIRR函数的结果大不相同。

IRR，XIRR和MIRR-哪个更好？

我相信没有人可以对这个问题给出通用的答案，因为在金融学者中，所有三种方法的理论基础，优势和缺点仍然存在争议。也许最好的方法是进行所有三个计算并比较结果：

通常，认为这是：

• XIRR提供的计算精度比IRR更好，因为它考虑了现金流的确切日期。
• IRR通常对项目的盈利能力进行过度乐观的评估，而Mirr给出了更现实的情况。

IRR计算器 - Excel模板

如果您需要定期在Excel中进行IRR计算，则设置内部返回模板可以使您的生活更加轻松。

我们的计算器将包括所有三个公式（IRR，XIRR和MIRR），因此您不必担心哪个结果更有效，但可以考虑所有结果。

1. 将现金流量和日期输入两列（在我们的情况下为A和B）。
2. 在2个单独的单元中输入金融率和再投资率。可选地，分别命名这些命名为finance_rate和renvest_rate 。
3. 创建两个动态定义的范围，命名为cash_flows和日期。

   假设您的工作表命名为Sheet1 ，则第一个现金流（初始投资）是在单元A2中，并且第一个现金流的日期在单元格B2中，根据这些公式将命名范围制成：

   cash_flows：

   =OFFSET(Sheet1!$A$2,0,0,COUNT(Sheet1!$A:$A),1)

   日期：

   =OFFSET(Sheet1!$B$2,0,0,COUNT(Sheet1!$B:$B),1)

   可以在如何创建Excel中的命名范围的动态范围中找到详细的步骤。

4. 使用您刚刚创建的名称作为以下公式的参数。请注意，公式可以在A和B以外的任何列中输入，分别保留用于现金流量和日期。

   =IRR(Cash_flows)

   =XIRR(Cash_flows, Dates)

   =MIRR(Cash_flows, Finance_rate, Reinvest_rate)

完毕！现在，您可以在A列中输入任何数量的现金流，并且您的动态内部收益公式将相应地重新计算：

作为对可能忘记填写所有必需输入单元格的粗心用户的预防措施，您可以将公式包装在IFERROR功能中以防止错误：

=IFERROR(IRR(Cash_flows), "")

=IFERROR(XIRR(Cash_flows, Dates), "")

=IFERROR(MIRR(Cash_flows, Finance_rate, Reinvest_rate), "")

请记住，如果finance_rate和/或renevest_rate单元格为空白，则excel mirr函数假定它们等于零。

如何在目标寻求目标中进行IRR

Excel IRR功能仅执行20次迭代，以达到速率，XIRR执行100次迭代。如果在许多迭代之后找不到在0.00001％以内的结果准确，则#num！返回错误。

如果您正在为IRR计算寻找更准确性，则可以通过使用目标Seek功能来强迫Excel进行超过32,000次迭代，这是何种分析的一部分。

这个想法是要寻求目标，以找到使NPV等于0的百分比。这就是：：

1. 以这种方式设置源数据：
   • 在列中输入现金流（在此示例中B2：B7）。
   • 将预期的IRR放入某些单元格（B9）。您输入的价值实际上并不重要，您只需要将某些东西“馈送”到NPV公式，因此只需将所有的百分比考虑在内，例如10％。
   • 在另一个单元格（B10）中输入以下NPV公式： =NPV(B9,B3:B7) B2

   

2. 在“数据”选项卡上，在“预测组”中，单击如果分析>目标寻求……
3. 在目标搜索对话框中，定义要测试的单元格和值：
   • 将单元格- 引用NPV单元格（B10）。
   • 值为- 类型0，这是设定单元格的所需值。
   • 通过更改细胞- 对IRR细胞的引用（B9）。

   完成后，单击确定。

   

4. 目标搜索状态对话框将出现，并让您知道是否找到了解决方案。如果成功，IRR单元格中的值将被新的NPV零替换。

   单击确定以接受新值或取消以恢复原始值。

   

以类似的方式，您可以使用目标寻求功能查找XIRR。唯一的区别是您需要使用XNPV公式而不是NPV。

笔记。通过目标寻求发现的IRR值是静态的，它不会像公式一样动态地重新计算。每次更改原始数据之后，您将必须重复上述步骤才能获得新的IRR。

这就是在Excel中进行IRR计算的方法。要仔细查看本教程中讨论的公式，欢迎您下面下载我们的示例工作簿。我感谢您阅读，并希望下周在我们的博客上见到您！

练习工作簿下载

Excel IRR计算器 - 示例（.xlsx文件）

以上是Excel中的IRR计算（内部收益率）的详细内容。更多信息请关注PHP中文网其他相关文章！