如何在Excel中计算IRR内部收益率

如何在Excel中计算IRR内部收益率

这个快速而轻松的 Microsoft Excel 教程展示了如何在 Excel 中计算 IRR。

您可以使用多种方式在 Excel 中计算投资回报率 (ROI)。其中一种选择是计算固定回报率。虽然您始终可以使用基于固定利率的方法，但在复杂的业务或投资场景中，这种方法可能无法为您提供正确的见解，因为您需要长期投资一笔资金并获得固定收入。

这里引入了 IRR。它考虑了金融产品中的复杂投资成分，例如共同基金、对企业的直接投资或 401(k)。

阅读本文直至结尾，了解使用真实数据集在 Excel 中计算 IRR 的所有经过尝试和测试的方法。我还将展示简短的步骤和插图，以便您可以按照自己的工作表进行操作。

推荐：YITH Easy Login & Register Popup for WooCommerce

IRR 是什么？

内部收益率 (IRR) 是评估潜在盈利能力的常用工具。它计算使投资的所有未来现金流（包括正现金流和负现金流）等于零的折现率。但是，IRR 有局限性。它不考虑通货膨胀、风险或资本成本等外部影响。

对于长期投资，如商业投资、共同基金或退休计划，IRR 提供了一个起点。但它不应该是唯一的决定因素。

假设一家公司想以 10 万美元的价格买下一个网站。卖家表示未来 10 年的预计收入为 15,000 美元。

在另一笔交易中，一家对冲基金要求您投资 10 万美元，为期 10 年。作为回报，您将在接下来的 10 年内每年获得 1 万美元，并收回本金。哪一笔交易更好？

通过在 Excel 中计算 IRR 来选择上述两种投资选项之一，从而做出明智的决定。

使用 IRR 函数计算 IRR

Excel IRR 函数是计算内部收益率的默认公式。它以百分比形式生成年收益率。

在开始使用 IRR 函数之前，请记住以下几点：

• IRR 函数的值部分需要收入（正现金流）和支出（负现金流）才能准确处理。
• IRR 函数仅处理Values参数内的数值。它会忽略所有类型的逻辑值、空单元格或文本字符串。
• 正现金流或负现金流必须是有规律的，例如每周、每两周、每月、每季度、每两年、每年等。
• 函数的Guess部分不是必需的。但是，如果您使用它，Excel 可以在不使用时生成更好的结果。
• 当使用Guess值时，IRR 公式会将最接近Guess参数的值近似。如果出现此类错误#NUM!，请调整Guess参数值。

现在您已经了解了使用 Excel IRR 函数的准则，让我们看看它的应用：

按照上例所示组织您的投资和现金流数据集。以下是对数据组件的解释：

• 年：可以是周、月、年，或者现金流出或流入的任何频率。
• 现金流量：此栏显示与投资项目相关的所有收入和支出。

现在，选择要填充 IRR 值的单元格。在其中输入以下公式：

=IRR(B2:B12,0.1)

在上式中，B2:B12表示IRR函数的Values参数，0.1表示Guess参数。

点击Enter获取IRR值。

使用“主页”选项卡的“数字”命令块内的“增加小数”按钮，以小数形式表示 IRR。

使用 XIRR 函数计算 IRR

如果您以月或年的固定间隔投资于企业或共同基金，并且希望获得时间敏感的 IRR 计算，则应使用XIRR函数。

例如，根据上述数据集，您每年在共同基金中投资10,000 美元。您预计从2024 年 1 月 5 日开始投资，并持续到2035 年 1 月 5 日。

此外，您预测到2035 年 2 月 5 日，您可以以200,000 美元的价格出售共同基金账户的所有单位。

在这种情况下，整个投资价值的不同部分在不同时期产生回报。例如，2024 年 5 月 1 日的投资将持续12 年， 2025 年 5 月 1 日的下一笔存款将持续11 年，依此类推。

因此，计算总体IRR变得非常复杂。

Microsoft Excel 在XIRR函数中考虑了所有与时间相关的因素。您只需输入值、日期和猜测参数即可快速获取补偿时间的内部收益率。

