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.

D E F G H 1 Year 5 Year 2 61,583.57 S 120,870.34 $ Year 3 64,690.72 S 117,763.19 S Year 4 67,955.31 $ 114,498.60 $ 71,385.34 111,068.58 ? ? $ 265,614.94 500,000.00 $ 61,583.57 S 17,500.00 $ 438,416.43 S 79,083.57 S 438,416.43 S 64,690.72 S 15,344.58 S 373,725.71 S 80,035.30 S 373,725.71 $ 67,955.31 $ 13,080.40 $ 305,770.40 $ 81,035.71 $ 305,770.40 71,385.34 10,701.96 234,385.06 82,087.30 ? ? ? ? ? B 1 Loan Payments 2 Year 1 3 Total Principal S S 4 Total Interest $ 5 6 Bond Payments 7 A 7 3.50% 8 Beginning balance S 9 Principal S 10 Interest $ 11 Ending balance S 500,000 $ 12 Total cash flow S 13 B 4.00% 14 Beginning balance S 15 Principal $ 16 Interest S 17 Ending balance S 800,000 $ 18 Total cash flow $ 19 C 5.25% 20 Beginning balance S 21 Principal $ 22 Interest S 23 Ending balance S 1,200,000 $ 24 Total cash flow (1,180,000) S 25 26 Assuming the initial purchase price is $1,180,000 27 So, the return for class C investors will be: 800,000.00 ? ? $ 800,000.00 S $ 32,000.00 S 800,000.00 $ 32,000.00 S 800,000.00 $ S 32,000.00 S 800,000.00 S 32,000.00 S 800,000.00 $ S 32,000.00 $ 800,000.00 $ 32,000.00 $ 32,000.00 800,000.00 32,000.00 ? ? ? 1,200,000.00 ? 1,200,000.00 $ $ 63,000.00 $ 1,200,000.00 $ 63,000.00 $ 1,200,000.00 S S 63,000.00 $ 1,200,000.00 $ 63,000.00 S 1,200,000.00 $ $ 63,000.00 $ 1,200,000.00 $ 63,000.00 S ? 2 63,000.00 1,200,000.00 63,000.00 $ Assuming interest can still be received based on the original loan pricipal. 2 ? ? ? B D E F G H 1 K L M N 0 P Q R S T U 1 Individual Assignment #3 2 3 A CMBS deal is issued by Bank of America. It has three loans in the pool: Dale Property, LMT, and TD Plaza. The loan information is given in the respective spreadsheets. 4 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. 5 The CMBS deal has a waterfall structure for cash flow distribution. That is, investors of class A security will first receive the principal payment and interest payment 6 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. 7 Similarly, investors of class C will recieive interest payment until class B's principal is paid off. 8 9 The total promised payments of the CMBS (including the total interest payments and principal payments from the three loans, as well as the allocations to the three classes 10 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. 11 12 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 13 from the liquidation of the underlying property, plus the interest payment of the 60th month. 14 To do list: 15 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 Dafault"). 16 Also, please calcualte the return for class Cinvestors, assuming the initial purchase price for class C is $1,180,000. 17 D E F G H 1 Year 5 Year 2 61,583.57 S 120,870.34 $ Year 3 64,690.72 S 117,763.19 S Year 4 67,955.31 $ 114,498.60 $ 71,385.34 111,068.58 ? ? $ 265,614.94 500,000.00 $ 61,583.57 S 17,500.00 $ 438,416.43 S 79,083.57 S 438,416.43 S 64,690.72 S 15,344.58 S 373,725.71 S 80,035.30 S 373,725.71 $ 67,955.31 $ 13,080.40 $ 305,770.40 $ 81,035.71 $ 305,770.40 71,385.34 10,701.96 234,385.06 82,087.30 ? ? ? ? ? B 1 Loan Payments 2 Year 1 3 Total Principal S S 4 Total Interest $ 5 6 Bond Payments 7 A 7 3.50% 8 Beginning balance S 9 Principal S 10 Interest $ 11 Ending balance S 500,000 $ 12 Total cash flow S 13 B 4.00% 14 Beginning balance S 15 Principal $ 16 Interest S 17 Ending balance S 800,000 $ 18 Total cash flow $ 19 C 5.25% 20 Beginning balance S 21 Principal $ 22 Interest S 23 Ending balance S 1,200,000 $ 24 Total cash flow (1,180,000) S 25 26 Assuming the initial purchase price is $1,180,000 27 So, the return for class C investors will be: 800,000.00 ? ? $ 800,000.00 S $ 32,000.00 S 800,000.00 $ 32,000.00 S 800,000.00 $ S 32,000.00 S 800,000.00 S 32,000.00 S 800,000.00 $ S 32,000.00 $ 800,000.00 $ 32,000.00 $ 32,000.00 800,000.00 32,000.00 ? ? ? 1,200,000.00 ? 1,200,000.00 $ $ 63,000.00 $ 1,200,000.00 $ 63,000.00 $ 1,200,000.00 S S 63,000.00 $ 1,200,000.00 $ 63,000.00 S 1,200,000.00 $ $ 63,000.00 $ 1,200,000.00 $ 63,000.00 S ? 2 63,000.00 1,200,000.00 63,000.00 $ Assuming interest can still be received based on the original loan pricipal. 2 ? ? ? B D E F G H 1 K L M N 0 P Q R S T U 1 Individual Assignment #3 2 3 A CMBS deal is issued by Bank of America. It has three loans in the pool: Dale Property, LMT, and TD Plaza. The loan information is given in the respective spreadsheets. 4 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. 5 The CMBS deal has a waterfall structure for cash flow distribution. That is, investors of class A security will first receive the principal payment and interest payment 6 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. 7 Similarly, investors of class C will recieive interest payment until class B's principal is paid off. 8 9 The total promised payments of the CMBS (including the total interest payments and principal payments from the three loans, as well as the allocations to the three classes 10 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. 11 12 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 13 from the liquidation of the underlying property, plus the interest payment of the 60th month. 14 To do list: 15 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 Dafault"). 16 Also, please calcualte the return for class Cinvestors, assuming the initial purchase price for class C is $1,180,000. 17