收益率曲线制作

1、2012年1月30日 用EXCEL VB制作收益率曲线正在为忙于撰写一本有关解读中国经济景气的书，在网上搜寻中国经济资料时， 看到一些有关收益率曲线的制作。由于我以前亦有撰写另一本关于金融工程的书，所以牵起我用EXCEL的VBA去编写此一挑战。但是为什庅我们要去每作收益率曲线？其用意又是什庅呢？如果 要知道为什庅收益率曲线那庅重要，我们便要先知道什庅是债券。债券是债务人（债券发行人）发给债权人（债券持有人）的有价凭证。由于债券持有人不一 定得持有至到期日，期间亦可进行交易， 交易时的债券价格与市场利率、投资人买入后可得的实际报酬（又称为收益率）。当市场利率低于债券票面利率时，有多余资金的人更乐

2、于把 资；金用来买债券，以利获取更高的利息，因而使得债券价格上涨：反之，当市场利率高于 债券票面利率时，债券价格便会下跌。 再者，虽然债券的票面利率是债券发行人定期要支付 给债券持有人的利率，决定了持有人将可得到的利息金额，但由于绝大多数投资人都是在不同的时间点，以不同的债券价格买入债券， 而买入债券的时间点关系着到期日时可取得利息 多寡，买进的债券价格高低，也会影响到期时可取回的本金盈亏，因此，投资人买入债券后至到期日可得的实际报酬率，并不等于票面利率，可获取的实质投资报酬率便称为收益率， 收益率则是指持有人买入债券至到期日为止，平均每年可预期的实质投资报酬率。收益率与债券价格的关系可以简单

3、如下表示：收益率=_票面利率x面額 债券价格收益率和债券价格为反向关系。买入的债券价格高，收益率便会降低；相对地，买入债券价格低，收益率便高。例如，当投资人买入的债券价格高于票面价格时，到期时取回持有期间 的票面固定利息，在扣除高于票面价格的买入成本后，实际所得的利息收入将小于票面固定利息，其实质报酬率，即收益率便会低于票面利率，因此，在掌握了收益率与债券价格的反 向关系下，透过观察债券市场中，各天期公债收益率与到期期间的连续变化所呈现的曲线图， 便可看出长短期借贷资金市场的供需情形，以及市场对未来景气的普遍预期。简单而言，收益率曲线隐含着对未来利率变动的预期。向右上方倾斜的收益率曲线反映市场

4、的一种平均的预期，认为未来的短期利率将高于当前水平，投资者就将证券从长期转向短期；当收益率曲线处于相对较高的水平， 而且是平坦的或者是向下倾斜的时候，表明市场预期未来短期利率将下降，投资者转向长期证券投资。 这种参照收益率曲线转换证券到期日的方法，关键是要准确预测市场利率变化的时间。那庅要什样去制作收益率曲线，在红预金融中心介绍如下：收益率曲线的制作方法（一）-三次样条拟合法在固息债、浮息债以及含权债的定价中，读者反复地看到两个概念：收益率曲线和利率期限结构，不熟悉的读者很容易对这两个概念造成混淆，而实际上它们的定义截然不同，在债券市场上的应用也完全不一样，因此读者有必要进行区分。在本期债券教

5、室中，我们将首先向大家介绍收益率曲线的概念并用Excel演示收益率曲线的制作方法。收益率曲线的认识（Yield Curve）收益率曲线描绘的是附息债券的到期收益率（Yield to Maturity）与剩余年期的关系，横轴表示基准债券的剩余年期，纵轴表示不同剩余年期相对应的到期收益率。曲线上的某一点代表着：如果将该点所对应的剩余年期的债券持有到期，期间所有的利息收入和期末返还的本金所带来的内部投资报酬率（Internal Rate ofReturn ）为多少。收益率曲线的作用在于：对于任何一个剩余年期，在收益率曲线上都可以找到相对应的合理到期收益率。 如果市场中某只债券的到期收益率大幅偏离收益

6、率曲线，则说明有可能定价不合理，存在套利机会。需要注意的是：对于浮息债和含权债无法制作收益率曲线，因为浮息债和含权债在未来的现金流是不确定 的，无法用以计算到期收益率。收益率曲线制作方法（一）-三次样条拟合法（1）曲线拟合函数曲线拟合函数定义为（x为债券剩余年期，y为债券的到期收益率）：公式（1）其中，a、b、c、d都是参数，采用最小二乘法求解。（2 ）目标函数最小二乘法的目标函数为：公式（2）其中，y为市场收益率，为透过样条法计算岀来的理论收益率。（3 ）优化方式选取市场中不同到期期限的基准债券的到期收益率，通过最小二乘法计算出 a、b、c、d,求得收益率曲线。在Excel中制作收益率曲线的

