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.

Download: http://pistulka.com/Excel_Shared/defeasance_4.0.xlsx

]]>Like my other spreadsheets that require spot rates, I start with a source for current Treasury yields. You can copy/paste them directly into the spot rate sheet in the yellow cells:

Next, because we don’t have a yield at every coupon payment, we need to interpolate a yield at each of these periods. This is accomplished through a linear interpolation. This linear interpolation looks for periods that we do have a yield and the next period that we have a yield and interpolates between the two. The formula below is entered next to the first 6 month yield and copied down:

=FORECAST(F10,OFFSET($C$7,MATCH(F10,$C$7:$C$18,1)-1,1,2,1),OFFSET($C$7,MATCH(F10,$C$7:$C$18,1)-1,0,2,1))

This method differs from a linear interpolation that calculates a straight line through the data points. The chart below shows the difference. Note how the above method follows the actual yields:

Now that we have a yield at each coupon payment (every six months) we can calculate the spot rates. Spot rates are considered zero-coupon bonds. The spot rate calculations assume the given yields represent par bonds. A discount factor is calculated for each coupon payment, that is actually a present value calculation. At each coupon payment all the previous discount factors are summed and multiplied by half the yield (which is also a coupon payment). We can now calculate the present value of all the previous coupon payments. The last cash flow is the 100 par value (or corpus) plus the last coupon payment. Since we now have the present value of the coupon payments and the future value of the last payment, we can calculate the semiannual return or spot rate. Below shows an example of the calculation of spot rates:

Next we consider the math behind forward yields. Implicit in every spot rate curve is a forecast of future yields. Assume that we want to know what the current yield curve is implying about the two year Treasury yield, five years from now.

The calculations for forward yields are actually break even calculations, or what Bloomberg used to call “gap rates”.

If investors are willing to pay 2.4393% for a 5-year note, and forego earning a higher yield of 2.5463% on a seven year note, the five year investors are implying that when the five year note matures, there is a two year yield that the funds can be reinvested at which will equal the total return of the seven year yield. The calculations for that two year yield are shown above and result in a forecast of 2.814% for the two year note, five years from now.

Now we convert the above formulas into a yield curve table:

The columns, going down from the six month to the 30-year represents a yield curve in the future. The column with the one in the dark blue cell, is the forward curve, one year from now. Likewise, the column with a five in the dark blue cell, represents the yield curve five years from now. Each of the years in the dark blue cells are dropdown boxes that allow for any year you wish to view.

The rows on the other hand, represent the forward yield for that particular term. The one year row shows how the one year yield will change at each year going out into the future.

Next to the table are two yellow input cells that allow you to find an implied future yield at some year in the future.

In the chart sheet, you can compare any future yield curve, with the current spot rate curve:

Before you download a spreadsheet, note that there are two spreadsheet versions. One has a format extension of .xlsm. That is because there is a very small VBA program included that formats the Y axis of one of the charts and makes the chart easier to read. Some firms will not allow downloads with VBA included. If you find this to me a problem, you can download a second version with no VBA and adjust the chart manually.

Downloads:

]]>

This post starts by entering the U.S Treasury Yield curve by copy/pasting the rates from the Treasury web site.

The yields are then interpolated in order to calculate spot rates using the bootstrap method.

The spot rates are then linked into sheet “NSS”, and are used to smooth the yield curve using the Nelson-Siegel-Svensson Model.

Excel’s Solver is used to calibrate the model:

Download: NSS: http://pistulka.com/Excel_Shared/NSS.xlsx

]]>This post takes the previous post one step further and uses the spot rates to calculate the forward curve consisting of the implied forward monthly rates from one month out to 30 years.

Download “Forward_Price”

I used a smoothing method for the forward rates as I explain below:

I could have use a smoothing method on the initial Treasury yield curve using something like a Piecewise Cubic Spine method. This might have helped with the exaggerated forward curve. Adding to the exaggerated forward curve was the fact that I used the current Treasury curve which is relatively flat and contains unusual patterns. For example, the three year security was one basis point lower than both the two and five year securities. Also, the two month bill was higher than both the one month and three month bills.

Below shows the beginning portion of the amortization sheet that calculates price given the forward one month curve.

Download “Forward_Price”

]]>Then, using the bootstrapping method, calculates the spot rates:

For more on the bootstrap method for calculating spot rates, see my post “A Fourth Way To Bootstrap Spot Rates.

The inputs for the Treasury yield curve looks like this:

The calculations run down for 360 months, but the first 12 months look like this:

A chart showing the Treasury Curve and the Treasury Spot Curve is included:

On the Amortization sheet, you have the traditional pricing method, which those that follow my posts will recognize. The input data looks like this:

This is followed by the traditional pricing method, based on one discount rate of 3.75%:

For the Yield spread to the Treasury spot rates, you need to enter the spread in the yellow cell below (default 1.000%):

Download: Z_Curve_Price

Download Excel File: http://pistulka.com/Excel_Shared/FHLB_spot_rates.xlsx

I used the formula below to interpolate the rates, and copied it down. The x in the formula is the yield curve range in the market reference curve.

=FORECAST(G8,OFFSET($C$7,MATCH(G8,x,1)-1,1,2,1),OFFSET($C$7,MATCH(G8,x,1)-1,0,2,1))

X in the formula is the market reference curve

The FHLB may use a different method for interpolation and may offer loans between the dates on their market reference curve at different rates, but for purposes of this post the, above formula was used.

Treasury yield curve I have attempted.

This is also the first spot rate post with monthly payments, rather than the Treasury’s constant semiannual payments and a corpus.

Each month of the market reference curve was converted to years.

I used the first and forth methods that I listed in my Treasury spot rate post, to calculate the spot rates. The cash flows are calculated the same way as a loan, using the pmt() Excel function. The two methods I used are both bootstrap methods. Sheet “Method 1” calculates a cumulative discount factor each month, that is used to calculate the present value of all previous cash flows to that point. That present value is then multiplied by the interpreted yield at that maturity. Sheet “Method 2”

]]>

Download Birthdays.xlsx

]]>- The end of the pay period (there was a five day delay between the end of the pay period and the day we were paid).
- The dates I was paid.
- The current day.

The first calendar is a yearly calendar. The whole year of payroll periods are set out on one Excel sheet, using conditional formatting. The only two pieces of information needed are any end of pay period date, and a pay day date. The calendar is perpetual after the two dates are entered. At the time of this post we are in 2019, however you will notice that I entered two dates in 2010 to start the calendar. On each first day of the year, the calendar will recalculate the new dates for that year. I some cases today’s date will be the same as a pay or period date, and today’s date overrides the other.

On the second sheet is a monthly calendar that uses the dates entered on the yearly calendar and shows the current month. Also, I have includes the holidays my firm observed and the holidays appear on the calendar. Like the yearly calendar, it is perpetual. The only changes you might make are to add or subtract holidays in the table. Note that each holiday is a formula, so that the holidays are perpetual also.

Download Payday.xlsx

]]>The assumption for this model is that all CDs maturing in the next 12 months will roll over into the same term. The interest rate (reinvestment rate) for these CDs will is determined by a lookup table of interest rates. The rates in the table are the organizations forecast of interest rates on CDs over the next 12 months.

The model will then take all CDs that mature in the next 12 months and do a lookup from this interest rate table. For example, a 24 month CD that mature in March will be reinvested at 2.35% for the remaining time of the budget period. CDs that do not mature over the next 12 months are also calculated, so interest rate expense for all CDs is given.

The example below shows the value of each CD that matures during the next 12 months, including interest. Then that balance is reinvested until the end of the period at the new reinvestment rate:

Because the model is limited to one rollover during the budget year, 3-month and some 6-month CDs will mature and be reinvested at a new rate once, and will assume to earn that rate until the remainder of the year.

Download: cds.xls

]]>I have not used the linked spreadsheet in any real-world applications, so there may be errors I am not aware of.

For a mortgage or commercial loan, this would normally mean modifying the original loan. Amoung other things, the modifications could be:

Skip one or more payments

Change the interest rate for one or more payments

Pay interest only for one or more payments

Increase the number of payments

Require a forbearance amount be paid

TRD modifications can produce a loss of income for the lender. One way to recognize the potential losses (impairment), is to calculate the present value of the differences in cash flows between the modified loan, and the original loan. The discount rate used could be the rate on the original loan, or the prevailing market rate.

First an amortization schedule of the original loan is produced using the inputs in the yellow cells:

Next, the inputs in yellow are used to create another amortization schedule along with the modifications. Again, only the yellow cells are inputs. The modified loan can use any number of months to calculate the payments on the modified loan, but defaults to the months remaining in the original loan. To use a different number of months for this calculation, enter the months in the yellow cell to the right. Next, if the lender agrees to allow interest only payments to the first few months, enter the number of months in the “Interest Only Months” yellow cell. The “New Term Months” are not an input, but is the sum of the terms in the “Modified Rates” table. Hopefully, the rest of the inputs are self-explanatory:

P.S. The light colored letters next to some cells are the named values used in the calculations.

The two amortizations and the differences in cash flows, are represented on the “Present Value” sheet:

Download: TDR

]]>The workbook contains two sheets. One is a sample of my model and the other the BMA model. I have set the BMA model to P & I advanced. In other words, there is a guarantee of principal and interest to the buyer.

Both models use the same inputs as below. The calculations below are for the first month’s payment only.

As I pointed out in my last post, my model uses a sequence of principle paydowns starting with the expected amortization, then prepayments, then defaults and loss severity. Each amortization paydown is dependent upon the calculation of the amortization that precedes it.

The BMA model is also sequential. In this model, defaults are calculated first. Actual amortization needs defaults before it can be calculated. Prepayments need the expected amortization derived from the amortization factors. The calculations from the amortization factors mimic my calculations of needing the actual amortization first. Note that prepayments for the first month are the same for both models (in blue).

Both are sequential calculations but in a different order:

The purist that argues that defaults should come before expected amortization and prepayments, should also insist that prepayments come before expected amortization, which the BMA model does not. Prepayments in the BMA model are calculated after expected amortization, as does my model. In fact, if you set default to zero in both models, they amortize exactly the same.

Default models are primarily used to predict credit risk. They are only as good as the users inputs. Although my model does not conform to the industry standard model, I continue to feel my model can be a useful tool for users that are not required to use an industry standard.

Download:BMA

]]>I felt obligated to defend the model I use. Even though the model I use does not conform to the model adopted by the Bond Market Association (BMA), it does not mean that the BMA’s model is the only way CDR can be applied. The key to the model I use lies in the use of sequential principal balances (see the example below).

In my model, for each month, it is assumed that all the expected principal is paid by the borrowers by the end of the grace period (let’s assume the 15th of each month). That produces a principal subtotal that the percent of prepayments (CPR) are than applied. After the prepayments have been applied, that produces another principal subtotal.

A graphic depiction of the process is on the attached Excel workbook, sheet “Default Model” and below on this page.

During the month (I used the second half of each month in my example to clarify the math, but it can be anytime during the month) the servicer is notified that a certain percent of loans will be in default next month. The servicer reduces that percent from the principal subtotal balance this month. The new ending principal balance for this month, is also the starting principal balance for the next month.

Both models assume the same definition for loan defaults and prepayments (CPR, CDR, PSA and SDA). The BMA’s model assumes the percent is applied to the month’s beginning value, where the model I use assumes the default percentage is applied the principal balance after expected and prepaid principal.

Models are comprised of various assumptions. There are numerous models for predicting defaults. Neither of these two models here have any chance of being a precise predictor of future events. That is why they are called models.

Originators of MBS would need to use BMA’s model to be in compliance with industry standards. The typical reader of this blog, however, would most likely be on the buy side of MBS with guaranteed P&I and not be concerned with defaults. Defaults do increase the prepayments to the owners of guaranteed MBS but increasing the CPR can compensate for that.

Firms that buy and sell whole loan pools between each other, such as banks, credit unions, and underwriters will continue to find my default model a creditable measurement of credit risk.

I have left out guarantee and servicing fees to make the data simpler.

Download: CDR_Model

]]>

https://www.mtgprofessor.com/A%20-%20Amortization/how_does_simple_interest_work.htm

Also, for an explanation of the difference between simple and compound interest, again the “Mortgage Professor” gives a good explanation:

https://www.mtgprofessor.com/A%20-%20Simple%20Interest%20Mortgages/simple_interest_nomenclature.htm

The Excel spreadsheet starts with the input cells in yellow.

I used the standard payment formula in Excel, =PMT(). This function works fine for standard mortgage loans, but for simple interest loans, the actual last payment will most likely differ from the expected final term.

I give the above warning on the spreadsheet, however, although the typical commercial loan will be amortized over a longer period of time (i.e. 30 years), they usually have a balloon payment in 3, 5, 10, etc. years. Therefore, the final payment after, for example 30 years, will never be reached.

I break out the payments into the total term and the balloon term. In the default example, the balloon is due in 10 years (120 months).

Download: SICLA

P.S. Thanks to the people at , where I could check my numbers.

]]>- Cash & Duration Neutral Butterfly (aka Duration-Hedged Barbell)
- Fifty-Fifty Butterfly Swap
- Regression Weighted Butterfly Swap
- Maturity Weighted Butterfly Swap

Note: Although this type of swap shows positive convexity at changes of + or – 25 basis points, it does not necessary display positive convexity for smaller yield changes.

The formula for the Wings of the maturity weighted butterfly swap is the same as the regression weighted butterfly swap. The difference is that instead of using historic spreads to create a regression coefficient, the maturity coefficient is:

C is plugged into the formula for the weighting of the Wings:

Download: Butterfly-W.xlsx

]]>