Excel会计与财务管理-理论、方案暨模型（第二版） 桂良军课件第7章 Excel在证券投资中应用

第7章Excel在证券投资分析中应用本章教学要求：掌握证券投资分析的相关函数学习如何建立债券估价、债券投资收益以及债券久期的模型并进行相关分析学习如何建立股票估价、股票投资收益和风险测度模型并进行相关分析主要内容：7.1证券投资分析概述7.2证券投资分析相关的函数7.3债券投资分析模型7.4股票投资分析模型第7章Excel在证券投资分析中应用7.1证券投资分析概述

证券投资是指投资者购买股票、债券、基金等有价证券以及这些有价证券的衍生品，以获取利息、红利及资本利得的一种投资行为。证券投资主要包括以下两类：

1.债券投资

债券是一种有价证券，是社会各类经济主体为筹措资金而向债券投资者出具的并承诺按一定利率定期支付利息、按约定条件偿还本金的债权债务凭证。

2.股票投资股票是股份公司发给股东作为入股凭证，股东借以取得股息收益的一种有价证券。

7.2证券投资分析相关的函数

本节分别从债券投资分析和股票投资分析的角度，列示各自经常用到的函数，使用这些函数，可以很方便地对证券的价值、收益、期限、风险等进行计算，便于进行证券投资决策。

1.债券投资分析的相关函数

2.股票投资分析的相关函数1．计算债券价值的有关函数（1）PRICE函数

功能：返回定期付息的面值100元的有价证券的价格。

公式：

=PRICE(settlement,maturity,rate,yld,redemption,frequency,basis)※

式中，settlement为有价证券的成交日，即在发行日之后，有价证券卖给购买者的日期；maturity为有价证券的到期日，到期日是有价证券有效期截止时的日期；rate为有价证券的年票面利率；yld为有价证券的年收益率；redemption为面值100元的有价证券的清偿价值；frequency为年付息次数，如果按年支付，frequency=1；按半年期支付，frequency=2；按季支付，frequency=4；basis为日计数基准类型，其取值情况如下表所示。7.2.1债券投资分析的相关函数basis参数类型Basis

日计数基准（年基准数）0或省略US(NASD)30/3601

实际天数/实际天数2

实际天数/3603

实际天数/3654

欧洲30/360

（2）PRICEDISC函数功能：返回折价发行的面值100元的有价证券的价格。公式：=PRICEDISC(settlement,maturity,discount,redemption,basis)※式中，discount为有价证券的贴现率；其他参数的含义可以参考PRICE函数。（3）PRICEMAT函数功能：返回到期付息的面值100元的有价证券的价格。公式：=PRICEMAT(settlement,maturity,issue,rate,yld,basis)※式中，issue为有价证券的发行日；rate为有价证券在发行日的利率；其他参数的含义可以参考PRICE函数。（4）TBILLPRICE函数功能：返回面值100元的国库券的价格。公式：=TBILLPRICE(settlement,maturity,discount)※式中，各参数如前所述。

注意：settlement或maturity必须是合法有效的日期，否则函数TBILLPRICE返回错误值#VALUE！；discount必须大于0，否则函数TBILLPRICE会返回错误值#NUM！；如果settlement＞maturity，或maturity比settlement晚一年以上，函数TBILLPRICE会返回错误值#NUM！。（5）ODDFPRICE函数功能：返回首期付息日不固定（长期或短期）的面值100元的有价证券的价格。公式：=ODDFPRICE(settlement,maturity,issue,firstcoupon,rate,yld,redemption,frequency,basis)※式中，first-coupon为有价证券的首期付息日；其他参数的含义可以参考PRICE函数和PRICEMAT函数。（6）ODDLPRICE函数功能：返回末期付息日不固定（长期或短期）的面值100元的有价证券的价格。公式：=ODDLPRICE(settlement,maturity,lastinterest,rate,yld,redemption,frequency,basis)※式中，last-interest为有价证券的末期付息日，其他参数的含义可以参考ODDFPRICE函数。

说明：PV函数也可用来计算债券的价值2．计算债券投资收益额的有关函数

