Bond Convexity in Excel and R

[This article was first published on K & L Fintech Modeling, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

Bond duration is the first order linear approximation and thus does not consider a non-linearity. Making up for this shortcoming of the duration, a convexity is needed to describe the non-linearity of a bond price. This post explains the meaning and calculation process of the convexity by using Excel and R.


We have calculated a bond duration in the previous post

Bond Price


Bond price with unit notional amount, coupon C, YTM y, annual frequency is as follows. \[\begin{align} P = \frac{C}{1+y} + \frac{C}{(1+y)^2} + … + \frac{C}{(1+y)^{n-1}} + \frac{1+C}{(1+y)^n} \end{align}\]

Convexity


The second order derivative of \(P\) with respect to \(y\).
\[\begin{align} \frac{d^2P}{d y^2} = \frac{1·2·C}{(1+y)^3} + \frac{2·3·C}{(1+y)^4} + … + \frac{(n-1)· n · C}{(1+y)^{n+1}} + \frac{n·(n+1)·(1+C)}{(1+y)^{n+2}} \end{align}\]
Like the duration, factoring out \(\frac{1}{(1+y)^2}\) in the above equation yields

\[\begin{align} \frac{d^2P}{d y^2} &= \frac{1}{(1+y)^2} \left[\frac{1·2·C}{(1+y)} + \frac{2·3·C}{(1+y)^2} + … + \frac{(n-1)· n · C}{(1+y)^{n-1}} + \frac{n·(n+1)·(1+C)}{(1+y)^{n}} \right] \\ &= \frac{1}{(1+y)^2} STT1DC \end{align}\]
To avoid lengthy expression, we substitute STT1DC for the bracketed term. STT1DC is the abbreviation of the sum of multiplications of time and time + 1 and discounted cash flow (only coupon or coupon + principal amount).

Similar to the duration in the previous post, let’s modify it by a ratio of its initial bond price. Dividing \(\left(\frac{d^2P}{d y^2}\right)\) by \(P\) gives the following expression for the convexity (\(C\)) of a bond.

\[\begin{align} &\frac{d^2P}{d y^2} \frac{1}{P} = \frac{1}{(1+y)^2} STT1DC \frac{1}{P} = C \\ \rightarrow &\frac{d^2P}{d y^2} = CP \end{align}\]
Rearranging this equation, we can get the following expression.
\[\begin{align} C \times {(\Delta y)^2} = \frac{\Delta^2 P}{P} \end{align}\]

Generalization


When interest conversion period is less than one year such as one quarter, convexity is redefined as follows.
\[\begin{align} C &= \frac{1}{(1+\frac{y}{k})^2} STT1DC \frac{1}{P} \\ D &= \frac{1}{1+\frac{y}{k}} STDC \frac{1}{P} \end{align}\] Here, k is the number of compounding periods per year.


Effective Convexity and Duration


Effective convexity (C) is obtained from the numerical differentiation like the effective duration (D). \[\begin{align} C &= \frac{P_u – 2P_0 + P_d}{(\Delta y)^2 \times P_0} \\ D &= \frac{P_u – P_d}{2 \Delta y \times P_0} \end{align}\]
Here, \(P_0\) denotes an initial bond price with yield to maturity (\(y\)). \(P_u\) and \(P_d\) represent bond prices after downward (\(y-\Delta y\)) and upward (\(y+\Delta y\)) shocks to interest rates (yield to maturity) respectively.


Excel Illustration


To the best of our knowledge, Excel does not provides a built-in function for the calculation of convexity. Therefore, we calculate convexity by using the definition and the numerical differentiation for our clear understanding. As an example, we use the same specification of bond as in the previous post. Specifically, coupon rate (\(C\)), YTM (\(y\)), maturity (\(m\)), and interest rate change (\(\Delta y\)) are 5%, 3%, 5-year, and 0.0001 (1bp) respectively.

The following Excel spreadsheet shows the case of k=1.
Bond Convexity, Effective Convexity using Excel and R

The following Excel spreadsheet shows the case of k=4.
Bond Convexity, Effective Convexity using Excel and R
It is worthwhile to note that when k > 1, \(t+1\) in the convexity definition means \(t+1/k\).


R code


The following R code use derivmkts R package library which provides functions for the calculations of price, yield, duration, and convexity of a coupon bond. The reason why we use this library is that manual implementation of convexity in R is not very different from the case of the duration in the previous post.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
#========================================================#
# Quantitative ALM, Financial Econometrics & Derivatives 
# ML/DL using R, Python, Tensorflow by Sang-Heon Lee 
#
# https://kiandlee.blogspot.com
#——————————————————–#
# Convexity and (modified) Duration
#========================================================#
 
graphics.off()  # clear all graphs
rm(list = ls()) # remove all files from your workspace
 
library(derivmkts) # price, yield, duration, convexity
 
#——————————————————-
# Input
#——————————————————-
C   < 0.05       # coupon rate
y   < 0.03       # YTM
m   < 5          # maturity
P   < 1          # principal amount
cpn < C*P       # annual coupon amount
 
# k = 1 : coupon payments annually
freq  < 1
price < bondpv(cpn, m, y, P, freq)
duration (price, cpn, m, P, freq, modified = TRUE)
convexity(price, cpn, m, P, freq)
 
# k = 4 : coupon payments quarterly
freq  < 4
price < bondpv(cpn, m, y, P, freq)
duration (price, cpn, m, P, freq, modified = TRUE)
convexity(price, cpn, m, P, freq)
 
cs


Running the above R code results in the same output as those of Excel illustration.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
> freq  < 1
> price < bondpv(cpn, m, y, P, freq)
> duration(price, cpn, m, P, freq, modified = TRUE)
[14.43501
> convexity(price, cpn, m, P, freq)
[125.03265
> 
> freq  < 4
> price < bondpv(cpn, m, y, P, freq)
> duration(price, cpn, m, P, freq, modified = TRUE)
[14.450557
> convexity(price, cpn, m, P, freq)
[122.32152
> 
cs


Final Thoughts


From this post, we have understood the meaning of convexity by using an simple derivation and Excel illustration. Finally owing to derivmkt R package, we can easily implement R code for the calculation of convexity not to mention duration and price of a bond. Now we are ready to describes the percent(%) change of bond price more precisely with the help of duration and convexity. This topic will be covered in the next post.


To leave a comment for the author, please follow the link and comment on their blog: K & L Fintech Modeling.

R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)