Excel在證券價(jià)值評(píng)估分析中的應(yīng)用

襄樊職業(yè)技術(shù)學(xué)院 鐘愛軍

一、Excel在股票價(jià)值評(píng)估分析中的應(yīng)用

（一）股票價(jià)值評(píng)估方法 股票價(jià)值評(píng)估是指對(duì)股票的內(nèi)在價(jià)值做出估計(jì)，股票的內(nèi)在價(jià)值是指股票投資所產(chǎn)生的未來現(xiàn)金流按照投資者期望的最低報(bào)酬率作為折現(xiàn)率所計(jì)算的現(xiàn)值。如果某股票的市場(chǎng)價(jià)格高于其內(nèi)在價(jià)值，說明該股票的價(jià)格被高估，其價(jià)格有下跌可能，投資者應(yīng)考慮賣出所持有的這種股票。如果某股票的市場(chǎng)價(jià)格低于其內(nèi)在價(jià)值，說明該股票的價(jià)格被低估，其價(jià)格有上漲的潛力，投資者可以考慮買進(jìn)該股票。股票價(jià)值評(píng)估方法主要有定期持有股票的估價(jià)、固定增長股的估價(jià)、多重增長股的估價(jià)、市盈率估價(jià)模型等，具體如下。

其一，定期持有股票的估價(jià)。定期持有股票的情形是指投資者在一定時(shí)期內(nèi)持有股票，然后將其賣掉，收回資金。定期持有股票的估價(jià)公式：

其二，固定增長股的估價(jià)。固定增長股是指未來股利以某一固定的比率穩(wěn)定增長的股票。固定增長股的估價(jià)公式：

其三，多重增長股的估價(jià)。多重增長股是指在未來的不同時(shí)期有不同的股利增長率，一定時(shí)期后股利穩(wěn)定增長的股票。對(duì)這類股票進(jìn)行估價(jià)，可以根據(jù)各期的股利增長率計(jì)算出每期的股利及其現(xiàn)值，同時(shí)計(jì)算出固定增長股的現(xiàn)值，再進(jìn)行加總求和。

其四，市盈率估價(jià)模型。市盈率被廣泛應(yīng)用在股票發(fā)行價(jià)格的分析確定上。

股票價(jià)值=市盈率×預(yù)計(jì)每股收益

（二）利用Excel計(jì)算股票價(jià)值 根據(jù)以上方法，下面通過具體實(shí)例來說明如何利用Excel評(píng)估股票價(jià)值。

［案例1］A公司于本日為其股票分配了2.60元/股的現(xiàn)金股利，股票目前市價(jià)為12元/股，預(yù)計(jì)4年內(nèi)每年分派現(xiàn)金股利逐年增加0.6元/股，如果投資者期望的最低報(bào)酬率為16%，并且預(yù)計(jì)三年后投資者可以按18.50元/股的價(jià)格出售該股票，那么該股票是否有投資價(jià)值？

在Excel工作表中建立如圖1所示的分析模型，單元格公式定義如下：B10=D2+D3，C10=B10+D3，D10=C10+D3，E10=D10+D3+D 6，B11=NPV（D5，B10:E10），B12=IF（B11>D7，"有"，"無"）。

關(guān)于凈現(xiàn)值函數(shù)NPV和邏輯函數(shù)IF的語法格式，請(qǐng)參見Excel的聯(lián)機(jī)幫助。

圖1 定期持有股票投資價(jià)值分析模型

結(jié)果顯示，該股票的內(nèi)在價(jià)值高于股票目前的市價(jià)，表明該股票具有投資價(jià)值。

［案例2］甲股票目前的市價(jià)為22.5元/股，預(yù)計(jì)在今后2年中每年末分配現(xiàn)金股利分別為2.3元/股、3元/股，2年后股利按3%的比率穩(wěn)定增長，投資者期望的報(bào)酬率為16%。乙股票目前的市價(jià)為18.80元/股，預(yù)計(jì)在今后的3年內(nèi)不分派現(xiàn)金股利，而在第4年末支付3.8元/股的股利，此后股利將按4%的比率穩(wěn)定增長，投資者期望的報(bào)酬率為15%。試評(píng)估這兩種股票的投資價(jià)值。

在Excel工作表中建立如表2所示的分析模型，單元格公式定義如下：B9=NPV（B6，B3:B4）+B4*（1+B5）/（B6-B5）/（1+B6）^2，B10=D4/（D6-D5）/（1+D6）^（D3-1），D9=IF（B9>B7，"有 "，"無 "），D10=IF（B10>D7，"有"，"無"）。

圖2 固定增長股投資價(jià)值分析模型

