内部收益率 – Excel22

Excel 应用IRR函数计算一系列现金流的内部收益率

IRR函数用于计算由数值代表的一组现金流的内部收益率。这些现金流不必为均衡的，但作为年金，它们必须按固定的间隔产生，如按月或按年。内部收益率为投资的回收利率，其中包含定期支付（负值）和定期收入（正值）。IRR函数的语法如下。

IRR(values,guess)

其中参数values为数组或单元格的引用，包含用来计算返回的内部收益率的数字。guess为对函数IRR计算结果的估计值。

典型案例

已知某公司某项业务的初期成本费用、前五年的净收入，计算投资若干年后的内部收益率。基础数据如图17-45所示。

步骤1：打开例子工作簿“IRR.xlsx”。

步骤2：在单元格A9中输入公式“=IRR（A2：A6）”，用于计算投资四年后的内部收益率。

步骤3：在单元格A10中输入公式“=IRR（A2：A7）”，用于计算五年后的内部收益率。

步骤4：在单元格A11中输入公式“=IRR（A2：A4，-10%）”，用于计算两年后的内部收益率（使用了一个估计值）。计算结果如图17-46所示。

图17-45　基础数据

图17-46　计算结果

使用指南

1）values必须包含至少一个正值和一个负值，以计算返回的内部收益率。函数IRR根据数值的顺序来解释现金流的顺序，故应确定按需要的顺序输入了支付和收入的数值。如果数组或引用包含文本、逻辑值或空白单元格，这些数值将被忽略。Excel使用迭代法计算函数IRR。从guess开始，函数IRR进行循环计算，直至结果的精度达到0.00001%。如果函数IRR经过20次迭代，仍未找到结果，则返回错误值“#NUM！”。

2）在大多数情况下，并不需要为函数IRR的计算提供guess值。如果省略guess，假设它为0.1（10%）。如果函数IRR返回错误值“#NUM！”，或结果没有靠近期望值，可用另一个guess值再试一次。

Excel 返回不定期发生现金流的内部收益率：XIRR函数

XIRR函数的功能是返回一组现金流的内部收益率，这些现金流不一定定期发生。它的语法格式为：

XIRR（values,dates,guess）

它一共有3个参数，values表示与dates中的支付时间相对应的一系列现金流；dates表示与现金流支付相对应的支付日期表；guess为对函数XIRR计算结果的估计值，如果省略，则默认为0.1。

例如，某公司在2006年3月10日开始一项18万元的投资。它的回报金额和日期参考原始文件，现要求计算这项投资的内部收益率。

步骤01：打开实例文件“XIRR函数计算模型.xlsx”工作簿。在单元格D11中输入公式“=XIRR（D2:D9,C2:C9,0.2）”，这里假设函数XIRR结果的估计值为0.2，按下Enter键后，计算出的内部收益率为20.14%，如图4-49所示。

图4-49　设置公式计算内部收益率

步骤02：通常，如果该报酬率大于一般的定期存款利率，则说明此项投资值得。因此，在单元格D12中输入公式“=IF（D11>=0.05,”值得”,”不值得”）”，按下Enter键后，单元格中显示公式计算结果为“值得”，如图4-50所示。

高手支招：自定义公式错误检查规则

在Excel中进行公式运算时，有时单元格左上角会显示一个绿色的小三角，该符号为错误检查标识。当然，当显示该符号时并不是说公式中一定发生了错误，而是可能违反了错误检查规则中的某一项，与计算的结果并没有直接关系。实际上，Excel允许用户自行设置公式错误检查规则，设置方法如下。

步骤01：在Excel窗口中单击“文件”菜单，从弹出的菜单项中单击“选项”命令，如图4-51所示。

步骤02：在“Excel选项”对话框中单击“公式”标签，在“错误检查规则”选项中可以勾选或取消勾选错误检查规则项，如图4-52所示。例如，如果希望忽略公式中引用空单元格，则取消勾选“引用空单元格的公式”复选框，最后单击“确定”按钮。

图4-50　设置公式判断投资是否值得

图4-51　单击“选项”命令

图4-52　设置公式错误检查规则

此外，用户还可以自定义错误标识的颜色，在如图4-52所示的“Excel选项”对话框中的“错误检查”区域单击“使用此颜色标识错误”图标中的下三角按钮，从显示的颜色中选择自己喜欢的颜色即可。

Excel 返回现金流的修正内部收益率：MIRR函数

修正内部收率又称为修正内部报酬率（Modified Internal Rate of Return），它是指在不同的利率下计算支出和收入时所得的内部收益率，同时考虑投资成本和现金再投资收率。

Excel中用于计算现金流修正内部收益率的函数为MIRR，它的语法格式为：

MIRR（values,finance_rate,reinvest_rate）

它一共有3个参数，values为数组类型，表示用来计算返回的内部收益率的数字；finance_rate表示现金流中使用的资金支付的利率；reinvest_rate表示将现金流再投资的收益率。

例如，5年前某公司以8.85%的年利率贷款30万元购买了一批设备，这批设备在这5年内带来的收入分别为：30000、89000、108500、120000和132500元。其中又将这些收入用于再投资，每年的收益率为15%。现要计算购买这批设备3年后及5年后的修正内部收益率。打开实例文件“MIRR函数计算修正内部收益率.xlsx”工作簿，该工作簿中已创建好表格并输入已知数据。

步骤01：在单元格E5中输入公式“=MIRR（B2:B5,B8,B9）”，按下Enter键后，计算结果为-5.8%，如图4-47所示。说明在投资3年后，该项投资仍处于亏损状态。

图4-47　输入公式计算3年后的MIRR

步骤02：在单元格E7中输入公式“=MIRR（B2:B7,B8,B9）”，按下Enter键后，公式计算结果为14.9%，如图4-48所示。说明在投资后的第五年，收益率为14.9%。

图4-48　输入公式计算5年后的MIRR

Excel 返回现金流的内部收益率函数IRR

内部收益率通常指投资的回收率，其中包括定期支付和定期收入。Excel中的IRR函数的功能是返回由数值代表的一组现金流的内部收益率。它的语法格式为：

IRR（values,guess）

它有两个参数，values为数组类型，表示用来计算返回的内部收益率的数字；guess为对函数IRR计算结果的估计值，如果省略，假设它为0.1（10%）。

例如，某人打算开办一家新公司，投资额为150万元。预计在今后5年内的收益分别为：40万元、45万元、58万元、65万元和72万元，现需要计算投资1年后、3年后和5年后的内部收益率。

步骤01：新建一个工作簿，在工作表Sheet1中创建如图4-43所示的表格，并输入已知数据。

图4-43　创建表格

步骤02：设置公式计算一年后的内部收益率。在单元格E4中输入公式“=IRR（B3:B4,0.2）”，按下Enter键后，计算结果为-73.33%，表示一年后还未收回成本，如图4-44所示。公式中第二个参数输入为0.2，表示假设IRR计算结果的估计值为0.2。

图4-44　输入公式计算一年后的内部收益率

步骤03：设置公式计算3年后的内部收益率。在单元格E6中输入公式“=IRR（B3:B6,0.2）”，按下Enter键后，计算结果为-2.22%，如图4-45所示。

图4-45　输入公式计算3年后的内部收益率

步骤04：设置公式计算5年后的内部收益率。在单元格E8中输入公式“=IRR（B3:B8,0.2）”，按下Enter键后，计算结果如图4-46所示。说明在第五年后，有22.26%的收益率。

图4-46　输入公式计算5年后的内部收益率

Excel 计算连续期间内现金流的修正内部收益率：MIRR函数

如果需要计算某一连续期间内现金流的修正内部收益率，可通过“MIRR”函数实现。MIRR函数的语法为：=MIRR (values, finance_rate, reinvest_rate)，其中各参数的含义介绍如下。

※　values：一个数组或对包含数字的单元格的引用，代表着各期的一系列支出（负值）及收入（正值）。

※　finance：现金流中使用的资金支付的利率。

※　rate：年利率。

