1、Good is good, but better carries it.精益求精，善益求善。excel在投資項目不確定性風(fēng)險分析中的應(yīng)用-第8章Excel在投資項目不確定性風(fēng)險分析中的應(yīng)用8.1盈虧平衡分析(1)盈虧平衡分析的原理就是根據(jù)量本利之間的關(guān)系，計算項目的盈虧平衡點的銷售量，從而分析項目對市場需求變化的適應(yīng)能力。一般來說，盈虧平衡點是指企業(yè)既不虧又不盈或營業(yè)利潤為零時的銷售量。根據(jù)是否考慮資金的時間價值，盈虧平衡分析又可分為靜態(tài)盈虧平衡分析和動態(tài)盈虧平衡分析。8.1.1靜態(tài)盈虧平衡分析靜態(tài)盈虧平衡分析是在不考慮資金的時間價值情況下，對投資項目的盈虧平衡進(jìn)行分析。當(dāng)某年的營業(yè)利潤為零時

2、，可以得到該年盈虧平衡點的銷售量為（這里假設(shè)只有一種產(chǎn)品）：式中，Qt為第t年的盈虧平衡點銷售量（又稱保本銷售量）；Ft為第t年的固定成本，這里假設(shè)非付現(xiàn)固定成本只有折舊，即Ft=Dt+Fc，Dt為第t年的折舊；Fc為付現(xiàn)固定成本；p為產(chǎn)品單價；v為產(chǎn)品的單位變動成本，并假設(shè)各年的付現(xiàn)固定成本、產(chǎn)品單價和產(chǎn)品的單位變動成本均不變。當(dāng)產(chǎn)銷量低于盈虧平衡點銷售量時，投資項目處于虧損狀態(tài)，反之，當(dāng)產(chǎn)銷量超過盈虧平衡點銷售量時，項目就有了盈利。當(dāng)企業(yè)在盈虧平衡點附近經(jīng)營，即銷售量接近于Qt時，投資項目的經(jīng)營風(fēng)險很大，或經(jīng)營上的安全程度很低，銷售量微小的下降都可能使企業(yè)發(fā)生虧損。單一產(chǎn)品的盈虧平衡分析比

3、較簡單。根據(jù)給定的各年的付現(xiàn)固定成本、折舊、產(chǎn)品單價和單位變動成本，即可由上述公式計算出各年的靜態(tài)保本銷售量。當(dāng)一個投資項目同時生產(chǎn)多種不同的產(chǎn)品，或?qū)σ粋€生產(chǎn)多種產(chǎn)品的整個企業(yè)進(jìn)行盈虧平衡分析時，則需要考慮多品種產(chǎn)品的情況。在進(jìn)行多品種盈虧平衡分析時，加權(quán)平均法是較常用的一種方法。【例8-1】某企業(yè)生產(chǎn)A、B、C三種產(chǎn)品，A產(chǎn)品年銷售量100000件，單價10元/件，單位變動成本8.5元/件；B產(chǎn)品年銷售量25000臺，單價20元/臺，單位變動成本16元/臺；C產(chǎn)品年銷售量10000套，單價50元/套，單位變動成本25元/套；全廠固定成本300000元。根據(jù)以上資料，可以建立分析表格如圖8-

4、1所示。有關(guān)計算分析公式如下：銷售收入=銷售量單價邊際貢獻(xiàn)=銷售量（單價單位變動成本）邊際貢獻(xiàn)率=邊際貢獻(xiàn)銷售收入銷售比重=某產(chǎn)品銷售收入全廠各產(chǎn)品銷售收入合計全廠綜合邊際貢獻(xiàn)率=某產(chǎn)品邊際貢獻(xiàn)率該產(chǎn)品銷售比重全廠綜合保本額=全廠固定成本全廠綜合邊際貢獻(xiàn)率某產(chǎn)品保本額=全廠綜合保本額該產(chǎn)品銷售比重某產(chǎn)品保本量=某產(chǎn)品保本額該產(chǎn)品單價圖8-1某企業(yè)的多品種盈虧平衡分析輸入已知數(shù)據(jù)及定義完公式后，即可馬上計算出各個可變單元格的數(shù)值來，即全廠綜合保本額1200000元，產(chǎn)品A、B、C的保本額分別為600000元、300000元和300000元，保本量分別為60000件、15000臺和6000套。各單

5、元格的計算公式為單元格E3:E5：“=B3:B5*C3:C5”（數(shù)組公式輸入）。單元格F3:F5：“=B3:B5*(C3:C5-D3:D5)”（數(shù)組公式輸入）。單元格G3:G6：“=F3:F6/E3:E6”（數(shù)組公式輸入）。單元格E6：“=SUM(E3:E5)”。單元格F6：“=SUM(F3:F5)”。單元格B8:B10：“=E3:E5/E6”（數(shù)組公式輸入）。單元格D8:D10：“=B8:B10*D11”（數(shù)組公式輸入）。單元格E8:E10：“=D8:D10/C3:C5”（數(shù)組公式輸入）。單元格D11：“=H6/G6”。圖8-1建立了各產(chǎn)品的單價、單位變動成本和固定成本與保本額或保本量之間的

6、關(guān)系，利用圖8-1就可分析它們對盈虧平衡點的影響。8.1.2動態(tài)盈虧平衡分析8.1.2.1獨立項目的動態(tài)盈虧平衡分析靜態(tài)盈虧平衡分析沒有考慮資金的時間價值、所得稅、利率，以及通貨膨脹等因素的影響，由此計算出的盈虧平衡點銷售量僅僅能使項目的當(dāng)期達(dá)到盈虧平衡，卻并不能保證項目的凈現(xiàn)值恰好為零。在考慮資金的時間價值和所得稅等因素的條件下，項目的動態(tài)盈虧平衡點就是項目凈現(xiàn)值為零的那一點，即動態(tài)保本銷售量就是使項目凈現(xiàn)值為零的銷售量。考慮單一產(chǎn)品的情況，令NPV=0，則可得到項目各年的動態(tài)保本銷售量的計算公式為式中，T為所得稅稅率；Qt為各年的保本銷售量；pt、vt和Fct分別為各年的產(chǎn)品單價、單位變動

7、成本、付現(xiàn)固定成本；i為項目的基準(zhǔn)收益率；I為初始投資（并假設(shè)在第0年一次性投入項目）。這樣，利用上述公式，即可分析各種情況下項目各年保本銷售量的變化情況。【例8-2】某企業(yè)準(zhǔn)備投資生產(chǎn)一種新產(chǎn)品，項目總投資350萬元，項目壽命期5年，期末無殘值，采用直線法計提折舊。經(jīng)預(yù)測，項目投產(chǎn)后每年可銷售產(chǎn)品85000臺，產(chǎn)品單價40元/臺，單位變動成本20元/臺，年付現(xiàn)固定成本50萬元，企業(yè)的基準(zhǔn)收益率為10%，所得稅稅率33%。此時，各年的折舊額相同，假設(shè)各年的銷售量、產(chǎn)品單價、單位變動成本、付現(xiàn)固定成本以及折舊均相同，可以利用上述公式直接導(dǎo)出動態(tài)盈虧平衡銷售量（保本銷售量）的計算公式如下：根據(jù)此公

8、式計算出保本銷售量為76664件，如圖8-2所示，單元格E4中的保本銷售量計算公式為：“=(E3+(B3/PV(F3,B3,-1)-G3)*A3/B3/(1-G3)/(C3-D3)*10000”。圖8-2投資項目的動態(tài)盈虧平衡分析但實際上，各年的銷售量受到企業(yè)內(nèi)部及市場條件的影響，它們是不同的，故可以利用規(guī)劃求解工具來計算各年的保本銷售量，步驟如下：（1）如圖8-2所示，將單元格C10:G10作為可變單元格，存放各年的保本銷售量。（2）在單元格B7中輸入公式“=-A3”，在單元格C7:G7中輸入凈現(xiàn)金流量計算公式“=(C9:G9/10000*(C3-D3)-E3)*(1-G3)+SLN(A3,

9、H3,B3)*G3”（數(shù)組公式輸入）。（3）在單元格H7中輸入凈現(xiàn)值計算公式“=NPV(F3,C7:G7)+B7”。（4）單擊工具菜單中的【規(guī)劃求解】項，出現(xiàn)【規(guī)劃求解參數(shù)】對話框，其中【設(shè)置目標(biāo)單元格】選“$H$7”，【等于】選“0”，【可變單元格】選“$C$9:$G$9”；單擊【求解】按鈕，即得各年的保本銷售量，如圖8-2所示。當(dāng)可變單元格C9:G9中賦予不同的初始值時，會得到不同的保本銷售量,如圖8-2所示），各年的銷售量變化范圍很大，這正反映了動態(tài)盈虧平衡的特點。實際上，各年保本銷售量組合有無數(shù)個，故為求得符合實際情況的保本銷售量組合，還要考慮一些約束條件，比如各年的會計利潤應(yīng)大于零，

10、各年取得的現(xiàn)金收入應(yīng)能夠償還各年到期的債務(wù)以及支付股利，等等。8.1.2.2互斥項目的動態(tài)盈虧平衡分析在需要對若干個方案進(jìn)行比較的情況下，若是某一個共有的不確定性因素（比如銷售量、產(chǎn)量、壽命、產(chǎn)品價格、單位變動成本等）影響這些方案的取舍，則可以利用盈虧平衡分析幫助決策?！纠?-3】某企業(yè)投資生產(chǎn)某種產(chǎn)品，現(xiàn)有兩個方案，有關(guān)資料如圖8-3所示，該產(chǎn)品的市場壽命具有較大的不確定性，如果基準(zhǔn)收益率為15%，不考慮期末資產(chǎn)殘值，那么，企業(yè)應(yīng)如何根據(jù)項目的壽命期來選擇方案？圖8-3根據(jù)方案壽命期選擇項目如圖8-3所示，進(jìn)行決策分析的步驟如下：（1）首先在單元格F3中輸入“5”，在單元格H3中輸入“11”

11、，單元格G3可先空置。（2）選取單元格區(qū)域F4:H5，輸入不同壽命期下兩個方案的凈現(xiàn)值計算公式“=PV(D4:D5,F3:H3,-C4:C5)-B4:B5”（數(shù)組公式輸入）。（3）在單元格G6中輸入公式“=G4-G5”，將單元格G6作為目標(biāo)單元格，將單元格G3作為可變單元格，利用【規(guī)劃求解】工具或【單變量求解】工具，可以求得兩個方案凈現(xiàn)值相等時的項目壽命期。由計算結(jié)果可以看出，兩個方案凈現(xiàn)值相等時的壽命期為9.92年，這就是以項目壽命期為共有變量時方案1與方案2的凈現(xiàn)值無差異點。因此，當(dāng)壽命期小于9.92年時，應(yīng)采用方案1；而當(dāng)壽命期大于9.92年時，應(yīng)采用方案2。8.1.3投資項目盈虧平衡分

12、析模型除了前面介紹的直接利用公式計算盈虧平衡點保本銷售量或設(shè)計工作表格進(jìn)行分析外，我們還可以設(shè)計盈虧平衡分析模型來分析每個因素各種可能的變動情況下對保本銷售量的影響?！纠?-4】投資項目的盈虧平衡分析模型如圖8-4所示，具體設(shè)計步驟如下：圖8-4投資項目盈虧平衡分析模型（1）首先設(shè)計好分析模型結(jié)構(gòu)，如圖8-4所示（表中數(shù)據(jù)以例8-2為例）。（2）單擊【視圖】，選擇【工具欄】，再單擊【窗體】，出現(xiàn)【窗體】工具欄，如圖8-5所示，單擊【滾動條】按鈕，然后在工作表的合適位置（這里為E4F4單元格）拖曳出一個矩形【組合框】控件，并調(diào)整其大小。圖8-5【窗體】工具欄（3）將鼠標(biāo)移到新建立的【滾動條】控件

13、上，單擊鼠標(biāo)右鍵，出現(xiàn)快捷菜單，選擇【設(shè)置控件格式】，出現(xiàn)【設(shè)置控件格式】對話框，選擇【控制】項，如圖8-6所示。圖8-6【設(shè)置控件格式】對話框（4）在【當(dāng)前解】欄輸入25，【最小值】輸入0，【最大值】輸入50，【步長】輸入1，【頁步長】輸入10，在【單元格鏈接】填入“E4”，然后單擊【確定】按鈕，這就建立了初始投資的【滾動條】控件。（5）其他項目的【滾動條】控件可按照上述方法進(jìn)行。（6）在單元格C4:C10中建立變動百分比與【滾動條】控件的聯(lián)系，即：在單元格C4中輸入公式“=E4/100-25%”，并將單元格C4分別復(fù)制到單元格C5:C10中。說明：本例中各因素的變動范圍為-25%+25%，

14、而滾動條控制按鈕的值的變化范圍為050，為了使?jié)L動條控制按鈕的變化表示為百分?jǐn)?shù)的變化，這里將控制按鈕的值除以100后再減去25%，則每次單擊滾動條兩端的箭頭，單元格C4:C10中的變動百分比就變化1%，而當(dāng)滾動條在中間位置，變動百分比恰好為零。（7）選取單元格D4:D10區(qū)域并輸入變化后數(shù)值的計算公式“=B4:B10*(1+C4:C10)”（數(shù)組公式輸入）。（8）在單元格C15中輸入靜態(tài)盈虧平衡銷售量計算公式“=(SLN(D4,D5,B11)+D6)/(D8-D9)*10000”。(9）在單元格C16中輸入動態(tài)盈虧平衡銷售量計算公式：“=(D6+(D4-PV(D10,B11,-1)*SLN(D

15、4,D5,B11)*B13-D5/(1+D10)B11)/PV(D10,B11,-1)/(1-B13)/(D8-D9)*10000”。這里，當(dāng)項目各年的銷售量、付現(xiàn)成本、產(chǎn)品單價、單位變動成本、折舊（直線法）相等、且考慮固定資產(chǎn)殘值回收時，動態(tài)盈虧平衡銷售量計算公式為式中，S為期末固定資產(chǎn)殘值。這樣，就建立了投資項目盈虧平衡分析模型。每次單擊滾動欄兩端的箭頭或用鼠標(biāo)拖曳滑塊，變動百分比就變化1%，當(dāng)在滾動框與滾動條之間單擊滾動條時，變動百分比就以10%變化。則通過不同的因素變化可以了解投資項目盈虧平衡銷售量的變化情況，有利于企業(yè)管理者進(jìn)行決策。第8章Excel在投資項目不確定性風(fēng)險分析中的應(yīng)用

16、8.2敏感性分析(1)敏感性分析是投資決策中常用的一種重要的分析方法。它是用來衡量當(dāng)投資方案中某個因素發(fā)生了變動時，對該方案預(yù)期結(jié)果的影響程度。如果某因素在較小范圍內(nèi)發(fā)生了變動就會影響原定方案的經(jīng)濟(jì)效果，即表明該因素的敏感性強(qiáng)；如果某因素在較大范圍內(nèi)變動時才會影響原定方案的經(jīng)濟(jì)效果，即表明該因素的敏感性弱。通常要作敏感性分析的因素有：（1）投資額，包括固定資產(chǎn)投資和追加的流動資產(chǎn)投資。（2）項目壽命期。（3）產(chǎn)品的產(chǎn)銷量。（4）產(chǎn)品價格。（5）經(jīng)營成本，特別是其中的變動成本。（6）項目壽命期末的設(shè)備殘值。（7）折現(xiàn)率。在長期投資決策中，敏感性分析通常用來研究有關(guān)投資方案的現(xiàn)金凈流量或固定資產(chǎn)壽

17、命發(fā)生變動時，對該方案的凈現(xiàn)值和內(nèi)部收益率的影響程度。同時，它也可以用來研究有關(guān)投資項目的內(nèi)部收益率變動時，對該方案的現(xiàn)金凈流量或使用年限的影響程度。敏感性分析有助于企業(yè)領(lǐng)導(dǎo)了解在執(zhí)行決策方案時應(yīng)注意的問題，從而可以預(yù)先考慮措施與對策，避免決策上的失誤。8.2.1一般的敏感性分析方法【例8-5】圖8-7為某一投資方案的有關(guān)資料，所采用的數(shù)據(jù)是根據(jù)對未來可能出現(xiàn)的情況預(yù)測的，未來的投資額、付現(xiàn)成本和銷售收入都有可能在30%的范圍內(nèi)變動。試對這三個因素做敏感性分析。企業(yè)采用直線法計提折舊，基準(zhǔn)收益率為15%。圖8-7不確定性因素對凈現(xiàn)值的影響一般性的敏感性分析方法和步驟如下：（1）設(shè)計如圖8-7所

18、示的分析表格。（2）在單元格B10:H10中輸入投資額變動對凈現(xiàn)值的影響計算公式：“=PV(E3,10,-(C4-C5)*(1-F3)+SLN(B3*(1+B9:H9),D6,10)*F3)+D6/(1+E3)10-B3*(1+B9:H9)”。（3）在單元格B11:H11中輸入銷售收入變動對凈現(xiàn)值的影響計算公式：“=PV(E3,10,-(C4*(1+B9:H9)-C5)*(1-F3)+SLN(B3,D6,10)*F3)+D6/(1+E3)10-B3”。（4）在單元格B12:H12中輸入付現(xiàn)成本變動對凈現(xiàn)值的影響計算公式：“=PV(E3,10,-(C4-C5*(1+B9:H9)*(1-F3)+S

19、LN(B3,D6,10)*F3)+D6/(1+E3)10-B3”。以上各單元格區(qū)域的公式輸入均為數(shù)組公式輸入，則計算結(jié)果如圖8-7所示。對計算結(jié)果繪制分析圖如圖8-8所示，步驟如下：圖8-8敏感性分析圖（1）選取單元格區(qū)域A9:H12，單擊工具欄上的【圖表向?qū)А堪粹o，在【圖表向?qū)?步驟之1圖表類型】對話框中，【圖表類型】選“XY散點圖”，【子圖表類型】選“平滑線散點圖”，單擊【下一步】按鈕。（2）在【圖表向?qū)?步驟之2圖表源數(shù)據(jù)】對話框中，不做任何輸入，單擊【下一步】按鈕。（3）在【圖表向?qū)?步驟之3圖表選項】對話框中，在【圖表標(biāo)題】欄中輸入“敏感性分析圖”，在【數(shù)值(X)軸】欄中輸入“不確定

