Need help creating IO, PO, Senior and Sub Tranches for a mortgage pass through.

Hello! Attached is a document with details about mortgage pass throughs. Also attached is my current Excel spreadsheet for the assignment. The following is what I need help with: 2. Create a WAC IO and PO tranche to give a 5.75% deal coupon. Assume that these two classes are paid first (before subs and seniors – see below). The WAC PO gets the aggregate (initial PO%) × (total principal cash flow), the WAC IO the aggregate excess interest on outstanding, current premium loan balance. 3. With the remaining assets, create a senior/sub structure with 5% subordination, and a shifting interest structure as given on p S-61, under “shift percentage.” Take a percentage of, for example, 60% to mean that the sub receives only 60% of its prorated prepay principal (provided the sub still exists). The sub will be a single class (called “B”) that absorbs all realized losses first. Once the sub is exhausted, all seniors (see below) are allocated losses on a prorated basis. Assume interest is paid to the aggregate senior classes at 5.75% and any remaining interest is distributed to the sub. 4. Use the senior class to create a sequential pay structure of two classes, with principal A1:A2 :: 3:1. Prorate potential senior losses to A1 and A2 based on pre-default principal; assign each class its interest payment prorated on post-default beginning balance. 5. Assume all classes sold under the base PP/D assumption at: A-1: 102:01; A-2: 99:20; B: 75:04; IO: 0:20 (notional); PO: 81:31. The notation “:nn” means “nn/32nds.” 6. Assume a Treasury term structure given by z(T) = 0.03 × ln[0.038 × (T+40)] where T is measured in months.

Calculate Price

Price (USD)