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 Deluxe's 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? Create 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.

Coupon Rate Art. Issued Weight WAC Assets Liah. mortgages #VALUE! The Class A bonds ?% #VALUE! The Class B bonds ? % #VALUE! The Class C bonds ?% #VALUE! The Class Z bonds #VALUE! Bonds Total 0.000 #VALUE! The overcollateralization of equity Assets Total #VALUE! The total debtet worth #VALUE! Pool of Mortgages The Interest Rate The Term . The Prepayment RatePrincipal Payments Int Ending Bal Period Beginning Balance Principal and Interest Payments Into Pool Pre Payments #VALUE! #VALUE #VALUE! #VALUE! #VALUE #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE #VALUE #VALUE! #VALUE #VALUE! #VALUE! #VALUE! #VALUE! #VALUE #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! Iraunche A interest rate ?% Ending Bal CF Int Princ Period Beginning Balance #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE #VALUE! #VALUE! #VALUE! W N #VALUE #VALUE! #VALUE #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE #VALUE! #VALUE! #VALUE! #VALUE #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE #VALUE NPV while at ?% #VALUE! Iraunche B interest rate 7 % Princ Ending Bal CF Beginning Balance Int Period #VALUE! O #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE #VALUE #VALUE! #VALUE! #VALUE #VALUE #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE #VALUE #VALUE! #VALUE #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE #VALUE! NPV while at ?% #VALUEIraunche C interest rate Princ CF Beginning Balance Ending Bal Period Int #VALUE! 0 ? #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! A W N #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! NPV while at ?% #VALUE! Traunche Z interest rate ? % Princ CF Year Beginning Balance Int Ending Bal #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! AWN - #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! NPV while at ?% #VALUE! The Residual Period Principal and Interest Cash flows to Traunch Resid Payments into the Pool #VALUE! o $ W N NPV while at ?% #VALUE!(WAC) Z WAC Traunche A B C Tot. 7% 0.00% coupon 0.00% 0.00% Ending Period/Year Balance #VALUE! O #VALUE! 1 #VALUE! 2 #VALUE! 3 #VALUE! #VALUE! un #VALUE! #VALUE! #VALUE! 8 #VALUE! 9 - #VALUE! #VALUE! #VALUE! 10 #VALUE! #VALUE