Home » Excel Spreadsheet » Effective Duration, Convexity, and Convexity Adjustment For Loans

This is an update of a post and spreadsheet I wrote in Oct. of 2014 called: “Modified & Effective Duration, Plus Convexity of a Loan”. 

The previous post was hard-wired for a plus or minus 100 basis point change in yields. These new formulas and inputs allow for changes in yield other than 100 basis points:

First, some repetition of the previous post: The formula for Macaulay Duration of an amortizing loan I posted was “an effort to more accurately measure the term of a loan, than just average life. Macaulay duration of course has limited value for amortizing loans because, unless there are hefty prepayment penalties over the entire term of the loan, amortizing loans have embedded options that will change, given a change in yields and prepayments. For the same reason, average life has limited value for amortizing loans, as does half life.

Next we will calculate another duration of limited value with amortizing loans, Modified Duration. Modified Duration is not intended as a measurement of time, as is Macaulay Duration, but an approximation of  price volatility, given a plus or minus 100 basis point change in yields. See my post “Approximating Duration, Modified Duration, and Convexity with Option Free Bonds” for bonds.

The formula for Modified Duration:

Modified Duration =Macaulay Duration/(1+r/n)

Where: r = yield, n = number of payments per year.

Below is the pool structure now used in the updated spreadsheet:

Modified Duration for this structure would be:

5.404/(1+5%/12) = 5.382

Effective Duration is a more accurate measurement of price volatility for loans and MBS, particularly when combined with convexity and the convexity adjustment. As stated earlier, the previous post was hard-wired for plus or minus 100 basis points. These new formulas and inputs allows for changes in yield other than 100 basis points:

Effective Duration = (% price change when yields fall – % price change when yields rise)/2*100

Convexity =  ((price change when yields fall + % price change when yields rise)-2*Initial price)/(Initial price*change in yield^2))/100

Convexity Adjustments = 0.5*Convexity*100*(change in yield)^2

These will be clearer when you down load the spreadsheet.

There is also a table showing that the estimated percentage price change equals the actual price change, using the duration and the convexity adjustment:


There is also a chart showing the percentage change of prices at each change in yield, along with a polynomial curve to fit the points.:



3 thoughts on “Effective Duration, Convexity, and Convexity Adjustment For Loans

  1. Li says:

    Does professionals usually change CPR accordingly with different yields to calculate the duration and convexity ? Do you know which way is the standard industry practice? Thanks a lot for your excel models. I really learned a lot about fixed income modeling by studying your modules. I think they are better than most of the text books to learn about fixed income pricing in an early stage.

    1. Don Pistulka Don Pistulka says:

      Hi Peter,
      The change in CPR is arbitrary and up to the expectations of the user. In different markets those expectations will change. You should play with different inputs, remembering however that normally when rates rise, expected CPR would fall and when they fall, CPR will normally rise.

      Thanks for the feedback.

Leave a Reply

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