收益率曲線制作

1、2012年1月30日 用EXCEL VB制作收益率曲線正在為忙于撰寫一本有關(guān)解讀中國經(jīng)濟景氣的書，在網(wǎng)上搜尋中國經(jīng)濟資料時， 看到一些有關(guān)收益率曲線的制作。由于我以前亦有撰寫另一本關(guān)于金融工程的書，所以牽起我用EXCEL的VBA去編寫此一挑戰(zhàn)。但是為什庅我們要去每作收益率曲線？其用意又是什庅呢？如果 要知道為什庅收益率曲線那庅重要，我們便要先知道什庅是債券。債券是債務(wù)人（債券發(fā)行人）發(fā)給債權(quán)人（債券持有人）的有價憑證。由于債券持有人不一 定得持有至到期日，期間亦可進行交易， 交易時的債券價格與市場利率、投資人買入后可得的實際報酬（又稱為收益率）。當市場利率低于債券票面利率時，有多余資金的人更樂

2、于把 資；金用來買債券，以利獲取更高的利息，因而使得債券價格上漲：反之，當市場利率高于 債券票面利率時，債券價格便會下跌。 再者，雖然債券的票面利率是債券發(fā)行人定期要支付 給債券持有人的利率，決定了持有人將可得到的利息金額，但由于絕大多數(shù)投資人都是在不同的時間點，以不同的債券價格買入債券， 而買入債券的時間點關(guān)系著到期日時可取得利息 多寡，買進的債券價格高低，也會影響到期時可取回的本金盈虧，因此，投資人買入債券后至到期日可得的實際報酬率，并不等于票面利率，可獲取的實質(zhì)投資報酬率便稱為收益率， 收益率則是指持有人買入債券至到期日為止，平均每年可預(yù)期的實質(zhì)投資報酬率。收益率與債券價格的關(guān)系可以簡單

3、如下表示：收益率=_票面利率x面額 債券價格收益率和債券價格為反向關(guān)系。買入的債券價格高，收益率便會降低；相對地，買入債券價格低，收益率便高。例如，當投資人買入的債券價格高于票面價格時，到期時取回持有期間 的票面固定利息，在扣除高于票面價格的買入成本后，實際所得的利息收入將小于票面固定利息，其實質(zhì)報酬率，即收益率便會低于票面利率，因此，在掌握了收益率與債券價格的反 向關(guān)系下，透過觀察債券市場中，各天期公債收益率與到期期間的連續(xù)變化所呈現(xiàn)的曲線圖， 便可看出長短期借貸資金市場的供需情形，以及市場對未來景氣的普遍預(yù)期。簡單而言，收益率曲線隱含著對未來利率變動的預(yù)期。向右上方傾斜的收益率曲線反映市場

4、的一種平均的預(yù)期，認為未來的短期利率將高于當前水平，投資者就將證券從長期轉(zhuǎn)向短期；當收益率曲線處于相對較高的水平， 而且是平坦的或者是向下傾斜的時候，表明市場預(yù)期未來短期利率將下降，投資者轉(zhuǎn)向長期證券投資。 這種參照收益率曲線轉(zhuǎn)換證券到期日的方法，關(guān)鍵是要準確預(yù)測市場利率變化的時間。那庅要什樣去制作收益率曲線，在紅預(yù)金融中心介紹如下：收益率曲線的制作方法（一）-三次樣條擬合法在固息債、浮息債以及含權(quán)債的定價中，讀者反復(fù)地看到兩個概念：收益率曲線和利率期限結(jié)構(gòu)，不熟悉的讀者很容易對這兩個概念造成混淆，而實際上它們的定義截然不同，在債券市場上的應(yīng)用也完全不一樣，因此讀者有必要進行區(qū)分。在本期債券教

5、室中，我們將首先向大家介紹收益率曲線的概念并用Excel演示收益率曲線的制作方法。收益率曲線的認識（Yield Curve）收益率曲線描繪的是附息債券的到期收益率（Yield to Maturity）與剩余年期的關(guān)系，橫軸表示基準債券的剩余年期，縱軸表示不同剩余年期相對應(yīng)的到期收益率。曲線上的某一點代表著：如果將該點所對應(yīng)的剩余年期的債券持有到期，期間所有的利息收入和期末返還的本金所帶來的內(nèi)部投資報酬率（Internal Rate ofReturn ）為多少。收益率曲線的作用在于：對于任何一個剩余年期，在收益率曲線上都可以找到相對應(yīng)的合理到期收益率。 如果市場中某只債券的到期收益率大幅偏離收益

