The spreadsheet has been changed for this post. Please go to:
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
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:
Downloads Written in Excel 2013