【www.myl5520.com--学习工具】
EXCEL财务函数(IRR、XIRR、MIRR)
篇一:irr与xirr的区别
EXCEL财务函数IRR
、XIRR、MIRR
日期：2009-06-25
IRR(Internal Rate of Return)称为内部报酬率，其应用非常广大，是学习财务管理不可或缺的工具。Excel也提供了一个相对函数IRR 来呼应。本篇也包含IRR相关的其他两个函数XIRR以及MIRR，让读者可以清楚知道每个函数的应用时机。
现金流量(Cash Flow)
阅读本篇之前读者必须先了解现金流量，才会知道IRR的意义。对现金流量还不了解的读者，怪老子网站的『现金流量』篇有详细介绍。
IRR函数
一个投资案会产生一序列的现金流量，IRR简单说：就是由这一序列的现金流量中，反推一个投资案的内部报酬率。
如何反推呢，所用的方法是将每笔现金流量以利率rate折现，然后令所有现金流量的净现值(NPV)等于零。若C0、C1、C2、C3...Cn分别代表为期初到n期的现金流量，正值代表现金流入，负值代表现金流出。
0 = C0 + C1/(1+rate)1 + C2/(1+rate)2 + C3/(1+rate)3....+ Cn/(1+rate)n
找出符合这方程式的rate，就称为内部报酬率。问题是这方程式无法直接解出rate，必须靠计算机程序去找。这个内部报酬率又和银行所提供的利率是一样的意思。
IRR函数的参数定义如下：
=IRR(Values, guess)
IRR的参数有两个，一个是Values也就是『一序列』现金流量；另一个就是猜个IRR最可能的落点。那么Value的值又该如何输入？有两种方式可输入一序列的现金流量：
1. 使用数组：例如=IRR({-100, 7, 107})，每一个数字代表一期的净现金流量。
2. 单元格的范围：例如=IRR(B2:B4)，范围中每一单元格代表一期
那么
=IRR({-100, 7, 107}) 或
=IRR(B2:B4)
都会得到同样答案：7%
使用者定义期间长短
IRR的参数并没有绝对日期，只有『一期』的观念。每一期可以是一年、一个月或一天，随着使用者自行定义。如果每一格是代表一个『月』的现金流量，那么传回的报酬率就是『月报酬率』；如果每一格是代表一个『年』的现金流量，那么传回的报酬率就是『年报酬率』。
例如{-100, 7, 107}数组有3个数值，叙述着第0期(期初)拿出100元，第1期拿回7元，第2期拿回107元。第一个数值代表0期，也是期初的意思。至于每一期是多久，使用者自己清楚，IRR并不需要知道，因为IRR传回的是『一期的利率』。当然如果使用月报酬率，要转换成年报酬率就得乘上12了。 一年为一期
例如期初拿出100元存银行，1年后拿到利息7元，2年后拿到本利和107元，那么现金流量是{-100, 7, 107}。很清楚的这现金流量的每期间隔是『一年』，所以=IRR({-100, 7, 107}) = 7%传回的就是『年报酬率』。
一个月为一期
换个高利贷公司的例子来看，期初借出100元，1个月后拿到利息7元，2个月拿到本利和107元，整个现金流量还是{-100, 7, 107}喔，不一样的是每期间隔是『一个月』。那么IRR传回的 7%就是『月报酬率』，年报酬率必须再乘上12，得到84%的年化报酬率。所以每一期是多久只有使用者知道，对IRR而言只是传回『每期』的报酬率。
guess-猜测报酬率可能的落点
guess真是个有趣的参数，IRR函数的任务不就是要解出报酬率的值吗，怎会要我们自己猜测报酬率的落点呢？这不是很奇怪吗，Excel计算功能那么强，难道IRR函数无法直接解出来？没错IRR是无法解的。以{-100, -102, -104, -106, 450}这现金流量为例，等于得求出下列方程式中rate的解：
0 = -100 -102/(1+rate)1 -104/(1+rate)2 -106/(1+rate)3 + 450/(1+rate)4
这就难了！因为有4次方。假若现金流量的期数更多，那就更复杂了，而且使用者会输入几期还不知道哩。还好虽然无法直接求解，Excel使用代入逼近法，先假设一个可能的rate(10%)，然后代入上面式子看看是否吻合，如果不是就变动rate的值，然后慢慢逼近、反复计算，直到误差小于 0.00001％ 为止。如果真正的解和默认值差距过远，运算超过20次还是无法求得答案，IRR 函数会传回错误值 #NUM!。这时使用者就必须使用较接近的 guess 值，然后再试一次。 所以guess参数只是IRR函数开始寻找答案的起始点而已，跟找到的答案是无关。下面三个IRR公式，同样的现金流量，但是guess参数都不同，结果答案
却都一样是3.60%。
=IRR({-100, -102, -104, -106, 450})
=IRR({-100, -102, -104, -106, 450}, 1%)
=IRR({-100, -102, -104, -106, 450}, 2%)
guess是选项参数
guess参数可以省略不输入，这时Excel会使用默认值10%。通常这是一年为一期报酬率都落在这附近，如果要计算月报酬率最好输入1%，依此类推。 XIRR函数
若要利用IRR函数来计算报酬率，现金流量必须是以『一期』为单位，也就是输入的现金流量必须有期数的观念。但是常常有些应用，现金流量并非定期式的。例如一个投资案，现金流量如下表：
可以看到现金流量发生日期是不定期的，并非以一期为单位。XIRR就是专为这类型的现金流量求报酬率，其他观念和IRR函数没有差别。XIRR传回来的报酬率已经是年报酬率。
XIRR参数
解决XIRR与 XNPV的问题
篇二:irr与xirr的区别
解决XIRR与 XNPV*的问题
2007．10．15
概述
XNPV和XIRR如何工作，你可以参考本书第1和33章。这些函数解决了一个重要问题（当现金流是日期时间时的折现），但它们一个数字问题。
下面我们讨论这些问题。我们定义两个新函数NXIRR 和NXNPV来解决这些问题。新函数显示在本书文档的电子表中；如何在你的电子表插入这些函数，参见本书光盘中Getformula“添加”文档。
XNPV的问题：0折现率
XNPV, 如下所示, 只有正的折现率才能工作
但是，0或负数折现率, XNPV 不工作:
*
This note, written by Simon Benninga () and Benjamin Czaczkes ( ) accompanies Financial Modeling, 3rd edition. Although it is copyrighted, we authorize its replication, provided full name credit is given the authors.
FM3: Problems with XNPV and XIRR
page 1
快速解决折现率为0的问题
针对折现率为0的问题, 快速解决方法是增加IF函数
:
新函数NXNPV 是更好的解决方法。
问题: XIRR对两个IRR不工作
Guess 打开 XIRR不工作.见下面的问题：
FM3: Problems with XNPV and XIRR page 2irr与xirr的区别。
模拟运算表显示有两个 IRR( 约为5%和 39%). 但 XIRR函数没有识别出来( B4:B6).
XNPV 和 XIRR重新编程
在解决XNPV与XIRR的问题的电子表中，我们定义了两个新函数, NXNPV 和 NXIRR 解决该问题。
FM3: Problems with XNPV and XIRR page 3
注意 B3，我们计算0折现率的 NPV。在B4:B6中我们说明了IRR的新的计算函数。 我们的 NXNPV 函数甚至对负的折现率也工作：
FM3: Problems with XNPV and XIRR page 4
NXNP 与NXIRR编程
该 VBA代码(由Benjamin Czaczkes开发)是:
NXNPV
The function NXNPV also forms the basis of NXIRR below:
'NXNPV solves some problems in the XNPV function 'Written by Benjamin Czaczkes, summer 2007
Function nxnpv(Rate As Double, Values As Range, Dates As Range) Dim Dsize As Integer Dim Vsize As Integer
Dsize = Dates.Rows.Count Vsize = Values.Rows.Count If Dsize <> Vsize Then
nxnpv = CVErr(xlErrNum) Exit Function End If
Dim aValues Dim aDates
Dim tempsum As Double Dim r As Double Dim dd As Long Dim i As Integer aValues = Values aDates = Dates r = 1 + Rate tempsum = 0
dd = aDates(1, 1) For i = 1 To Dsize
tempsum = tempsum + aValues(i, 1) / r ^ ((aDates(i, 1) - dd) / 365) Next i
nxnpv = tempsum End Function
For NXIRR
Our new function NXIRR uses a slightly different version of the time-dated NPV function (called ANNPV )
Private Function annpv(Rate As Double, aValues, aDates) Dim Dsize As Integer Dsize = UBound(aDates) Dim tempsum As Double Dim r As Double Dim dd As Long Dim i As Integer r = 1 + Rate tempsum = 0
dd = aDates(1, 1) For i = 1 To Dsize
tempsum = tempsum + aValues(i, 1) / r ^ ((aDates(i, 1) - dd) / 365) Next i
annpv = tempsum
FM3: Problems with XNPV and XIRR page 5
IRR XIRR MIRR
篇三:irr与xirr的区别
IRR函数 一个投资案会产生一序列的现金流量，IRR简单说：就是由这一序列的现金流量中，反推一个投资案的内部报酬率。
如何反推呢，所用的方法是将每笔现金流量以利率rate折现，然後令所有现金流量的净现值(NPV)等於零。若C0、C1、C2、C3...Cn分别代表为期初到n期的现金流量，正值代表现金流入，负值代表现金流出。
0 = C0 + C1/(1+rate)1 + C2/(1+rate)2 + C3/(1+rate)3....+ Cn/(1+rate)n
找出符合这方程式的rate，就称为内部报酬率。问题是这方程式无法直接解出rate，必须靠电脑程式去找。这个内部报酬率又和银行所提供的利率是一样的意思。
IRR函数的参数定义如下：
=IRR(Values, guess)
参数意义必要参数Values现金流量必要guess猜测IRR可能的落点选项
IRR的参数有两个，一个是Values也就是『一序列』现金流量；另一个就是猜个IRR最可能的落点。那麼Value的值又该如何输入？有两种方式可输入一序列的现金流量： 1.
2. 使用阵列：例如=IRR({-100, 7, 107})，每一个数字代表一期的净现金流量。 储存格的范围：例如=IRR(B2:B4)，范围中每一储存格代表一期
那麼
=IRR({-100, 7, 107}) 或
=IRR(B2:B4)
都会得到同样答案：7%irr与xirr的区别。
使用者定义期间长短
IRR的参数并没有绝对日期，只有『一期』的观念。每一期可以是一年、一个月或一天，随著使用者自行定义。如果每一格是代表一个『月』的现金流量，那麼传回的报酬率就是『月报酬率』；如果每一格是代表一个『年』的现金流量，那麼传回的报酬率就是『年报酬率』。 例如{-100, 7, 107}阵列有3个数值，叙述著第0期(期初)拿出100元，第1期拿回7元，第2期拿回107元。第一个数值代表0期，也是期初的意思。至於每一期是多久，使用者自己清楚，IRR并不需要知道，因为IRR传回的是『一期的利率』。当然如果使用月报酬率，要转换成年报酬率就得乘上12了。
一年为一期
例如期初拿出100元存银行，1年後拿到利息7元，2年後拿到本利和107元，那麼现金流量是{-100, 7, 107}。很清楚的这现金流量的每期间隔是『一年』，所以=IRR({-100, 7, 107}) = 7%传回的就是『年报酬率』。
一个月为一期
换个高利贷公司的例子来看，期初借出100元，1个月後拿到利息7元，2个月拿到本利和107元，整个现金流量还是{-100, 7, 107}喔，不一样的是每期间隔是『一个月』。那麼IRR传回的 7%就是『月报酬率』，年报酬率必须再乘上12，得到84%的年化报酬率。所以每一期是多久只有使用者知道，对IRR而言只是传回『每期』的报酬率。
guess-猜测报酬率可能的落点
guess真是个有趣的参数，IRR函数的任务不就是要解出报酬率的值吗，怎会要我们自己猜测报酬率的落点呢？这不是很奇怪吗，Excel计算功能那麼强，难道IRR函数无法直接解出来？没错IRR是无法解的。以{-100, -102, -104, -106, 450}这现金流量为例，等於得求出下列方程式中rate的解：
0 =-100 -102/(1+rate)1 -104/(1+rate)2 -106/(1+rate)3 + 450/(1+rate)4
这就难了！因为有4次方。假若现金流量的期数更多，那就更复杂了，而且使用者会输入几期还不知道哩。还好虽然无法直接求解，Excel使用代入逼近法，先假设一个可能的rate(10%)，然後代入上面式子看看是否吻合，如果不是就变动rate的值，然後慢慢逼近、反覆计算，直到误差小於 0.00001％ 为止。如果真正的解和预设值差距过远，运算超过20次还是无法求得答案，IRR 函数会传回错误值 #NUM!。这时使用者就必须使用较接近的 guess 值，然後再试一次。
所以guess参数只是IRR函数开始寻找答案的起始点而已，跟找到的答案是无关。下面三个IRR公式，同样的现金流量，但是guess参数都不同，结果答案却都一样是3.60%。 =IRR({-100, -102, -104, -106, 450})
=IRR({-100, -102, -104, -106, 450}, 1%)
=IRR({-100, -102, -104, -106, 450}, 2%)
guess是选项参数
guess参数可以省略不输入，这时Excel会使用预设值10%。通常这是一年为一期报酬率都落在这附近，如果要计算月报酬率最好输入1%，依此类推。
XIRR函数
若要利用IRR函数来计算报酬率，现金流量必须是以『一期』为单位，也就是输入的现金流量必须有期数的观念。但是常常有些应用，现金流量并非定期式的。例如一个投资案，现金流量如下表：
日期金额2007/8/15-100,0002007/11/623,6502008/3/425,0002009/6/882,500
可以看到现金流量发生日期是不定期的，并非以一期为单位。XIRR就是专为这类型的现金流量求报酬率，其他观念和IRR函数没有差别。XIRR传回来的报酬率已经是年报酬率。 XIRR参数
XIRR(values, dates, guess)
参数意义必要参数Values现金流量的值必要dates现金流量发生日期必要guess猜测XIRR可能的落点选项
和IRR函数的差别是多了一个日期(dates)参数，此日期参数(dates)必须跟现金流量(Value)成对。例如上面的例子可以如下图的方式来完成。储存格B7的公式irr与xirr的区别。
=XIRR(A2:A5,B2:B5)，算出来这投资案相当於每年24.56%的报酬率。
需要开启分析工具箱
使用XIRR函数必须安装「分析工具箱」，否则会传回#NAME? 错误。
1) 工具/增益集
2) 将分析工具箱打勾
3) 按确定
内部回报率（IRR）
内部回报率的概念
内部回报率（Internal rate of return），又称内部收益率，是指项目投资实际可望达到的收益率。实质上，它是能使项目的净现值等于零时的折现率。IRR满足下列等式： 计算内部收益率的一般方法是逐次测试法。 当项目投产后的净现金流量表现为普通年金的形式时，可以直接利用年金现值系数计算内部收益率，公式为： （P/A，IRR，n）=1/NCF 内部收益率，就是资金流入现值总额与资金流出现值总额相等、净现值等于零时的折现率。如果不使用电子计算机，内部收益率要用若干个折现率进行试算，直至找到净现值等于零或接近于零的那个折现率。
内部收益率法的公式
（1）计算年金现值系数(p／A，FIRR，n)=K／R； （2）查年金现值系数表，找到与上述年金现值系数相邻的两个系数(p／A，i1，n)和(p／A，i2，n)以及对应的i1、i2，满足(p／A，il，n) >K／R>(p／A，i2，n)； （3）用插值法计算FIRR： （FIRR-I）/（i1—i2）=[K／R-(p/A，i1，n) ]/[(p／A，i2，n)—(p／A，il，n)] 若建设项目现金流量为一般常规现金流量，则财务内部收益率的计算过程为： 1、首先根据经验确定一个初始折现率ic。 2、根据投资方案的现金流量计算财务净现值FNpV(i0)。 3、若FNpV(io)=0，则FIRR=io； 若FNpV(io)>0，则继续增大io； 若FNpV(io)<0，则继续减小io。 （4）重复步骤3)，直到找到这样两个折现率i1和i2，满足FNpV(i1) >0，FNpV (i2)<0，其中i2-il一般不超过2％-5％。 （5）利用线性插值公式近似计算财务内部收益率FIRR。其计算公式为： （FIRR- i1）/ （i2-i1）= NpVl/ （NpV1-NpV2） 内部收益率法的计算步骤
(1)在计算净现值的基础上，如果净现值是正值，就要采用这个净现值计算中更高的折现率来测算，直到测算的净现值正值近于零。 (2)再继续提高折现率，直到测算出一个净现值为负值。如果负值过大，就降低折现率后再测算到接近于零的负值。 (3)根据接近于零的相邻正负两个净现值的折现率，用线性插值法求得内部收益率。
内部收益率法的优缺点
内部收益率法的优点是能够把项目寿命期内的收益与其投资总额联系起来，指出这个项目的收益率，便于将它同行业基准投资收益率对比，确定这个项目是否值得建设。使用借款进行建设，在借款条件（主要是利率）还不很明确时，内部收益率法可以避开借款条件，先求得内部收益率，作为可以接受借款利率的高限。但内部收益率表现的是比率，不是绝对值，一个内部收益率较低的方案，可能由于其规模较大而有较大的净现值，因而更值得建设。所以在各个方案选比时，必须将内部收益率与净现值结合起来考虑。
内部收益率法的分析
内部收益率，是一项投资可望达到的报酬率，是能使投资项目净现值等于零时的折现率。就是在考虑了时间价值的情况下，使一项投资在未来产生的现金流量现值，刚好等于投资成本时的收益率，而不是你所想的“不论高低净现值都是零，所以高低都无所谓”，这是一个本末倒置的想法了。因为计算内部收益率的前提本来就是使净现值等于零。 说得通俗点,内部收益率越高，说明你投入的成本相对地少，但获得的收益却相对地多。比如A、 B
两项投资，成本都是10万，经营期都是5年，A每年可获净现金流量3万，B可获4万，通过计算，可以得出A的内部收益率约等于15%，B的约等于28%，这些，其实通过年金现值系数表就可以看得出来的。 内部收益率，就是资金流入现值总额与资金流出现值总额相等、净现值等于零时的折现率。如果不使用电子计算机，内部收益率要用若干个折现率进行试算，直至找到净现值等于零或接近于零的那个折现率。
非常规的内部收益率方程
求解内部收益率的方程是一个n次多项式方程，可能出现无解或多解。 （1）出现无解的情况：当项目方案随时间变化的现金流量分布都是正值或都为负值时，净现值函数曲线与水平轴不相交，内部收益率无解。这种情况一般少见，可改用其他方法计算。 （2）出现多解的情况：当项目方案连续累计的现金流量符号变化超过一次以上时，就会出现一个以上的内部收益率解。这种情况下需要通过内部收益率的经济含义加以验证。
Excel 财务应用 使用XIRR函数
篇四:irr与xirr的区别
Excel 财务应用 使用XIRR函数
XIRR函数用于返回一组现金流的内部收益率，这些现金流可以是不定期内发生的，若要计算一组定期现金流的内部收益率，则使用IRR函数就可以解决。
其语法格式为：XIRR (values, dates, guess)
其中，参数Values是与参数dates中的支付时间相对应的一系列现金流。首次支付是可选的，并与投资开始时的成本或支付有关，如果第一个值是成本或支付，则它必须是负值。所有后续支付都基于365天/年贴现，系列中必须包含至少一个正值和一个负值，否则XIRR函数将返回错误值 #NUM!。
参数Dates是与现金流支付相对应的支付日期表。第一个支付日期代表支付表的开始，其他日期应迟于该日期，但可以按照任意顺序进行排列。输入日期时，应该使用DATE函数进行，或者将函数作为其他公式或函数的结果输入。例如，若要在单元格中输入2008年10月12日，则可以使用函数DATE(2008,10,12)来完成。
参数Guess是一个对计算结果的估计值。多数情况下，用户不需要为XIRR函数的计算提供Guess值，如果省略该值，系统将自动假定Guess值为0.1。
如果参数Dates中的任一数值不是合法日期，那么XIRR函数将返回错误值#VALUE；例如果参数Dates中的任一数字先于开始日期，XIRR函数将返回错误值#NUM!；如果 如，某注 意 参数Values和参数Dates所含数值的数目不同，XIRR函数也返回错误值#NUM!。 企业
要进行一项投资，投入的成本为15万元，该投资之后的几次回报金额和日期如图3-14所示，要求计算该项投资的内部收益率。
图3-14 输入相关数据
在计算该投资的内部收益率之前，首先应该将相关的数据输入到Excel工作表中。需要注意的是，在单元格中输入日期时，不可以使用文本格式输入，最好使用DATE函数来输入，或者将其设置为时间格式。
例如，要在A2单元格中输入2008年3月20日，可以选择该单元格，单击【日期和时间】下拉按钮，选择DATE函数，在【函数参数】对话框的Year、Month和Day文本框中，分别输入年、月、日，如图3-15所示。
图3-15 设置函数参数