A newer version of this post and spreadsheet is available here:
A reader was asking about my original post on defeasance where I used the Treasury strip yields to discount a stream of loan payments to provide an estimate of the prepayment penalty on a loan that has a defeasance clause that requires the use of Treasury securities. Read the other post for more information. I used strips because they are spot rates and would be more accurate. The true defeasance penalty cannot be known until a broker or a firm that specializes in defeasance offers the exact amount of securities, including coupon payments, if any, that will be sufficient to match the amount of each loan payment. The cost of those Treasury securities, less the remaining principal of the loan is the prepayment penalty. The reader’s firm owns many commercial properties with loans that contained defeasance clauses. He was interested in finding a “back of the envelope” type of way to give management a rough idea of the penalty cost on potentially dozens of loans. I used the Yahoo Treasury market yields, even though they have only seven points on the yield curve, because they update the yields during the day and it is easy to automate. I interpolated the Treasury yield curve for each month. There can be many loans, each on their own sheet. The data required looks like this: In order to truly automate this workbook, this data should be linked to current monthly balances. The refresh button brings in the most current Yahoo data and even though each loan sheet has a refresh button, it realistically only has to be done once, when the workbook is first opened. As you see, it handles interest only as well as balloon payments.
On the right had side of the amortization schedule are the Treasury rates for each month which are used to discount each payment. The present values for all payments are then summed. The difference between the present value and the loan balance is the estimated penalty. I put this together in a few hours using other posted spreadsheets. It is not tested for all loan types, nor its it meant to. There could be errors. If you find any let me know. As usual, use at your own risk. There is no guarantee either stated or implied that this workbook represents the exact requirements for defeasance. It is only a very rough estimate.