1) Calculate the 3-year swap rate for 6-month LIBOR. 2) Below is a list of 100 m
ID: 2800860 • Letter: 1
Question
1) Calculate the 3-year swap rate for 6-month LIBOR.
2) Below is a list of 100 mortgage loans that are to be bundled and split into a mortgage-backed security. Note that these are all 10-year loans, so the security you create will also have a duration of 10 years.
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.
Duration USD LIBOR U.S. Treasuries overnight 1.19% 1.01% 1 week 1.21% 1.03% 2 weeks 1.28% 1.09% 1 month 1.36% 1.17% 2 months 1.43% 1.24% 3 months 1.48% 1.28% 6 months 1.66% 1.44% 12 months 1.94% 1.60% 18 months 2.10% 1.68% 24 months 2.28% 1.76% 30 months 2.54% 1.83% 36 months 2.70% 1.89% 42 months 2.88% 1.95% 48 months 2.95% 2.01% 54 months 3.00% 2.06% 60 months 3.05% 2.11%Explanation / Answer
Soln: 2) We have defined the securities in Excel and filter them from smallest to largest rate of interest.
Total principal of the securities = $38053900
Senior tranche must have 80% of the portfolio i.e. = $30443120
Mezanine tranche should have portfolio of 15% = 15%*38053900 = 5708085
Equity tranche = 5%*38053900 = $1902695
(b) we have assigned top 81 safest portfolio in senior tranche.
Lets assume that interest rate offered for senior tranche is 6% in that case using the PMT function, for senior tranche the monthly payment made = $337981
Using this we get the mezzanine tranche interest rate as 12% and equity tranche as 24%
On calculating the EMI for both we get total payment of EMI in 3 tranches = 337981 + 81894.44 (monthly payment to mezzanine tranche) + 41950.80 (monthly payment to equity tranche) = $461826.30 (approx.)
EMI payments received from all the loans = $423455.66
Hence, we cannot keep the interest rate as 6%
While on actually using hit and trial method, when the payments are equivalent to EMIs received the interest rate of senior tranche = 4.55%
Tranches Principal Interest rate EMI payments Senior 30443120 4.55 3,16,241.91 Mezzanine 5708085 9.1 72,616.89 Equity 1902695 18.2 34,529.11 Total 4,23,387.92Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.