I have posted previous calculations of effective duration and convexity for a pool of mortgage loans, however past spreadsheets required three amortization schedules, on separate sheets. Using the mega formula for pricing, this spreadsheet (in my opinion) is cleaner and easier to understand.
Effective duration is used to analyze mortgage pools, rather then Macaulay or Modified durations, due to the options embedded in mortgage loans. The borrower has the option to put the loan back to the lender. Typically, the duration calculation assumes a plus or minus 100 basis point change in interest rates. Any factor that will change the cash flows of a pool, when rates are changed, needs to be considered when calculating effective duration.
The factors that will affect the initial cash flows are entered as follows:
Now comes the factors that will change the cash flows of the pool when interest rates move plus or minus the amount entered above (100 basis points or 1.0%):
The assumptions of factors that will change, given the change in interest rates, are entered in yellow cells. These entries require either an historical bases, or the experienced users best guess as to how the factors might change.
Then the calculations of effective duration and convexity are made, along with the convexity adjustment. The adjustment is needed because of the non-liner relationship between prices and yields.
Lastly, I included a chart, showing the negative convexity of the mortgage pool. Negative meaning that when interest rates fall, prices will rise less than prices will fall, when rates rise the same amount.