（1）ACCRINT函数功能：返回定期付息有价证券的应计利息。公式：=ACCRINT(issue,firstinterest,settlement,rate,par,frequency,basis)※式中，issue为有价证券的发行日；first-interest为有价证券的起息日，即首次支付利息的日期；rate为有价证券的年票面利率；par为有价证券的票面价值，如果省略par，函数ACCRINT则视par为1000元，其他各参数如前所述。（2）ACCRINTM函数功能：返回到期一次性付息有价证券的应计利息。公式：=ACCRINTM(issue,maturity,rate,par,basis)※式中，各参数含义如前所述。（3）RECEIVED函数功能：返回一次性付息的有价证券到期收回的金额。公式：=RECEIVED(settlement,maturity,investment,discount,basis)※式中，investment为有价证券的投资额，其他参数的含义如前所述。3．计算债券投资收益率的有关函数（1）YIELD函数功能：返回定期付息有价证券的收益率。公式：=YIELD(settlement,maturity,rate,pr,redemption,frequency,basis)※式中，pr为面值100元的有价证券的价格，其他参数的含义如前所述。（2）YIELDDISC函数功能：返回折价发行的有价证券的年收益率。公式：=YIELDDISC(settlement,maturity,pr,redemption,basis)※式中，各参数含义如前所述。（3）YIELDMAT函数功能：返回到期付息的有价证券的年收益率。公式：=YIELDMAT(settlement,maturity,issue,rate,pr,basis)※式中，各参数含义如前所述。（4）TBILLYIELD函数功能：返回国库券的收益率。公式：=TBILLYIELD(settlement,maturity,pr)※式中，各参数含义如前所述。（5）TBILLEQ函数功能：返回国库券的等效收益率，即以discount贴现时相当于到期时的收益率。公式：=TBILLEQ(settlement,maturity,discount)※式中，各参数含义如前所述。（6）INTRATE函数功能：返回一次性付息证券的利率。公式：=INTRATE(settlement,maturity,investment,redemption,basis)※式中，各参数含义如前所述。（7）DISC函数功能：返回有价证券的贴现率。公式：=DISC(settlement,maturity,pr,redemption,basis)※式中，各参数含义如前所述。（8）ODDFYIELD函数功能：返回首期付息日不固定（长期或短期）的有价证券的收益率。公式：=ODDFYIELD(settlement,maturity,issue,firstcoupon,rate,pr,redemption,frequency,basis)※式中，pr为面值100元的有价证券的价格，其他参数的含义如前所述。（9）ODDLYIELD函数功能：返回末期付息日不固定（长期或短期）的有价证券的收益率。公式：=ODDLYIELD(settlement,maturity,lastinterest,rate,pr,redemption,frequency,basis)※式中，各参数含义如前所述。

4．计算债券久期的有关函数久期，全称麦考雷久期（MacaulayDuration），指的是全部收回债券利息和本金的加权平均时间。在债券投资分析中，投资者可以用它来衡量债券价格变动对利率变化的敏感度，并且经过一定的修正，还能精确地量化利率变动给债券价格造成的影响，因此，可以用来预测债券价格，也可以用来构造免疫的投资组合（利率变化对债券价格的影响可以互相抵消，投资组合在整体上对利率不具有敏感性）。债券久期的计算公式是：

式中，D为债券久期；r为债券的到期收益率；t为期数；CFt为债券投资后各期的现金流；P0为债券的现值。此外，为了更精确地描述债券价格对于利率变化的灵敏性，又引入了修正久期。修正久期的计算公式为：（1）DURATION函数功能：返回面值100元的定期付息有价证券的久期。公式：=DURATION(settlement,maturity,coupon,yld,frequency,basis)※式中，coupon为有价证券的年票面利率；yld为有价证券的年收益率，其他参数的含义如前所述。（2）MDURATION函数功能：返回面值100元的有价证券的修正久期。公式：=MDURATION(settlement,maturity,coupon,yld,frequency,basis)※式中，各参数含义如前所述。5．计算债券投资的其他有关函数（1）COUPNCD函数功能：返回成交日过后的下一个付息日的日期。公式：=COUPNCD(settlement,maturity,frequency,basis)※式中，各参数含义如前所述。（2）COUPNUM函数功能：返回成交日和到期日之间的付息次数。公式：=COUPNUM(settlement,maturity,frequency,basis)※式中，各参数含义如前所述。（3）COUPDAYSNC函数功能：返回从成交日到下一付息日之间的天数。公式：=COUPDAYSNC(settlement,maturity,frequency,basis)※式中，各参数含义如前所述。7.2.2股票投资分析相关的函数

