巧用EXCEL求解内部收益率

荆全忠

摘要：内部收益率（IRR）是进行项目投资决策的重要依据，使用非常广泛。传统的计算内部收益率的方法，如插值法、迭代法、趋势逼近法等，存在计算工作量大、精度不高、难以掌握等缺点。常用的办公软件Excel提供的“单变量求解”工具和“IRR”函数都可用于求解内部收益率，要比传统方法简便快捷，其中IRR函数适合于实际工作，单变量求解更适于教学练习使用。

关键词：内部收益率；EXCEL；单变量求解；IRR函数

中图分类号：G642.41 文献标志码：A 文章编号：1674-9324（2015）15-0146-03

一、引言

在“企业管理”或“财务管理”课程中，一般都会讲到项目投资决策的一个重要指标：内部收益率（internal rate of return，IRR），又称内含报酬率，它是使投资项目的净现值等于零时的折现率。内部收益率反映了投资项目的真实报酬，是进行项目评价、投资决策时的重要依据，目前越来越多的企业使用该项指标对投资项目进行评价[1]。因此内部收益率的计算就显得非常重要。内部收益率的计算涉及一元高次方程的求解、没有根的一般解析式。在课堂教学中通常采用“插值法”计算内部收益率，这是求解内部收益率的传统方法。但是由于这种方法计算量比较大，加上要查“复利系数表”，非常烦琐，而且难以保证精度，尤其是当备选方案多、分期投资、每年收益额不等的时候，用手工计算内部收益率的工作量非常大，不利于学生计算练习和日常使用。如何才能方便快捷地计算内部收益率？人们对此进行了广泛研究，提出了不少独创方法，比如泰勒展式趋近法[2]、黄金分割法[3]、微分法[4]、Aitken加速迭代法[5]、非线性迭代法[6]、牛顿迭代法[7]、快速逼近法[8]、斐波那契法[9]等，还有学者为此开发出了计算机程序[9，10]。这些探索及成果都有很好的理论意义，但从实用角度看，这些方法仍显笨拙和烦琐，不便掌握和推广，不适于在日常工作中广泛使用。随着计算机应用技术的发展，办公软件应用越来越普及。常用的Excel表格具有很强的运算与分析能力，可广泛应用于有繁重计算任务的预算、财务、金融、数据汇总等工作。将Excel用于内部收益率计算，能极大提高计算工作效率和精度。本文重点介绍应用EXCEL求解内部收益率的两种方法。

二、“单变量求解”法

Excel提供了一个非常有用的工具——单变量求解，也可用于计算内部收益率。“单变量求解”是一组命令的组成部分，这些命令也称作假设分析工具。如果设定某个公式的预期结果，则可使用“单变量求解”功能确定此公式中的未知输入值。当进行单变量求解时，Excel会根据所提供的目标值，将引用单元格的值不断调整，直到依赖此单元格的公式返回所需的目标值为止。用“单变量求解”计算内部报酬率的具体操作步骤如下。

（1）任选一个单元格，作为“可变单元格”代表内部收益率。例如，B2。

（2）再选另一单元格，作为“目标单元格”。在其中输入净现值公式：

“=C0/（1+B2）^0+C1/（1+B2）^1+C2/（1+B2）^2+…+Cn/（1+B2）^n”。其中，Ct为t期的现金净流量（期末值）。

（3）单击“工具”菜单中的“单变量求解”命令，会弹出“单变量求解”对话框。完成对话框后单击“确定”按钮，要计算的内部收益率就会出现在“可变单元格”B2中，目标单元格中也会变成一个非常接近于0的值。同时会弹出“单变量求解状态”对话框，若需要保留这个结果，可单击“单变量求解状态”对话框中的“确定”按钮。

例如：（1）将B2作为代表内部收益率的“可变单元格”。在B4中输入计算内部收益率的公式，如表1。

（2）在工具栏中选择“单变量求解”，会弹出“单变量求解”对话框。在“目标单元格”框中输入/选定含有净现值公式的单元格B4；在“目标值”框中输入想要的结果，即希望净现值为0；在“可变单元格”框中输入/选定B2。然后点击“确定”按钮，出现“单变量求解状态”对话框，点击“确定”保留结果。单元格B2中就是要求的内部收益率，为22.56%，单元格B4中是净现值，为0.000679896（非常接近于0），能够满足精度要求，如下表2。

如果在目标单元格中要输入的净现值公式比较烦琐（比如周期数较多、现金流量大），或者希望直接利用财务报表中的数据计算时，可采用分步、分项列表计算的方法。

（1）首先在Excel表中输入现金流量数据和计算净现值的相关公式。例如，在单元格（B1：H1）中分别输入期数“0，1，2，…，6”；在（B2：H2）中分别输入各年的现金净流量；在B3中输入折现系数公式“=1/（1+$B$6）^B1”，然后通过拖拽复制到（C3：H3）中，其中B6为可变单元格，用于存放IRR；在B4中输入计算现值的公式“=B2*B3”，然后通过拖拽复制到（C4：H4）中；在单元格B5中输入计算净现值的公式“=SUM（B4：H4）”，B5为目标单元格，希望其结果为0。如下表3。

