can you please do it by excel.I give you a thumb up.

- A CMO is being issued with 3 investor tranches and a residual class. The A tranche will consist of $50MM of principal and have a coupon of 4%. The B tranche will consist of $25MM of principal and have a coupon of 5%. The Z tranche (accrual tranche) will consist of $20MM of principal and have a coupon of 6%. The issue will be overcollateralized by $5M and the issuer (residual) will receive cash flows only after all classes of investors have been paid in a specific period. - The mortgages backing the security issued are FRM at 7% with 10 year maturities and annual payments. - Priority payments will be made to the A tranche and will include the promised coupon. Interest will be accrued to the Z tranche until A is completely repaid. The B class will receive interest payments only until the A class is repaid. The A class will then receive all remaining CFs from the pool that year. After A is repaid, B will receive priority principal payments. The Z class will accrue interest at 6% until both A and B are repaid. It will receive current interest and principal payments at that time. The residual class will paid the remaining cash flow at the end of each year. - Assume there is no prepayment and no servicing fees. - A) Given the information presented above, what is the total cash flow to each class of investors in year 2 (i.e. How much will class A receive, how much will Class B receive, how much will class Z receive, and how much will the residual class receive)? - B) What is the balance of the Class A tranche at the end of year 4? - C) What is the balance of the Class B tranche at the end of year 5 ? - D) What is the balance of the Class Z tranche at the end of year 7 ? - E) What is the IRR on the residual class