投资者在进行股票投资分析的时候，也要对股票的内在价值、投资收益率、风险等进行计算和权衡。下面列示一些股票投资分析时常用到的函数。1．STDEV函数功能：估算样本的标准偏差。标准偏差反映相对于平均值的离散程度。公式：=STDEV(number1,number2,…,numberN)※式中，number1,number2,…,numberN为对应于总体样本的1到30个参数。也可以不使用这种用逗号分隔参数的形式，而用单一数组，即对数组单元格的引用。在计算中，逻辑值和文本将被忽略。※该函数假设其参数是总体中的样本，在根据历史数据计算股票标准偏差时可以使用本函数。2．STDEVP函数功能：返回以参数形式给出的整个样本总体的标准偏差。公式：=STDEVP(number1,number2,…,numberN)※式中，各参数含义同STDEV函数。※该函数假设其参数是整个样本总体，在根据历史数据计算股票标准偏差时可以使用本函数。3．LN函数功能：返回一个数的自然对数。公式：=LN(number)※式中，number是用于计算其自然对数的正实数。※根据历史数据计算的股票投资收益率可分为离散收益率和连续复利收益率。假设购买股票后可以连续不断地收回投资并反复进行再投资，此时按照连续复利的方式所计算的投资收益率就是连续复利收益率。根据公式，计算连续复利收益率可以使用本函数。4．SLOPE函数功能：返回根据known_y’s和known_x’s中的数据点拟合的线性回归直线的斜率。公式：=SLOPE(known_y’s,known_x’s)※式中，known_y’s为数字型因变量数组或单元格区域；known_x’s为自变量数据点集合。参数可以是数字，或者是涉及数字的名称、数组或引用。如果数组或引用参数里包含文本、逻辑值或空白单元格，这些值将被忽略，但包含零值的单元格将计算在内。※对股票投资分析而言，SLOPE函数可以用来计算一只股票的β系数。5．INTERCEPT函数功能：利用现有的x值与y值计算直线与y轴的截距。公式：=INTERCEPT(known_y’s,known_x’s)※式中，参数含义可以参考SLOPE函数。※该函数通过计算出截距，可以用来绘制一只股票的特征线。6．COVAR函数功能：返回协方差，即每对数据点的偏差乘积的平均数，利用协方差可以决定两个数据集之间的关系。公式：=COVAR(array1,array2)※式中，array1为第一个单元格区域；array2为第二个单元格区域。参数必须是数字，或者是包含数字的名称、数组或引用。如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略，但包含零值的单元格将计算在内。如果array1和array2所含数据点的个数不等，则函数COVAR返回错误值#N/A；如果array1和array2当中有一个为空，则函数COVAR返回错误值#DIV/0!。※要计算一只股票的β系数，根据公式，则首先要计算出该股票的收益率和市场投资组合收益率之间的协方差，本函数可以实现这一功能。7．CORREL函数功能：返回单元格区域array1和array2之间的相关系数。公式：=CORREL(array1,array2)※式中，参数含义可以参考COVAR函数。※本函数可以用来计算一只股票与市场投资组合之间的相关系数。7.3债券投资分析模型7.3.1债券估价模型1．债券估价的基本原理债券作为一种投资，现金流出是其购买价格，现金流入是利息和归还的本金，或者是出售时得到的现金。债券的价值是指债券未来现金流入量的现值，即债券各期利息收入的现值加上债券到期偿还本金的现值之和。只有债券的内在价值大于购买价格时，才值得购买。对不同类型的债券来说，内在价值的计算公式也不同。下面列示几个不同类型债券的估价公式。（1）定期付息债券。定期付息的债券是指每年一次或数次向投资者支付固定的利息，到期按面值偿还本金的债券。估价公式为：式中，V为债券的价值；F为债券的面值；i为债券的年票面利率；k为投资者要求的最低年投资收益率；n为债券的期限；m为每年付息的次数。（2）到期还本付息债券。到期还本付息债券是指债券到期后一次性地向投资者支付全部利息和按面值偿还本金的债券。估价公式为：式中各符号含义如前所述。（3）零息债券。零息债券以折价（发行价格低于面值）方式发行，没有票面利率，到期按面值偿还。因此，投资者购买这种债券只能取得购买价格和面值之间的差价收入。估价公式为：式中各符号含义如前所述。（4）永久债券。永久债券是没有到期日，不返还本金，但可以无限期地按期取得固定利息的债券。估价公式为：（5）流通债券。流通债券是已经发行并在二级市场上流通的债券。流通债券具有如下特点：债券到期的时间小于发行在外的时间；估价的时点不在发行日，可以是任何时点，会产生“非整数计息期”问题。对流通债券进行估价，可以考虑以下两种办法：一是以估价日为折算时间点，未来各期的现金流量按非整数计息期折现；二是以估价日之后的下一次付息日为折算时间点，先将该付息日之后的未来各期现金流量折现，然后再连同付息日的利息一起加总，折算到估价日。2．债券估价的基本模型【例7-1】某公司2010年1月1日发行A、B两种债券，其详细资料如表7-2所示。要求建立一个模型，能够分别利用PRICE函数和PV函数计算两种债券的价值。所建立的模型的结构如图7-1所示：3．债券价值与到期期限之间的关系分析模型【例7-2】现有A、B、C三种债券，已知条件如表7-3所示。要求建立一个模型，能够对三种债券的价值与距离到期期限（10年）之间的关系进行分析。图7-1债券估价的基本模型所建立的模型的结构如图7-2所示：图7-2债券价值与到期期限的关系分析模型4．债券价值与市场利率之间的关系分析模型【例7-3】现有A、B、C三种债券，已知条件如表7-4所示。要求建立一个模型，能够对三种债券的价值与市场利率之间的关系进行分析。所建立的模型的结构如图7-3所示：5．可调整参数大小的债券价值计算模型【例7-4】某债券的资料如表7-5所示。要求建立一个可以调整各参数大小的债券价值计算模型，以便能够随时得到各参数发生变化后债券价值的变化结果，并绘制债券价值与票面年利率之间的关系图。图7-3债券价值与市场利率的关系分析模型所建立的模型的结构如图7-4所示：图7-4可调参数的债券价值计算模型7.3.2债券投资收益计算模型1．债券投资收益额的计算模型【例7-5】现有A、B、C三种债券，已知条件如表7-6所示。要求建立一个模型，能够分别计算A、B债券的应计利息和C债券到期可收回的金额。所建立的模型的结构如图7-5所示：2．债券投资收益率的计算模型【例7-6】现有A、B、C、D四种债券，已知条件如表7-7所示。要求建立一个模型，利用有关函数分别计算这四种债券的年投资收益率。图7-5债券投资收益额的计算模型所建立的模型的结构如图7-6所示：图7-6利用函数计算债券投资收益率模型【例7-7】现有甲、乙、丙三种债券，其中甲为国库券，基本资料如表7-8所示。要求建立一个模型，利用有关函数分别计算甲国库券的等效收益率、乙债券的年利率和丙债券的贴现率。所建立的模型的结构如图7-7所示：

