Understand the expected losses from defaults in that MBS. For this assignment, you can use the same spreadsheet that you used for that previous homework assignment. Just add your new calculations directly into that spreadsheet. 1. Using the SDA, create three new columns in your model: a. CDR; b. MDR; and c. Default losses in total dollars. Your fund wants to make conservative assumptions. They advise you to assume that the prepayments column does not include involuntary prepayments. 2. So, you need to recalculate each months loan balance by subtracting the previous months default losses. Finally, your fund asks you to calculate the nominal spread that they will earn on this investment. 3. So, first, calculate the bond equivalent yield, and then use a comparable Treasury to calculate the nominal spread. 4. Would you recommend that the fund uses this spread or a different spread? What, if any, problems do you see with this metric?

\begin{tabular}{|rrl|} \hline Net Note Rate & Balance in Cohort & Premium or Discount? \\ 4.382% & 1,200,000.00 & Discount \\ 4.441% & 6,700,000.00 & Discount \\ 4.810% & 13,000,000.00 & Discount \\ 4.923% & 14,500,000.00 & Discount \\ 5.011% & 19,000,000.00 & Discount \\ 5.662% & 22,000,000.00 & Discount \\ 5.717% & 22,500,000.00 & Discount \\ 5.829% & 23,400,000.00 & Discount \\ 5.999% & 20,100,000.00 & Discount \\ 6.091% & 18,000,000.00 & Discount \\ 6.226% & 16,500,000.00 & Discount \\ 6.373% & 14,000,000.00 & Discount \\ 6.498% & 11,900,000.00 & Discount \\ 6.680% & 3,000,000.00 & Premium \\ 6.705% & 1,500,000.00 & Premium \\ \hline \end{tabular} Assumptions: Deal Coupon 6.50% Assumptions: \begin{tabular}{|lr|} \hline Pool Balance & 10,000,000 \\ Interest Rate & 12% \\ WAM (months) & 360 \\ Month 1 Prepayments & \\ \hline \end{tabular} \begin{tabular}{|lr|} \hline WAC Coupon & 5.708% \\ \hline Total Loan Balance & 207,300,000 \\ \hline \end{tabular} \begin{tabular}{|lr|} \hline Total Scheduled Payments & 102,861 \\ Scheduled Interest Payments & 100,000 \\ Scheduled Principal Payments & 2,861 \\ \hline \end{tabular} \begin{tabular}{|ll|} \hline SMM & 0 \\ CPR & 0 \\ \hline \end{tabular} \begin{tabular}{|cr|} \hline Month & CPR \\ \hline 1 & 0.020 \\ 2 & 0.024 \\ 3 & 0.028 \\ 4 & 0.033 \\ \hline \end{tabular} 0.140 150 PSA ma 110m Standard nofault ceumntinn (Sn). \begin{tabular}{|rrl|} \hline Net Note Rate & Balance in Cohort & Premium or Discount? \\ 4.382% & 1,200,000.00 & Discount \\ 4.441% & 6,700,000.00 & Discount \\ 4.810% & 13,000,000.00 & Discount \\ 4.923% & 14,500,000.00 & Discount \\ 5.011% & 19,000,000.00 & Discount \\ 5.662% & 22,000,000.00 & Discount \\ 5.717% & 22,500,000.00 & Discount \\ 5.829% & 23,400,000.00 & Discount \\ 5.999% & 20,100,000.00 & Discount \\ 6.091% & 18,000,000.00 & Discount \\ 6.226% & 16,500,000.00 & Discount \\ 6.373% & 14,000,000.00 & Discount \\ 6.498% & 11,900,000.00 & Discount \\ 6.680% & 3,000,000.00 & Premium \\ 6.705% & 1,500,000.00 & Premium \\ \hline \end{tabular} Assumptions: Deal Coupon 6.50% Assumptions: \begin{tabular}{|lr|} \hline Pool Balance & 10,000,000 \\ Interest Rate & 12% \\ WAM (months) & 360 \\ Month 1 Prepayments & \\ \hline \end{tabular} \begin{tabular}{|lr|} \hline WAC Coupon & 5.708% \\ \hline Total Loan Balance & 207,300,000 \\ \hline \end{tabular} \begin{tabular}{|lr|} \hline Total Scheduled Payments & 102,861 \\ Scheduled Interest Payments & 100,000 \\ Scheduled Principal Payments & 2,861 \\ \hline \end{tabular} \begin{tabular}{|ll|} \hline SMM & 0 \\ CPR & 0 \\ \hline \end{tabular} \begin{tabular}{|cr|} \hline Month & CPR \\ \hline 1 & 0.020 \\ 2 & 0.024 \\ 3 & 0.028 \\ 4 & 0.033 \\ \hline \end{tabular} 0.140 150 PSA ma 110m Standard nofault ceumntinn (Sn)