# Bond Convexity in Excel and R

[This article was first published on

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 **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.

**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

**s**um of multiplications of

**t**ime and

**t**ime +

**1**and

**d**iscounted

**c**ash 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.

The following Excel spreadsheet shows the case of k=4. 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) [1] 4.43501 > convexity(price, cpn, m, P, freq) [1] 25.03265 > > freq <– 4 > price <– bondpv(cpn, m, y, P, freq) > duration(price, cpn, m, P, freq, modified = TRUE) [1] 4.450557 > convexity(price, cpn, m, P, freq) [1] 22.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.