利用Ｅｘｃｅｌ構建投資項目內部收益率敏感性分析模型

[摘 要] 文章介紹了利用Excel構建投資項目內部收益率敏感性分析模型的步驟和方法，以及如何利用該模型進行投資項目內部收益率的敏感性分析#65377;

[關鍵詞] Excel;投資項目;內部收益率;敏感性分析

[中圖分類號]F232 ;F275 [文獻標識碼]A[文章編號]1673-0194(2007)07-0069-03

在進行固定資產投資決策時，如果是在貼現率和未來現金流量確定的條件下，利用Excel的IRR函數(內部收益率函數)即可直接求出投資項目的內部收益率，并可據此判斷投資項目的可行性#65377;而實際上，固定資產投資項目涉及的時間較長，對未來收益和成本很難準確預測，投資活動中充滿了不確定性或風險，項目投產后所帶來的未來現金流量只是對未來可能發生結果的一種估計和預測，而不是未來實際發生的結果#65377;因此，在投資決策中，應充分考慮到風險因素#65377;

敏感性分析是固定資產投資決策中常用的一種重要的分析方法，用來衡量當投資方案中某個因素發生了變動時，對該方案預期結果的影響程度#65377;本文將介紹利用Excel構建投資項目內部收益率敏感性分析模型的步驟和方法，以及如何利用該模型進行投資項目內部收益率的敏感性分析#65377;

一#65380;投資項目內部收益率敏感性分析模型

構建的投資項目內部收益率敏感性分析模型，如圖1所示#65377;

圖1 投資項目內部收益率敏感性分析模型

二#65380;構建投資項目內部收益率敏感性分析模型的步驟

對投資項目內部收益率的敏感性進行分析，可以利用Excel構建如圖1所示的敏感性分析模型，分別進行多因素變動和單因素變動對內部收益率的影響分析#65377;但需要注意的是，當要分析單因素變動對內部收益率的影響時，內部收益率的計算是一件很麻煩的事，因為當投資項目壽命期內各年的凈現金流量不相等時，不能使用RATE函數來計算內部收益率，不過可以通過自定義內部收益率函數來解決這個問題#65377;

1. 自定義內部收益率函數的計算原理及步驟

(1)首先假定一個內部收益率的初始值，并以此內部收益率作為貼現率i，計算項目的凈現值NPV;

(2)根據計算出的凈現值數據，利用下面的公式計算第1次迭代后的內部收益率IRR:

式中 I為初始投資現值#65377;

若相鄰兩次計算的內部收益率相差不大，或計算出的凈現值接近于零，則停止計算，就得到了內部收益率的近似值，否則重復上述迭代步驟#65377;

2. 定義“內部收益率”自定義函數

定義一個名為“內部收益率”的自定義函數，其語法為:內部收益率(初始投資，期末殘值，壽命期，年付現成本，年銷售量，產品價格，單位變動成本，所得稅稅率)#65377;自定義函數可以通過一小段程序對其參數及參數之間的關系進行描述，這種程序又稱過程代碼#65377;

“內部收益率”自定義函數的建立方法和步驟如下:

單擊[工具]菜單，選擇[宏]項，在[宏]項的子菜單中選擇[Visual Basic編輯器]，打開Visual Basic編輯器窗口，再單擊Visual Basic編輯器窗口的[插入]菜單，選擇[模塊]項，則顯示模塊1的窗口#65377;在模塊1窗口中，單擊[插入]菜單，選擇[過程]項，則系統彈出[添加過程]對話框，如圖2所示#65377;在[添加過程]對話框中，[名稱]欄中輸入“內部收益率”，[類型]選“函數”，單擊[確定]，出現編輯過程頁面#65377;在該頁面中，將Public Function內部收益率和End Function修改為如下的過程代碼:

Public Function 內部收益率(初始投資，期末殘值，壽命期，年付現成本，年銷售量，產品價格，單位變動成本，所得稅稅率)

凈現金流量=(年銷售量*(產品價格-單位變動成本)/

10 000-年付現成本)*(1-所得稅稅率)+(初始投資-期末殘值)/壽命期*所得稅稅率