（2）利用“单变量求解”工具求解。选择“工具”菜单中的“单变量求解”命令，完成单变量求解对话框，“目标单元格”输入/选定B5，“目标值”输入0，“可变单元格”输入/选定B6（如下图1）。

单击“确定”按钮，可变单元格B6中就会出现欲求的内部收益率，IRR=0.225565418，此时目标单元格B5中的净现值为一接近于0的数，如下表4。

在默认的情况下，“单变量求解”命令会在执行100次求解、与指定目标值的差在0.001之内时停止计算。如果不需要这么高的精度，可以选择“工具”菜单中的“选项”命令，单击“重新计算”修改“最多迭代次数”和“最大误差”框中的值。可变单元格中可以事先给定一个估计的收益率，也可以不给定；每次执行单变量求解得到的内部收益率及净现值可能会有微小的差异，都能满足精度要求。

三、IRR函数法

更为简洁的方法是直接利用Excel的IRR函数。Excel 2003提供了53个财务函数，常用的财务指标几乎都可以通过Excel来计算，内部收益率就是其中之一。IRR函数用于计算在固定时间间隔（例如每年或每月）发生的现金流的内部报酬率（如果不是固定时间间隔发生的现金流，则可使用XIRR函数）。IRR函数的语法为：IRR（values，guess）。其中，value为数组或单元格的引用，表示项目所发生的一序列现金流量。应该注意的是，Values表示的每个现金流量都应发生在周期的末尾，而且必须包含至少一个正值（收入）和一个负值（支出）。参数Guess是对IRR的估计值，对guess的指定是可选的，若不指定，Excel则使用默认值0.1（10%）。IRR函数是通过一个迭代搜索过程进行计算的，该迭代过程guess开始进行循环计算，直至结果的精度达到0.00001%。如果有多个适用的答案，IRR函数将只返回其找到的第一个答案。如果函数IRR经过20次迭代，没有找到任何答案，它将返回一个错误值#NUM！。如果得到错误值或结果与期望不符，可用另一个guess值再试一次。如果有多个可能的内部报酬率，不同的guess值可能会返回不同的结果。使用IRR函数有两种方式。

（1）在某单元格中直接输入包含各参数的IRR函数。例如，在某单元格输入：“=IRR（{-10000，3200，3200，3200，3200，3200，3200}，0）”，回车即可得到内部报酬率为23%（可通过设置单元格格式调整小数位数）。

（2）在单元格中先输入每个现金流量，通过IRR函数引用单元格进行计算。例如，如下表5所示，先在（B2：H2）中输入各期的现金净流量“-10000，3200，3200，3200，3200，3200，3200”；再在单元格B3中插入IRR函数，完成弹出的“函数参数”对话框后，单击“确定”按钮，单元格B3中表5就会出现内部报酬率0.23。

四、结论

计算机应用技术的发展为我们解决传统难题提供了有力支持，Excel软件不仅能用来进行各种数据的处理、统计分析，也能很好地解决内部收益率的计算难题。利用IRR函数计算内部收益率简洁方便，可直接在Excel版的财务报表中使用，非常适于实际工作中计算内部收益率。但是，由于这种方法不需要编写公式，不利于学生深入理解和掌握内部收益率的内涵。因此在课堂讲授、练习内部收益率的计算时，最好先让学生使用“单变量求解”的方法，在特别熟练的情况下再使用IRR函数。

参考文献：

[1]荆新，王化成，刘俊彦.财务管理学[M].第四版.北京：中国人民大学出版社，2006：300.

[2]冯立燕.求解内部收益率的方法及其精确度的问题[J].山西建筑，2009，（20）：266-267.

[3]唐凯，王玉坤.黄金分割法在求解内部收益率中的应用[J].技术经济，2002，（7）：57-58.

[4]崔升飞.微分方法在内部收益率计算中的应用[J].内蒙古科技与经济，2011，（15）：35-37.

[5]刘亚铮，张昭.项目投资内部收益率计算之迭代法[J].财会月刊，2012，（12）：49-50.

[6]王羽，刘伟，杨转运.计算内部收益率（IRR）的改进方法[J].重庆交通学院学报，2005，（4）：105-106.

[7]杨改强，完颜华，张国珍，许丽萍.牛顿法求解内部收益率探讨[J].兰州交通大学学报（自然科学版），2004，（4）：50-53.

[8]张维全.内部收益率的计算及其在投资决策中的应用[J].工业技术经济，2005，（6）：125-126.

[9]傅毓维，赵镇.计算内部收益率的斐波那契法[J].技术经济，2002，（12）：58-60.

[10]苏红顺.内部收益率的计算方法和两个计算程序[J].石家庄经济学院学报，1997，（2）：130-136.