20、性因素變動幅度”，在【數(shù)值(Y)軸】欄中輸入“凈現(xiàn)值”，單擊【下一步】按鈕。（4）在【圖表向?qū)?步驟之4圖表位置】對話框中，不做任何輸入，單擊【確定】按鈕。（5）對圖表的大小、坐標(biāo)數(shù)值、標(biāo)題等格式進(jìn)行調(diào)整，使圖表賞心悅目，則圖表制作即告完成?？梢?，銷售收入對凈現(xiàn)值的影響最大，付現(xiàn)成本其次，而投資額的影響最小。然后可以利用單變量求解工具求出當(dāng)凈現(xiàn)值為零時每個不確定性因素的變動數(shù)值，方法是：在J10中輸入公式“=PV(E3,10,-(C4-C5)*(1-F3)+SLN(B3*(1+I10),D6,10)*F3)+D6/(1+E3)10-B3*(1+I10)”，并將J10作為目標(biāo)單元格，I10作為可

21、變單元格，即可利用單變量求解工具計算出凈現(xiàn)值為零時的投資額最大變動率。用同樣的方法可以求出凈現(xiàn)值為零時的銷售收入和付現(xiàn)成本最大變動率?？梢姡?dāng)銷售收入和付現(xiàn)成本不變時，投資額增加到41.64%以上時會使方案變得不應(yīng)被接受；當(dāng)投資額和付現(xiàn)成本不變時，銷售收入低于預(yù)期值的11.48%以上時會使方案變得不應(yīng)被接受；而當(dāng)投資額和銷售收入不變時，付現(xiàn)成本高于預(yù)期值18.79%以上時會使方案變得不應(yīng)被接受。因此，三個因素的敏感性由強(qiáng)到弱的排序依次為：銷售收入、付現(xiàn)成本和投資額。8.2.2投資項目凈現(xiàn)值敏感性分析模型我們也可以參照前面所述的投資項目盈虧平衡分析模型，來建立投資項目凈現(xiàn)值敏感性分析模型。【例8

