Bond Convexity in Excel and R

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



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

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.