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:

- The percent of the pool that will make up the floater (example is 75%)
- The floater’s floor is (.25% in the example, the inverse-floater is set a zero)
- The current index (1-month LIBOR) initial rate (2.50% in the example)

**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%**

Pingback: CMO – PAC | Excel@CFO

ryani 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?

Don PistulkaPost authorRyan,

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.

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

Don PistulkaPost authorNikhil,

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.