7、方法（一）-三次样条拟合法（1 ）选取样本，计算到期收益率以制作上交所企业债的收益率曲线为例，我们选取岀20个样本（在此基于演示方便），分别列示岀它们的剩余年期和到期收益率，一种方法是根据我们在前几期金融工程报中介绍的收益率计算方法自行计算，具 体过程在此不赘述，仅于下表中显示计算结果：另一种方法非常简单，我们可以直接从"红顶收益战略家 5.0"中直接导入这20个样本的剩余年期和收益率 字段，操作方法参见以下画面：（2）设置曲线拟合函数，计算最小二乘法参数根据三次样条拟合法，假设理论收益率满足公式（1），首先设置参数a=0, b=0，c=0, d=0，分别计算出各债券的理论

8、收益率接着根据公式（2），计算最小二乘法的参数，即实际收益率与理论收益率的残差的平方，从而得到最小目 标函数E。（3）规划求解计算参数有了目标函数，就可以利用规划求解计算出四个参数 a，b, c, do用鼠标或键盘方向键选中单元格 F20后， 点击"工具"菜单中的"规划求解”，在如下对话框中点选最小值，并将可变单元格设置为a, b, c, d所对应的四个单元格$C$24:$C$27,然后点击求解按钮：在Excel中将出现如下计算结果，即利用最小二乘法计算出的a, b, c, d四个参数。（4 ）绘制收益率曲线和散点将收益率曲线的横轴分为 30年（符合市场标准），每

9、隔0.5年取一个点，利用求解的曲线拟合方程计算出 每个点对应的理论收益率，并将原有样本的剩余年期和对应的市场收益率列示在右边。用鼠标或键盘方向键选中插入图表的区域，点击"插入”菜单中的"图表”，以上述30年的剩余年期系列为 x轴，对应的理论收益率系列为y轴，建立一条平滑曲线，这条曲线就是我们制作的收益率曲线。同时，以右侧列示样本的剩余年期系列为x轴，对应的市场收益率系列为y轴，往收益率曲线图表中添加入对应的散点。这样就可以直观地观测到曲线的拟合效果。5)曲线的修正由于我们直接将市场上的所有债券价格拿来拟合收益率曲线，效果很可能会不理想，因此在实际制作中，还需要充分考虑以下四

10、个因素并对曲线进行修正：该用哪些债券作为样本如何处理债券流动性问题债券价格岀现异常状况是否考虑从样本中剔除曲线端点的长年期债券利率不合理该如何设定”我介绍的方法亦都是用三次样条插值法我会以任何可以運用的债券價格资通過自助法去計算收益率曲线。在標準課本里自助法就是計算附息票债券的所有息票经贴现率計算后的净现值即可計算出零息率。由於可能没有足夠的债券资料，所以用自助法会可能缺少某些利率，令到整個計算過程停顿，而無法完成。而样条法是一個很好的填補多項式的空缺。而填補多項式的空缺的最隣近的点叫結点。假設有s個結点(xi,yi),(X2,y2),s,ys)的插值，都可以由n-1的多項式? 1, ?2，,

11、n?i達成所有 点的样条。当然越高次元的多項式要做到精准，额外的假設設定必不可少。最通常用到是定 義额外线性的假設在插值的兩端。在三次样条插值法里假設有s個結点(X1,y”，(X2,y2),s(x)而X1 <X2<<x会有n-1三次兀2O多項式？ i(X)= ai + biX + CiX + diX (i= 1,2,)。而多項式会产生每一段左方及右方相对应结点的数值。?=y?x?, i=1,2,-,n(1)作者：江冠亨32012年1月30日 用EXCEL VB制作收益率曲线?+y= ?X?+1 , i=1,2,-,n多項式的平滑性條伴在每一結点会分别为其一阶导数?i(X)=

12、bi + 2gx+ 3diX2及二阶导数? i (x)= + 2ci+ 6dix。根據公式1及2，多項式会延伸每一结点。一阶导数及二阶导数的延伸会-,n-,n由?+似?+1 = ?+(x?+d , i=1,2,?+似？?+1 = ?+(x?+d , i=1,2,在此根據公式(1)至(4)会有4(n-1)-2的吻合條件。样條系数定義为自然样条条件去推动样条走 出插值的分段。此可以在二阶导数成为零印證出。1 ( ?1)= ? ?-1 (x?_1)= 0吻合條件加上然样条条件可改寫成矩陣形式作者：江冠亨#2012年1月30日用EXCEL VB制作收益率曲线?-1? =: .：(6)0?-1?-1?-

13、10?-1(7)测是計算债券價格的公式根據公式(6)会产生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: