Home » Excel Spreadsheet » Floater & Inverse-Floater CMO

This is a follow-up to my last post, Sequential Pay CMO. The purpose of this example spreadsheet is to show a simple example of how floating and inverse-floating rate securities can be produced from a fixed rate pool of mortgages. It is not the only way floating rate CMOs can be structured, but for example purposes, the simplest.

We start (sheet “Pool”) with a $10 million pool of fixed rate 30-year mortgages, packaged and guaranteed by a government agency. The WAC (weighted average coupon) is 6.50% with a 50 basis point servicing / guarantee fee, for a net coupon of 6.00%.  The initial assumed prepayment assumption is 100 PSA. This can be changed later.

Both the floater and inverse-floater will have a pro rata collateral distribution. In other words, both will receive principal payments as a percent of the established distribution. The example distribution on sheet “Summary” is 75% for the floater and 25% for the inverse-floater. You only need to enter three yellow input cells to create both the floater and inverse-floater initial stucture:

  1. The percent of the pool that will make up the floater (example is 75%)
  2. The floater’s floor is (.25% in the example, the inverse-floater is set a zero)
  3.  The current index (1-month LIBOR) initial rate (2.50% in the example)
Obviously, since there are only two tranches, the inverse-floater has to have a distribution of the remainder of the pool, or 25%.
Floater’s Cap:
Net Collateral Coupon / Percent of Floater’s Pool
6.00% / 75%
8.00%
 Floater’s Initial Coupon:
Initial Index + Floor
2.50% +.25%
2.75%
Inverse-Floater Cap:
Floater % of Pool / Inverse-Floater % of Pool * (Floater Cap – Floater Floor)
75% / 25% * (8.0% -.25%)
3 * 7.75%
23.25%
Inverse-Floater Initial Coupon:
Inverse-Floater’s Cap – Floater % of Pool / Inverse-Floater % of Pool * Initial Index 
 23.25% – 75% / 25% * 2.5%
23.25% – 3 * 2.5%
15.75%
 The coupons for both tranche are assumed to reset each month, based on the index. You can enter a monthly change in 1-month LIBOR, to view how potentially volatile the tranches can be. Below I assumed a 25 bases point increase each month. This may seem a steep increase, but historically fast changes in overnight rates are not that unusual.
 
Under this assumption, the floater will cap out and the inverse-floater will hit it’s floor in month 22. If 1-month LIBOR stayed at or above 7.75% after month 22, the chart below would reflect the coupons on each tranche:
You can test your own assumptions with the monthly change in the index.

5 thoughts on “Floater & Inverse-Floater CMO

  1. ryan says:

    i think you are pretty fantastic. Im a CMO trader and trying to learn to structure in my free time. Your inverse floater lesson was fantastic. Are there other lessons like this for 2 tier index bonds or inverse IO’s. Have to say, I have 2 analysts and I don’t think they have as much talent as you do in your fingertips. You’ve reinvigorated my thirst for learning. thanks!

    P.S. are there some passwords needed for some of these lessons/excel?

    1. Don Pistulka Don Pistulka says:

      Ryan,
      Thank you for the kind words.
      I was on the buy side for most of my career, with the exception trading Treasury notes and bills for a bank for a few years. Like yourself, I learned about structured notes in my spare time. As a buyer, I stayed always from CMO’s. I had more confidence in buying and selling MBS. I think I did do some playing around with inverse IO’s, but I will have to look back at some of my old Excel workbooks.

      I will send you the password for most of the Excel workbooks on my blog that use VBA.

      1. Nikhil K says:

        Hi Don – Enjoyed reading your post as well. Do you mind spending your Inverse IO spreadsheet to me as well ? Thanks.

        1. Don Pistulka Don Pistulka says:

          Nikhil,
          I don’t have an “inverse IO” spreadsheet. Possibly my spreadsheet breaking down a pool into Interest Only, Principal Only, and Servicing may help.
          I will e-mail it to you.

Leave a Reply

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

*
*