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. Also 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, without imbedded options. 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, as it includes imbedded options. 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.: