Excel@CFO

A reader asked if I had an example of a CMO Inverse IO, and I did not. Although I have not had access to a Bloomberg to check my assumption, I thought I would give it a try. Actual IO’s may vary from my example, but I think I have captured the essences of an IO CMO.  The assumptions I used are as follows:

1. My example is made up of two tranches, a Floater and an Inverse IO.
2. The Floater retains all the principal payments.
3. The Floater rate equals 1 month LIBOR, plus the margin.
4. The IO rate equals the APR collateral rate, minus the Floater margin and minus 1 month LIBOR
5. Assumptions 3 and 4 guarantee the sum of both tranches rates are equal to the collateral rate.
6. The IO rate =MAX(0, IO Index-LIBOR)
7. The Floater rate =MIN(Collateral Rate, LIBOR + Floater Margin)
8. A LIBOR based interest payment is based on an actual/360 basis. For simplicity, interest for this example is assumed to be paid the same as the underlying MBS, 30/360.
9. Only the yellow cells are variables.

Download “CMO-InverseIO”