6、率曲線，則說明有可能定價不合理，存在套利機會。需要注意的是：對于浮息債和含權(quán)債無法制作收益率曲線，因為浮息債和含權(quán)債在未來的現(xiàn)金流是不確定 的，無法用以計算到期收益率。收益率曲線制作方法（一）-三次樣條擬合法（1）曲線擬合函數(shù)曲線擬合函數(shù)定義為（x為債券剩余年期，y為債券的到期收益率）：公式（1）其中，a、b、c、d都是參數(shù)，采用最小二乘法求解。（2 ）目標函數(shù)最小二乘法的目標函數(shù)為：公式（2）其中，y為市場收益率，為透過樣條法計算岀來的理論收益率。（3 ）優(yōu)化方式選取市場中不同到期期限的基準債券的到期收益率，通過最小二乘法計算出 a、b、c、d,求得收益率曲線。在Excel中制作收益率曲線的

7、方法（一）-三次樣條擬合法（1 ）選取樣本，計算到期收益率以制作上交所企業(yè)債的收益率曲線為例，我們選取岀20個樣本（在此基于演示方便），分別列示岀它們的剩余年期和到期收益率，一種方法是根據(jù)我們在前幾期金融工程報中介紹的收益率計算方法自行計算，具 體過程在此不贅述，僅于下表中顯示計算結(jié)果：另一種方法非常簡單，我們可以直接從"紅頂收益戰(zhàn)略家 5.0"中直接導(dǎo)入這20個樣本的剩余年期和收益率 字段，操作方法參見以下畫面：（2）設(shè)置曲線擬合函數(shù)，計算最小二乘法參數(shù)根據(jù)三次樣條擬合法，假設(shè)理論收益率滿足公式（1），首先設(shè)置參數(shù)a=0, b=0，c=0, d=0，分別計算出各債券的理論

8、收益率接著根據(jù)公式（2），計算最小二乘法的參數(shù)，即實際收益率與理論收益率的殘差的平方，從而得到最小目 標函數(shù)E。（3）規(guī)劃求解計算參數(shù)有了目標函數(shù)，就可以利用規(guī)劃求解計算出四個參數(shù) a，b, c, do用鼠標或鍵盤方向鍵選中單元格 F20后， 點擊"工具"菜單中的"規(guī)劃求解”，在如下對話框中點選最小值，并將可變單元格設(shè)置為a, b, c, d所對應(yīng)的四個單元格$C$24:$C$27,然后點擊求解按鈕：在Excel中將出現(xiàn)如下計算結(jié)果，即利用最小二乘法計算出的a, b, c, d四個參數(shù)。（4 ）繪制收益率曲線和散點將收益率曲線的橫軸分為 30年（符合市場標準），每

9、隔0.5年取一個點，利用求解的曲線擬合方程計算出 每個點對應(yīng)的理論收益率，并將原有樣本的剩余年期和對應(yīng)的市場收益率列示在右邊。用鼠標或鍵盤方向鍵選中插入圖表的區(qū)域，點擊"插入”菜單中的"圖表”，以上述30年的剩余年期系列為 x軸，對應(yīng)的理論收益率系列為y軸，建立一條平滑曲線，這條曲線就是我們制作的收益率曲線。同時，以右側(cè)列示樣本的剩余年期系列為x軸，對應(yīng)的市場收益率系列為y軸，往收益率曲線圖表中添加入對應(yīng)的散點。這樣就可以直觀地觀測到曲線的擬合效果。5)曲線的修正由于我們直接將市場上的所有債券價格拿來擬合收益率曲線，效果很可能會不理想，因此在實際制作中，還需要充分考慮以下四