※　reinvest_rate：将现金流再投资的收益率。

下面给定资产原值和三年的收益额，计算三年后现金流的修正收益率。

01　在“B1:B4”单元格区域中分别输入资产原值，以及第一年到第三年的收益，在“B5”单元格中输入年利率，在“B6”单元格中输入再投资的收益率。

02　在单元格中输入公式：=MIRR(B1:B4,B5,B6)，然后按下“Enter”键确认即可。

alt

Excel 计算一组现金流的内部收益率：IRR函数

如果需要计算由数值代表的一组现金流的内部收益率，可通过“IRR”函数实现。IRR函数的语法为：=IRR (values, guess)，各参数的含义介绍如下。

※　values：为数组或单元格的引用，包含用来计算返回的内部收益率的数字。

※　guess：为对函数IRR计算结果的估计值。

下面分别举例计算投资n年后不包含估计值和包含估计值的内部收益率。

计算投资四年后的内部收益率

01　在“B1:B5”单元格区域中分别输入初期成本和第一年到第四年的净收入。

02　在单元格中输入公式：=IRR(B1:B5)，按下“Enter”键确认即可。

alt

计算两年后的内部收益率（需包含估计值）

01　在“B1:B3”单元格区域中分别输入初期成本和第一年到第二年的净收入，在“B4”单元格中输入一个估计值。

02　在需要显示结果的单元格中输入公式：=IRR(B1:B3,B4)，按下“Enter”键确认即可。

alt

Excel 计算现金流内部收益率：XIRR函数详解

XIRR函数用于计算一组现金流的内部收益率，这些现金流不一定定期发生。如果要计算一组定期现金流的内部收益率，则需要使用函数IRR。XIRR函数的语法如下：

XIRR(values,dates,guess)

其中，values参数为与dates中的支付时间相对应的一系列现金流。首期支付是可选的，并与投资开始时的成本或支付有关。如果第1个值是成本或支付，则它必须是负值。所有后续支付都基于365天/年贴现。系列中必须包含至少一个正值和一个负值。dates参数为与现金流支付相对应的支付日期表。第1个支付日期代表支付表的开始，其他日期应迟于该日期，但可按任何顺序排列。应使用DATE函数输入日期，或者将函数作为其他公式或函数的结果输入。例如，使用函数DATE(2008,5,23)输入2008年5月23日。如果日期以文本形式输入，则会出现问题。guess参数为对函数XIRR计算结果的估计值。下面通过实例详细讲解该函数的使用方法与技巧。

打开“XIRR函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-59所示。该工作表记录了现金流的值与支付时间，要求根据给定的数据计算其内部收益率。具体的计算步骤如下。

选中A8单元格，在编辑栏中输入公式“=XIRR(A2:A6,B2:B6,0.1)”，然后按“Enter”键返回，即可计算出现金流的内部收益率，如图19-60所示。

图19-59　原始数据

图19-60　计算现金流内部收益率

函数XIRR要求至少有一个正现金流和一个负现金流，否则函数XIRR返回错误值“#NUM!”。如果dates参数中的任一数值不是合法日期，函数XIRR返回错误值“#VALUE”。如果dates参数中的任一数字先于开始日期，函数XIRR返回错误值“#NUM!”。如果values参数和dates参数所含数值的数目不同，函数XIRR返回错误值“#NUM!”。多数情况下，不必为函数XIRR的计算提供guess参数值，如果省略，guess参数值假定为0.1(10%)。函数XIRR与净现值函数XNPV密切相关。函数XIRR计算的收益率即为函数XNPV=0时的利率。Excel使用迭代法计算函数XIRR。通过改变收益率（从guess开始），不断修正计算结果，直至其精度小于0.000001%。如果函数XIRR运算100次，仍未找到结果，则返回错误值“#NUM!”。

Excel 计算不同利率内部收益率：MIRR函数

MIRR函数用于计算某一连续期间内现金流的修正内部收益率。函数MIRR同时考虑了投资的成本和现金再投资的收益率。MIRR函数的语法如下：

MIRR(values,fi nance_rate,reinvest_rate)

