A better post and spreadsheet on defeasance can be found here:
If you are involved in commercial lending or perhaps municipal bonds you are probably familiar with the term defeasance. Commercial loans normally have hefty prepayment penalties, while some loan documents prohibit prepayment altogether. Commercial loan defeasance is a collateral substitution that guarantees the lender the same cash flow as the loan, in return for prepaying the loan. That guarantee usually involves substituting government guaranteed Treasury obligations.
What I am trying to do in this spreadsheet is give the user an estimate of what it might cost to defease a loan, while at the same time make it easier to understand what is sometimes a difficult concept. This calculation, using only Treasury strips, is intended to show one possible way to estimate the call penalty of a defeasance. More sophisticated models with live quotes are capable of optimizing possible combinations of Treasury securities that may include strips, notes, and bonds. There are also third party fees to brokers, CPA’s, and servicers that are not included in these calculations.
I chose to us Treasury strips for my collateral Treasury rates, primarily because they are spot rates with no coupon payments. They also have a lot more points along the yield curve to interpolate the closest rate for each loan cash flow. I developed a model in Excel years ago that would take a list of Treasury notes and bonds from a broker and determine to the penny if they would satisfy the defeasance. The problem was that I had to start at the last payment and work my way to the first payment because the coupon payments might either partially fund or over fund some of the more current loan payments. Using Treasury strips I did not have to deal with that problem.
Treasury strips get their name from the process of stripping a Treasury note or bond of its coupons and selling them along with the last cash flow which includes the principal payment. When it is profitable to sell the pieces for more than the cost of the security, a broker will strip the security. When the pieces (coupons and principal) are worth less than the whole security a broker will purchase the coupons and principal and reconstitute the original security.
On workbook sheet “Strips” I copied/pasted the strips from a website for each maturity along with the closing yields. Principal strips are quoted separately from coupon strips and may sell at a premium to a coupon strip that matures on the same date. That is because coupon strips are fungible, while principal strips are not. If a broker wants to reconstitute a security, they must purchase back that original securities’ principal, but can use any securities’ coupons as long as they mature on the same date. From the standpoint of the borrower, we are only interested in the highest yield (lowest price) to satisfy the defeasance. I combined principal and coupon strips into a table (Table1) and used a formula to construct an actual date using a lookup table (Table2). I then sorted the table by maturity. Table1 had many duplicates with different yields, so I created a pivot table to eliminate the duplicates and because we want the highest yield for each date, adjusted the field settings for maximum.
In order to make it easier to work with than a pivot table, I constructed a new table by linking the data from the pivot table. I named the date range “Mydates”. Back on sheet “Com Loan”, I used an interpolation formula to get a strip rate that matched the each loan payment date. I them converted the semiannual compound Treasury rate to a monthly compound loan rate. I then calculated the present value of each loan cash flow, discounted by each monthly equivalent Treasury strip rate. The sum of the present values less the principal balance is the call penalty.
Example of interpolation formula: