Please display answer in the provided excel set up pictured below the question.

Question:

Answer Setup:

Consider a CMBS with the following attributes: Priority payments in the CMBS will be made to the class AAA tranche and will include the promised coupon and principal repayment on the loan's maturity date plus all recoveries from a foreclosure sale, if any. At the loan's maturity date once the class AAA tranche is repaid, then the class AA tranche will receive principal repayment plus recoveries, etc. The loan backing this CMBS is to a single borrower. The loan is to have a 10 -year (interest-only) term with a fixed interest rate. The loan will provide for scheduled amortization for its entire term. Assume prepayments to be zero. (h) What interest rate on the underlying loan is needed to generate an IRR on the residual tranche of 10% under the following assumptions: - The loan has a constant annual probability of default of 0.15%. - The loan has a recovery rate of 75%. - Tranche AAA is to be issued at par (1.e., Tranche AAA is to have a vield equal to its coupon). - Tranche AA is to be issued at par (i.e., Tranche AA is to have a yield equal to its coupon). - Tranche A is to be issued at par (1.e., Tranche A is to have a rield equal to its coupon). - Tranche B is to be issued at par (1.e., Tranche B is to have a pield equal to its coupon). (1) What interest rate on the underlying loan would completely wipe out the return to residual tranche. (The residual tranche would earn an IRR of 0% ). (j) Would you ever expect your answer in (a) to be lower than the interest rate on a commercial mortgage held by, say, life insurance companies in their direct mortgage loan programs? Why