x1= 0.1

10jxz=凈現金流量*(1-(1+x1)^-壽命期)/x1-初始投資+期末殘值/(1+x1)^壽命期

x2=(1+x1)*(1+jxz/初始投資)^(1/壽命期)-l

If Abs(x2-x1)<=0.000 000 000 1Then 內部收益率=x2Elsex1=x2:

GoTo10

End Function

自定義函數“內部收益率”定義完畢，可以與Excel函數一樣使用#65377;

圖２ 添加過程對話框

3. 設計多因素變動對內部收益率綜合影響分析表格

在單元格B14中輸入預計內部收益率的計算公式為“=內部收益率(B4，B5，B6，B7，B8，B9，B10，B11)”(步驟為:單擊工具欄的[粘貼函數]按鈕，選擇“用戶定義”，選中“內部收益率”函數，出現該函數對話框，輸入相應的內容即可);單元格D14中的計算公式為“=內部收益率(C4，C5，C6，C7，C8，C9，C10，B11)”，在單元格F14中輸入公式“=D14-B14”#65377;這樣，就得到了多因素變動對內部收益率的綜合影響結果#65377;

4. 設計單因素變動影響分析表格

如圖1所示，在單元格B17:B23中輸入公式“=D4:D10”(數組公式輸入)，在單元格C17:C23中分別輸入各個因素單獨變動時的內部收益率計算函數如下:

單元格C17:“=內部收益率(C4，B5，B6，B7，B8，B9，B10，B11)”

單元格C18:“=內部收益率(B4，C5，B6，B7，B8，B9，B10，B11)”

單元格C19:“=內部收益率(B4，B5，C6，B7，B8，B9，B10，B11)”

單元格C20:“=內部收益率(B4，B5，B6，C7，B8，B9，B10，B11)”

單元格C21:“=內部收益率(B4，B5，B6，B7，C8，B9，B10，B11)”

單元格C22:“=內部收益率(B4，B5，B6，B7，B8，C9，B10，B11)”

單元格C23:“=內部收益率(B4，B5，B6，B7，B8，B9，C10，B11)”

在單元格D17:D23中輸入公式“=(C17:C23-B14)/B14”(數組公式輸入)#65377;

這樣，一個投資項目內部收益率的敏感性分析模型就建立起來了#65377;單擊各個影響因素滾動條的箭頭，改變其變動幅度，就可以很方便地了解各個因素對投資項目內部收益率的單獨影響程度以及綜合影響程度#65377;

三#65380;投資項目內部收益率敏感性分析模型的應用

在這個模型中，通過單擊滾動欄兩端的箭頭或用鼠標拖曳滑塊，即可改變各種因素的變動率，并分析其對投資項目內部收益率的影響程度#65377;如果某因素在較小范圍內發生了變動就會影響原定方案的經濟效果，即表明該因素的敏感性強;如果某因素在較大范圍內變動時才會影響原定方案的經濟效果，即表明該因素的敏感性弱#65377;

在長期投資決策中，敏感性分析通常用來研究有關投資方案的現金凈流量或固定資產壽命發生變動時，對該方案的凈現值和內部收益率的影響程度#65377;同時，它也可以用來研究有關投資項目的內部收益率變動時，對該方案的現金凈流量或使用年限的影響程度#65377;敏感性分析有助于企業領導了解在執行決策方案時應注意的問題，從而可以預先考慮措施與對策，避免決策上的失誤#65377;

主要參考文獻

[1] 韓良智等. Excel在財務管理與分析中的應用[M]. 北京:中國水利水電出版社，2004.

[2] 韓良智. Excel在投資理財中的應用[M]. 北京:電子工業出版社，2005.

[3] 鐘愛軍.用Excel進行利潤的敏感性分析[J]. 中國管理信息化，2006，(2):64.

[4] 楊鑒淞. 基于Excel的盈虧平衡分析在投資項目不確定性分析中應用[J]. 中國管理信息化，2006，(4):9.