22、-6】建立投資項目凈現(xiàn)值敏感性分析模型。如圖8-9所示，這里最大變化區(qū)間取50%，在【設(shè)置控件格式】對話框的【當(dāng)前解】欄中輸入“50”，【最大值】欄中輸入“100”，【頁步長】欄中輸入“5”。其他項目的計算方法同投資項目盈虧平衡分析模型。定義一個名為“凈現(xiàn)值”的自定義函數(shù)，其語法為：凈現(xiàn)值(初始投資,期末殘值,壽命期,年付現(xiàn)固定成本,年銷售量,產(chǎn)品價格,單位變動成本,基準(zhǔn)收益率,所得稅稅率)。自定義函數(shù)可以通過一小段程序?qū)ζ鋮?shù)及參數(shù)之間的關(guān)系進(jìn)行描述，這種程序又稱過程代碼?！皟衄F(xiàn)值”自定義函數(shù)的過程代碼如下：PublicFunction凈現(xiàn)值(初始投資,期末殘值,壽命期,年付現(xiàn)固定成本,年銷

23、售量,產(chǎn)品價格,單位變動成本,基準(zhǔn)收益率,所得稅稅率)凈現(xiàn)金流量=(年銷售量*(產(chǎn)品價格-單位變動成本)/10000-年付現(xiàn)固定成本)*(1-所得稅稅率)+(初始投資-期末殘值)/壽命期*所得稅稅率圖8-9投資項目凈現(xiàn)值敏感性分析模型凈現(xiàn)值=凈現(xiàn)金流量*(1-(1+基準(zhǔn)收益率)-壽命期)/基準(zhǔn)收益率-初始投資+期末殘值/(1+基準(zhǔn)收益率)壽命期EndFunction在單元格A16中輸入預(yù)計凈現(xiàn)值計算公式“=凈現(xiàn)值(B4,B5,B6,B7,B8,B9,B10,B11,$B$12)”，（步驟為：單擊工具欄的【粘貼函數(shù)】按鈕，選擇“用戶定義”，選中“凈現(xiàn)值”函數(shù)，出現(xiàn)該函數(shù)對話框，輸入相應(yīng)的內(nèi)容即可

24、），并復(fù)制到單元格B16中；在單元格C16中輸入公式“=B16-A16”，在單元格D16輸入公式“=C16/A16”。這樣，就得到了多因素變動對凈現(xiàn)值的綜合影響結(jié)果。設(shè)計單因素變動影響分析表格，如圖8-9所示，在單元格B19:B26中輸入公式“=D4:D11”（數(shù)組公式輸入），在單元格C19:C26中分別粘貼各個因素單獨變動時的凈現(xiàn)值計算函數(shù)如下：單元格C19：凈現(xiàn)值(C4,B5,B6,B7,B8,B9,B10,B11,B12)單元格C20：凈現(xiàn)值(B4,C5,B6,B7,B8,B9,B10,B11,B12)單元格C21：凈現(xiàn)值(B4,B5,C6,B7,B8,B9,B10,B11,B12)單元

