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年版。