Please display answer in the given excel setup.

Question:

The Remington Corporation is issuing a CMO with three tranches. The A tranche will consist of $1,000 million bonds with a coupon rate of 4.5 percent. The B tranche will consist of $200 million bonds with a coupon rate of 5.5 percent. The Z tranche will consist of $200 million bonds with an interest rate of 5.0 percent. The Z tranche will be a Z-bond accrual tranche. The issue will be overcollateralized by $50 million. The issuer will receive all net cash flows after priority payments are made to each class of securities.

The characteristics of the $1,450 million mortgage loans underlying the MBS (as of the date of issuance) are as follows: The mortgages were originated at a fixed rate of interest of 6 percent with a maturity of 10 years (annual payments).

1. For all ten years, show the:
   1. Cash distributions to holders of the A, B, and Z tranches
   2. End-of-year amount owed to security holders
   3. Assume the mortgages in the underlying pool prepay at a rate of 5 percent per year (assume all prepayments are based on the pool balance at the end of the year).
   4. If class A, B, and Z investors demand a 4 percent, 5 percent, and 4.5 percent yield to maturity, respectively, what will be the price of each security?
   5. Determine the profit/loss made by issuer in question (c) by buying the MBS at a market price of $1,450 million in the secondary market and simultaneously selling the CMO.
   6. What are the residual cash flows to Deluxe Capital Corporation? What would be the price of Deluxes residual interest right now if the interest rate demanded by equity investors is a 10 percent yield to maturity?
   7. What would happen to the yield to maturity on the A Tranche if prepayment rates were 10 percent, 15 percent, or 20 percent? Do an IRR analysis assuming prepayment rates increase from 10 percent, to 15 percent, and to 20 percent. What does this tell you about the relative importance of prepayments? Assume a market price of $1,010 million.
   8. Explain how the process of issuing a CMO creates value.

Answer Format:

Irounche C interest rate ?% Iraunche Z ?% Period The Residual interest rate ?% Beginning Bulance Int ? \#VALUE! \#VALUE! \#VALUE! \#VALUE! \#VALUE! \#UALUE! \#UALUE! \#UALUE! \#UALUE! Principal and Interest Cash flowt to Traunche Resid Payments into the Pool 0 1 2 3 4 5 6 7 8 9 10 \#UALUE! $ $ $ $ $ $ $ $ $ $ NPV while at ?\% (WAC)