Excel會計與財務管理-理論、方案暨模型（第二版） 桂良軍課件第7章 Excel在證券投資中應用

第7章Excel在證券投資分析中應用本章教學要求：掌握證券投資分析的相關函數(shù)學習如何建立債券估價、債券投資收益以及債券久期的模型并進行相關分析學習如何建立股票估價、股票投資收益和風險測度模型并進行相關分析主要內(nèi)容：7.1證券投資分析概述7.2證券投資分析相關的函數(shù)7.3債券投資分析模型7.4股票投資分析模型第7章Excel在證券投資分析中應用7.1證券投資分析概述

證券投資是指投資者購買股票、債券、基金等有價證券以及這些有價證券的衍生品，以獲取利息、紅利及資本利得的一種投資行為。證券投資主要包括以下兩類：

1.債券投資

債券是一種有價證券，是社會各類經(jīng)濟主體為籌措資金而向債券投資者出具的并承諾按一定利率定期支付利息、按約定條件償還本金的債權債務憑證。

2.股票投資股票是股份公司發(fā)給股東作為入股憑證，股東借以取得股息收益的一種有價證券。

7.2證券投資分析相關的函數(shù)

本節(jié)分別從債券投資分析和股票投資分析的角度，列示各自經(jīng)常用到的函數(shù)，使用這些函數(shù)，可以很方便地對證券的價值、收益、期限、風險等進行計算，便于進行證券投資決策。

1.債券投資分析的相關函數(shù)

2.股票投資分析的相關函數(shù)1．計算債券價值的有關函數(shù)（1）PRICE函數(shù)

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

公式：

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

式中，settlement為有價證券的成交日，即在發(fā)行日之后，有價證券賣給購買者的日期；maturity為有價證券的到期日，到期日是有價證券有效期截止時的日期；rate為有價證券的年票面利率；yld為有價證券的年收益率；redemption為面值100元的有價證券的清償價值；frequency為年付息次數(shù)，如果按年支付，frequency=1；按半年期支付，frequency=2；按季支付，frequency=4；basis為日計數(shù)基準類型，其取值情況如下表所示。7.2.1債券投資分析的相關函數(shù)basis參數(shù)類型Basis

日計數(shù)基準（年基準數(shù)）0或省略US(NASD)30/3601

實際天數(shù)/實際天數(shù)2

實際天數(shù)/3603

實際天數(shù)/3654

歐洲30/360

（2）PRICEDISC函數(shù)功能：返回折價發(fā)行的面值100元的有價證券的價格。公式：=PRICEDISC(settlement,maturity,discount,redemption,basis)※式中，discount為有價證券的貼現(xiàn)率；其他參數(shù)的含義可以參考PRICE函數(shù)。（3）PRICEMAT函數(shù)功能：返回到期付息的面值100元的有價證券的價格。公式：=PRICEMAT(settlement,maturity,issue,rate,yld,basis)※式中，issue為有價證券的發(fā)行日；rate為有價證券在發(fā)行日的利率；其他參數(shù)的含義可以參考PRICE函數(shù)。（4）TBILLPRICE函數(shù)功能：返回面值100元的國庫券的價格。公式：=TBILLPRICE(settlement,maturity,discount)※式中，各參數(shù)如前所述。

注意：settlement或maturity必須是合法有效的日期，否則函數(shù)TBILLPRICE返回錯誤值#VALUE！；discount必須大于0，否則函數(shù)TBILLPRICE會返回錯誤值#NUM??；如果settlement＞maturity，或maturity比settlement晚一年以上，函數(shù)TBILLPRICE會返回錯誤值#NUM！。（5）ODDFPRICE函數(shù)功能：返回首期付息日不固定（長期或短期）的面值100元的有價證券的價格。公式：=ODDFPRICE(settlement,maturity,issue,firstcoupon,rate,yld,redemption,frequency,basis)※式中，first-coupon為有價證券的首期付息日；其他參數(shù)的含義可以參考PRICE函數(shù)和PRICEMAT函數(shù)。（6）ODDLPRICE函數(shù)功能：返回末期付息日不固定（長期或短期）的面值100元的有價證券的價格。公式：=ODDLPRICE(settlement,maturity,lastinterest,rate,yld,redemption,frequency,basis)※式中，last-interest為有價證券的末期付息日，其他參數(shù)的含義可以參考ODDFPRICE函數(shù)。

說明：PV函數(shù)也可用來計算債券的價值2．計算債券投資收益額的有關函數(shù)

（1）ACCRINT函數(shù)功能：返回定期付息有價證券的應計利息。公式：=ACCRINT(issue,firstinterest,settlement,rate,par,frequency,basis)※式中，issue為有價證券的發(fā)行日；first-interest為有價證券的起息日，即首次支付利息的日期；rate為有價證券的年票面利率；par為有價證券的票面價值，如果省略par，函數(shù)ACCRINT則視par為1000元，其他各參數(shù)如前所述。（2）ACCRINTM函數(shù)功能：返回到期一次性付息有價證券的應計利息。公式：=ACCRINTM(issue,maturity,rate,par,basis)※式中，各參數(shù)含義如前所述。（3）RECEIVED函數(shù)功能：返回一次性付息的有價證券到期收回的金額。公式：=RECEIVED(settlement,maturity,investment,discount,basis)※式中，investment為有價證券的投資額，其他參數(shù)的含義如前所述。3．計算債券投資收益率的有關函數(shù)（1）YIELD函數(shù)功能：返回定期付息有價證券的收益率。公式：=YIELD(settlement,maturity,rate,pr,redemption,frequency,basis)※式中，pr為面值100元的有價證券的價格，其他參數(shù)的含義如前所述。（2）YIELDDISC函數(shù)功能：返回折價發(fā)行的有價證券的年收益率。公式：=YIELDDISC(settlement,maturity,pr,redemption,basis)※式中，各參數(shù)含義如前所述。（3）YIELDMAT函數(shù)功能：返回到期付息的有價證券的年收益率。公式：=YIELDMAT(settlement,maturity,issue,rate,pr,basis)※式中，各參數(shù)含義如前所述。（4）TBILLYIELD函數(shù)功能：返回國庫券的收益率。公式：=TBILLYIELD(settlement,maturity,pr)※式中，各參數(shù)含義如前所述。（5）TBILLEQ函數(shù)功能：返回國庫券的等效收益率，即以discount貼現(xiàn)時相當于到期時的收益率。公式：=TBILLEQ(settlement,maturity,discount)※式中，各參數(shù)含義如前所述。（6）INTRATE函數(shù)功能：返回一次性付息證券的利率。公式：=INTRATE(settlement,maturity,investment,redemption,basis)※式中，各參數(shù)含義如前所述。（7）DISC函數(shù)功能：返回有價證券的貼現(xiàn)率。公式：=DISC(settlement,maturity,pr,redemption,basis)※式中，各參數(shù)含義如前所述。（8）ODDFYIELD函數(shù)功能：返回首期付息日不固定（長期或短期）的有價證券的收益率。公式：=ODDFYIELD(settlement,maturity,issue,firstcoupon,rate,pr,redemption,frequency,basis)※式中，pr為面值100元的有價證券的價格，其他參數(shù)的含義如前所述。（9）ODDLYIELD函數(shù)功能：返回末期付息日不固定（長期或短期）的有價證券的收益率。公式：=ODDLYIELD(settlement,maturity,lastinterest,rate,pr,redemption,frequency,basis)※式中，各參數(shù)含義如前所述。

4．計算債券久期的有關函數(shù)久期，全稱麥考雷久期（MacaulayDuration），指的是全部收回債券利息和本金的加權平均時間。在債券投資分析中，投資者可以用它來衡量債券價格變動對利率變化的敏感度，并且經(jīng)過一定的修正，還能精確地量化利率變動給債券價格造成的影響，因此，可以用來預測債券價格，也可以用來構造免疫的投資組合（利率變化對債券價格的影響可以互相抵消，投資組合在整體上對利率不具有敏感性）。債券久期的計算公式是：

