case study return of the loan

Questions for Case Study (2019)-Word AYOUT REFERENCES MAILINGS REVIEWVEW Foxit PDF AaBbCDx AaBbCr AaBbcct AaBl Aabb 1 Normal NoSpac Heading 1 Heading 2 Title Subt AA- | :E . | a-, - Paragraph Styles Instructions timately, you are to make the recommendation that you believe Greenwood should make to United Principal Life (UPL). You should build up to the recommendations by completing the following steps: Direct Mortgage Lending 1) Construct the promised cash flows to directly lending $s.8 million in commercial mortgages using the information contained in Exhibit 6 and Exhibit 2. Assume any loans are 5 year ballon loans. Estimate the yield to maturity (contractual yield) and IRR of expected cash flows on direct lending (this should be in a spreadsheet tab names "Direct Lending-") Some things you may want to consider: a. b. G. d. How many direct loans would UPL be making? 2 What credit terms is UPL likely to be able to achieve? LTV How would you qualitatively assess the credit risk of these loans? When calculating the IRR of expected cash flows, you should account for default. Hint: Exhibit 15 reports cumulative losses rates on different bond types. You may assume deterministic default losses on the commercial mortgages and that any losses occur in year 5 CMBS investing Construct the promised cash flows from each of the mortgages contained in the CMBS offering (monthly payments). There should be a separate tab/worksheet for each loan named for the loan (e.g., Cole I). Each tab should contain an amortization table (monthly), as well as the aggregated principal and interest payments into annual amounts 2) 1 3 Sample CMBS Excel HOME INSERT PAGE LAYOUT FORMULAS DATA REVEw ww Fait Por cut Copy Format Panter Blu-1 . Formatting- Table $2,900,000 Loan amount Interest rate 7.50 Pmts/year Interest Principal 1 $216593$25,733 25214518 $28,809 Year Month Beg Balance Pmt interest Principal Ending Balance $212,282 2,844,458 $2,900,000 $20,277 22 $18,125 $2.152.22 $2,897,847.78 2 $2,897,848 $20,277.22 $18,112 $2,165.67 $2,895,682.11 $2,895,682 $20,277.22 $18,098 $2,17921 $2,833,502.90 4 $2,893,503 $20,277.22 $18,084 $2,192.83 $2,891,310.07 5 $2,891,310 $20,277.22 $18,071 $2,206.53 $2,889,103.54 6 $2,889,104 $20,277.22 S18,057 $2,220.32 $2,836,833.22 7 $2,886,883 $20,277.22 $18,043 $2,234.20 $2,834,649.01 8$2,884,649 $20,277.22 $18,029 $2,248.16 $2,882-400.85 9 $2,882.401 $20,277.22 $18015 $2,262.22 $2380.138.64 10 $2,880,139 $20,27722 $18,001 $2,235 $2877,862.28 $2,877,862 $20,277.22 $1797 2,29058 $2875 571.0 12 $2,875,572 $20,277.22 $17,972 $2.304 90 $2.873,266.80 13 $2,873,267 $20,27722 $17,958 2.319.30 $2.870 947.50 14 $2.870,947 $20,277.22 $17,943 $2.133.80 $2,868,613.70 Loan 2 6 5 4 2 Questions for Case Study (2019)-Word AYOUT REFERENCES MAILINGS REVIEWVEW Foxit PDF AaBbCDx AaBbCr AaBbcct AaBl Aabb 1 Normal NoSpac Heading 1 Heading 2 Title Subt AA- | :E . | a-, - Paragraph Styles Instructions timately, you are to make the recommendation that you believe Greenwood should make to United Principal Life (UPL). You should build up to the recommendations by completing the following steps: Direct Mortgage Lending 1) Construct the promised cash flows to directly lending $s.8 million in commercial mortgages using the information contained in Exhibit 6 and Exhibit 2. Assume any loans are 5 year ballon loans. Estimate the yield to maturity (contractual yield) and IRR of expected cash flows on direct lending (this should be in a spreadsheet tab names "Direct Lending-") Some things you may want to consider: a. b. G. d. How many direct loans would UPL be making? 2 What credit terms is UPL likely to be able to achieve? LTV How would you qualitatively assess the credit risk of these loans? When calculating the IRR of expected cash flows, you should account for default. Hint: Exhibit 15 reports cumulative losses rates on different bond types. You may assume deterministic default losses on the commercial mortgages and that any losses occur in year 5 CMBS investing Construct the promised cash flows from each of the mortgages contained in the CMBS offering (monthly payments). There should be a separate tab/worksheet for each loan named for the loan (e.g., Cole I). Each tab should contain an amortization table (monthly), as well as the aggregated principal and interest payments into annual amounts 2) 1 3 Sample CMBS Excel HOME INSERT PAGE LAYOUT FORMULAS DATA REVEw ww Fait Por cut Copy Format Panter Blu-1 . Formatting- Table $2,900,000 Loan amount Interest rate 7.50 Pmts/year Interest Principal 1 $216593$25,733 25214518 $28,809 Year Month Beg Balance Pmt interest Principal Ending Balance $212,282 2,844,458 $2,900,000 $20,277 22 $18,125 $2.152.22 $2,897,847.78 2 $2,897,848 $20,277.22 $18,112 $2,165.67 $2,895,682.11 $2,895,682 $20,277.22 $18,098 $2,17921 $2,833,502.90 4 $2,893,503 $20,277.22 $18,084 $2,192.83 $2,891,310.07 5 $2,891,310 $20,277.22 $18,071 $2,206.53 $2,889,103.54 6 $2,889,104 $20,277.22 S18,057 $2,220.32 $2,836,833.22 7 $2,886,883 $20,277.22 $18,043 $2,234.20 $2,834,649.01 8$2,884,649 $20,277.22 $18,029 $2,248.16 $2,882-400.85 9 $2,882.401 $20,277.22 $18015 $2,262.22 $2380.138.64 10 $2,880,139 $20,27722 $18,001 $2,235 $2877,862.28 $2,877,862 $20,277.22 $1797 2,29058 $2875 571.0 12 $2,875,572 $20,277.22 $17,972 $2.304 90 $2.873,266.80 13 $2,873,267 $20,27722 $17,958 2.319.30 $2.870 947.50 14 $2.870,947 $20,277.22 $17,943 $2.133.80 $2,868,613.70 Loan 2 6 5 4 2