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

楊毅 孫楠楠

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

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

1 內(nèi)含報酬率計算原理

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

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

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

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

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

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

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

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

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

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

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

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

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

■

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

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

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

■

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

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

■

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

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

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

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

■

2.3 利用插值法，計算內(nèi)含報酬率

根據(jù)年金現(xiàn)值系數(shù)表，五年期的PVIFA為3.125的時候，正好介于3.127-3.058之間，其對應(yīng)的折現(xiàn)率為18%-19%。所以推斷IRR應(yīng)該介于18%-19%之間。根據(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%。我們將此計算過程轉(zhuǎn)化為

EXCEL模型，如下：

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

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

■

③根據(jù)以上得到的PVIFA值，確定其所對應(yīng)的折現(xiàn)率i。可用INDEX公式設(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計算公式：

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

3 該模型總結(jié)

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

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

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

參考文獻：

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

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

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