式中，D為債券久期；r為債券的到期收益率；t為期數(shù)；CFt為債券投資后各期的現(xiàn)金流；P0為債券的現(xiàn)值。此外，為了更精確地描述債券價格對于利率變化的靈敏性，又引入了修正久期。修正久期的計算公式為：（1）DURATION函數(shù)功能：返回面值100元的定期付息有價證券的久期。公式：=DURATION(settlement,maturity,coupon,yld,frequency,basis)※式中，coupon為有價證券的年票面利率；yld為有價證券的年收益率，其他參數(shù)的含義如前所述。（2）MDURATION函數(shù)功能：返回面值100元的有價證券的修正久期。公式：=MDURATION(settlement,maturity,coupon,yld,frequency,basis)※式中，各參數(shù)含義如前所述。5．計算債券投資的其他有關函數(shù)（1）COUPNCD函數(shù)功能：返回成交日過后的下一個付息日的日期。公式：=COUPNCD(settlement,maturity,frequency,basis)※式中，各參數(shù)含義如前所述。（2）COUPNUM函數(shù)功能：返回成交日和到期日之間的付息次數(shù)。公式：=COUPNUM(settlement,maturity,frequency,basis)※式中，各參數(shù)含義如前所述。（3）COUPDAYSNC函數(shù)功能：返回從成交日到下一付息日之間的天數(shù)。公式：=COUPDAYSNC(settlement,maturity,frequency,basis)※式中，各參數(shù)含義如前所述。7.2.2股票投資分析相關的函數(shù)