25、格C22：凈現(xiàn)值(B4,B5,B6,C7,B8,B9,B10,B11,B12)單元格C23：凈現(xiàn)值(B4,B5,B6,B7,C8,B9,B10,B11,B12)單元格C24：凈現(xiàn)值(B4,B5,B6,B7,B8,C9,B10,B11,B12)單元格C25：凈現(xiàn)值(B4,B5,B6,B7,B8,B9,C10,B11,B12)單元格C26：凈現(xiàn)值(B4,B5,B6,B7,B8,B9,B10,C11,B12)在單元格D19:D26中輸入公式“=C19:C26-A16”（數(shù)組公式輸入），在單元格E19:E26中輸入公式“=D19:D26/A16”（數(shù)組公式輸入）。則投資項目敏感性分析模型就建立起來了。

26、單擊各個影響因素滾動條的箭頭，改變其變動幅度，就可以很方便地了解各個因素對投資項目凈現(xiàn)值的單獨影響程度以及綜合影響程度。這樣，通過單擊滾動欄兩端的箭頭或用鼠標(biāo)拖曳滑塊，即可改變各種因素的變動率，并分析其對項目凈現(xiàn)值的影響。8.2.3投資項目內(nèi)部收益率敏感性分析模型【例8-7】建立投資項目內(nèi)部收益率敏感性分析模型。我們也可以對投資項目的內(nèi)部收益率的敏感性進(jìn)行分析，方法與投資項目凈現(xiàn)值敏感性分析模型是一樣的。但需要注意的是，當(dāng)要分析單因素變動對內(nèi)部收益率的影響時，內(nèi)部收益率的計算是一件很麻煩的事，因為當(dāng)投資項目壽命期內(nèi)各年的凈現(xiàn)金流量不相等時，不能使用RATE函數(shù)來計算內(nèi)部收益率，不過可以通過自定

27、義內(nèi)部收益率函數(shù)來解決這個問題。作者研究了一種內(nèi)部收益率的穩(wěn)定迭代計算方法，具有穩(wěn)定、快速、收斂性好的優(yōu)點，計算原理及步驟如下：（1）首先假定一個內(nèi)部收益率的初始值，并以此內(nèi)部收益率作為貼現(xiàn)率i，計算項目的凈現(xiàn)值NPV；（2）根據(jù)計算出的凈現(xiàn)值數(shù)據(jù)，利用下面的公式計算第1次迭代后的內(nèi)部收益率IRR：式中I初始投資現(xiàn)值。若相鄰兩次計算的內(nèi)部收益率相差不大，或計算出的凈現(xiàn)值接近于零，則停止計算，就得到了內(nèi)部收益率的近似值，否則重復(fù)上述迭代步驟。內(nèi)部收益率函數(shù)的過程代碼如下：PublicFunction內(nèi)部收益率(初始投資,期末殘值,壽命期,年付現(xiàn)成本,年銷售量,產(chǎn)品價格,單位變動成本,所得稅稅率)

28、凈現(xiàn)金流量=(年銷售量*(產(chǎn)品價格-單位變動成本)/10000-年付現(xiàn)成本)*(1-所得稅稅率)+(初始投資-期末殘值)/壽命期*所得稅稅率x1=0.110jxz=凈現(xiàn)金流量*(1-(1+x1)-壽命期)/x1-初始投資+期末殘值/(1+x1)壽命期x2=(1+x1)*(1+jxz/初始投資)(1/壽命期)-1IfAbs(x2-x1)=0.0000000001Then內(nèi)部收益率=x2Elsex1=x2:GoTo10EndFunction這樣，就可以分析不同因素變動對內(nèi)部收益率的影響。圖8-10為投資項目內(nèi)部收益率敏感性分析模型。其中單元格B14中的計算公式為“=內(nèi)部收益率(B4,B5,B6,B

29、7,B8,B9,B10,B11)”；單元格D14中的計算公式為“=內(nèi)部收益率(C4,C5,C6,C7,C8,C9,C10,B11)”，單元格C17:C23中的計算公式分別如下：圖8-10投資項目內(nèi)部收益率敏感性分析模型單元格C17：“=內(nèi)部收益率(C4,B5,B6,B7,B8,B9,B10,B11)”單元格C18：“=內(nèi)部收益率(B4,C5,B6,B7,B8,B9,B10,B11)”單元格C19：“=內(nèi)部收益率(B4,B5,C6,B7,B8,B9,B10,B11)”單元格C20：“=內(nèi)部收益率(B4,B5,B6,C7,B8,B9,B10,B11)”單元格C21：“=內(nèi)部收益率(B4,B5,B6

30、,B7,C8,B9,B10,B11)”單元格C22：“=內(nèi)部收益率(B4,B5,B6,B7,B8,C9,B10,B11)”單元格C23：“=內(nèi)部收益率(B4,B5,B6,B7,B8,B9,C10,B11)”其他各單元格的計算公式可參閱例8-6。第8章Excel在投資項目不確定性風(fēng)險分析中的應(yīng)用8.3概率分析概率分析是通過研究各種不確定性因素發(fā)生不同幅度變動的概率分布及其對投資方案經(jīng)濟(jì)效果的影響，對方案的凈現(xiàn)金流量及其經(jīng)濟(jì)效果指標(biāo)作出某種概率描述，從而對方案的風(fēng)險情況作出比較準(zhǔn)確的判斷。在實際經(jīng)濟(jì)活動中，影響投資方案經(jīng)濟(jì)效果的大多數(shù)因素（如投資額、成本、銷售量、產(chǎn)品價格、項目壽命期等）都是隨機(jī)變