其中，values参数为一个数组或对包含数字的单元格的引用。这些数值代表着各期的一系列支出（负值）及收入（正值）。values参数中必须至少包含一个正值和一个负值，才能计算修正后的内部收益率，否则函数MIRR会返回错误值“#DIV/0!”。如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。finance_rate参数为现金流中使用的资金支付的利率。reinvest_rate参数为将现金流再投资的收益率。下面通过实例详细讲解该函数的使用方法与技巧。

已知某公司某项资产的原值、前5年每年的收益，要求计算5年后投资的修正收益率、3年后的修正收益率及基于14%的再投资收益率的5年修正收益率。打开“MIRR函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-41所示。具体的计算步骤如下。

STEP01：选中A11单元格，在编辑栏中输入公式“=MIRR(A2:A7,A8,A9)”，然后按“Enter”键返回，即可计算出5年后投资的修正收益率，如图19-42所示。

STEP02：选中A12单元格，在编辑栏中输入公式“=MIRR(A2:A5,A8,A9)”，然后按“Enter”键返回，即可计算出3年后的修正收益率，如图19-43所示。

图19-41　原始数据

图19-42　计算5年后的修正收益率

STEP03：选中A13单元格，在编辑栏中输入公式“=MIRR(A2:A7,A8,14%)”，然后按“Enter”键返回，即可计算出基于14%的再投资收益率的5年修正收益率，如图19-44所示。

图19-43　计算3年后的修正收益率

图19-44　计算结果

函数MIRR根据输入值的次序来解释现金流的次序。所以，务必按照实际的顺序输入支出和收入数额，并使用正确的正负号（现金流入用正值，现金流出用负值）。如果现金流的次数为n，finance_rate为frate，而reinvest_rate为rrate，则函数MIRR的计算公式为：

Excel 计算现金流内部收益率：IRR函数详解

IRR函数用于计算由数值代表的一组现金流的内部收益率。这些现金流不必为均衡的，但作为年金，它们必须按固定的间隔产生，如按月或按年。内部收益率为投资的回收利率，其中包含定期支付（负值）和定期收入（正值）。IRR函数的语法如下：

IRR(values,guess)

其中，values参数为数组或单元格的引用，包含用来计算返回的内部收益率的数字。guess参数为对函数IRR计算结果的估计值。下面通过实例详细讲解该函数的使用方法与技巧。

已知某公司某项业务的初期成本费用、前5年的净收入，需要计算投资若干年后的内部收益率。打开“IRR函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-37所示。具体的计算步骤如下。

STEP01：选中A9单元格，在编辑栏中输入公式“=IRR(A2:A6)”，然后按“Enter”键返回，即可计算出投资4年后的内部收益率，如图19-38所示。

图19-37　原始数据

图19-38　计算4年后的内部收益率

STEP02：选中A10单元格，在编辑栏中输入公式“=IRR(A2:A7)”，然后按“Enter”键返回，即可计算出投资5年后的内部收益率，如图19-39所示。

STEP03：选中A11单元格，在编辑栏中输入公式“=IRR(A2:A4,-10%)”，然后按“Enter”键返回，即可计算出两年后的内部收益率（使用了一个估计值），如图19-40所示。

图19-39　计算五年后的内部收益率

图19-40　计算两年后的内部收益率

Values参数必须包含至少一个正值和一个负值，以计算返回的内部收益率。函数IRR根据数值的顺序来解释现金流的顺序，故应确定按需要的顺序输入了支付和收入的数值。如果数组或引用包含文本、逻辑值或空白单元格，这些数值将被忽略。Excel使用迭代法计算函数IRR。从guess参数开始，函数IRR进行循环计算，直至结果的精度达到0.00001%。如果函数IRR经过20次迭代，仍未找到结果，则返回错误值“#NUM!”。

在大多数情况下，并不需要为函数IRR的计算提供guess值。如果省略guess参数，假设它为0.1(10%)。如果函数IRR返回错误值“#NUM!”，或结果没有靠近期望值，可用另一个guess值再试一次。