結(jié)果顯示，甲股票的內(nèi)在價(jià)值低于目前的市價(jià)，表明該股票沒有投資價(jià)值；乙股票的內(nèi)在價(jià)值高于目前的市價(jià)，表明該股票具有投資價(jià)值。

［案例3］B公司股票目前的股利為1.6元/股，預(yù)計(jì)未來6年的股利增長率為18%，在第6年末股利增長率恢復(fù)到正常增長率10%的水平。若投資者的期望報(bào)酬率為15%，那么股票的當(dāng)前價(jià)值是多少？

在Excel工作表中建立如圖3所示的分析模型，單元格公式定義如下：

B9=$E$2*（1+$E$4）^B8，（用鼠標(biāo)拖動(dòng)B9單元格右下角的填充柄到G9單元格，通過復(fù)制方法完成C9、D9、E9、F9、G9單元格公式定義），B10=G9*（1+E5）/（E6-E5），B11=NPV（E6，B9:G9）+B10/（1+E6）^E3

圖3 多重增長股現(xiàn)值的計(jì)算分析模型

分析結(jié)果顯示，B公司股票的現(xiàn)值為51.50元/股。

［案例4］C公司股票目前的市價(jià)是28.6元/股，本期每股收益為5.56元/股，每股股利為1.2元，預(yù)計(jì)今后的收益和股利的長期增長率均為13%，若投資者期望的報(bào)酬率為15%，那么該公司現(xiàn)在的市盈率為多少？評(píng)估該公司股票是否具有投資價(jià)值。

在Excel工作表中建立如圖4所示的分析模型，其中的單元格公式如下：

B8=B3/B2，B9=B8*（1+B4）/（B5-B4），B10=B9*B2，B11=IF（B10>B6，"有"，"無"）

圖4 C公司股票投資價(jià)值分析模型

分析結(jié)果表明，C公司股票現(xiàn)在的市盈率為12.19，股票的內(nèi)在價(jià)值是67.80元/股，大大超過目前的市價(jià)，具有投資價(jià)值。

二、Excel在債券價(jià)值評(píng)估分析中的應(yīng)用

債券是籌資者為籌集資金而發(fā)行的有價(jià)證券，籌資者在發(fā)行債券時(shí)需要確定債券的面值、票面利率、償還期限、還本付息方式等，并根據(jù)市場(chǎng)利率確定債券的發(fā)行價(jià)格；投資者投資債券前需要估計(jì)債券的價(jià)值，預(yù)計(jì)債券投資的風(fēng)險(xiǎn)和收益。債券價(jià)值是指債券未來各期現(xiàn)金流的現(xiàn)值之和，債券價(jià)值受面值、票面利率、期限、還本付息方式及市場(chǎng)利率等因素的影響。

（一）利用Excel的PV函數(shù)直接計(jì)算債券價(jià)值

［案例5］2010年3月10日發(fā)行的某債券，面值為100元，票面年利率為10%，期限為5年。若市場(chǎng)年利率為6%，該債券每年付息一次，估計(jì)該債券發(fā)行時(shí)的價(jià)值。

確定該債券發(fā)行時(shí)的價(jià)值，其實(shí)就是確定該債券還本付息資金流的現(xiàn)值，應(yīng)用Excel的現(xiàn)值函數(shù)PV即可輕松解決問題。在Excel單元格中錄入：=PV（6%，5，100*10%，100），結(jié)果顯示為-116.85，說明該債券發(fā)行時(shí)的價(jià)值為116.85元。該債券發(fā)行時(shí)的價(jià)值包含兩部分：一是到期后債券面值（本錢）的現(xiàn)值，二是債券每年利息的總現(xiàn)值。即：PV（6%，5，100）+PV（6%，5，100*10%）=-74.73-42.12=-116.85?，F(xiàn)值計(jì)算函數(shù)PV的語法說明：PV（利率，投資期，每期收付金額，未來值，0或1），參數(shù)0可以缺省，意為期末，參數(shù)1則意為期初。

（二）利用動(dòng)態(tài)分析模型計(jì)算債券價(jià)值 為了方便地計(jì)算和分析市場(chǎng)利率、償還期限、還本付息方式等各因素變動(dòng)對(duì)債券價(jià)值的影響，我們可以在Excel工作表中建立債券價(jià)值評(píng)估的動(dòng)態(tài)分析模型，如圖5所示。

圖5 債券價(jià)值評(píng)估分析模型1

具體方法與步驟如下：

