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.
Prior to this post and spreadsheet, I had two other posts on defeasance. The other two are Defeasance and Defeasance 2.0. The first spreadsheet ( Defeasance) used the Treasury strip market. The idea was that strips were preferable to Treasury securities with coupons, because they represent spot rates, with no reinvestment risk. The problem with the Treasury strip market was that the rates had to be copied and pasted into the spreadsheet from the Wall Street Journal each time an estimated prepayment penalty was required.
The next spreadsheet (Defeasance 2.0) used the “on the run” Treasury market rates from Yahoo. The rates can be easily pulled into Excel as a data table and refreshed at any time. These rates where interpolated for each month from month one to month 360. This method seemed to give a reasonably good estimate of the cost of prepaying a loan. The problem is that Yahoo does not always update their yields, or the table is not available at all.
This spreadsheet (Defeasance 3.0) gives the user three sources for Treasury yields, used to calculate defeasance. They are The U.S. Treasury (yields from the previous day), Google Finance, and Yahoo Finance.
Go to the sheet “Pick A Source” and pick from yellow drop down cell.
Click the green refresh button, then go to a loan type sheet and enter loan data.
Download: Defeasance 3.0