a. Divide this security into 3 traches. The senior tranche should have 80% of the principal. The mezzanine tranche should have 15% of the principal, and the equity tranche should have 5% of the principal.

b. Assign an arbitrary rate of interest to the senior tranche. Calculate the monthly payment for this tranche based on the rate of interest. The excel function PMT will be useful here.

c. Your analysts have determined that in order to sell this debt, the riskiest tranche will need to be sold with a rate of interest that is twice as high as the middle tranche, which will need to be twice as high as the safest tranche. Based on your arbitrary assignment of an interest rate in part (b), and the knowledge from your analysts, assign interest rates to the other two tranches. (Hint: Use a formula here so that if you change the interest rate on the senior tranche, the others will automatically update).

d. Assign monthly payments to the other two tranches.

e. Determine whether the total payments for these tranches are more or less than the payments received from the mortgages. Modify the interest rate assigned in part (b) until these sums are comparable. Solver will be useful, here.

(Note that these are all 30-year loans, so the security you create will also have a duration of 30 years.)