考虑到您的输入数据集已经是如上所示的格式，请突出显示任意单元格并在其中输入以下公式：

=XIRR(B2:B14,A2:A14,0.1)

在给定的公式中B2:B14，、A2:A14和分别0.1代表XIRR公式的值、日期和猜测参数。

点击Enter即可获得您一直在寻找的投资回报率 (ROI)。

不要忘记将数值四舍五入到小数点后两位，以准确表示利率值。

使用 MIRR 函数计算 IRR

通常，你会从贷方借钱，然后将钱投资于企业或任何其他投资计划，以获得固定收入。你的目标是利用这些收入偿还借款，同时赚取利润。

因此，投资的IRR必须大于借款金额的利息。这可确保您能从这项投资中获利。在这种情况下，您可以使用MIRR函数。

要计算MIRR ，除了Values参数之外，还需要加权平均资本成本 (WACC) 或融资利率和再投资率。

加权平均资本成本是你从贷方借入资本的利率。再投资率是你将用第一个投资计划的回报再投资的投资回报率。

考虑上面显示的投资数据集。你在第 0 年投资了100,000 美元。从第二年开始，你在接下来的10 年里每年都会赚取15,000 美元。

数据库还显示 MIRR 所需的以下输入：

• 年利率：每年 6%
• 年度再投资率：每年 10%

现在，我可以使用以下公式计算MIRR ：

=MIRR(B2:B12,E2,E3)

单元格区域告诉 Excel ValuesB2:B12参数的来源、代表融资利率和代表再投资利率。E2E3

我输入公式E4并点击Enter，得到MIRR值为9.11%。

我使用“增加小数”按钮将百分比值表示为最多两位小数。

从MIRR数据的调查结果可以看出，该投资项目是有利可图的，因为您可以以6% 的利率借款并以9.11% 的利率赚取利润，利润率为3.11%。

推荐：提高WooCommerce网站速度插件Disable Bloat for WordPress & WooCommerce

计算 IRR 来比较多个选项

你不能只根据不同项目的 IRR 值来决定投资方向。你还需要考虑项目产生的总回报、项目的稳定性等等。

例如，上面的数据集比较了三个投资项目。下面对数据进行简单分析：

• 选项 1：以 49% 的内部收益率获得 1,680.30 美元的回报。
• 选项 2：以 12% 的内部收益率产生 1,015.00 美元的回报。
• 选项 3：以 32% 的内部收益率产生总收入 3,570.00 美元。

您绝对应该避免选择 2，因为它无法为您带来足够的回报。

选项 1确实显示出高达49% 的 IRR，但累计回报却很低，投资7 年后仅为1,680.30 美元。

选项 3 的IRR 为 32%，回报率不断增加。累计金额也高于所有其他选项。因此，您可以选择选项 3。

这样，您就可以组织来自多个投资选项的投资和回报数据，并使用IRR公式以及返回的总和来选择一个选项。

了解投资何时产生正 IRR

在选择最佳投资计划时，了解项目多久开始产生正 IRR 也很重要。

转到您已在年份和现金流量列中组织投资详细信息的输入数据集。

在现金流的右侧创建一个新列并将其命名为定期 IRR。

您需要从开始获得ROI 的第一年开始计算IRR，它在单元格中表示。B3

选择单元格C3并输入以下公式：

=IRR($B$2:B3,0.1)

第一年即可Enter获得IRR 。

现在，将填充柄从单元格向下拖动C3到单元格C12。这将复制公式并计算其余年份的IRR值。

从这个IRR数据分析可以看出，投资项目在第7年开始产生正回报。

在使用上述公式之前，请根据您的工作表进行调整，以便计算出准确的值。

使用规划求解计算 IRR

您还可以使用Excel 的Solver 插件来计算 IRR，并为投资项目创建收益明细列表。在此方法中，您主要使用PV公式来计算NPV。然后，使用Solver工具进行迭代计算，以找到NPV变为零时的IRR。

这个方法听起来很复杂吗？别担心！只需尝试以下步骤，您就会发现它相当简单。

我认为您已经有如上所示的投资计划数据库。

现在，创建列和行标题，如上图所示。

在现值（PV）列的第一个单元格中，输入PV公式，如下所示：

=B2/(1+$E$2)^A2

点击Enter计算负现金流的现值 (PV) 。

现在，使用填充柄为现值 (PV)列的其余单元格生成PV。

现在，使用以下公式计算NPV ：

=SUM(C2:C12)

转到“数据”选项卡，然后单击“规划求解”命令按钮以启动该插件。如果您没有看到该选项，请查看此 Excel 教程：

📒阅读更多：在 Microsoft Excel 中安装规划求解的 2 种方法

配置“规划求解”对话框的方法如下：

• 设定目标：选择计算的NPV单元，即C13。
• 值：将其设置为0。
• 通过更改变量单元格：选择您想要计算IRR值的单元格。请注意，您还必须使用此单元格来计算现值 (PV)列中的PV。

现在，点击“解决”按钮。

Excel Solver 插件将计算IRR并将其放入单元格中E2。

随后，Excel 将自动计算C 列的PV。

此外，您会发现NPV的值是它0或者接近它。

使用 Excel VBA 计算 IRR

如果您使用 Excel VBA，您可以毫不费力地计算 IRR，而无需记住任何公式及其参数。此外，如果您对 IRR、NPV 等不太了解，您仍然可以通过简单地遵循VBA 宏显示的视觉说明来计算 IRR 。

下面是一个 VBA 脚本，它可以指导您通过从自己的工作表中选择输入来直观地计算 IRR：

Sub CalculateIRR()

    ' Declare variables
    Dim rngValues As Range
    Dim dblGuess As Double
    Dim rngDestination As Range

    ' Prompt user to select range of values
    On Error Resume Next
    Set rngValues = Application.InputBox("Please select the range of values for the IRR calculation:", Type:=8)
    On Error GoTo 0

    ' Exit if no range selected
    If rngValues Is Nothing Then Exit Sub

    ' Prompt user to input guess value
    dblGuess = Application.InputBox("Please enter your guess value for the IRR calculation:", Type:=1)

    ' Prompt user to select destination cell
    On Error Resume Next
    Set rngDestination = Application.InputBox("Please select the destination cell for the IRR result:", Type:=8)
    On Error GoTo 0

    ' Exit if no cell selected
    If rngDestination Is Nothing Then Exit Sub

    ' Calculate and print IRR in the destination cell
    rngDestination.Value = Application.WorksheetFunction.IRR(rngValues, dblGuess)

End Sub

阅读此 Microsoft Excel 教程，了解使用上述脚本创建 VBA 宏的步骤：

推荐：如何从iPhone导入来捕获扫描和草绘图像到您的Mac

运行脚本时，您将看到以下输入框：

• 提示输入值参数，例如定期的负现金流和正现金流。

• 一个输入框，以便您可以选择猜测值。

• 提示您选择 IRR 值的目的地。

如果您按要求输入值，Excel 将计算如上所示的IRR 。

如果您希望使用 VBA 脚本以编程方式计算 XIRR ，则可以使用以下代码：

Sub CalculateXIRR()
    ' Declare variables
    Dim rngValues As Range
    Dim rngDates As Range
    Dim dblGuess As Double
    Dim rngDestination As Range
    
    ' Prompt user to select range for Values
    On Error Resume Next
    Set rngValues = Application.InputBox("Please select the range for Values:", Type:=8)
    On Error GoTo 0
    If rngValues Is Nothing Then Exit Sub
    
    ' Prompt user to select range for Dates
    On Error Resume Next
    Set rngDates = Application.InputBox("Please select the range for Dates:", Type:=8)
    On Error GoTo 0
    If rngDates Is Nothing Then Exit Sub
    
    ' Prompt user to enter Guess value
    dblGuess = Application.InputBox("Please enter the Guess value:", Type:=1)
    If dblGuess = False Then Exit Sub
    
    ' Prompt user to select destination cell
    On Error Resume Next
    Set rngDestination = Application.InputBox("Please select the destination cell for XIRR value:", Type:=8)
    On Error GoTo 0
    If rngDestination Is Nothing Then Exit Sub
    
    ' Calculate and print XIRR in the destination cell
    rngDestination.Value = Application.WorksheetFunction.Xirr(rngValues, rngDates, dblGuess)
    
    ' Format the XIRR result as a percentage with up to two decimal points
    rngDestination.NumberFormat = "0.00%"
End Sub

使用此 VBA 脚本在 Excel 中计算MIRR：

Sub CalculateMIRR()
    ' Prompt to select the Values range
    Dim ValuesRange As Range
    Set ValuesRange = Application.InputBox("Select the Values range", Type:=8)

    ' Prompt to select the Finance Rate cell
    Dim FinanceRateCell As Range
    Set FinanceRateCell = Application.InputBox("Select the Finance Rate cell", Type:=8)

    ' Prompt to select the Reinvest Rate cell
    Dim ReinvestRateCell As Range
    Set ReinvestRateCell = Application.InputBox("Select the Reinvest Rate cell", Type:=8)

    ' Prompt to select the Destination cell
    Dim DestinationCell As Range
    Set DestinationCell = Application.InputBox("Select the Destination cell", Type:=8)

    ' Calculate MIRR and print in the Destination cell
    DestinationCell.Value = Application.WorksheetFunction.MIRR(ValuesRange, FinanceRateCell.Value, ReinvestRateCell.Value)
End Sub

使用 Office 脚本计算 IRR

由于您无法在 Excel Web 应用程序上使用 VBA，因此您可以选择Office 脚本作为替代方案。

转到“自动化”选项卡并单击“新脚本”按钮。

在代码编辑器面板中，复制并粘贴此脚本：

function main(workbook: ExcelScript.Workbook) {
    // Get the currently active worksheet
    let activeSheet: ExcelScript.Worksheet = workbook.getActiveWorksheet();

    // Get the currently selected range
    let selectedRange: ExcelScript.Range = workbook.getSelectedRange();

    // Get the values in the selected range
    let cashFlows: (number[][]) = selectedRange.getValues();

    // Flatten the 2D array to 1D array
    let cashFlowsFlat: number[] = [].concat(...cashFlows);

    // Calculate the IRR
    let irr: number = calculateIRR(cashFlowsFlat);

    // Convert the IRR to a percentage and round to two decimal places
    let irrPercentage: number = parseFloat((irr * 100).toFixed(2));

    // Log the IRR
    console.log(`The IRR for the selected range is ${irrPercentage}%`);
}

// Function to calculate IRR
function calculateIRR(cashFlows: number[]) {
    let guess: number = 0.1;  // Initial guess for IRR
    let maxIter: number = 100;  // Maximum number of iterations
    let tol: number = 0.00001;  // Tolerance

    for (let i = 0; i 

单击保存脚本按钮。

现在，选择 IRR 函数输入值可用的列。

点击运行按钮来计算IRR。

您可以在代码编辑器的输出界面中看到计算的值。

IRR 的局限性

以下是 IRR 公式的缺点：

• IRR不考虑投资的绝对价值。
• 它通常会显示低金额项目的回报率更高。
• 风险投资家和个人投资者通常更青睐价值更高的项目，即使投资回报率低于小项目。因此，IRR并不是衡量投资绩效的唯一指标。
• IRR公式假设资本或现金流的再投资利率与计算出的 IRR相同。由于资本市场的回报率波动，这并不总是可行的。
• 如果投资计划或项目涉及正负现金流交替，则可能导致同一项目产生多个 IRR 。

结论

现在，您可以使用本文中提到的任何一种方法计算IRR、XIRR或MIRR ，轻松确定一项投资是否值得选择。

这篇文章对你有帮助吗？你知道获取 IRR 的更好方法吗？请在下方评论！此外，如果你已经弄清楚了网站选项和对冲基金交易之间哪种投资更好，请在你的评论中添加。

推荐：如何在Excel中创建热力图