Home » Excel Spreadsheet » Sequential Pay CMO

A reader ask me if I had published a sequential pay CMO example. I had not. I did start a spreadsheet at one time, but never completed it. I worked on it over the weekend and came up with a simple, three tranche and residual sequential. I’m going to assume that the readers already knows what a collateralized mortgage obligation (CMO) is, so I won’t be describing it here.

The spreadsheet starts off with a mortgage amortization that represents a pool of mortgages. It is up to the user to describe the mortgage pool (yellow cells are inputs), including assumed prepayments (either CPR or PSA) and a default rate (CDR). I am assuming this pool is guaranteed by an agency of the government, so although the default rate may be used, the Loss Severity remains at zero. Note that the PSA number is not yellow. That is because the input cell is on the summary sheet, so the user can see what happens if the prepayment assumptions change.

Sequential tranches are, as the name implies, tranches that pay the holders of the first sequential tranche interest and principal, until the principal is paid off and then hands off the principal payments to the next tranche that up until that time was paying the holder only interest. When the principal on the second tranche is paid off, the principal payment starts on the third tranche. This goes on until the last sequential tranche. The residual tranche gets the interest from the sequential tranches, left over after the sequential tranches are given their market rates.

We need a yield curve to price each tranche. I used the Treasury curve below:

Then used the average life of each tranche to interpolate a Treasury yield for that average life:

 

The Summary sheet collects the data needed to price each tranche. Each one is priced using a spread over the Treasury yield. The pool itself was purchased at 1.25% over Treasuries, for a yield of 3.179% and a price of 102.395.

The pool is broken down into each tranche, depending on the percent of par value you allocate to each tranche. Note that the example break down is 40%, 35%, and 25% for each of the three tranches. Each tranche is priced with a coupon equal to the yield spread over Treasuries, at a price of par ($100). First, we will price all three tranches and the residual at the same yield the pool was purchased at, which was 3.179%. If each of the parts yield the same as the whole (pool), there should be no profit. All the way to the right your will see zero profit:

The intent is obviously to purchase the pool and sell the pieces for more than the cost of the pool. If the pieces could be sold at the yields below, the profit would be around $197K.

If you go to the sheet named “Tranche” you will see how each tranche and the residual works.

Download:     http://pistulka.com/Excel_Shared/CMO-Seq.xlsx

 

3 thoughts on “Sequential Pay CMO

  1. John Stone says:

    The “C” in CMO actually means “Collateralized.”

    Enjoy reading your post.

    Thanks

    1. Don Pistulka Don Pistulka says:

      Right you are. I will correct it. Thanks

Leave a Reply

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

*
*