Hello,

I attached pictures from the Excel file which is including the question.

Please find the solution and provide it to me with explanation. I prefer to be in Excel sheet.

The answers will be in the question mark blank.

Thank you.

L In J5 In I: |3 In i515 |' | l\" i'\" lm i\" I'\" I\"|"' #BiCIDIEiFIGIHIIIllKll-iMLNIDIPIQiRisiTI Individual Assignment #3 A CMBS deai is issued by Bank of America. It has three loans in the pool: Dale Property, LMTf and TD Plaza. The loan information is given in the respective spreadsheets. There are three classes of security in the CMBS deal: class A, class B and class C. The coupon rates and the principal amounts are proivded in the "Total Promised Payments" sheet. The CMBS deal has a waterfall structure for cash flow distribution. That is, investors of class A security will rst receive the principal payment and interest payment based on the cash flows available from the mortgage pool each year. Investors of class B will only receive interest payment until class A's principal is paid off. Similarly, investors of class C will recieive interest payment until class B's principal is paid off. The total promised payments of the CM BS (including the total interest payments and principal payments from the three loans, as well as the allocations to the three classes of security] are provided in the sheet of "Total Promised Payments." This is the base case for all the promised cash flows, assuming no loan will be in default. Now suppose that the borrower of the LMT loan will default at the end of year 5. As a result, the lender of the LMT loan can only recover $500,000 at the end of 60th month from the liquidation of the underlying property, plus the interest payment of the 60th month. To do list: Please reconstruct the cash flows from the loans and the allocations for the CMBS investors, given the default assumption [see the sheet titled "Total Payments with Dafauit"). Also, please calcualte the return for class C investors. assuming the initial purchase price for class C is $1,180,000. A B C D E F G H J 1 Loan Payments Year 1 Year 2 Year 3 Year 4 Year 5 W N Total Principal $ 61,583.57 $ 64,690.72 S 67,955.31 $ 71,385.34 .9 .9 Total Interest S 120,870.34 $ 117,763.19 $ 114,498.60 $ 111,068.58 6 Bond Payments 7 A 3.50% 8 Beginning balance 500,000.00 $ 438,416.43 $ 373,725.71 $ 305,770.40 9 Principal 61,583.57 $ 64,690.72 S 67,955.31 $ 71,385.34 $ 265,614.94 10 Interest 17,500.00 15,344.58 S 13,080.40 $ 10,701.96 11 Ending balance S 500,000 438,416.43 373,725.71 305,770.40 $ 234,385.06 12 Total cash flow 79,083.57 $ 80,035.30 81,035.71 $ 82,087.30 13 B 4.00% 14 Beginning balance 800,000.00 800,000.00 S 800,000.00 $ 800,000.00 15 Principal $ 16 Interest 32,000.00 32,000.00 32,000.00 32,000.00 17 Ending balance S 800,000 800,000.00 $ 800,000.00 800,000.00 $ 800,000.00 18 Total cash flow 32,000.00 $ 32,000.00 32,000.00 $ 32,000.00 19 C 5.25% 20 Beginning balance 1,200,000.00 $ 1,200,000.00 S 1,200,000.00 $ 1,200,000.00 21 Principal .9 .9 .9 S 22 Interest 63,000.00 63,000.00 63,000.00 63,000.00 Assuming interest can still be received 23 Ending balance S 1,200,000 1,200,000.00 $ 1,200,000.00 1,200,000.00 $ 1,200,000.00 based on the original loan pricipal. 24 Total cash flow (1,180,000) $ 63,000.00 S 63,000.00 S 63,000.00 $ 63,000.00 25 26 Assuming the initial purchase price is $1,180,000 27 So, the return for class C investors will be: