Home » Uncategorized » Defeasance 3.0

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

4 thoughts on “Defeasance 3.0

  1. Mac says:

    Hi there.

    I am a mortgage banker and have been looking for a good defeasance model to avoid doing it myself! Very helpful.

    Question for you. We a arrange primarily 10/25 or 10/30 deals (i.e. 10 year term deals with 25 or 30 year amortizations, with balloons in months 120).

    In cell D7 where it says “Remaining Balloon Payment (years)” do I enter the number of years UNTIL the balloon? In other words, If I am 90 months in to a 10/25 deal, would I enter =30/12 in that cell? I am just unclear on exactly what you mean by “remaining balloon payment.” I am interpreting that to mean, “months until balloon payment” but perhaps this is wrong.

    Thank you, sir!

    1. Don Pistulka Don Pistulka says:

      Mac,

      Thank you for the comment.
      Your input of 30/12 in the “Remaining Balloon Payment (Years)” is correct. With 30 months left until the 120 month balloon payment, the balloon is due in 2.5 years.

      Don

      1. Don Pistulka Don Pistulka says:

        Mac,
        I am curious as to which of the three sources for Treasury rates you are using. It looks like Yahoo has just given up on trying to update the rates.

        Don

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*