基于EXCEL的內(nèi)含報(bào)酬率計(jì)算模型的設(shè)計(jì)應(yīng)用

楊毅 孫楠楠

摘要：本文利用EXCEL結(jié)合插值法的計(jì)算原理，將財(cái)務(wù)管理中內(nèi)含報(bào)酬率指標(biāo)的計(jì)算過(guò)程通過(guò)數(shù)據(jù)關(guān)聯(lián)加以簡(jiǎn)化，建立內(nèi)含報(bào)酬率的EXCEL模型，實(shí)現(xiàn)直接通過(guò)財(cái)務(wù)數(shù)據(jù)得到相應(yīng)財(cái)務(wù)分析指標(biāo)。

關(guān)鍵詞：EXCEL內(nèi)含報(bào)酬率插值法

1 內(nèi)含報(bào)酬率計(jì)算原理

1.1 內(nèi)含報(bào)酬率（internal rate of return,IRR ） 內(nèi)含報(bào)酬率，簡(jiǎn)單說(shuō)就是整個(gè)投資方案凈現(xiàn)值為零的貼現(xiàn)率，它作為投資項(xiàng)目本身的報(bào)酬率，反映的是投資項(xiàng)目的收益水平，是投資項(xiàng)目的真實(shí)報(bào)酬率的實(shí)際反映，目前已被越來(lái)越多的企業(yè)應(yīng)用到對(duì)投資項(xiàng)目的評(píng)價(jià)體系中來(lái)。

1.2 計(jì)算原理 根據(jù)內(nèi)含報(bào)酬率的定義，不難得出IRR的計(jì)算原理：NPV=I0+■+■+…+■

當(dāng)測(cè)算期內(nèi)每年現(xiàn)金凈流量相等時(shí)，首先，確認(rèn)年金現(xiàn)值系數(shù)。

年金現(xiàn)值系數(shù)=初始投資額÷每年現(xiàn)金凈流量

其次，根據(jù)年金現(xiàn)值系數(shù)表，在期數(shù)相同的前提下，分別確定與第一步當(dāng)中確認(rèn)的年金現(xiàn)值系數(shù)相鄰的上下各一個(gè)折現(xiàn)率，即確定較年金現(xiàn)值系數(shù)一大一小兩個(gè)折現(xiàn)率。

再次，根據(jù)這兩個(gè)已知折現(xiàn)率和已求得的年金現(xiàn)值系

數(shù)，采用插值法，或稱插值法，計(jì)算出投資項(xiàng)目的內(nèi)含報(bào)酬率。

當(dāng)期內(nèi)每年現(xiàn)金凈流量不同時(shí)：

首先，預(yù)估一個(gè)折現(xiàn)率，并計(jì)算出此折現(xiàn)率水平下的凈現(xiàn)值；然后根據(jù)年金現(xiàn)值系數(shù)表中臨近兩個(gè)折現(xiàn)率使用插值法，計(jì)算出方案的實(shí)際內(nèi)含報(bào)酬率。這個(gè)過(guò)程就要涉及到一定的測(cè)算步驟，進(jìn)而測(cè)算出凈現(xiàn)值為零的折現(xiàn)率。

無(wú)論每年現(xiàn)金凈流量是否相等，在計(jì)算的過(guò)程中都要涉及到使用插值法對(duì)內(nèi)含報(bào)酬率進(jìn)行測(cè)算的過(guò)程。插值法（interpolation）做為一種常用的數(shù)學(xué)分析方法，其基本原理是由已知的離散因變量的值來(lái)估計(jì)未知的中間插值。這種計(jì)算方法并不復(fù)雜，但是在實(shí)際財(cái)務(wù)分析中卻因?yàn)樾枰婕胺槟杲瓞F(xiàn)值系數(shù)表、進(jìn)行折現(xiàn)率測(cè)算等步驟而顯得比較繁瑣費(fèi)時(shí)，所以我們?cè)谪?cái)務(wù)分析實(shí)際應(yīng)用中想到通過(guò)EXCEL數(shù)據(jù)分析功能對(duì)此步驟進(jìn)行簡(jiǎn)化。

2 基于EXCEL的內(nèi)含報(bào)酬率計(jì)算模型的設(shè)計(jì)應(yīng)用

2.1 現(xiàn)金流量表計(jì)算公式設(shè)置

表1投資項(xiàng)目A的營(yíng)業(yè)現(xiàn)金流量表初始數(shù)據(jù)（單位：萬(wàn)元）

■

如已知上述投資項(xiàng)目A的相關(guān)營(yíng)業(yè)現(xiàn)金流量數(shù)據(jù)，我們?cè)贓XCEL表中反映時(shí)，可以直接設(shè)置公式對(duì)未知數(shù)據(jù)進(jìn)行計(jì)算。

公式設(shè)置依據(jù)：①稅前利潤(rùn)=銷售收入-付現(xiàn)成本-折舊；②所得稅=稅前利潤(rùn)×稅率（40%）；③稅后凈利=稅前利潤(rùn)-所得稅；④營(yíng)業(yè)現(xiàn)金流量=銷售收入-付現(xiàn)成本-所得稅。由此我們可以對(duì)未知數(shù)據(jù)進(jìn)行公式設(shè)置：

