The use of Excel’s PRICE function can eliminate much of the math required to understand how modified duration and convexity can be used to predict the price change in option-free bonds. The spreadsheet for this post is called “Option Free” and like all of my spreadsheets, it can be downloaded for free. The sheet is initially protected, except for the yellow input cells, but there is no password required to unlock the sheet.
We start with a simple calculation of a 5.3 year bond based on a 30/360 day assumption. I wanted to use a bond with accrued interest to make the spreadsheet more practical for general use.
Then we reprice the bond both up and down 10 basis points (.1%). We then name the original yield y, the original full price ( including accrued interest) p, and the absolute changes in prices x and z.
With these four variables we have enough information to calculate Macaulay duration, modified duration, and convexity.
Now, we can predict the price change of the bond if rates instantaneously moved up 1%. We need one more calculation shown on the calculations below. This is the convexity adjustment. As you can see, the difference between the actual price change and the predicted price change using modified duration and convexity would only make a difference of 2 cents on a $1000 par bond.
Download the spreadsheet “Option Free“