如何在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 的更好方法吗？请在下方评论！此外，如果你已经弄清楚了网站选项和对冲基金交易之间哪种投资更好，请在你的评论中添加。
