Excel在證券價值評估分析中的應用

襄樊職業技術學院 鐘愛軍

一、Excel在股票價值評估分析中的應用

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

其一，定期持有股票的估價。定期持有股票的情形是指投資者在一定時期內持有股票，然后將其賣掉，收回資金。定期持有股票的估價公式：

其二，固定增長股的估價。固定增長股是指未來股利以某一固定的比率穩定增長的股票。固定增長股的估價公式：

其三，多重增長股的估價。多重增長股是指在未來的不同時期有不同的股利增長率，一定時期后股利穩定增長的股票。對這類股票進行估價，可以根據各期的股利增長率計算出每期的股利及其現值，同時計算出固定增長股的現值，再進行加總求和。

其四，市盈率估價模型。市盈率被廣泛應用在股票發行價格的分析確定上。

股票價值=市盈率×預計每股收益

（二）利用Excel計算股票價值 根據以上方法，下面通過具體實例來說明如何利用Excel評估股票價值。

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

在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，"有"，"無"）。

關于凈現值函數NPV和邏輯函數IF的語法格式，請參見Excel的聯機幫助。

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

結果顯示，該股票的內在價值高于股票目前的市價，表明該股票具有投資價值。

［案例2］甲股票目前的市價為22.5元/股，預計在今后2年中每年末分配現金股利分別為2.3元/股、3元/股，2年后股利按3%的比率穩定增長，投資者期望的報酬率為16%。乙股票目前的市價為18.80元/股，預計在今后的3年內不分派現金股利，而在第4年末支付3.8元/股的股利，此后股利將按4%的比率穩定增長，投資者期望的報酬率為15%。試評估這兩種股票的投資價值。

在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 固定增長股投資價值分析模型

結果顯示，甲股票的內在價值低于目前的市價，表明該股票沒有投資價值；乙股票的內在價值高于目前的市價，表明該股票具有投資價值。

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

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

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

圖3 多重增長股現值的計算分析模型

分析結果顯示，B公司股票的現值為51.50元/股。

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

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

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

圖4 C公司股票投資價值分析模型

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

二、Excel在債券價值評估分析中的應用

債券是籌資者為籌集資金而發行的有價證券，籌資者在發行債券時需要確定債券的面值、票面利率、償還期限、還本付息方式等，并根據市場利率確定債券的發行價格；投資者投資債券前需要估計債券的價值，預計債券投資的風險和收益。債券價值是指債券未來各期現金流的現值之和，債券價值受面值、票面利率、期限、還本付息方式及市場利率等因素的影響。

（一）利用Excel的PV函數直接計算債券價值

［案例5］2010年3月10日發行的某債券，面值為100元，票面年利率為10%，期限為5年。若市場年利率為6%，該債券每年付息一次，估計該債券發行時的價值。

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

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

圖5 債券價值評估分析模型1

具體方法與步驟如下：

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

第二，定義微調按鈕與參數值之間數據傳遞關系的公式。B4=C4，B5=C5/10000，B6=C6，B7=C7/10000，B8=C8。同時，設置B5、B7單元格為百分比格式。

第三，利用現值函數PV，定義債券價值計算公式。B10=PV（B7/B8，B6*B8，-B4*B5/B8，-B4）

第四，利用調節按鈕調整出相應參數值，以實現各種不同參數值的組合，此時通過已經設計好的Excel公式即可自動實現債券價值的動態分析。

（三）深入應用

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

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

圖6 債券價值評估分析模型2

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

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

兩種方法，結果一致，該債券的市場價格低于其內在價值，在投資者要求年收益率為10%的情況下，具有投資價值。

［1］韓良智：《Excel在投資理財中的應用》，電子工業出版社2005年版。