Home » Excel Spreadsheet » 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.

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

OptionFree2

With these four variables we have enough information to calculate Macaulay duration, modified duration, and convexity.

OptionFree3

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.

OptionFree4

Download the spreadsheet “Option Free

 

 

3 thoughts on “Approximating Duration, Modified Duration, and Convexity with Option Free Bonds

  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 ?

    Many thanks for your sharing.

    1. Don Pistulka Don Pistulka says:

      Chuang,
      Thank you for your comments. You are correct about cell J6. It should be “Yield moves up”.
      I do sometimes use YTM, yield, and interest rates interchangeably in my posts. I can see where it could be confusing. When I say interest rates moving up or down 100 basis points, I am referring to a general change in rates. I then apply the general change in “interest rates” to a specific description of a security. At that point the term “interest rates” also becomes more specific and is then referred to as “Yield”, which is Yield-To-Maturity in this case. I hope that helps.

      Don

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*