10、個因素并對曲線進行修正：該用哪些債券作為樣本如何處理債券流動性問題債券價格岀現(xiàn)異常狀況是否考慮從樣本中剔除曲線端點的長年期債券利率不合理該如何設(shè)定”我介紹的方法亦都是用三次樣條插值法我會以任何可以運用的債券價格資通過自助法去計算收益率曲線。在標準課本里自助法就是計算附息票債券的所有息票經(jīng)貼現(xiàn)率計算后的凈現(xiàn)值即可計算出零息率。由於可能沒有足夠的債券資料，所以用自助法會可能缺少某些利率，令到整個計算過程停頓，而無法完成。而樣條法是一個很好的填補多項式的空缺。而填補多項式的空缺的最隣近的點叫結(jié)點。假設(shè)有s個結(jié)點(xi,yi),(X2,y2),s,ys)的插值，都可以由n-1的多項式? 1, ?2，,

11、n?i達成所有 點的樣條。當然越高次元的多項式要做到精準，額外的假設(shè)設(shè)定必不可少。最通常用到是定 義額外線性的假設(shè)在插值的兩端。在三次樣條插值法里假設(shè)有s個結(jié)點(X1,y”，(X2,y2),s(x)而X1 <X2<<x會有n-1三次兀2O多項式？ i(X)= ai + biX + CiX + diX (i= 1,2,)。而多項式會產(chǎn)生每一段左方及右方相對應(yīng)結(jié)點的數(shù)值。?=y?x?, i=1,2,-,n(1)作者：江冠亨32012年1月30日 用EXCEL VB制作收益率曲線?+y= ?X?+1 , i=1,2,-,n多項式的平滑性條伴在每一結(jié)點會分別為其一階導(dǎo)數(shù)?i(X)=

12、bi + 2gx+ 3diX2及二階導(dǎo)數(shù)? i (x)= + 2ci+ 6dix。根據(jù)公式1及2，多項式會延伸每一結(jié)點。一階導(dǎo)數(shù)及二階導(dǎo)數(shù)的延伸會-,n-,n由?+似?+1 = ?+(x?+d , i=1,2,?+似？?+1 = ?+(x?+d , i=1,2,在此根據(jù)公式(1)至(4)會有4(n-1)-2的吻合條件。樣條系數(shù)定義為自然樣條條件去推動樣條走 出插值的分段。此可以在二階導(dǎo)數(shù)成為零印證出。1 ( ?1)= ? ?-1 (x?_1)= 0吻合條件加上然樣條條件可改寫成矩陣形式作者：江冠亨#2012年1月30日用EXCEL VB制作收益率曲線?-1? =: .：(6)0?-1?-1?-

13、10?-1(7)測是計算債券價格的公式根據(jù)公式(6)會產(chǎn)生4(n-1)行及2列的向量。而公式債券價格=刀?=1希?+面值(1+?)?：(7)J51”RI Ml I IA f A s-好ARRI丈H 3K»<?-«*2 鈕-E*D-鼻叭rgnjEs壇號 TRUEo.au4喚5W沖0 2«4 5K%110 50C4-684%97 70121 »04. KM%時311Ji.m4 MWiog wuJ.KKI5 044%頫如li7 5D0Sill%ioq oq16IB 0004»9%300 m1718IS料ihii *?1聞ttl曲呀垂如# I 齡

14、站 I He 1 jSjrJ?I DO QC1)0 ：'0I DO WDC401D0帥QO«DHO M0JMIDO.P2 JOT兀：.："2：<>1DDOO2.E1D1DD 0D2 3«DHIKH1U0 0 0 01-111.Q D D D*.5 5 5軍屛囂欝Q.VS2Z60.DS14巧雅jjr0潮4訊為OtOT睥29DTK4 571%)0aisi431am4 S?232om4 S?3%O.fJ?0 J5S強4%am4 EH «DT714-淞D.H4.177%»OtffT4 577%D 144 578%0.2S24W*41

15、伽J|j蘭 * 11 H - 聲捕占號20” I朋 I些A作者：江冠亨42012年1月30日用EXCEL VB制作收益率曲線作者：江冠亨#2012年1月30日用EXCEL VB制作收益率曲線圖1:收益率曲線佈局作者：江冠亨#2012年1月30日 用EXCEL VB制作收益率曲線Option ExplicitPrivate Const eps = 1 * 10 A -14Private Const mmax = 100Sub CalZeroRates()Dim n As Integer: n = Range("B4").ValueDim precise As Double:

16、precise = Range("E4").ValueDim T As Double: T = Range("A8").Offset(1,0)Dim bondPrice As Double: bondPrice = Range("C8").Offset(1,0)Dim parValue As Double: parValue = Range("D8").6ffset(1,0)Dim bnit As Double: bnit = -(1 / T) * Log(bondPrice / parValue)Dim i As

17、 IntegerDim zeroRate() As Double: ReDim zeroRate(1 To n)Dim preciseFlag As BooleanDim maxDeviation As DoubleFor i = 1 To n: zeroRate(i) = bnit: Next iCall NewtonRaphson(n, precise, zeroRate, preciseFlag, maxDeviation)For i = 1 To n: Range("B8").Offset(i, 0) = zeroRate(i): Next iRange("

18、;E5").Value = preciseFlagRange("E6").Value = maxDeviationEnd Sub原代碼 1: calZeroRates()Sub GenYieldCurve()Dim n As Integer: n = Range("B4").ValueDim T() As Double: ReDim T(1 To n)Dim zeroRate() As Double: ReDim zeroRate(1 To n)Dim i As Integer, j As Integer, k As IntegerDim a(

19、) AsDouble:ReDima(1Ton -1)Dim b() AsDouble:ReDimb(1Ton -1)Dim c() AsDouble:ReDimc(1Ton -1)Dim d() AsDouble:ReDimd(1Ton -1)For i = 1 To nT(i) = Range("A8").Offset(i, 0) zeroRate(i) = Range("B8").Offset(i, 0)Next iCall CubicSpline(n, T, zeroRate, a, b, c, d)Dim nupoint As Integer:

20、nupoint = Range("E23").ValueDim term As DoubleRange("A25:B300").CIearContentsk = 0For i = 1 To n - 1For j = 0 To nupointk = k + 1term = T(i) + j * (T (i + 1) - T(i) / (nupoint + 1) Range("A24").Offset(k, 0) = termRange("B24").Offset(k, 0) = a(i) + b(i) * term

21、+ c(i) * term a 2 + d(i) * term a 3 Next jNext iRange("A24").Offset(k + 1,0) = T(n)Range("B24").Offset(k + 1,0) = zeroRate(n)End SubSub FunctionArray(n As Integer, zeroRate() As Double, ByRef g() As Double)Dim T() As Double: ReDim T(1 To n)Dim bondPrice() As Double: ReDim bondPri

22、ce(1 To n)Dim parValue() As Double: ReDim parValue(1 To n)Dim couponBond() As Double: ReDim couponBond(1 To n)Dim m() As Integer: ReDim m(1 To n)Dim tb() As Double: ReDim tb(1 To n, 1 To mmax)Dim rb() As Double: ReDim rb(1 To n, 1 To mmax)Dim i As Integer, j As Integer, k As Integer, pointer As Inte

23、gerDim tau As DoubleDim a() As Double:ReDima(1Ton-1)Dim b() As Double:ReDimb(1Ton-1)Dim c() As Double:ReDimc(1Ton-1)Dim d() As Double:ReDimd(1Ton-1)For i = 1 To nT(i) = Range("A8").Offset(i, 0) bondPrice(i) = Range("C8").Offset(i, 0) parValue(i) = Range("D8").Offset(i,

24、0) couponBond(i) = Range("E8").Offset(i, 0) m(i) = Range("F8").Offset(i, 0)For j = 1 To m(i)tb(i, j) = Range("G8").Offset(i, j - 1)Next jNext iCall CubicSpline(n, T, zeroRate, a, b, c, d)For i = 2 To npointer = 1For j = 1 To m(i)tau = tb(i, j)If (Abs(tau - T(1) <= ep

25、s) Thenrb(i, j) = zeroRate(1)GoTo nextjEnd IfFor k = pointer To i - 1If (tau > T(k) And tau <= T(k + 1) Thenrb(i, j) = a(k) + b(k) * tau + c(k) * tau A 2 + d(k) * tau A 3 pointer = k GoTo nextjEnd IfNext knextj: Next jNext iFor i = 1 To ng(i) = bondPrice(i) - parValue(i) * Exp(-zeroRate(i) * T

26、(i)For j = 1 To m(i)g(i) = g(i) - couponBond(i) * Exp(-rb(i, j) * tb(i, j)Next jNext iEnd Sub原代碼 3: FunctionArray()作者：江冠亨62012年1月30日 用EXCEL VB制作收益率曲線Sub NewtonRaphson(n As Integer, precise As Double, ByRef x() As Double, ByRef preciseFlag As Boolean, ByRef maxDeviation As Double)Const itMax As Integ

27、er = 1000Dim yElder() As Double: ReDim yElder(1 To n)Dim yMover() As Double: ReDim yMover(1 To n)Dim iMover() As Double: ReDim iMover(1 To n)Dim jx() As Double: ReDim jx(1 To n)Dim omega() As Double: ReDim omega(1 To n, 1 To n)Dim Dxter() As Double: ReDim Dxter(1 To n)Dim i As Integer, j As Integer,

28、 k As Integer, itr As IntegerFor itr = 1 To itMaxFor i = 1 To n: yElder(i) = x(i): Next iCall FunctionArray(n, yElder, jx)Forj = 1 To nFor k = 1 To n: yMover(k) = yElder(k) + precise * IIf(j = k, 1,0): Next kCall FunctionArray(n, yMover, iMover)For i = 1 To n: omega(i, j) = (iMover(i) - jx(i) / prec

29、ise: Next iNext jCall SolveAxb(omega, jx, Dxter, n, 1, 1, 1)For i = 1 To n: x(i) = yElder(i) - Dxter(i): Next iFor i = 1 To nIf Abs(x(i) - yElder(i) <= precise ThenpreciseFlag = TrueElsepreciseFlag = FalseExit ForEnd IfNext iIf preciseFlag Then Exit ForNext itrCall FunctionArray(n, x, jx)maxDevia

30、tion = 0For i = 1 To nIf Abs(jx(i) > maxDeviation Then maxDeviation = Abs(jx(i)Next iEnd Sub原代碼 4: NewtonRaphson()Sub SolveAxb(Amatrix() As Double, bvec() As Double, ByRef xvec() As Double, n As Integer, iptr As Integer, jptr As Integer, kptr As Integer) -Dim wsAmatrix As Variant: ReDim wsAmatrix

31、(1 To n, 1 To n)Dim row As Integer, column As IntegerFor row = 1 To nFor column = 1 To n: wsAmatrix(row, column) = Amatrix(iptr + row - 1, jptr + column - 1): Next column Next rowDim wsbvec As Variant: ReDim wsbvec(1 To n, 1 To 1)For row = 1 To n: wsbvec(row, 1) = bvec(kptr + row - 1): Next rowDim w

32、sxvec As Variant:With Application.WorksheetFunctionwsxvec = .MMult(.MInverse(wsAmatrix), wsbvec)End WithDim i As IntegerIf n = 1 ThenFor i = kptr To kptr + n - 1: xvec(i) = wsxvec(i - kptr + 1): Next iElseFor i = kptr To kptr + n - 1: xvec(i) = wsxvec(i - kptr + 1, 1): Next iEnd IfEnd Sub原代碼 5: Solv

33、eAxb()作者：江冠亨82012年1月30日用EXCEL VB制作收益率曲線原代碼 6: CubicSpline ()作者：江冠亨92012年1月30日用EXCEL VB制作收益率曲線Double, ByRef d() As Double)Dim i As Integer, j As Integer, pointer As IntegerDim Tmatrix() As Double: ReDim Tmatrix(1 To 4 * (n - 1), 1 To 4 * (n - 1)Dim Rvector() As Double: ReDim Rvector(1 To 4 * (n - 1)Dim Qvector() As Double: ReDim Qvector(1 To 4 * (n - 1)For i = 1 To (n - 1)Rvector(i) = y(i)Rvector(n - 1 + i)Rvector(2 * (n - 1)Rvector(3 * (n - 1) Next ioo一一=-+ +For i = 1 To 4 * (n - 1)For j = 1 To 4 * (n - 1): Tmatrix(i, j) = 0: