EXCEL财务函数IRRXIRRMIRR共11页文档.docx

1、EXCEL财务函数IRRXIRRMIRR共11页文档EXCEL财务函数IRR、XIRR、MIRR日期：2009-06-25IRR(Internal Rate of Return)称为内部报酬率，其应用非常广大，是学习财务管理不可或缺的工具。Excel也提供了一个相对函数IRR 来呼应。本篇也包含IRR相关的其他两个函数XIRR以及MIRR，让读者可以清楚知道每个函数的应用时机。现金流量(Cash Flow)阅读本篇之前读者必须先了解现金流量，才会知道IRR的意义。对现金流量还不了解的读者，怪老子网站的现金流量篇有详细介绍。IRR函数一个投资案会产生一序列的现金流量，IRR简单说：就是由这一序列

2、的现金流量中，反推一个投资案的内部报酬率。如何反推呢，所用的方法是将每笔现金流量以利率rate折现，然后令所有现金流量的净现值(NPV)等于零。若C0、C1、C2、C3.分别代表为期初到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)参数意义必要参

3、数Values现金流量必要guess猜测IRR可能的落点选项IRR的参数有两个，一个是Values也就是一序列现金流量；另一个就是猜个IRR最可能的落点。那么Value的值又该如何输入？有两种方式可输入一序列的现金流量：1.使用数组：例如=IRR(-100, 7, 107)，每一个数字代表一期的净现金流量。2.单元格的范围：例如=IRR(B2:B4)，范围中每一单元格代表一期那么=IRR(-100, 7, 107) 或=IRR(B2:B4)都会得到同样答案：7%使用者定义期间长短IRR的参数并没有绝对日期，只有一期的观念。每一期可以是一年、一个月或一天，随着使用者自行定义。如果每一格是代表一个

4、月的现金流量，那么传回的报酬率就是月报酬率；如果每一格是代表一个年的现金流量，那么传回的报酬率就是年报酬率。例如-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) =

5、 7%传回的就是年报酬率。一个月为一期换个高利贷公司的例子来看，期初借出100元，1个月后拿到利息7元，2个月拿到本利和107元，整个现金流量还是-100, 7, 107喔，不一样的是每期间隔是一个月。那么IRR传回的 7%就是月报酬率，年报酬率必须再乘上12，得到84%的年化报酬率。所以每一期是多久只有使用者知道，对IRR而言只是传回每期的报酬率。guess-猜测报酬率可能的落点guess真是个有趣的参数，IRR函数的任务不就是要解出报酬率的值吗，怎会要我们自己猜测报酬率的落点呢？这不是很奇怪吗，Excel计算功能那么强，难道IRR函数无法直接解出来？没错IRR是无法解的。以-100, -1

6、02, -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 函数会

7、传回错误值 #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%。通常这是一年为一期报酬率都落在这附近，如果要计

8、算月报酬率最好输入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

9、现金流量的值必要dates现金流量发生日期必要guess猜测XIRR可能的落点选项和IRR函数的差别是多了一个日期(dates)参数，此日期参数(dates)必须跟现金流量(Value)成对。例如上面的例子可以如下图的方式来完成。单元格B7的公式 =XIRR(A2:A5,B2:B5)，算出来这投资案相当于每年24.56%的报酬率。需要开启分析工具箱使用XIRR函数必须安装分析工具箱，否则会传回#NAME? 错误。1) 工具/加载宏2) 将分析工具箱打勾3) 按确定MIRR函数MIRR参数MIRR(values, finance_rate, reinvest_rate)参数意义必要参数value

10、s现金流量的值必要finance_rate融资利率必要reinvest_rate再投资报酬率必要MIRR是Modified Internal Rate of Return的缩写，意思是改良式的IRR。IRR到底有何缺点，需要去修正呢？主要的原因是IRR并未考虑期间领回现金再投资问题！IRR的现金流量里可分为正值及负值两大类，正值部分属于投资期中投资者拿回去的现金，这些期中拿回去的现金该如何运用，会影响报酬率的。负值部分属于投资期中额外再投入的资金，这些资金的取得也有融资利率方面要考虑。MIRR使用的方式是将期间所有的现金流入，全部以再投资利率计算终值FV。期间所有的现金流出，全部以融资利率计算