表2投資項(xiàng)目A的營(yíng)業(yè)現(xiàn)金流量相關(guān)數(shù)據(jù)公式設(shè)置（單位：萬(wàn)元）

■

可以得出如下數(shù)據(jù)結(jié)果：

表3投資項(xiàng)目A的營(yíng)業(yè)現(xiàn)金流量相關(guān)數(shù)據(jù)自動(dòng)計(jì)算結(jié)果（單位：萬(wàn)元）

■

2.2 計(jì)算年金現(xiàn)值系數(shù)

年金現(xiàn)值系數(shù)=初始投資額÷每年現(xiàn)金凈流量

如果已知初始投資額為10000萬(wàn)元，那么公式設(shè)置過(guò)程如下：

表4年金現(xiàn)值系數(shù)公式設(shè)置及結(jié)果（單位：萬(wàn)元）

■

2.3 利用插值法，計(jì)算內(nèi)含報(bào)酬率

根據(jù)年金現(xiàn)值系數(shù)表，五年期的PVIFA為3.125的時(shí)候，正好介于3.127-3.058之間，其對(duì)應(yīng)的折現(xiàn)率為18%-19%。所以推斷IRR應(yīng)該介于18%-19%之間。根據(jù)插值法計(jì)算原理：(18%-IRR)/（18%-19%）=(3.127-3.125)/(3.127-3.058)；IRR=18%-(3.127-3.125)/(3.127-3.058)* （18%-19%）=18.03%。我們將此計(jì)算過(guò)程轉(zhuǎn)化為

EXCEL模型，如下：

①首先在原有EXCEL工作簿中插入一張工作表，將PVIFA表插入其中。

②根據(jù)現(xiàn)金流量表，設(shè)置已得到的年金現(xiàn)值系數(shù)3.125，自動(dòng)搜索當(dāng)期數(shù)為5年時(shí)，最接近3.125的兩個(gè)值，使用公式“=SMALL(PVIFA表!11:11,COUNTIF(PVIFA表!11:11,"<"&B11)+1)”和“=LARGE(PVIFA表!7:7,COUNTIF(PVIFA表!7:7,">"&B11)+1)”分別得到PVIFA表中對(duì)應(yīng)的3.058和3.127。

■

③根據(jù)以上得到的PVIFA值，確定其所對(duì)應(yīng)的折現(xiàn)率i?？捎肐NDEX公式設(shè)置在PVIFA表中查找。i下限公式為“=INDEX(PVIFA表!2:2,MATCH(B12,PVIFA表!7:7,0))”； i上限公式為“=INDEX(PVIFA表!2:2,MATCH(B13,PVIFA表!7:7,0))”。

④根據(jù)上述數(shù)據(jù)設(shè)置IRR計(jì)算公式：

IRR=B14-(B13-B11)/(B13-B12)*(B15-B14)

3 該模型總結(jié)

此項(xiàng)基于EXCEL的內(nèi)含報(bào)酬率計(jì)算模型可以將財(cái)務(wù)管理中內(nèi)含報(bào)酬率指標(biāo)的計(jì)算過(guò)程通過(guò)數(shù)據(jù)關(guān)聯(lián)加以簡(jiǎn)化，建立內(nèi)含報(bào)酬率的EXCEL計(jì)算界面，實(shí)現(xiàn)直接通過(guò)簡(jiǎn)單財(cái)務(wù)數(shù)據(jù)得到相應(yīng)財(cái)務(wù)分析指標(biāo)，直觀簡(jiǎn)單，避免了翻查PVIFA表以及手工計(jì)算插值法的繁瑣程序，通過(guò)期初現(xiàn)金流量數(shù)據(jù)可以直接得到IRR及相關(guān)分析數(shù)據(jù)。

另外相對(duì)于EXCEL公式中的IRR公式，應(yīng)用更加豐富和簡(jiǎn)便，規(guī)避了IRR的公式計(jì)算中大量的期初計(jì)算過(guò)程以及指數(shù)設(shè)置，從而實(shí)現(xiàn)了內(nèi)含報(bào)酬率計(jì)算的真正自動(dòng)化。

此模型不足之處在于在設(shè)置對(duì)應(yīng)的PVIFA表過(guò)程中由于表格中公式的對(duì)應(yīng)性，不能夠更大范圍以及更加靈活的處理所有變量，對(duì)于已知年限的情況下如果能夠自動(dòng)通過(guò)年限對(duì)應(yīng)i值，避免了后續(xù)公式設(shè)置中的個(gè)別手動(dòng)設(shè)置成分，直接實(shí)現(xiàn)數(shù)據(jù)輸入和結(jié)果得出的一步性，那么該模型就更加完善了。

參考文獻(xiàn)：

[1]荊新，王化成，劉俊彥.財(cái)務(wù)管理學(xué)[M].中國(guó)人民大學(xué)出版社,2012.

[2]張瑞君.計(jì)算機(jī)財(cái)務(wù)管理[M].中國(guó)人民大學(xué)出版社，2011.

[3]桂良軍.Excel會(huì)計(jì)與財(cái)務(wù)管理——理論、方案暨模型[M].高等教育出版社，2011.