Consider a 100,000,000 CMBS pass-through (PT) security consisting of fresh 15 year fixed rate loans that fully amortize over a period of 30 years, and have a WAC of 7% with fees amounting to 0.5%. For purposes of this HW, a CMBS is like a RMBS, except that all loans have a prepayment lock-out (assume for the entire term), but default occurs according to the standard SDA function. Additionally, since the loans do not fully amortize, there is generally a balloon loss - quoted as a fraction of the outstanding balance at mortgage maturity. To simplify, assume that there is no recovery (unrealistic, of course). Assume that the CMBS is sold at par (again, unrealistic).

[10 pts.] Assume there are no defaults. Compute the PT cash flows and show that the cash flow yield is 6.5% (MEY), the McCauley duration is 8.8 years, and the average life is 13.4 years (annotate your formulae). Hint: 1. To make sure you get it right, you may want to start out pretending that the loan has a term of 30 years to check that the ending balance is zero. 2. To compute the CF yield, use the function IRR and make sure that you (a) include the t=0 CF and (b) use, say, 0.05/12 as the initial guess and then multiply the answer by 12 to get the MEY.

[20 pts.] Now introduce credit risk by allowing for loans to default at the PSAs SDA CDR, and allow for balloon risk. The current (corresponding maturity) Treasury yield is 5%. Create a table and graph of the spread (in bps) of cash flow yield to Treasury versus SDA, for 0% and 10% balloon loss. In the spreadsheet that you hand-in, show the situation for 100 SDA and 10% balloon loss. Use par pricing. See notes below for further assumptions and hints.

[2 pts.] Why is there generally a large fraction of outstanding principal that defaults at maturity?

Notes:

Note that there is no messy tranching going on here.

Allow for a general SDA; see the graph and spreadsheet in your lecture notes.

Here is one way to get the CDR (as there are better versions, feel free to use your own, extra points if you can show me that the formula below is wrong); A13 refers to the month and SDA to the SDA factor: =(IF(A13<=30,A13*0.006/30,0)+IF(AND(A13>30,A13<=60),0.006,0)+IF(AND(A13>60,A13<=120),0.006-(0.006-0.0003)/(120-60)*(A13-60),0)+IF(A13>120,0.0003,0))*SDA/100

Assume that loss recovery is zero any loan that defaults is a completely written down.

Defaults work much like prepayment, with one exception: the monthly default happens just before the payment (why pay interest and principal if you are defaulting anyway). So take default = beginning balance MDR and then apply the appropriate pool survival factor to get the realized mortgage payment.

Make sure you create a column of the PT cash flows, including the initial investment at par, so that you can calculate the CF yield.

Balloon losses are quoted as a fraction of the final outstanding principal.

In your graph, show a range of SDAs of 0 to 1,000. You will find that you do not have to compute very many values in that range.

Display all cash flows to the nearest dollar (use format cell).