Longer exercise 1: CMO Excel spreadsheet [For this question either create your own spreadsheet or use the one provided in Session 4 (Z-class cash flow model)]. Consider an agency CMO with the following characteristics: Mortgage pool The mortgage collateral consists of $275 million of 30 year FRMs with a weighted average coupon of 4.2%. Assume that prepayment on the pool is consistent with a 150 PSA assumption. The servicing and guarantee fee on this pool is 70 basis points. Mortgage payments are monthly, as usual. CMO structure The pool consists of three classes, an A class of $100m, a B class of $125m, and a Z class of $50m. All three classes pay a 3.5% net coupon rate. The B class is further split into an IO tranche and a PO tranche. (Thus, there are four securities, an A class security, a B class IO security, a B class PO security, and a Z bond). Given this information, answer the following questions: 1. Build a spreadsheet that builds out the total cashflows generated by this pool over its life. Your spreadsheet should show the cashflows that accrue to investors, and the cashflows paid as servicing and guarantee fees. 2. Based on the structure shown above, calculate how the cashflows of the pool are split between the different types of securities (similar to the Excel spreadsheet example posted on NYU Classes). [Hint: To make sure you haven't made any calculation errors, make sure that the principal payments to each of the securities adds up to the total principal (i.e. scheduled principal + prepayment) generated by the underlying mortgage collateral. The same should be true of the interest payments. i.e. everything should add up]. 3. Assume that the relevant discount rate for valuing these cashflows is 3.25%. What is the NPV of each tranche? [Hint: As above, if you wanted to check, the NPV of each tranche, plus the NPV of the servicing and guarantee fees, should add up to the NPV of the cash flows from the underlying mortgages]. 4. Change the prepayment assumption to 250 PSA. What happens to the NPV of each tranche (including the NPV of the servicing and guarantee fee strip)? Explain why the NPV of each tranche changes as it does.