第一，在Excel工作表中設(shè)計(jì)如表5所示的表格，執(zhí)行“視圖”菜單下的“工具欄”中的“窗體”命令，單擊窗體工具按鈕中的“微調(diào)項(xiàng)”按鈕，移動(dòng)鼠標(biāo)到C4單元格，此時(shí)出現(xiàn)一個(gè)十字標(biāo)記，即可在C4單元格中畫一個(gè)大小合適的微調(diào)按鈕，然后再右擊該微調(diào)按鈕圖標(biāo)，選中“設(shè)置控件格式”命令，進(jìn)入“設(shè)置控件格式”對(duì)話框，在“控制”選項(xiàng)卡中，單元格鏈接設(shè)置為C4單元格，其余參數(shù)可按默認(rèn)，也可以根據(jù)實(shí)際限制要求進(jìn)行相應(yīng)設(shè)置。用復(fù)制的方法，設(shè)置C5、C6、C7、C8單元格的微調(diào)按鈕，單元格鏈接為各微調(diào)按鈕所在的單元格。

第二，定義微調(diào)按鈕與參數(shù)值之間數(shù)據(jù)傳遞關(guān)系的公式。B4=C4，B5=C5/10000，B6=C6，B7=C7/10000，B8=C8。同時(shí)，設(shè)置B5、B7單元格為百分比格式。

第三，利用現(xiàn)值函數(shù)PV，定義債券價(jià)值計(jì)算公式。B10=PV（B7/B8，B6*B8，-B4*B5/B8，-B4）

第四，利用調(diào)節(jié)按鈕調(diào)整出相應(yīng)參數(shù)值，以實(shí)現(xiàn)各種不同參數(shù)值的組合，此時(shí)通過已經(jīng)設(shè)計(jì)好的Excel公式即可自動(dòng)實(shí)現(xiàn)債券價(jià)值的動(dòng)態(tài)分析。

（三）深入應(yīng)用

［案例6］2010年6月1日，某債券的市場(chǎng)價(jià)格為90元，已知該債券的面值為100元，票面利率為8%，2012年9月30日到期，每季度付息一次，若投資要求的年收益率為10%，日計(jì)數(shù)基準(zhǔn)為“實(shí)際天數(shù)/365”，那么該債券是否具有投資價(jià)值？

方法一：利用前面設(shè)計(jì)的模型進(jìn)行計(jì)算。利用表5所建立的動(dòng)態(tài)分析模型，在B5單元，將票面利率調(diào)整為8%；在B6單元格，直接錄入期限數(shù)（年）為2.334；在B7單元，將市場(chǎng)年利率調(diào)整為10%；在B8單元格，將年付息次數(shù)調(diào)整為4。2.334年是從2010年6月1日到2012年9月30日計(jì)算出來的，其中0.334是122天除以365天的結(jié)果。也可以利用函數(shù)YEARFRAC實(shí)現(xiàn)2010年6月1日至2012年9月30之間的年數(shù)計(jì)算，在B6單元格里錄入“=YEARFRAC（"2010-6-1"，"2012-9-30"，3）”。B10單元格自動(dòng)顯示出該債券的評(píng)估價(jià)值為95.88元，如圖6所示。

圖6 債券價(jià)值評(píng)估分析模型2

方法二：利用Excel的PRICE函數(shù)進(jìn)行計(jì)算。在Excel單元格中錄入：=PRICE（"2010-6-1"，"2012-9-30"，8%，10%，100，4，3），結(jié)果同樣是95.88元。PRICE函數(shù)的功能是返回定期付息、面值為100的有價(jià)證券的價(jià)格，其語法格式是：PRICE（settlement，maturity，rate，yld，redemption，frequency，basis）

參數(shù)說明：settlement為證券的結(jié)算日，結(jié)算日是在發(fā)行日之后，證券賣給購買者的日期；maturity為有價(jià)證券的到期日，到期日是有價(jià)證券有效期截止時(shí)的日期；rate為有價(jià)證券的年息票利率；yld為有價(jià)證券的年收益率；redemption為面值100的有價(jià)證券的清償價(jià)值；frequency為年付息次數(shù)，如果按年支付，frequency=1；按半年期支付，frequency=2；按季支付，frequency=4；Basis為日計(jì)數(shù)基準(zhǔn)類型。參數(shù)0（可以省略）代表的意義：US30/360，參數(shù)1代表的意義：實(shí)際天數(shù)/實(shí)際天數(shù)，參數(shù)2代表的意義：實(shí)際天數(shù)/360，參數(shù)3代表的意義：實(shí)際天數(shù)/365，參數(shù)4代表的意義：歐洲30/360，本例日期基準(zhǔn)類型參數(shù)是3。

兩種方法，結(jié)果一致，該債券的市場(chǎng)價(jià)格低于其內(nèi)在價(jià)值，在投資者要求年收益率為10%的情況下，具有投資價(jià)值。

［1］韓良智：《Excel在投資理財(cái)中的應(yīng)用》，電子工業(yè)出版社2005年版。