完整wordEXCEL财务函数IRRXIRRMIRR.docx

完整wordEXCEL财务函数IRRXIRRMIRR.docx

《完整wordEXCEL财务函数IRRXIRRMIRR.docx》由会员分享，可在线阅读，更多相关《完整wordEXCEL财务函数IRRXIRRMIRR.docx（10页珍藏版）》请在冰点文库上搜索。

完整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），也就是资金存放目标之报酬率