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. 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 first 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 CMBS (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 Dafault"). Also, please calcualte the return for class C investors, assuming the initial purchase price for class C is $1,180,000. Loan Payments Year 1 Year 5 Total Principal Total Interest Year 2 61,583.57 $ 120,870.34 $ $ $ Year 3 64,690.72 $ 117,763.19 $ ? Year 4 67,955.31 $ 114,498.60 $ 71,385.34 111,068.58 ? 3.50% ? $ 265,614.94 $ $ $ 500,000 $ A A A A 500,000.00 $ 61,583.57 $ 17,500.00 $ 438,416.43 $ 79,083.57 $ 438,416.43 $ 64,690.72 $ 15,344.58 $ 373,725.71 $ 80,035.30 $ 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 ? ? ? $ $ ? 4.00% $ 800,000.00 ? Bond Payments Beginning balance Principal Interest Ending balance Total cash flow B Beginning balance Principal Interest Ending balance Total cash flow C Beginning balance Principal Interest Ending balance Total cash flow $ ? $ A A A A A $ 800,000 $ 800,000.00 $ $ 32,000.00 $ 800,000.00 $ 32,000.00 $ 800,000.00 $ $ 32,000.00 $ 800,000.00 $ 32,000.00 $ 800,000.00 $ $ 32,000.00 $ 800,000.00 $ 32,000.00 $ ? $ 32,000.00 800,000.00 32,000.00 ? $ ? 5.25% $ 1,200,000.00 ? $ ? 1,200,000.00 $ $ 63,000.00 $ 1,200,000.00 $ 63,000.00 $ 1,200,000.00 $ $ 63,000.00 $ 1,200,000.00 $ 63,000.00 $ $ 1,200,000 $ (1,180,000) $ 1,200,000.00 $ $ 63,000.00 $ 1,200,000.00 $ 63,000.00 $ ? $ $ Assuming interest can still be received based on the original loan pricipal. 63,000.00 1,200,000.00 63,000.00 ? ? Assuming the initial purchase price is $1,180,000 So, the return for class C investors will be: ? $ Loan Information Mortgage amount Payments per year Interest rate Maturity Amortization Payoff payment 900,000 12 4.75% 5 25 60 Year Payment Payment Interest Principal Year 1 1 1 2 3 Yearly interest Yearly principal ($42,334.76) ($19,237.92) ($41,400.80) ($20,171.88) ($40,421.50) ($21,151.18) $ ($39,394.65) ($22,178.02) $ ($38,317.96) ($817,261.01) 1 4 1 1 5 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 Balance 900,000.00 1 $898,431.44 2 $896,856.68 3 $895,275.68 4 $893,688.42 5 $892,094.88 6 $890,495.04 ** 7 $888.888.86 ce 8 $887,276.32 9 $885,657.40 writo 10 $884,032.07 11 $882,400.31 12 $880,762.08 13 $879,117.38 or 14 $877,466.16 Oude 15 $875,808.41 16 $874,144.09 *** 17 $872,473.19 18 $870,795.67 19 $869,111.52 20 Cocz 2007 20 $867,420.70 er 4 21 $865,723.18 22 $864,018.94 23 $862,307.96 re 24 $860,590.21 25 $858,865.66 26 $857,134.28 27 $855,396.04 28 $853,650.93 29 $851.898.91 30 $850,139.95 31 $848,374.03 22 $846.601.12 ($5,131.06) ($5,131.06) $) ($5,131.06) ($5,131.06) ($5,131.06) ($5,131.06) avec ($5,131.06) ($5,131.06) ($5,131.06) ($5,131.06) avec ($5,131.06) ci ($5,131.06) ($5,131.06) we? ($5,131.06) ($5,131.06) ($5,131.06) ($5,131.06) . ($5,131.06) ($5,131.06) ($5,131.06) ** ($5,131.06) ($5,131.06) ($5,131.06) ($5,131.06) KM ($5,131.06) veel ($5,131.06) ($5,131.06) ($5,131.06) ($5,131.06) ($5,131.06) ($5,131.06) ($5.131.061 ($3,562.50) ($3,556.29) ($3,550.06) ($3,543.80) ($3,537.52) 192941 ($3,531.21) ($3,524.88) ($3,518.52) ($3,512.14) * ($3,505.73) 2002 ($3,499.29) 2:43 ($3,492.83) 6:02 ($3,486.35) ($3,479.84) KOMIC ($3,473.30) ($3,466.74) ($3,460.15) ($3,453.54) ($3,446.90) 4402 ($3,440.23) 20 ($3,433.54) ($3,426.82) $3,420.07) $3,413.30) ce ($3,406.50) Wonde $3,399.68) 000 ($3,392.82) ($3,385.94) ($3,379.03) ($3,372.10) ($3,365.14) $3.358 151 ($1,568.56) ($1,574.77) ($1,581.00) ($1,587.26) ($1,593.54) ($1,599.85) 12:02 ($1,606.18) de! ($1,612.54) ? ($1,618.92) ($1,625.33) ? ($1,631.76) re! ($1,638.22) 20:22 ($1,644.71) ! ($1,651.22) ? ($1,657.75) ($1,664.31) ($1,670.90) on. ($1,677.52) ? ($1,684.16) con $ ($1,690.82) ($1,697.52) ($1,704.24) ($1,710.98) el ($1,717.75) ($1,724.55) ($1,731.38) MUSION ($1,738.23) ($1,745.11) ($1,752.02) ($1,758.96) ($1,765.92) ($ 1.772.91) 2 2 3 3 3 3 3 3 3 3 Loan Payments Year 1 Year 5 Total Principal Total Interest Year 2 $61,583.57 $ 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 2,234,385.06 $107,464.69 $ 500,000.00 500,000.00 $ 61.583.57 S 17.500.00 $ 438,416.43 $ 79,083.57 $ 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 $ 234,385,06 234,385.06 $ 8,203.48 0.00 242,588.54 S Bond Payments A A Beginning balance Principal Interest Ending balance Total cash flow B B Beginning balance Principal Interest Ending balance Total cash flow Beginning balance Principal Interest Ending balance Total cash flow 800,000.00 3.50% $ $ $ 500,000 $ $ 4.00% $ $ $ 800,000 $ $ 5.25% $ $ $ 1,200,000 $ (1,180,000) $ 800,000.00 $ $ S 32,000.00 $ 800,000.00 $ 32,000.00 $ 800,000.00 S S S 32,000.00 S 800,000.00 S 32,000.00 S 800,000.00 $ $ 32,000.00 $ 800,000.00 $ 32,000.00 $ 800,000.00 $ $ $ 32,000.00 $ 800,000.00 32,000.00 $ 800,000.00 800,000.00 $ 32,000.00 0.00 832,000.00 S 1,200,000.00 1,200,000.00 $ $ S 63,000.00 $ 1.200,000.00 $ 63,000.00 $ 1,200,000.00 S S S 63,000.00 S 1,200,000.00 S 63,000.00 S 1,200,000.00 $ $ 63,000.00 $ 1,200,000.00 $ 63,000.00 $ 1,200,000.00 $ $ S 63,000.00 $ 1,200,000.00 63,000.00 $ 1,200,000.00 1,200,000.00 $ 63,000.00 0.00 1,263,000.00 $ Assuming the initial purchase price is $1,180,000 So, the return for class C investors will be: Total principal payments of all classes 2,234,385.06 5.642% 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. 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 first 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 CMBS (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 Dafault"). Also, please calcualte the return for class C investors, assuming the initial purchase price for class C is $1,180,000. Loan Payments Year 1 Year 5 Total Principal Total Interest Year 2 61,583.57 $ 120,870.34 $ $ $ Year 3 64,690.72 $ 117,763.19 $ ? Year 4 67,955.31 $ 114,498.60 $ 71,385.34 111,068.58 ? 3.50% ? $ 265,614.94 $ $ $ 500,000 $ A A A A 500,000.00 $ 61,583.57 $ 17,500.00 $ 438,416.43 $ 79,083.57 $ 438,416.43 $ 64,690.72 $ 15,344.58 $ 373,725.71 $ 80,035.30 $ 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 ? ? ? $ $ ? 4.00% $ 800,000.00 ? Bond Payments Beginning balance Principal Interest Ending balance Total cash flow B Beginning balance Principal Interest Ending balance Total cash flow C Beginning balance Principal Interest Ending balance Total cash flow $ ? $ A A A A A $ 800,000 $ 800,000.00 $ $ 32,000.00 $ 800,000.00 $ 32,000.00 $ 800,000.00 $ $ 32,000.00 $ 800,000.00 $ 32,000.00 $ 800,000.00 $ $ 32,000.00 $ 800,000.00 $ 32,000.00 $ ? $ 32,000.00 800,000.00 32,000.00 ? $ ? 5.25% $ 1,200,000.00 ? $ ? 1,200,000.00 $ $ 63,000.00 $ 1,200,000.00 $ 63,000.00 $ 1,200,000.00 $ $ 63,000.00 $ 1,200,000.00 $ 63,000.00 $ $ 1,200,000 $ (1,180,000) $ 1,200,000.00 $ $ 63,000.00 $ 1,200,000.00 $ 63,000.00 $ ? $ $ Assuming interest can still be received based on the original loan pricipal. 63,000.00 1,200,000.00 63,000.00 ? ? Assuming the initial purchase price is $1,180,000 So, the return for class C investors will be: ? $ Loan Information Mortgage amount Payments per year Interest rate Maturity Amortization Payoff payment 900,000 12 4.75% 5 25 60 Year Payment Payment Interest Principal Year 1 1 1 2 3 Yearly interest Yearly principal ($42,334.76) ($19,237.92) ($41,400.80) ($20,171.88) ($40,421.50) ($21,151.18) $ ($39,394.65) ($22,178.02) $ ($38,317.96) ($817,261.01) 1 4 1 1 5 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 Balance 900,000.00 1 $898,431.44 2 $896,856.68 3 $895,275.68 4 $893,688.42 5 $892,094.88 6 $890,495.04 ** 7 $888.888.86 ce 8 $887,276.32 9 $885,657.40 writo 10 $884,032.07 11 $882,400.31 12 $880,762.08 13 $879,117.38 or 14 $877,466.16 Oude 15 $875,808.41 16 $874,144.09 *** 17 $872,473.19 18 $870,795.67 19 $869,111.52 20 Cocz 2007 20 $867,420.70 er 4 21 $865,723.18 22 $864,018.94 23 $862,307.96 re 24 $860,590.21 25 $858,865.66 26 $857,134.28 27 $855,396.04 28 $853,650.93 29 $851.898.91 30 $850,139.95 31 $848,374.03 22 $846.601.12 ($5,131.06) ($5,131.06) $) ($5,131.06) ($5,131.06) ($5,131.06) ($5,131.06) avec ($5,131.06) ($5,131.06) ($5,131.06) ($5,131.06) avec ($5,131.06) ci ($5,131.06) ($5,131.06) we? ($5,131.06) ($5,131.06) ($5,131.06) ($5,131.06) . ($5,131.06) ($5,131.06) ($5,131.06) ** ($5,131.06) ($5,131.06) ($5,131.06) ($5,131.06) KM ($5,131.06) veel ($5,131.06) ($5,131.06) ($5,131.06) ($5,131.06) ($5,131.06) ($5,131.06) ($5.131.061 ($3,562.50) ($3,556.29) ($3,550.06) ($3,543.80) ($3,537.52) 192941 ($3,531.21) ($3,524.88) ($3,518.52) ($3,512.14) * ($3,505.73) 2002 ($3,499.29) 2:43 ($3,492.83) 6:02 ($3,486.35) ($3,479.84) KOMIC ($3,473.30) ($3,466.74) ($3,460.15) ($3,453.54) ($3,446.90) 4402 ($3,440.23) 20 ($3,433.54) ($3,426.82) $3,420.07) $3,413.30) ce ($3,406.50) Wonde $3,399.68) 000 ($3,392.82) ($3,385.94) ($3,379.03) ($3,372.10) ($3,365.14) $3.358 151 ($1,568.56) ($1,574.77) ($1,581.00) ($1,587.26) ($1,593.54) ($1,599.85) 12:02 ($1,606.18) de! ($1,612.54) ? ($1,618.92) ($1,625.33) ? ($1,631.76) re! ($1,638.22) 20:22 ($1,644.71) ! ($1,651.22) ? ($1,657.75) ($1,664.31) ($1,670.90) on. ($1,677.52) ? ($1,684.16) con $ ($1,690.82) ($1,697.52) ($1,704.24) ($1,710.98) el ($1,717.75) ($1,724.55) ($1,731.38) MUSION ($1,738.23) ($1,745.11) ($1,752.02) ($1,758.96) ($1,765.92) ($ 1.772.91) 2 2 3 3 3 3 3 3 3 3 Loan Payments Year 1 Year 5 Total Principal Total Interest Year 2 $61,583.57 $ 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 2,234,385.06 $107,464.69 $ 500,000.00 500,000.00 $ 61.583.57 S 17.500.00 $ 438,416.43 $ 79,083.57 $ 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 $ 234,385,06 234,385.06 $ 8,203.48 0.00 242,588.54 S Bond Payments A A Beginning balance Principal Interest Ending balance Total cash flow B B Beginning balance Principal Interest Ending balance Total cash flow Beginning balance Principal Interest Ending balance Total cash flow 800,000.00 3.50% $ $ $ 500,000 $ $ 4.00% $ $ $ 800,000 $ $ 5.25% $ $ $ 1,200,000 $ (1,180,000) $ 800,000.00 $ $ S 32,000.00 $ 800,000.00 $ 32,000.00 $ 800,000.00 S S S 32,000.00 S 800,000.00 S 32,000.00 S 800,000.00 $ $ 32,000.00 $ 800,000.00 $ 32,000.00 $ 800,000.00 $ $ $ 32,000.00 $ 800,000.00 32,000.00 $ 800,000.00 800,000.00 $ 32,000.00 0.00 832,000.00 S 1,200,000.00 1,200,000.00 $ $ S 63,000.00 $ 1.200,000.00 $ 63,000.00 $ 1,200,000.00 S S S 63,000.00 S 1,200,000.00 S 63,000.00 S 1,200,000.00 $ $ 63,000.00 $ 1,200,000.00 $ 63,000.00 $ 1,200,000.00 $ $ S 63,000.00 $ 1,200,000.00 63,000.00 $ 1,200,000.00 1,200,000.00 $ 63,000.00 0.00 1,263,000.00 $ Assuming the initial purchase price is $1,180,000 So, the return for class C investors will be: Total principal payments of all classes 2,234,385.06 5.642%
