The reason this is version 4.0 is that prior to this post and spreadsheet, I had three other posts on defeasance. With the addition of using Treasury spot rates, rather than posted yields on current coupons or Treasury strips, I hope to increase the accuracy of the calculations.
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 method 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. This spreadsheet only calculates the penalty, not any other expenses involved with defeasance.
Copy/pasted from the Treasury site by clicking the button:
After the spot rates are calculated out every six months for 30 years, the rates are converted from semiannual to monthly compound equivalents:
In the “Loan Amortization” sheet, the loan information goes into the yellow cells:
The present value of each loan payment is calculated using the spot Treasury rates at each payment. The sum of the present values is subtracted from the loan balance. The difference is the prepayment penalty. In certain conditions, where interest rates have risen substantially since the loan was originated, the borrower may receive a premium to defease the loan. Of course the premium would have to be sufficient enough to pay for the other expenses involved in defeasance, which can be substantial.