图7-7计算债券的等效收益率、年利率和贴现率的模型1．债券久期的计算模型【例7-8】某债券的资料如表7-9所示。要求建立一个模型，能够分别利用公式和函数计算该债券的债券久期和修正久期。所建立的模型的结构如图7-8所示：7.3.3债券久期的有关模型2．债券投资组合的久期免疫策略模型久期免疫策略是通过价格风险和再投资风险相互抵消作用的原理从投资期限的角度考虑利率风险免疫的。债券投资组合的久期等于投资组合内各种债券久期的加权平均数，计算公式为：图7-8债券久期的计算模型※式中，Dp为债券投资组合的麦考雷久期；wi为债券i在债券投资组合中的比例；Di为债券i的麦考雷久期；n为债券组合中债券的种类数。※

在构造投资组合的时候，应该合理安排组合内各种债券的比例，使得投资组合的久期等于投资者负债的久期，以免除所负债务的利率风险。

【例7-9】某投资者甲向银行长期借款500000元，期限10年，年利率为15%。每年年末，甲需向银行偿还利息75000元，并在到期时归还本金。为了满足负债现金流的需求且能规避所负债务的利率风险，甲拟构造一个由A、B、C三种债券所组成的投资组合。其他已知条件如表7-10所示。要求建立一个模型，能够确定A、B、C三种债券的投资比例。所建立的模型的结构如图7-9所示：图7-9债券投资组合的久期免疫策略模型7.4股票投资分析模型7.4.1股票估价模型1．股票估价的基本原理股票的价值就是股票带给持有者的未来现金流入的现值，即由一系列的未来股利的现值和将来出售股票时售价的现值之和构成。只有股票的价值大于购买价格时，才值得购买。下面列举几个不同类型股票的估价公式。（1）短期持有的股票。短期持有的股票的未来现金的流入量包括预期获得的股利和转让价款收入。估价公式为：※式中，V为股票的价值；Dt为第t期的每股股利；k为投资者要求的最低投资收益率；n为股票的持有期限；Vn为股票的出售价格。（2）长期持有、股利固定不变的股票。长期持有、股利固定不变的股票的未来现金的流入量包括未来各期获得的股利。估价公式为：※式中，V为股票的价值；D为每期的固定股利；k为投资者要求的最低投资收益率。（3）长期持有、股利按固定比率增长的股票。这种股票的估价公式为：※式中，V为股票的价值；D0为当期支付的股利；D1为第1期期末支付的股利；k为投资者要求的最低投资收益率；g为股利增长率。（4）长期持有、股利非固定增长的股票。指在未来不同的时间段内，股利会出现超常高速增长和稳定增长阶段的情况。这样，可以分段计算高速增长阶段和稳定增长阶段未来收益的现值，两阶段现值之和就是这种股票的股票价值。假设某股票的股利在前n期高速增长，然后又按照固定比率增长，则估价公式为：※式中：V为股票的价值；D0为当期支付的股利；Dn为第n期期末支付的股利；k为投资者要求的最低投资收益率；g1为前n期的股利增长率；g2为股利固定增长时的增长率。2．股票估价的基本模型【例7-10】现有A、B两只股票，已知条件如表7-11所示，要求建立一个模型，能够计算A、B两种股票的股票价值并判断是否值得投资。所建立的模型的结构如图7-10所示：【例7-11】甲公司为一家在上海证券交易所上市的公司。投资者对该公司发展情形预计如下：在未来的前4年里，股利将按照20%的增长率高速增长，此后，将以6%的增长率稳定增长。甲公司股票的其他已知条件如表7-12所示。要求建立一个模型，能够计算该公司股票的股票价值并判断是否值得投资。图7-10股票估价与投资价值判断模型所建立的模型的结构如图7-11所示：图7-11股利非固定增长股票的估价与投资价值判断模型7.4.2股票投资收益与风险测度模型1．股票投资收益与风险测度的公式

※投资者在从事股票投资的时候，需要对股票的收益和风险进行度量，以便合理安排资金，尽可能选择那些低风险、高收益的股票。

※股票投资的收益一般用收益率表示，在分析一只股票的历史交易情况时，收益率可分为离散收益率和连续复利收益率。如果是对股票的未来走势做出预期，此时的收益率称为期望收益率。

※无论是对历史交易情况进行分析还是对未来走势做出预期，股票投资的风险都可以用标准差或变化系数来度量。股票投资收益与风险测度的有关公式如下：（1）离散收益率。计算公式为：※式中，Rt为第t期的离散收益率；Dt为第t期的股利；