股票內在價值的Excel模型設計

摘要：股票投資中經常用內在價值評估股票價值是否會被高估或者是低估，股票內在價值是由這種資產在未來產生的現金流量按照一定的貼現率貼現后的現值。計算股票內在價值的模型主要有股利固定模型、股利固定比例增長模型和非固定增長模型。筆者利用Excel的自動計算功能，設計出股票內在價值的計算模型，利用模型可以大大減輕計算量，可廣泛應用于財務管理和證券投資學等課程的教學當中。

關鍵詞：股票內在價值；現值；Excel模型

1.引言

股票內在價值就是股票在未來時期支付的股利按照一定的貼現率折算成的現值，利用股票的內在價值和當時的市場價格相比較就可以判斷股票價值是被高估還是被低估。股票內在價值的多少主要取決于貼現率和股利，對股利的估計方法最常用的是歷史資料的統計分析，而股票評價的基本模型要求無限期的預計歷年股利，所以應用的模型都是各種簡化方法，主要有：股利固定模型、股利固定增長模型、非固定增長股利模型。

2.股票內在價值模型及案例計算

2.1股票內在價值計算公式

2.1.1股利固定模型

股利固定模型是假設未來的股利不變，則股利的未來支付就是一個永續年金，股票內在價值的計算公式為：

V=D/k（V代表股票的內在價值，D代表每期相等的股利，k代表貼現率）

2.1.2股利固定增長模型

股利固定增長模型假設公司每年股利按照一定的比率固定增長，用g代表股利增長比率，k代表貼現率，如果k﹥g，則股票的內在價值公式可以簡化為：

V=D1/（k-g） （D1代表開始固定增長的第一期股利）

2.1.3非固定增長模型

非固定增長模型考慮到現實中企業的股利有可能出現在一段時間里告訴增長，在另外一段時間里低速增長或者是固定不變，就需要分段計算，再確定股票的內在價值。所以此模型下沒有固定的公式，要視具體情況而定。

2.2案例計算

例題：某投資者持有某上市公司股票，他的投資必要報酬率是10%，預計該公司未來4年股利將高速增長，增長率為20%，，此后進入正常增長階段，增長率為6%，公司最近支付的股利是1元，求股票的內在價值是多少？

本例題屬于股利非固定增長模型，計算過程如下：

首先，計算高速增長時期股利的現值：

最后，計算股票的內在價值：V=5.00+37.53=42.53（元）

通過例題的演示發現，非固定增長模型中的股票內在價值計算十分復雜，且計算工作量巨大。

3.股票內在價值計算模型的設計

3.1 控件按鈕設計

（1）單擊插入控件按鈕的選項按鈕（窗體控件），此時鼠標變成"+"號，在單元格的合適位置拖拽出三個選項按鈕，右擊選擇"編輯文字"選項，分別輸入"股利固定模型"、"股利固定比例增長模型"和"股利非固定增長模型"，最后右擊選擇"設置控件格式"，單元格鏈接選擇"$E$5"，并將"三維陰影"選項勾選。

（2）單擊插入控件按鈕的分組框（窗體控件），此時鼠標變成"+"號，在單元格的合適位置拖拽出兩個個分組框，修改標簽為"請輸入初始條件"和"股票內在價值"；然后右擊分組框單擊"設置控件格式"命令，勾選"三維陰影"選項。

3.2 基礎文字和數據的錄入

（1）為了使模型的輸入條件和輸出結果清晰明了，需要進行相關文字的輸入。在C9、C11和D16中分別輸入"最近一期的股利是"、"折現率是"、"股票內在價值="。

（2）其它文本需要利用"IF"語句，使三種不同模型下的文字顯示不同，在F9輸入函數命令：=IF（E5=1，" "，IF（E5=2，" "，"在"）），顯示結果是如果選項按鈕選擇的是"股利固定模型"則此單元格不顯示任何內容，如果選擇的是"股利固定比例增長模型"則單元格顯示"股利增長速度是"的文本內容，如果選項按鈕選擇的是"股利非固定增長模型"則此單元格顯示的是"在"的文本內容。利用同樣的原理設置其它文本單元格如下：

F11 =IF（E5=1，" "，IF（E5=2，" "，"從第"））

H9 =IF（E5=1，" "，IF（E5=2，"股利增長率是"，"年股利增長率是"））

H11 =IF（E5=1，" "，IF（E5=2，" "，"年開始增長率是"））

3.3計算輔助數據和函數的設計

（1）選中K列，在編輯欄中輸入公式"=ROW（）"，然后同時按下"CTRL"和"ENTER"鍵，此命令使K列從1開始依次編號。

（2）選中L列，在編輯欄輸入"=I9"，然后同時按下"CTRL" "SHIFT"和"ENTER"鍵；選中M列，在編輯欄輸入"=D11"，然后同時按下"CTRL" "SHIFT"和"ENTER"鍵；選中N列，在編輯欄輸入"=D9"，然后同時按下"CTRL" "SHIFT"和"ENTER"鍵。

（3）選中O列，在編輯欄輸入函數"=N1*POWER（1+L1，K1）/POWER（1+M1，K1）"， 然后同時按下"CTRL"和"ENTER"鍵。

（4）在D13中輸入函數"=SUMIF（K：K，"<"&G11，O：O），此函數運行結果是O列中符合條件的數值求和，主要應用于股利非固定增長模型第一階段凈現值計算中。

（5）在E13、F13、G13中根據三種模型股票內在價值的計算公式分別輸入計算函數如下：

"=D9/D11"；

"=D9*（1+I9）/（D11-I9）"；

"=D13+D9*POWER（1+I9，G9）*（1+I11）/（（D11-I11）*POWER（1+D11，G9））"。

（6）在F16中輸入條件語句"=IF（E5=1，E13，IF（E5=2，F13，G13））"

3.4模型后期設計

（1）選中單元格中合適區域，設計此區域顏色為黃色。

（2）隱藏輔助數據列K、L、M、N、O和輔助數據行第5和13行，模型設計完成。

4.模型檢驗

在模型中輸入本文例題中的初始數據，計算結果如圖4所示：

模型輸出結果與計算結果相同，反復輸入多組數值進行計算，模型輸出結果與計算結果相同，充分驗證了模型計算結果的可靠性。

參考文獻：

[1] 陳芳平，馬寧. 基于層次分析法的股票內在價值研究[J]. 淮海工學院學報（社會科學版）. 2011（03）

[2] 常冶衡，寧曉. Excel在送轉股模型中的分析與應用[J]. 中國管理信息化. 2010（23）.

[3] 歐陽紅東.數據處理中Excel的應用分析[J].電腦知識與技術，2014（12）.

[4] 許國，斯泉均. Excel在學校管理中的應用[J].辦公自動化，2012（18）.

作者簡介：

李雙（1982.9--），女，漢族，山東青島人，講師，學士，主要從事經濟類教育研究 。