完整wordEXCEL财务函数IRRXIRRMIRR.docx
《完整wordEXCEL财务函数IRRXIRRMIRR.docx》由会员分享,可在线阅读,更多相关《完整wordEXCEL财务函数IRRXIRRMIRR.docx(8页珍藏版)》请在冰豆网上搜索。
![完整wordEXCEL财务函数IRRXIRRMIRR.docx](https://file1.bdocx.com/fileroot1/2023-1/31/fb53e838-7b19-4b2b-8276-8ece58ad5769/fb53e838-7b19-4b2b-8276-8ece58ad57691.gif)
完整wordEXCEL财务函数IRRXIRRMIRR
EXCEL财务函数IRR、XIRR、MIRR
日期:
2009-06—25
IRR(InternalRateofReturn)称为内部报酬率,其应用非常广大,是学习财务管理不可或缺的工具。
Excel也提供了一个相对函数IRR来呼应。
本篇也包含IRR相关的其他两个函数XIRR以及MIRR,让读者可以清楚知道每个函数的应用时机。
现金流量(CashFlow)
阅读本篇之前读者必须先了解现金流量,才会知道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)
参数
意义
必要参数
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的参数并没有绝对日期,只有『一期』的观念。
每一期可以是一年、一个月或一天,随着使用者自行定义。
如果每一格是代表一个『月』的现金流量,那么传回的报酬率就是『月报酬率』;如果每一格是代表一个『年』的现金流量,那么传回的报酬率就是『年报酬率』。
例如{—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,000
2007/11/6
23,650
2008/3/4
25,000
2009/6/8
82,500
可以看到现金流量发生日期是不定期的,并非以一期为单位。
XIRR就是专为这类型的现金流量求报酬率,其他观念和IRR函数没有差别。
XIRR传回来的报酬率已经是年报酬率。
XIRR参数
XIRR(values,dates,guess)
参数
意义
必要参数
Values
现金流量的值
必要
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)
参数
意义
必要参数
values
现金流量的值
必要
finance_rate
融资利率
必要
reinvest_rate
再投资报酬率
必要
MIRR是ModifiedInternalRateofReturn的缩写,意思是改良式的IRR。
IRR到底有何缺点,需要去修正呢?
主要的原因是IRR并未考虑期间领回现金再投资问题!
IRR的现金流量里可分为正值及负值两大类,正值部分属于投资期中投资者拿回去的现金,这些期中拿回去的现金该如何运用,会影响报酬率的.负值部分属于投资期中额外再投入的资金,这些资金的取得也有融资利率方面要考虑。
MIRR使用的方式是将期间所有的现金流入,全部以『再投资利率』计算终值FV。
期间所有的现金流出,全部以『融资利率』计算现值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%,也就是投资者期中拿回来的金额,到期末只有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%,便可轻易得到实际报酬率:
=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内部报酬率是假设再投资报酬率等于内部报酬率.
融资利率
如果有第0期以外的现金投入(负现金流量),这些资金是在未来的期数才会使用。
只要在期初准备这些资金的现值。
就足以支付未来的这些金额.所以将所有的现金流出均以『融资利率』折现(PV),代表未来所有的投资额,都相当于期初投资PV的金额。
举个例子:
一个储蓄险,头两年、年初缴保费5万元.第二年底开始,往后四年均领回30,000元,若再投资利率为3%,融资利率为5%,求MIRR为多少?
=MIRR({—50000,-50000,30000,30000,30000,30000},5%,3%)
=5.15%
这是利率MIRR函数直接套入公式算出来的,为了解其中道理,将这些正负现金流量分开来,以分解动作求取终值及现值,再算投资报酬率。
这样读者就很清楚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),也就是资金存放目标之报酬率