A CMO is being issued with $3$ investor tranches and a residual class. The A tranche will consist of $$150$MM of principal and have a coupon of $6\%.$ The B tranche will consist of $$50$MM of principal and have a coupon of $6\mathrm{.}5\%.$ The Z tranche $($accrual tranche$)$ will consist of $$50$MM of principal and have a coupon of $7\%.$ The issue will be overcollateralized by $$20$MM 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 $8\%$ 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 $7\%$ 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?

Please provide excel file shwoing all steps and answers.