投資者在進行股票投資分析的時候，也要對股票的內(nèi)在價值、投資收益率、風險等進行計算和權衡。下面列示一些股票投資分析時常用到的函數(shù)。1．STDEV函數(shù)功能：估算樣本的標準偏差。標準偏差反映相對于平均值的離散程度。公式：=STDEV(number1,number2,…,numberN)※式中，number1,number2,…,numberN為對應于總體樣本的1到30個參數(shù)。也可以不使用這種用逗號分隔參數(shù)的形式，而用單一數(shù)組，即對數(shù)組單元格的引用。在計算中，邏輯值和文本將被忽略。※該函數(shù)假設其參數(shù)是總體中的樣本，在根據(jù)歷史數(shù)據(jù)計算股票標準偏差時可以使用本函數(shù)。2．STDEVP函數(shù)功能：返回以參數(shù)形式給出的整個樣本總體的標準偏差。公式：=STDEVP(number1,number2,…,numberN)※式中，各參數(shù)含義同STDEV函數(shù)?！摵瘮?shù)假設其參數(shù)是整個樣本總體，在根據(jù)歷史數(shù)據(jù)計算股票標準偏差時可以使用本函數(shù)。3．LN函數(shù)功能：返回一個數(shù)的自然對數(shù)。公式：=LN(number)※式中，number是用于計算其自然對數(shù)的正實數(shù)?！鶕?jù)歷史數(shù)據(jù)計算的股票投資收益率可分為離散收益率和連續(xù)復利收益率。假設購買股票后可以連續(xù)不斷地收回投資并反復進行再投資，此時按照連續(xù)復利的方式所計算的投資收益率就是連續(xù)復利收益率。根據(jù)公式，計算連續(xù)復利收益率可以使用本函數(shù)。4．SLOPE函數(shù)功能：返回根據(jù)known_y’s和known_x’s中的數(shù)據(jù)點擬合的線性回歸直線的斜率。公式：=SLOPE(known_y’s,known_x’s)※式中，known_y’s為數(shù)字型因變量數(shù)組或單元格區(qū)域；known_x’s為自變量數(shù)據(jù)點集合。參數(shù)可以是數(shù)字，或者是涉及數(shù)字的名稱、數(shù)組或引用。如果數(shù)組或引用參數(shù)里包含文本、邏輯值或空白單元格，這些值將被忽略，但包含零值的單元格將計算在內(nèi)?！鶎善蓖顿Y分析而言，SLOPE函數(shù)可以用來計算一只股票的β系數(shù)。5．INTERCEPT函數(shù)功能：利用現(xiàn)有的x值與y值計算直線與y軸的截距。公式：=INTERCEPT(known_y’s,known_x’s)※式中，參數(shù)含義可以參考SLOPE函數(shù)。※該函數(shù)通過計算出截距，可以用來繪制一只股票的特征線。6．COVAR函數(shù)功能：返回協(xié)方差，即每對數(shù)據(jù)點的偏差乘積的平均數(shù)，利用協(xié)方差可以決定兩個數(shù)據(jù)集之間的關系。公式：=COVAR(array1,array2)※式中，array1為第一個單元格區(qū)域；array2為第二個單元格區(qū)域。參數(shù)必須是數(shù)字，或者是包含數(shù)字的名稱、數(shù)組或引用。如果數(shù)組或引用參數(shù)包含文本、邏輯值或空白單元格，則這些值將被忽略，但包含零值的單元格將計算在內(nèi)。如果array1和array2所含數(shù)據(jù)點的個數(shù)不等，則函數(shù)COVAR返回錯誤值#N/A；如果array1和array2當中有一個為空，則函數(shù)COVAR返回錯誤值#DIV/0!?！嬎阋恢还善钡摩孪禂?shù)，根據(jù)公式，則首先要計算出該股票的收益率和市場投資組合收益率之間的協(xié)方差，本函數(shù)可以實現(xiàn)這一功能。7．CORREL函數(shù)功能：返回單元格區(qū)域array1和array2之間的相關系數(shù)。公式：=CORREL(array1,array2)※式中，參數(shù)含義可以參考COVAR函數(shù)?！竞瘮?shù)可以用來計算一只股票與市場投資組合之間的相關系數(shù)。7.3債券投資分析模型7.3.1債券估價模型1．債券估價的基本原理債券作為一種投資，現(xiàn)金流出是其購買價格，現(xiàn)金流入是利息和歸還的本金，或者是出售時得到的現(xiàn)金。債券的價值是指債券未來現(xiàn)金流入量的現(xiàn)值，即債券各期利息收入的現(xiàn)值加上債券到期償還本金的現(xiàn)值之和。只有債券的內(nèi)在價值大于購買價格時，才值得購買。對不同類型的債券來說，內(nèi)在價值的計算公式也不同。下面列示幾個不同類型債券的估價公式。（1）定期付息債券。定期付息的債券是指每年一次或數(shù)次向投資者支付固定的利息，到期按面值償還本金的債券。估價公式為：式中，V為債券的價值；F為債券的面值；i為債券的年票面利率；k為投資者要求的最低年投資收益率；n為債券的期限；m為每年付息的次數(shù)。（2）到期還本付息債券。到期還本付息債券是指債券到期后一次性地向投資者支付全部利息和按面值償還本金的債券。估價公式為：式中各符號含義如前所述。（3）零息債券。零息債券以折價（發(fā)行價格低于面值）方式發(fā)行，沒有票面利率，到期按面值償還。因此，投資者購買這種債券只能取得購買價格和面值之間的差價收入。估價公式為：式中各符號含義如前所述。（4）永久債券。永久債券是沒有到期日，不返還本金，但可以無限期地按期取得固定利息的債券。估價公式為：（5）流通債券。流通債券是已經(jīng)發(fā)行并在二級市場上流通的債券。流通債券具有如下特點：債券到期的時間小于發(fā)行在外的時間；估價的時點不在發(fā)行日，可以是任何時點，會產(chǎn)生“非整數(shù)計息期”問題。對流通債券進行估價，可以考慮以下兩種辦法：一是以估價日為折算時間點，未來各期的現(xiàn)金流量按非整數(shù)計息期折現(xiàn)；二是以估價日之后的下一次付息日為折算時間點，先將該付息日之后的未來各期現(xiàn)金流量折現(xiàn)，然后再連同付息日的利息一起加總，折算到估價日。2．債券估價的基本模型【例7-1】某公司2010年1月1日發(fā)行A、B兩種債券，其詳細資料如表7-2所示。要求建立一個模型，能夠分別利用PRICE函數(shù)和PV函數(shù)計算兩種債券的價值。所建立的模型的結構如圖7-1所示：3．債券價值與到期期限之間的關系分析模型【例7-2】現(xiàn)有A、B、C三種債券，已知條件如表7-3所示。要求建立一個模型，能夠?qū)θN債券的價值與距離到期期限（10年）之間的關系進行分析。圖7-1債券估價的基本模型所建立的模型的結構如圖7-2所示：圖7-2債券價值與到期期限的關系分析模型4．債券價值與市場利率之間的關系分析模型【例7-3】現(xiàn)有A、B、C三種債券，已知條件如表7-4所示。要求建立一個模型，能夠?qū)θN債券的價值與市場利率之間的關系進行分析。所建立的模型的結構如圖7-3所示：5．可調(diào)整參數(shù)大小的債券價值計算模型【例7-4】某債券的資料如表7-5所示。要求建立一個可以調(diào)整各參數(shù)大小的債券價值計算模型，以便能夠隨時得到各參數(shù)發(fā)生變化后債券價值的變化結果，并繪制債券價值與票面年利率之間的關系圖。圖7-3債券價值與市場利率的關系分析模型所建立的模型的結構如圖7-4所示：圖7-4可調(diào)參數(shù)的債券價值計算模型7.3.2債券投資收益計算模型1．債券投資收益額的計算模型【例7-5】現(xiàn)有A、B、C三種債券，已知條件如表7-6所示。要求建立一個模型，能夠分別計算A、B債券的應計利息和C債券到期可收回的金額。所建立的模型的結構如圖7-5所示：2．債券投資收益率的計算模型【例7-6】現(xiàn)有A、B、C、D四種債券，已知條件如表7-7所示。要求建立一個模型，利用有關函數(shù)分別計算這四種債券的年投資收益率。圖7-5債券投資收益額的計算模型所建立的模型的結構如圖7-6所示：圖7-6利用函數(shù)計算債券投資收益率模型【例7-7】現(xiàn)有甲、乙、丙三種債券，其中甲為國庫券，基本資料如表7-8所示。要求建立一個模型，利用有關函數(shù)分別計算甲國庫券的等效收益率、乙債券的年利率和丙債券的貼現(xiàn)率。所建立的模型的結構如圖7-7所示：