31、量，我們可以預(yù)測其未來可能的取值范圍，估計各種取值或值域發(fā)生的概率，但不能肯定地預(yù)知它們?nèi)∈裁粗?。因此，這就需要對投資項目進(jìn)行概率分析。假設(shè)投資項目有m種可能出現(xiàn)的凈現(xiàn)金流量狀態(tài)，各種狀態(tài)所對應(yīng)的凈現(xiàn)金流量序列為yj，各種狀態(tài)發(fā)生的概率為Pj（），則在第j種狀態(tài)下，方案的凈現(xiàn)值為式中，為在第j種狀態(tài)下，第t周期的凈現(xiàn)金流量；n為項目的壽命期。則投資方案的凈現(xiàn)值期望值為而凈現(xiàn)值的方差為標(biāo)準(zhǔn)差為對于獨立方案，計算其凈現(xiàn)值期望值和標(biāo)準(zhǔn)差的大小，可以分析其獲利能力及風(fēng)險的大小。對于幾個互斥方案，可以比較它們的變異系數(shù)的大小，以便衡量其相對風(fēng)險的高低，從而作出決策，變異系數(shù)計算公式為8.3.1獨立項目的

32、概率分析8.3.1.1各年凈現(xiàn)金流量互不相關(guān)情況下的獨立項目概率分析【例8-8】某企業(yè)的投資方案在其壽命期內(nèi)可能出現(xiàn)5種狀態(tài)的凈現(xiàn)金流量序列及其發(fā)生的概率如圖8-11所示。各年的凈現(xiàn)金流量互不相關(guān)，基準(zhǔn)收益率10%，試對方案進(jìn)行概率分析。圖8-11投資方案的概率分析如圖8-11所示，選取單元格C8:G8區(qū)域，輸入不同狀態(tài)下凈現(xiàn)值的計算公式“=PV(10%,10,-C5:G5)+C6:G6/(1+10%)11+C4:G4”（數(shù)組公式輸入）；在單元格C9中輸入凈現(xiàn)值期望值計算公式“=SUMPRODUCT(C3:G3,C8:G8)”，在單元格C10中輸入凈現(xiàn)值方差計算公式“=SUMPRODUCT(C

33、3:G3,(C8:G8-C9)2)”，在單元格C11中輸入凈現(xiàn)值標(biāo)準(zhǔn)差計算公式“=SQRT(C10)”，在單元格C12中輸入變異系數(shù)計算公式“=C11/C9”，從而得到該方案的有關(guān)計算結(jié)果數(shù)據(jù)，如圖8-11所示?？梢?，該方案的凈現(xiàn)值期望值大于零，是可行的，但風(fēng)險也較大。8.3.1.2各年凈現(xiàn)金流量相關(guān)情況下的獨立項目概率分析概率樹分析法【例8-9】某企業(yè)擬投資開發(fā)一項專有技術(shù)，其初始投資為12萬元，該項技術(shù)預(yù)計在3年內(nèi)有效，3年內(nèi)每年為企業(yè)帶來的現(xiàn)金流量是不確定的，其有關(guān)資料如表8-1所示。該企業(yè)的資本成本為15%，試對該投資項目的可行性進(jìn)行評價。表81投資項目有關(guān)資料單位：萬元項目初始投資1

34、2壽命/年3貼現(xiàn)率15%第1年第2年第3年凈現(xiàn)金流量概率凈現(xiàn)金流量概率凈現(xiàn)金流量概率7.50.6100.7140.6110.37.50.160.390.470.440.240.480.2100.390.480.370.490.750.350.47.50.150.32.50.6為便于分析計算，將表8-1中的數(shù)據(jù)填列在Excel上，其格式如圖8-12所示，則具體計算步驟如下：（1）在單元格G6中輸入第一種現(xiàn)金流序列情況下的凈現(xiàn)值計算公式“=NPV($F$3,A6,C6,E6)-$B$3”，然后將此單元格復(fù)制到單元格G7G19。（2）在單元格H6:H19中輸入聯(lián)合概率計算公式“=B6:B19*D6:

35、D19*F6:F19”（數(shù)組公式輸入）。（3）在單元格I6中輸入凈現(xiàn)值期望值計算公式“=SUMPRODUCT(G6:G19,H6:H19)”。（4）在單元格J6中輸入凈現(xiàn)值標(biāo)準(zhǔn)差計算公式“=SQRT(SUMPRODUCT(H6:H19,(G6:G19-I6)2)”。（5）在單元格J6中輸入凈現(xiàn)值為負(fù)的概率計算公式“=SUMIF(G6:G19,0,H6:H19)”?？梢?，該項目的期望凈現(xiàn)值為5.21萬元，凈現(xiàn)值為負(fù)的概率很小。只有0.144，該項目的獲利能力較高而風(fēng)險不大，故該項目是可行的。圖8-12投資項目概率分析8.3.2互斥項目的概率分析在此種情況下，可分別計算各個項目的凈現(xiàn)值期望值、標(biāo)準(zhǔn)

36、差及變異系數(shù)，對它們進(jìn)行綜合分析，以確定最優(yōu)方案?！纠?-10】某企業(yè)有A、B兩個投資項目，其投資額及未來各年可能的凈現(xiàn)金流量如圖8-13所示，假設(shè)各年的凈現(xiàn)金流量相互獨立。那么，企業(yè)應(yīng)選擇哪個項目？圖8-13各年現(xiàn)金流獨立的互斥方案的概率分析如圖8-13所示，分析決策過程如下：（1）在合并單元格G5G7中輸入項目A第1年的凈現(xiàn)金流量期望值計算公式“=SUMPRODUCT(C5:C7,D5:D7)”，然后將此合并單元格分別復(fù)制到合并單元格G8G10、G11G13、G14G16中，得到項目A各年的凈現(xiàn)金流量期望值。（2）在合并單元格H5H7中輸入項目B第1年的凈現(xiàn)金流量期望值計算公式“=SUMP

37、RODUCT(E5:E7,F5:F7)”，然后將此合并單元格分別復(fù)制到合并單元格H8H10、H11H13、H14H16中，得到項目B各年的凈現(xiàn)金流量期望值。（3）在合并單元格I5I7中輸入項目A第1年的凈現(xiàn)金流量標(biāo)準(zhǔn)差計算公式“=SQRT(SUMPRODUCT(C5:C7,(D5:D7-G5)2)”，然后將此合并單元格分別復(fù)制到合并單元格I8I10、I11I13、I14I16中，得到項目A各年的凈現(xiàn)金流量標(biāo)準(zhǔn)差。（4）在合并單元格J5J7中輸入項目B第1年的凈現(xiàn)金流量標(biāo)準(zhǔn)差計算公式“=SQRT(SUMPRODUCT(E5:E7,(F5:F7-H5)2)”，然后將此合并單元格分別復(fù)制到合并單元格

38、J8J10、J11J13、J14J16中，得到項目B各年的凈現(xiàn)金流量標(biāo)準(zhǔn)差。（5）在單元格C20中輸入項目A的凈現(xiàn)值期望值計算公式“=NPV(B17,G5,G8,G11,G14)+G4”，在單元格C21中輸入凈現(xiàn)值標(biāo)準(zhǔn)差計算公式“=SQRT(SUM(I5:I162/(1+B17)(2*A5:A16)”（數(shù)組公式輸入），在單元格C22中輸入變異系數(shù)公式“=C21/C20”；這里，整個項目的凈現(xiàn)值標(biāo)準(zhǔn)差現(xiàn)值的計算公式為（6）同樣在單元格D20、D21、D22中分別輸入項目B的凈現(xiàn)值期望值、標(biāo)準(zhǔn)差和變異系數(shù)計算公式“=NPV(B17,H5,H8,H11,H14)+H4”、“=SQRT(SUM(J5:

39、J162/(1+B17)(2*A5:A16)”（數(shù)組公式輸入）和“=D21/D20”。根據(jù)計算結(jié)果可見，項目A風(fēng)險較低但收益也低，而項目B風(fēng)險較高但收益也高。但兩個項目的變異系數(shù)相差并不是很大，在這種情況下，如何作出決策，在很大程度上取決于決策人員對風(fēng)險的態(tài)度。愿意冒風(fēng)險的決策人員，會選擇項目B，而對風(fēng)險很敏感的決策者可能會傾向于選擇項目A。第8章Excel在投資項目不確定性風(fēng)險分析中的應(yīng)用8.4蒙特卡羅模擬前面介紹的概率分析法在各年現(xiàn)金流彼此獨立、且變量及其可能發(fā)生的情況較少的情況下，進(jìn)行模擬計算是比較簡單的。但在變量及其可能發(fā)生的情況較多的情況下，則必須進(jìn)行大量的組合計算，例如，假設(shè)我們估

40、計的銷售量、單價、單位變動成本的可能發(fā)生概率次數(shù)分別為5次、4次和4次，則每年就需要計算544=80個可能組合，假設(shè)項目的壽命為5年，則需要計算805=400個可能組合，計算工作量非常大。利用蒙特卡羅模擬法可以解決這個問題，并且還可以對項目的風(fēng)險進(jìn)行更為深入的分析。蒙特卡羅模擬法，是根據(jù)隨機(jī)數(shù)對影響因素的概率分布進(jìn)行隨機(jī)抽樣，根據(jù)每次抽樣值來計算項目的凈現(xiàn)金流量、凈現(xiàn)值及內(nèi)部收益率等指標(biāo)。在利用蒙特卡羅模擬法對投資項目進(jìn)行分析計算時，主要利用兩個函數(shù)：RANDBETWEEN函數(shù)和VLOOKUP函數(shù)，利用RANDBETWEEN函數(shù)產(chǎn)生隨機(jī)數(shù)，然后利用VLOOKUP函數(shù)來查找對應(yīng)隨機(jī)數(shù)的變量數(shù)值。

41、當(dāng)進(jìn)行足夠的模擬計算次數(shù)后，即可得到投資項目凈現(xiàn)值的期望值、標(biāo)準(zhǔn)差及概率分布。8.4.1獨立項目的蒙特卡羅模擬【例8-11】某企業(yè)計劃投資一項目，初始投資20000元，項目壽命3年，期末無殘值。經(jīng)統(tǒng)計分析得出產(chǎn)品銷售量、單價、單位變動成本和付現(xiàn)固定成本在各年可能達(dá)到的水平及有關(guān)的概率情況如圖8-14所示。則利用蒙特卡羅模擬法進(jìn)行投資項目不確定性分析的方法和步驟如下：圖8-14投資項目的有關(guān)資料及整理（1）首先設(shè)計模擬計算表格，如圖8-15所示。圖8-15只列出了對第1年的3次模擬計算過程，其他各年的模擬計算過程及表格排列順序與第1年相同，其中第2年的模擬計算單元格在第JR列，第3年的模擬計算單

42、元格在第SAA列。下面僅就第1年的模擬計算過程進(jìn)行介紹。圖8-15第1年的前3次模擬計算結(jié)果（2）在A25:A5024單元格輸入第1年銷售量的隨機(jī)數(shù)計算公式：“=RANDBETWEEN(0,99)”（第25行輸入后，再將其復(fù)制到A26A5024單元格中），然后在B25:B5024單元格輸入對應(yīng)的銷售量公式：“=VLOOKUP(A25:A5024,C3:D6,2)”（數(shù)組公式輸入，共運算5000行，即進(jìn)行5000次模擬計算）。說明：當(dāng)利用VLOOKUP函數(shù)尋找符合某一概率的銷售量時，必須先對概率及銷售量進(jìn)行分區(qū)。以第1年為例，隨機(jī)數(shù)為0、22、56和86時對應(yīng)的銷售量分別為4000、5000、6

43、000、7000件，其意義就是當(dāng)產(chǎn)生的隨機(jī)數(shù)分別為021、2255、5685和8699時，對應(yīng)的銷售量分別為4000、5000、6000、7000件，而累計概率分別為0.22、0.56、0.86和1.00。（3）在C25:C5024單元格輸入第1年單價的隨機(jī)數(shù)計算公式：“=RANDBETWEEN(0,99)”，然后在D25:D5024單元格輸入對應(yīng)的單價公式：“=VLOOKUP(C25:C5024,C7:D11,2)”（數(shù)組公式輸入）。（4）在E25:E5024單元格輸入第1年單位變動成本的隨機(jī)數(shù)計算公式：“=RANDBETWEEN(0,99)”，然后在F25:F5024單元格輸入對應(yīng)的單位變

44、動成本公式：“=VLOOKUP(E25:E5024,C12:D15,2)”（數(shù)組公式輸入）。（5）在G25:G5024單元格輸入第1年付現(xiàn)成本的隨機(jī)數(shù)計算公式：“=RANDBETWEEN(0,99)”，然后在H25:H5024單元格輸入對應(yīng)的付現(xiàn)固定成本公式：“=VLOOKUP(G25:G5024,C16:D19,2)”（數(shù)組公式輸入）。（6）在I25:I5024單元格輸入第1年凈現(xiàn)金流量計算公式：“=(B25:B5024*(D25:D5024-F25:F5024)-H25:H5024)*(1-F20)+B20/3*F20”（數(shù)組公式輸入），這里采用直線法計提折舊，年折舊=$B$20/3。第2

45、年、第3年的凈現(xiàn)金流量分別放在單元格R25:R5024和單元格AA25:AA5024中，而凈現(xiàn)值則存放在AB25:AB5024中，其計算公式為：“=I25/(1+$D$20)+R25/(1+$D$20)2+AA25/(1+$D$20)3-$B$20”（可先輸入AB25單元格，然后再復(fù)制到AB26AB5024單元格中。（7）最后的模擬計算結(jié)果存放在單元格K1:M7中，如圖8-16所示，各單元格的計算公式為：單元格M2“=AVERAGE(AB25:AB5024)”，單元格M3“=STDEV(AB25:AB5024)”，單元格M4“=MAX(AB25:AB5024)”，單元格M5“=MIN(AB25

46、:AB5024)”，單元格M6“=M3/M2”，單元格M7“=COUNTIF(AB25:AB5024,0)/COUNT(AB25:AB5024)”。圖8-16模擬計算結(jié)果在模擬計算完畢后，還可以利用Excel的繪圖工具繪制凈現(xiàn)值概率分布圖，具體步驟如下：（1）首先對凈現(xiàn)值進(jìn)行分組，以便于進(jìn)行統(tǒng)計分析，分組結(jié)果存放在單元格O4:O17中。（2）設(shè)計圖表上凈現(xiàn)值的分區(qū)，存放在單元格P4:P17中。（3）再選取單元格區(qū)域Q4:Q16，輸入FREQUENCY函數(shù)：“=FREQUENCY(AB25:AB5024,O4:O16)/5000”（數(shù)組公式輸入），即可得到凈現(xiàn)值概率分布結(jié)果，如8-17所示。然后

47、插入直方圖，做法是：選擇單元格區(qū)域P4:Q17，在圖表向?qū)е羞x柱形圖，再依據(jù)有關(guān)說明輸入有關(guān)資料。凈現(xiàn)值概率分布圖如圖8-18所示。圖8-17凈現(xiàn)值概率分布統(tǒng)計圖8-18凈現(xiàn)值概率分布圖根據(jù)計算結(jié)果及凈現(xiàn)值概率分布圖，就可以對投資項目的不確定性進(jìn)行分析。由結(jié)果可以看出，該項目的凈現(xiàn)值期望值為10642元，標(biāo)準(zhǔn)差8823元，凈現(xiàn)值為負(fù)的概率為0.112，故該項目的投資風(fēng)險還是比較小的。該項目的凈現(xiàn)值主要集中在020000元，概率為0.74，其中在500015000元的概率為0.42；而凈現(xiàn)值為負(fù)時主要集中在-50000之間，概率為0.08。由圖8-18還可以看出，該項目的凈現(xiàn)值大致符合正態(tài)分布。

48、8.4.2互斥項目的蒙特卡羅模擬在對互斥方案進(jìn)行比較時，可對每個方案進(jìn)行蒙特卡羅模擬，計算出其凈現(xiàn)值及其概率分布，并繪制概率分布圖，進(jìn)行綜合比較分析，選擇最優(yōu)方案?！纠?-12】以例8-7的有關(guān)資料為例，其模擬準(zhǔn)備、模擬計算過程、模擬計算結(jié)果如圖8-19所示。共進(jìn)行6000次模擬計算，即從第21行到6020行。表中僅列舉了三次模擬，即第21到23行，其中，隨機(jī)數(shù)計算公式為“=RANDBETWEEN(1,100)”，項目A第1、2、3、4年的凈現(xiàn)金流量計算公式分別為：“=VLOOKUP(A21:A6020,$E$4:$F$6,2)”、“=VLOOKUP(C21:C6020,$E$7:$F$9,2

49、)”、“=VLOOKUP(E21:E6020,$E$10:$F$12,2)”和“=VLOOKUP(G21:G6020,$E$13:$F$15,2)”；項目B第1、2、3、4年的凈現(xiàn)金流量計算公式分別為：“=VLOOKUP(J21:J6020,$I$4:$J$6,2)”、“=VLOOKUP(L21:L6020,$I$7:$J$9,2)”、“=VLOOKUP(N21:N6020,$I$10:$J$12,2)”和“=VLOOKUP(P21:P6020,$I$13:$J$15,2)”。圖8-19項目A、B的凈現(xiàn)值概率分布計算項目A、B的凈現(xiàn)值時，需先在單元格I21和R21中分別輸入項目A、B的凈現(xiàn)值計算公式：“=NPV($C$16,B21,D21,F21,H21)+$F$3”和“=NPV($C$16,K21,M21,O21,Q21)+$J$3”，然后再復(fù)制到其他各行。項目A、B的凈現(xiàn)值概率分布圖如圖8-20所示，其制作方法同上。圖8-20項目A、B的凈現(xiàn)值概率分布圖由模擬計算結(jié)果可知，項目A風(fēng)險較低但收益也低，而項目B風(fēng)險較高但收益也高。但兩個項目的變異系數(shù)相差并不是很大，且凈現(xiàn)值為負(fù)的概率也都較?。ǚ謩e為0.08