股票内在价值的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--），女，汉族，山东青岛人，讲师，学士，主要从事经济类教育研究 。