# Approximating Duration, Modified Duration, and Convexity with Option Free Bonds

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.

1. Chuang Tsu Li says:

Hi Mr. Don Pistulka,

I am new to bond investing, and am reading up on modified duration and convexity. I am particularly interested in the effect on bond prices from the imminent rise in interest rate . I found your blog and excel sheet useful.

I do have a query in this article though. Shouldn’t the J6 cell ‘Price moves up 1%’ really be ‘Interest rate moves up 1%’, or “Yield-To-Maturity moves up 1%’ ?

Am I right to interpret that the terms ‘Interest rate’ and ‘Yield-To-Maturity’ can be used rather interchangeably because the Yield-To-Maturity’ is seen as the sum of (i) the interest rate by the risk-free 30 years Treasury Notes and (ii) the premium rate assigned to the corporate risk ?