Solve problem on a spreadsheet

using Financial Markets and Institutions Textbook 8th edition

5. Consider $200 million of 30 -year mortgages with a coupon of 10 percent paid quarterly. ( (\$) LG 254 ) a. What is the quarterty mortgage payment? b. What are the interest repayments over the fint year of life of the morteages? What are the principal repayments? c. Construct a 30-year CMO using this mortgage pool as collateral. There are three tranches (where A offers the least protection against prepayment and C offers the most). A $50 million tranche A makes quarterly payments of 9 pereent; a $100 million tranche B makes quarierly payments of 10 persent; and a $50 million tranche C makes quarterly payments of 11 percent. d. Assuming no amortization of principal and no prepayments, what are the total promised coupon payments to the three classes? What are the principal payments to each of the three classes for the first year? e. If, over the first year, the trustee receives quarterly prepayments of $10 million on the morteage pool. how are the funds distributed? I. Howv can the CMO issuer earn a positive spread on the CMO