11、现值PV。那么MIRR的报酬率：=(FV/PV)1/n - 1再投资报酬率以例子来解说会较为清楚，一个投资案的现金流量如下：-10000, 500, 500, 10500这现金流量一期为一年，期初拿出10,000元，第1年底拿回500元，第2年底也拿回500元，第3年底拿回10500元。将现金流量代入IRR求内部报酬率：=IRR(-10000, 500, 500, 10500) = 5%从这投资案的经营者来说，期初拿到10,000元，然后每年支付5%报酬500元，到了第3年底还本10000元，这投资案确实是每年发放5%的报酬没错。可是若从投资者角度来看，假若每年底拿到的500元只会放定存2%，

12、也就是投资者期中拿回来的金额，到期末只有2%的报酬率，那么投资者到第3年底时，实际拿到的总金额为：= 500*(1+2%) + 500*(1+2%)2 +10500 ( 以Excel 表示 =500*(1+2%)+500*(1+2%)2 + 10500 )= 11,530期初拿出10,000元，3年后拿回11,530，这样相当于年化报酬率：= (11530/10000)1/3-1 (Excel 表示 =(11530/10000)(1/3)-1 )= 4.86%这可解读为拿出10,000元，以复利4.86%成长，3年后会拿回11,530元。MIRR可以不用那么麻烦，只需输入再投资报酬率2%，便可

13、轻易得到实际报酬率：= MIRR(-10000, 500, 500, 10500), 0, 2%)= 4.86%投资者期间内所拿回的现金(正值)，再投资的报酬率，会影响整体投资的实际报酬率。同一个例子，假若另一位投资者的再投资报酬率为4%，那么实际报酬率修正为：= MIRR(-10000, 500, 500, 10500), 0, 4%)= 4.95%如果投资者的再投资报酬率为5%，实际报酬率等于：= MIRR(-10000, 500, 500, 10500), 0, 5%)= 5.00%可以看到当再投资报酬率为5%时，MIRR = IRR = 5%。这也同时说明了，IRR内部报酬率是假设再投

14、资报酬率等于内部报酬率。融资利率如果有第0期以外的现金投入(负现金流量)，这些资金是在未来的期数才会使用。只要在期初准备这些资金的现值。就足以支付未来的这些金额。所以将所有的现金流出均以融资利率折现(PV)，代表未来所有的投资额，都相当于期初投资PV的金额。举个例子：一个储蓄险，头两年、年初缴保费5万元。第二年底开始，往后四年均领回30,000元，若再投资利率为3%，融资利率为5%，求MIRR为多少？= MIRR(-50000, -50000, 30000, 30000, 30000, 30000, 5%, 3%)= 5.15%这是利率MIRR函数直接套入公式算出来的，为了解其中道理，将这些正

15、负现金流量分开来，以分解动作求取终值及现值，再算投资报酬率。这样读者就很清楚MIRR是如何计算的。总共有两笔现金流出(负值部份)，但是只有第2期的资金流出需要折现，所以用融资利率5%，总现值(PV)：=50000 + 50000/(1+5%) =97,619这意思是说：若融资利率为5%，只需要在期初准备97,619，就足以支付前两期各5万所需的现金支出。总共有4笔现金流入(正值部份)，以再投资利率为3%，求取所有现金流入的终值(FV)= 30000 + 30000*(1+3%) + 30000*(1+3%)2 + 30000*(1+3%)3=125,508那么期初投入97,619，期末拿回125,508，期间为5年，这样的年化投资报酬率： = (125508/97619)(1/5)-1= 5.15%可以看到和直接用MIRR函数所计算出来的报酬率一模一样都是5.15%。融资利率是微软的说明所使用的名词，我认为较为适当的应该是资金报酬率(finance_rate)，也就是资金存放目标之报酬率希望以上资料对你有所帮助，附励志名言3条：1、要接受自己行动所带来的责任而非自己成就所带来的荣耀。2、每个人都必须发展两种重要的能力适应改变与动荡的能力以及为长期目标延缓享乐的能力。3、将一付好牌打好没有什么了不起能将一付坏牌打好的人才值得钦佩。