圖7-7計算債券的等效收益率、年利率和貼現(xiàn)率的模型1．債券久期的計算模型【例7-8】某債券的資料如表7-9所示。要求建立一個模型，能夠分別利用公式和函數(shù)計算該債券的債券久期和修正久期。所建立的模型的結構如圖7-8所示：7.3.3債券久期的有關模型2．債券投資組合的久期免疫策略模型久期免疫策略是通過價格風險和再投資風險相互抵消作用的原理從投資期限的角度考慮利率風險免疫的。債券投資組合的久期等于投資組合內(nèi)各種債券久期的加權平均數(shù)，計算公式為：圖7-8債券久期的計算模型※式中，Dp為債券投資組合的麥考雷久期；wi為債券i在債券投資組合中的比例；Di為債券i的麥考雷久期；n為債券組合中債券的種類數(shù)?！?/p>

在構造投資組合的時候，應該合理安排組合內(nèi)各種債券的比例，使得投資組合的久期等于投資者負債的久期，以免除所負債務的利率風險。

【例7-9】某投資者甲向銀行長期借款500000元，期限10年，年利率為15%。每年年末，甲需向銀行償還利息75000元，并在到期時歸還本金。為了滿足負債現(xiàn)金流的需求且能規(guī)避所負債務的利率風險，甲擬構造一個由A、B、C三種債券所組成的投資組合。其他已知條件如表7-10所示。要求建立一個模型，能夠確定A、B、C三種債券的投資比例。所建立的模型的結構如圖7-9所示：圖7-9債券投資組合的久期免疫策略模型7.4股票投資分析模型7.4.1股票估價模型1．股票估價的基本原理股票的價值就是股票帶給持有者的未來現(xiàn)金流入的現(xiàn)值，即由一系列的未來股利的現(xiàn)值和將來出售股票時售價的現(xiàn)值之和構成。只有股票的價值大于購買價格時，才值得購買。下面列舉幾個不同類型股票的估價公式。（1）短期持有的股票。短期持有的股票的未來現(xiàn)金的流入量包括預期獲得的股利和轉讓價款收入。估價公式為：※式中，V為股票的價值；Dt為第t期的每股股利；k為投資者要求的最低投資收益率；n為股票的持有期限；Vn為股票的出售價格。（2）長期持有、股利固定不變的股票。長期持有、股利固定不變的股票的未來現(xiàn)金的流入量包括未來各期獲得的股利。估價公式為：※式中，V為股票的價值；D為每期的固定股利；k為投資者要求的最低投資收益率。（3）長期持有、股利按固定比率增長的股票。這種股票的估價公式為：※式中，V為股票的價值；D0為當期支付的股利；D1為第1期期末支付的股利；k為投資者要求的最低投資收益率；g為股利增長率。（4）長期持有、股利非固定增長的股票。指在未來不同的時間段內(nèi)，股利會出現(xiàn)超常高速增長和穩(wěn)定增長階段的情況。這樣，可以分段計算高速增長階段和穩(wěn)定增長階段未來收益的現(xiàn)值，兩階段現(xiàn)值之和就是這種股票的股票價值。假設某股票的股利在前n期高速增長，然后又按照固定比率增長，則估價公式為：※式中：V為股票的價值；D0為當期支付的股利；Dn為第n期期末支付的股利；k為投資者要求的最低投資收益率；g1為前n期的股利增長率；g2為股利固定增長時的增長率。2．股票估價的基本模型【例7-10】現(xiàn)有A、B兩只股票，已知條件如表7-11所示，要求建立一個模型，能夠計算A、B兩種股票的股票價值并判斷是否值得投資。所建立的模型的結構如圖7-10所示：【例7-11】甲公司為一家在上海證券交易所上市的公司。投資者對該公司發(fā)展情形預計如下：在未來的前4年里，股利將按照20%的增長率高速增長，此后，將以6%的增長率穩(wěn)定增長。甲公司股票的其他已知條件如表7-12所示。要求建立一個模型，能夠計算該公司股票的股票價值并判斷是否值得投資。圖7-10股票估價與投資價值判斷模型所建立的模型的結構如圖7-11所示：圖7-11股利非固定增長股票的估價與投資價值判斷模型7.4.2股票投資收益與風險測度模型1．股票投資收益與風險測度的公式

※投資者在從事股票投資的時候，需要對股票的收益和風險進行度量，以便合理安排資金，盡可能選擇那些低風險、高收益的股票。

※股票投資的收益一般用收益率表示，在分析一只股票的歷史交易情況時，收益率可分為離散收益率和連續(xù)復利收益率。如果是對股票的未來走勢做出預期，此時的收益率稱為期望收益率。

※無論是對歷史交易情況進行分析還是對未來走勢做出預期，股票投資的風險都可以用標準差或變化系數(shù)來度量。股票投資收益與風險測度的有關公式如下：（1）離散收益率。計算公式為：※式中，Rt為第t期的離散收益率；Dt為第t期的股利；