基于Excel的内部收益率计算——从理论到实操

在工程经济分析中，内部收益率（IRR） 是重要的投资评价方法。下面我们采用Excel来对其进行分析。

理论基础

内部收益率是指使项目净现值（NPV）为零的折现率。换句话说，它是项目自身产生的现金流量可以再投资的收益率。内部收益率（IRR）方法是进行工程经济分析时应用最为广泛的收益率计算方法。它也被称作投资者方法、折现现金流法以及盈利指数法。其计算公式为：

如果一个项目的内部收益率大于最低可接受收益率（MARR），那么该项目就是可接受的。

对于单一方案，从贷款人的角度来看，除非满足以下两个条件，否则内部收益率不会为正值：现金流模式中同时存在收入和支出；收入的总和超过所有现金流出的总和。

IRR方法在工业界被广泛接受，在项目选择过程中，各种类型的收益率和比率通常会被用到。项目IRR与所需回报率（即最低可接受回报率MARR）之间的差异被管理层视为投资安全性的衡量标准。较大的差异表明有更大的安全边际（或相对风险更低）。

求解方法： 试错法：通过不断尝试不同的折现率，直到净现值接近零。数值方法：使用数学软件或电子表格（如Excel的IRR函数或RATE函数）进行求解。

需要注意的是，我们希望得到正的IRR，而使用正的IRR有两个前提，通过这两个前提的判断，可以避免在发现IRR为负数时进行不必要的计算工作。

• 现金流模式中同时存在收益和支出，也就是现金流应该是有正有负。
• 收益的总和超过所有现金流出的总和，总现金流入应该大于总现金流出，

通过粗估这两个条件，可以协助我们节约我们很多时间。

应用内部收益率（IRR）方法的两个主要困难是计算复杂以及某些类型问题中可能出现多个IRR。首先是计算复杂，因为涉及多次根的求解，如果没有合适的工具，计算IRR是非常困难的。使用电子表格软件可以极大地帮助求解内部收益率（IRR）。在Excel中，我们一般使用 IRR(range, guess) 或 RATE(nper, pmt, pv) 函数来进行计算。因为IRR不考虑项目规模，而且可能存在多根问题，必须谨慎应用和解释内部收益率（IRR）。一般来说，多个IRR对于决策目的而言是没有意义的，最好使用其他评估方法（例如净现值NPV）进行验证分析。

基于Excel的内部收益率计算公式

Excel中的IRR、RATE和XIRR函数都用于计算投资的内部收益率，但它们在应用场景和计算方法上存在一些关键差异。下面对这三个函数的详细解释和比较：

IRR 函数

用途：计算一系列定期发生的现金流的内部收益率。

语法：

 IRR(cash_flows,[guess])

• cash_flows：一系列数字，代表各期的现金流，首期现金流通常为负值（表示支出），后续现金流为正值（表示收入）。

• [guess]：对IRR的估计值，如果省略，Excel会假设为0.1（10%）。

特点：

• 假设现金流在每个周期的期末发生。

• 适用于现金流周期固定的情况。

RATE 函数

用途：计算基于固定付款和固定期限的投资或贷款的利率。

语法：

 RATE(nper,pmt,pv,[fv],[type],[guess])

• nper：总期数。

• pmt：每期付款金额。

• pv：现值。

• [fv]：未来值（可选）。

• [type]：指定付款在期初还是期末进行（可选）。

• [guess]：对RATE的估计值（可选）。

特点：

• 适用于计算具有固定付款和固定期限的投资或贷款的利率。

• 假设现金流在每个周期的期末发生。

XIRR 函数

用途：计算一系列不定期发生的现金流的内部收益率。

语法：

 XIRR(cash_flows,dates,[guess])

• cash_flows：一系列数字，代表各期的现金流。

• dates：与现金流相对应的日期。

• [guess]：对XIRR的估计值（可选）。

特点：

• 可以处理不定期现金流。

• 适用于现金流周期不固定的情况。

应用场景比较

• IRR：适用于定期现金流，如每月、每季度或每年的固定投资回报。

• RATE：适用于计算固定付款和固定期限的贷款或投资的利率，如按揭贷款、年金等。

• XIRR：适用于不定期现金流，如项目投资中现金流入和流出时间不固定的情况。

选择哪个函数取决于你的具体需求和现金流的特点。如果现金流是定期的，可以使用IRR或RATE。如果你的现金流是不定期的，那么XIRR是更合适的选择。理解这些函数的差异可以帮助你更准确地评估投资项目的财务可行性。

基于Excel的内部收益率计算实例

IRR公式

制造公司（我们称之为AMT公司）正在考虑投资一套新的自动化质量检测系统，该系统能够通过直接将产品图像输入到质量控制工作站来维护产品质量标准。这使得质量控制工程师能够对比产品图像与设计规格的差异，并根据需要进行调整。

项目详情

• 资本投资需求：该系统的初始投资成本为345,000元人民币。
• 预计残值：预计在六年的研究期结束后，该系统的市场价值为115,000元人民币。
• 年度收入：新系统预计每年能为公司带来120,000元人民币的额外收入。
• 额外年度费用：同时，该系统每年将产生额外的运营成本22,000元人民币。

为了计算IRR，我们需要首先确定每年的净现金流。净现金流由年度收入减去额外的年度费用，再加上（或减去）残值的现值。Excel现金流量表及IRR计算公式（见单元格B13）如下所示，结果为可行。

Rate公式

2000年，张伟从一家中国银行借了700000元，用于购买一处房产，并约定每三个月偿还贷款的7%，直至总共支付50次。在第50次支付时，700000元的贷款将被完全偿还。张伟计算他的年利率为[0.07(700000元) × 4]/700000= 0.28（28%）。张伟实际支付的真实年化利率是多少？

要计算真实的利率，我们需要将每季的真实利率作为未知数进行计算，其公式为：

700000(A/P， i%，50) = 0.07*700000

(A/P， i%，50) =0.07

在此，我们可以用Rate公式进行直接计算，

实际年化利率为29.76%，比28%高出不少。

XIRR公式

一家公司进行了一项投资，现金流入和日期如下，请计算其内部收益率。

日期	现金流
2024-01-01	-200,000
2024-07-01	50,000
2025-01-01	60,000
2025-07-01	70,000
2025-12-01	50,000

项目的内部收益率为

项目的内部收益率为12.05%。

总结

我们通过对内部收益率的思想进行了介绍，对比分析了IRR、XIRR和Rate三个有效收益率计算的公式，并对其进行了具体分析，请问你学会了吗？

知识点：

• 净现值的原理
• IRR公式
• RATE公式
• XIRR公式