Home » Excel Spreadsheet » Modified & Effective Duration, Plus Convexity of a Loan

The spreadsheet has been changed for this post. Please go to:

Effective Duration, Convexity, and Convexity Adjustment For Loans

My last post showed the formula to calculate a Macaulay Duration on an amortizing loan. The definition of a Macaulay Duration I used was “an effort to more accurately measure the term of an option-free fixed income security”. This of course limits the value of this duration with amortizing loans because, unless there are hefty prepayment penalties over the entire term of the loan, amortizing loans have embedded options. 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.

If there was a guarantee your amortizing loan would never prepay or you owned an option-free fixed income security, Modified Duration would give a close approximation of the price change at plus or minus 100 basis points. Modified Duration is just what it says, a modification of the Macaulay Duration:

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

Where: r = yield, n = number of payments per year, and “/100” to express it as a percent.

I used the same pool structure as assumed in the last post on Macaulay Duration.

Modified Duration = 5.509/(1+5%/12)/100  = 5.486

Now we come to a price volatility number that is useful for amortizing loans. The blue number below the Modified Duration is the Effective Duration. For this calculation we will need two more amortization sheets. If you are wondering why I am using extra sheets and not VBA to calculate these numbers, it is because I feel spreadsheets are easier to follow than VBA for most Excel users. I have UDFs and macros for all of the calculations, but to many readers, it might not be practical. I could be wrong. I frequently am.

Open sheet “Controls” in workbook “EffectiveDuration“. All three amortizations are linked to the Controls sheet.

The additional two amortization schedules are for yields moving up or down 100 basis points (1%). The base case is still a 5% market yield. The CPR change is the difference in determining  prepayment speed and cash flow. The CPRs I used are arbitrary,  but not unlikely. If rates dropped to 4% and the borrowers have 5% loans, it is not unrealistic that 20% of the mortgages would be prepaid each year. Likewise, If rates move up to 6%, there will likely be less prepayments, so I used a CPR of 5.

Effective Duration = (Change in price if rates move down – Change in price if rates move up)/2

5.258% =(3.53152%- -6.98523%)/2

Using an effective duration of 5.258% as an indication of price volatility does not tell the whole story, however. If you were to plot the price changes between plus and minus 100 basis points, incremented  by 1 basis point, you might get a red line, like the chart below. The red line looks convex( having an outline or surface curved like the exterior of a circle or sphere) . In this case It is referred to  as negative convexity. Convexity is a measurement of the curvature of a bonds price / yield relationship.  Having negative convexity, means when rates drop the price will rise less, than the price will fall when interest rates rise.

If you change the CPRs back to 10 for all three amortization schedules, you get a chart like below, which has a slight curve, but in the other direction, referred to as positive convexity. Having positive convexity, means when rates drop the price will rise more, than the price will fall when interest rates rise. 

Moving back to the original CPRs of 20, 10 and 5, we will use an approximation formula for convexity.

Approximation of Convexity:

=(Change in price if rates fall + Change in price if rates increase) *10000/Base price

-3.454=(3.53152%+-6.98523%)*10000/100

Now we can determine the change in price due to convexity:

Change due to convexity = .5*(convexity)*100*(change in yield)^2

-1.7269% = .5 * -3.454*100*(.01)∧2

Download workbook “EffectiveDuration” from:

http://www.pistulka.com/Excel_Shared/ 

Downloads Written in Excel 2013

3 thoughts on “Modified & Effective Duration, Plus Convexity of a Loan

  1. Eric says:

    I find your posts very helpful to read. I’m an equity research associate with Keefe, Bruyette, and Woods, and have used your work for some analysis I frequently do.

    I’d like to model the convexity (negative convexity, rather) for an MBS, given changes along the yield curve. For example, given the current environment, if I know that an MBS is trading above par and has negative convexity, how can I reflect that in your model? Going one step further, how can I model the sensitivity for a single bond, including adjusting the prepay speeds at different points in the life of the loan? I suppose I could hard-code different ‘base cases’ like you have in your model and build a curve out of it, but what if I wanted to run dozens of scenarios with different prepay speeds, market value of the bonds, etc?

    I really appreciate any help you are willing and able to give! Of course don’t hesitate to call or email me, and thank you again for the great material you provide.

    Eric Hagen
    443-224-1339

    1. Don Pistulka Don Pistulka says:

      Eric,
      If you want to start with a mortgage that is trading above par (on the Controls sheet) you would enter a yield in cell D11 that is lower than the APR rate you entered in cell D5. For example if you entered 4% in D11, the starting price goes to 105.777.

      The only reason that the change in yields is “hard wired” at plus or minus 100 basis points is because that is the standard change in yields used to calculate effective duration. You can change the yields on ether side of cell D11 if you need to.

      AS far as changing the prepay speeds at different points in the life of the loan, have you considered PSA? You could take the formulas in my spreadsheet call “PSA” and apply those formulas.

      As far as running dozen’s of scenarios, you could use VBA to make the changes.

      I hope that helps.

      Don

Leave a Reply

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

*
*