Instructions
This involves building an Excel model and preparing a clear and concise written memo. It is an individual exercise that only requires Excel and Word.
The case below contains a partial set of data and you may need to make estimates and assumptions to construct a case. As such, no additional clarifications will be provided outside of this document.
A PDF summary memo of your assessment and recommendation whether to proceed with a loan as described below. This memo may include additional tables and charts.
An Excel model of your business case and calculations. Please ensure your excel model references the source excel provided. This model should be created from scratch and not utilize a previously-created template.
The subject of your email should be in the format "Lument Case Study - Your Name"
Please note that this case is not designed to test your specialized real estate or sector knowledge. We are interested in learning more about how you think and communicate your conclusions.
Background
Lument operates in a wide space providing financing and advisory solutions to owners of Senior Living, Healthcare, Hospitals, and Market-Rate/Affordable Multifamily projects. In today's marketplace much of the financing solutions required to meet clients' needs include some form of agency-insured mortgage (i.e. FNMA, FHLMC, FHA, etc.). The mortgages allow a borrower to receive a non-recourse loan (the only collateral is the property itself), at historically low rates by "borrowing" the credit score of a government-insured entity.
One of Lument's existing clients, Aissi Enterprises, recently heard that FHA is offering attractive interest rates to strong borrowers and has approached an originator at the firm to request an insured loan on a Market-Rate Multifamily Housing property they have owned since late 2017. The property they currently own contains 150 units located in Lancaster, OH and has $5,650,000 of existing debt from Bank of Kent. The client feels that the property is under-levered and can afford at least $8,250,000 of debt. The client has a large appetite for multifamily projects and is looking to grow, therefore they would like to refinance the existing debt and take any remaining cash to acquire a new property they are considering purchasing.
The client has been a long-standing client of Lument and has five other loans outstanding totaling approximately $16,500,000. The originator has received the facility's income statement and budget in Excel. Your manager has asked you to determine if the property can afford to take on this debt and if it is a prudent credit decision for Lument to make.
Details
A. Assumptions
Assume that the borrowing entity is a single-purpose entity which only owns the subject property and receives 100% of the net cash-flow.
The interest rates today are 4.19% for a 35-year loan term with 35-year amortization.
Lument will charge a fee of 1% to make the loan and there will be another 1% of various costs related to closing the loan. These fees can be included in the new loan.
Assume property did not request or receive any COVID relief funds.
B. Credit Limits
The program limits the loan to a maximum of 80% of the project's value.
The program requires that the project can generate cash flow of at least 130% (1.30x) of the projected debt payments.
C. Borrower Group
Aissi Enterprises, the owner of the borrowing entity, currently has $150,000 of liquidity.
You have the following information on their other loans with Lument.
Monthly Operating Report: Meal Time Enterprises The flowing formation is Rotanal. The recipient should not have this with any other addas, cang cutent or ture candides. ACCOUNT: 1 1 BUDGET Operating and Maintenance Checking Account _JAN FEB. MAR APR 96.00% 100.00% 100.00% 100.00% 10 LTM Occupancy % or Units Vacan JULY 96.00% 10 AUG. 92.00% 20 SEPT. 92.00% 20 OCT. 96.00% % 10 DEC 100.00% NOV 100.00% 0 . 92531 213,000.00 2,575.67 215,413.3) 3,681107 M-1010-Rent 441 Saundry #4580-SD Fee Income N1385-Tenant Damages M4605-Misc Tot.inc. 2,501.420.00 34,666 67 1 1 MAY JUNE 100.00% 100.00% 0 0 1 I 208,286.67 211,573.331 3,441.67 0.00 0.00 0.00 0.001 211.TEN. 212.05.17 207,626.67 5.786.73 41.33 0.00 0.00 21.3.454.73 217.610.00 2,6111.00 0.00 0.00 0.00 220,250.00 217,000.00 2,641 67 0.00 0.00 0.00 219.661.67 213,340.00 1 KRST 42.93 0.00 0.00 215.26.XII 0.001 214.226.67 4,8401,00 44.60 0.001 00 219,111.27 201.193.33 2,250,07 0.00 0.00 0.00 206,445.40 207.520.00 2,450.87 0.00 0.00 0.00 209,970.87 203,140,00 3,211.69 42.60 0.00 0.00 2016.394.27 0.00 0.00 215,575.67 0.00 0.00 219,095.40 2,666.67 0.00 2.506,990.00 22,606.60 7.759.47 CLIHI 19.728.20 5.752.67 18.00 0.00 22.616.73 3.548.13 0.00 0.00 22,097.60 19,362 62 0.11 0.00 11.993.09 0.10 0.00 53,453.33 20,851.60 9,035.60 2,4R1.22 0.00 0.00 1.595.93 0.00 33.956.40 N5180 Maint. Payroll 504X-Man Supplies A506X-Main Contact NSLOX Painting 4548X-San Removal ASOXX-4 rounds #LIXX Cap. Exp. Oper. Sub-Tol Oper. & Msini. 4501 X-Electric Ciality 5000 Wwer Utility N5031-Sower Utility 502Xhes Utility SIX-Trash tility Sub-Total Utility 29,101.00 165420 0.00 0.00 2.880.00 514.29 14,121.73 48,294.60 1.749 401 22.135.13 19.601.33 214.00 0.00 0.00 1,685,00 38, 138.67 82,365.JJ 19.211.871 7,409 071 810.000 0ool 0001 19.594.071 0.000 45, 125.001 , 21,177.07 5,918.87 D. 0.00 .303 93 0. 16,041.62 45,441.53 169.759.26 61,111 11 7,407.41 6.296.30 26,384.53 3,965.40 20.00 0.00 9,195.13 4611.00 0.00 40,005.07 1,582.27 2,532.980.000 36,357.001 521.471 0.001 0.001 2.59.58.471 1 280.111.00! 115,268,131 10,722.471 0.001 32.274.401 24,906.07 90,500.601 542.782.671 Pas037.04 0.00 32,259.67 11.00 0.00 36,194,87 X,518.52 210,694.44 520,824.07 11.00 0.00 29,800.87 3,227,40 2,281.27 6.950.-10 11.00 1,928.73 3,599.33 7.501.42 7,137.80 O 4,242.82 22,471.67 2,718 401 8,426.33 7,925.07 000 1,702.51 7,911.22 7.232.80 0. 1, 1114,0 7,212.40 6.637.73 11.00 4,384.00 19.238.93 2.152.67 7.517.47 6,897.53 0.00 7,436.13 50,1166.67 108,926.67 80,000.00 0.00 6.396.60 0.00 21.695.73 23,124.47 22,970.60 21,216.27 20.938.40 3,710.73 7,215.02 6,894.00 O 4.254.62 22,074.47 7.81280 22,026 67 0.00 10,260 53 ONE 0.00 1,352.00 1,104 33 7,832.80 21.653.33 13.686.67 10,260.53 0.00 0.00 1,331.53 2,426.23 21,203.40 7,832.80 22.026.67 0.00 10,260.53 0.00 23,1126.67 13,666.67 10.260.53 26,8015, 131 89,547.00 83.511.931 11,001 35,1150.20 254.944.331 93,993.60 , 261,7116,67 54,666.67! 123.126.10 11.00 1,181.6illi 7.485.401 6,870.201 0.00 4.226.07 19.93.271 7,8J2.80 22.026.67 0.00 10,260.53 0.010 1,674.62 1,350.00 1,057 201 0.00 0.00 1.921871 1,022.40 413.27 47,559.40 7.832.80 21,653.39 0.00 10.260.51 11.00 7,832.80 21,2811.00 0.00 LO.260.33 11.00 21.130.53 32,936.73 8,184,80 22,985.93 714.611 0.00 0.00 0.00 00 493,33 1.2013 12.198.51 0.00 48.007.00 57,836.80 1.500.13 2,129.00 7,292.13 7.001.40 6.703.60 6,458.07 11.00 0.00 4,370.22 19,877.93 19,959.20 7.832.80 7,832.80 21,280.00 21.653.32 0.00 0.00 10.260.53 10,260.53 11.00 01.10) 0.00 1.351.73 1,356.53 2,667.27 1,908,27 0.00 0.00 1.598.9-5.398.20 2,082,07 3,243.67 1,109.67 1.754.0 413.27 18,518.67 50,594.27 71,926.80 62,1184.07 01.10 ) 9.966.97 m01 7,832.80 22.126.67 13,666.67 10.260.53 11.00 284.106.67 93.993 13 264,5201,00 54,666.67 123.126.671 II. 15181-Site Odio Payroll S470-Management Fee 45365 Project Auditing N5361-Project Xping. 516-Project 1529x Advertising N5-46X-Iject l'homes 4540X-lice Supplies 545X-Training 1525X-Empl. Benefits 5182-Maint. Pigil Taxes 220Maint. Wirs, Comp. 154.X-Other Admin Exp. Sub-Total Administration 54TX-Real Estate Taxes 45455 Property Insurance 7,832.80 22.026.62 0.00 10,260.53 01.11 0.00 1,471.33 2011.02 0.00 0.00 2,060,53 1.04.20 2,827.80 49,586,93 01.11 9.988.92 16.268.20, 18,089,67 1.335.17 858.60 0.00 0.00 1,966.47 1,1146,20 2,029,67 61,023.07 1,350.00 906.53 3.000.000 0.00 2,873 87 1.528.X2 0.00 2,21293 1,177.20 413.27 61,143,20 7,832.80 22,126,67 13,666.67 10,260.53 0.00 0.00 1,349.93 1,69113 0.00 3,598.93 2,080.00 1,123.29 779.53 1.351.7) 592.67 0.00 0.00 2,179.99 1,159,67 413.27 45.443.93 0.00 2.15040 1,144.000 4.3.27 47,050.67 1.351.73 995.00 0.00 3.598.87 2,153.07 1.145.40 596.33 49,213.73 1.316.20 1,669.3 20.00 3,598.87 2,269.27 1.633.87 1.747,67 66,221.87 11.933.33 16,566.67 6,666.67 - 0.00 30,086.67 16,226.67 40,000.00 666,020.00 19.793.80 27,194.07 14,941.50 28.979.27 664,967.87 224,999.40! 119,603,60! 1 10 75,326.01 9.986.97 2001 11,00 9.966.97 50,792.33 0.000 9.986.97 19.SK SE! 0.11 9,986.97 mm 11.00 9,966.93 9.986.92 narro 11.00 9,966.97 0.01 9.966.92 11.00 9.966.97 227,053.31 122,100.00 10 POP 93211 38 Fle 8 8 8 816 8 8 6 8 8 8 8 SOOS 4,528.58 SE li SES NE ir le 888 83 8888888888 7,433.13 12,017.1 8388 ini DZELT S98 IBS ale 2 - si 888 ABS 988 8888888888 88888888 998 S28 5 818 588 298 28 $$ 12,123.56 SubTotal R.. Tax & Ind Total Operating Expense 136 leweciation & Amortization REO Schedule The following information is fictional. The recipient should not share this with any other individuals, including current or future candidates. Aissi Enterprises Real Estate Owned Property Name Property Location Property Type Occupancy % Units 2019 NOI/Cash Flow Mike Scott Tower Shiloh, IL Conventional 85% 15 $ 46,125 St. Luke Apartments Waterloo, IA Affordable Housing 100% 10 $ 27,625 Beakas Nest Cleveland, OH Senior Living 80% 120 $ 1,950,000 Hobbs Cape Yellow Springs, OH Conventional 70% 30 $ 77,760 Kansas Walls St. Charles, MO Conventional 100% 50 $ 16,400 $ Est. Value 562,500 425,000 15,000,000 1,080,000 200,000 $ $ $ $ $ $ $ $ $ $ $ $ | | |-- Value/Unit 37,500 42,500 125,000 36,000 40,000 Current Loan 438,750 306,000 12,300,000 1,188,000 100,000 Loan-To-Value 78.0% 72.0% 82.0% 110.0% 50.0% $ sol Monthly Operating Report: Meal Time Enterprises The flowing formation is Rotanal. The recipient should not have this with any other addas, cang cutent or ture candides. ACCOUNT: 1 1 BUDGET Operating and Maintenance Checking Account _JAN FEB. MAR APR 96.00% 100.00% 100.00% 100.00% 10 LTM Occupancy % or Units Vacan JULY 96.00% 10 AUG. 92.00% 20 SEPT. 92.00% 20 OCT. 96.00% % 10 DEC 100.00% NOV 100.00% 0 . 92531 213,000.00 2,575.67 215,413.3) 3,681107 M-1010-Rent 441 Saundry #4580-SD Fee Income N1385-Tenant Damages M4605-Misc Tot.inc. 2,501.420.00 34,666 67 1 1 MAY JUNE 100.00% 100.00% 0 0 1 I 208,286.67 211,573.331 3,441.67 0.00 0.00 0.00 0.001 211.TEN. 212.05.17 207,626.67 5.786.73 41.33 0.00 0.00 21.3.454.73 217.610.00 2,6111.00 0.00 0.00 0.00 220,250.00 217,000.00 2,641 67 0.00 0.00 0.00 219.661.67 213,340.00 1 KRST 42.93 0.00 0.00 215.26.XII 0.001 214.226.67 4,8401,00 44.60 0.001 00 219,111.27 201.193.33 2,250,07 0.00 0.00 0.00 206,445.40 207.520.00 2,450.87 0.00 0.00 0.00 209,970.87 203,140,00 3,211.69 42.60 0.00 0.00 2016.394.27 0.00 0.00 215,575.67 0.00 0.00 219,095.40 2,666.67 0.00 2.506,990.00 22,606.60 7.759.47 CLIHI 19.728.20 5.752.67 18.00 0.00 22.616.73 3.548.13 0.00 0.00 22,097.60 19,362 62 0.11 0.00 11.993.09 0.10 0.00 53,453.33 20,851.60 9,035.60 2,4R1.22 0.00 0.00 1.595.93 0.00 33.956.40 N5180 Maint. Payroll 504X-Man Supplies A506X-Main Contact NSLOX Painting 4548X-San Removal ASOXX-4 rounds #LIXX Cap. Exp. Oper. Sub-Tol Oper. & Msini. 4501 X-Electric Ciality 5000 Wwer Utility N5031-Sower Utility 502Xhes Utility SIX-Trash tility Sub-Total Utility 29,101.00 165420 0.00 0.00 2.880.00 514.29 14,121.73 48,294.60 1.749 401 22.135.13 19.601.33 214.00 0.00 0.00 1,685,00 38, 138.67 82,365.JJ 19.211.871 7,409 071 810.000 0ool 0001 19.594.071 0.000 45, 125.001 , 21,177.07 5,918.87 D. 0.00 .303 93 0. 16,041.62 45,441.53 169.759.26 61,111 11 7,407.41 6.296.30 26,384.53 3,965.40 20.00 0.00 9,195.13 4611.00 0.00 40,005.07 1,582.27 2,532.980.000 36,357.001 521.471 0.001 0.001 2.59.58.471 1 280.111.00! 115,268,131 10,722.471 0.001 32.274.401 24,906.07 90,500.601 542.782.671 Pas037.04 0.00 32,259.67 11.00 0.00 36,194,87 X,518.52 210,694.44 520,824.07 11.00 0.00 29,800.87 3,227,40 2,281.27 6.950.-10 11.00 1,928.73 3,599.33 7.501.42 7,137.80 O 4,242.82 22,471.67 2,718 401 8,426.33 7,925.07 000 1,702.51 7,911.22 7.232.80 0. 1, 1114,0 7,212.40 6.637.73 11.00 4,384.00 19.238.93 2.152.67 7.517.47 6,897.53 0.00 7,436.13 50,1166.67 108,926.67 80,000.00 0.00 6.396.60 0.00 21.695.73 23,124.47 22,970.60 21,216.27 20.938.40 3,710.73 7,215.02 6,894.00 O 4.254.62 22,074.47 7.81280 22,026 67 0.00 10,260 53 ONE 0.00 1,352.00 1,104 33 7,832.80 21.653.33 13.686.67 10,260.53 0.00 0.00 1,331.53 2,426.23 21,203.40 7,832.80 22.026.67 0.00 10,260.53 0.00 23,1126.67 13,666.67 10.260.53 26,8015, 131 89,547.00 83.511.931 11,001 35,1150.20 254.944.331 93,993.60 , 261,7116,67 54,666.67! 123.126.10 11.00 1,181.6illi 7.485.401 6,870.201 0.00 4.226.07 19.93.271 7,8J2.80 22.026.67 0.00 10,260.53 0.010 1,674.62 1,350.00 1,057 201 0.00 0.00 1.921871 1,022.40 413.27 47,559.40 7.832.80 21,653.39 0.00 10.260.51 11.00 7,832.80 21,2811.00 0.00 LO.260.33 11.00 21.130.53 32,936.73 8,184,80 22,985.93 714.611 0.00 0.00 0.00 00 493,33 1.2013 12.198.51 0.00 48.007.00 57,836.80 1.500.13 2,129.00 7,292.13 7.001.40 6.703.60 6,458.07 11.00 0.00 4,370.22 19,877.93 19,959.20 7.832.80 7,832.80 21,280.00 21.653.32 0.00 0.00 10.260.53 10,260.53 11.00 01.10) 0.00 1.351.73 1,356.53 2,667.27 1,908,27 0.00 0.00 1.598.9-5.398.20 2,082,07 3,243.67 1,109.67 1.754.0 413.27 18,518.67 50,594.27 71,926.80 62,1184.07 01.10 ) 9.966.97 m01 7,832.80 22.126.67 13,666.67 10.260.53 11.00 284.106.67 93.993 13 264,5201,00 54,666.67 123.126.671 II. 15181-Site Odio Payroll S470-Management Fee 45365 Project Auditing N5361-Project Xping. 516-Project 1529x Advertising N5-46X-Iject l'homes 4540X-lice Supplies 545X-Training 1525X-Empl. Benefits 5182-Maint. Pigil Taxes 220Maint. Wirs, Comp. 154.X-Other Admin Exp. Sub-Total Administration 54TX-Real Estate Taxes 45455 Property Insurance 7,832.80 22.026.62 0.00 10,260.53 01.11 0.00 1,471.33 2011.02 0.00 0.00 2,060,53 1.04.20 2,827.80 49,586,93 01.11 9.988.92 16.268.20, 18,089,67 1.335.17 858.60 0.00 0.00 1,966.47 1,1146,20 2,029,67 61,023.07 1,350.00 906.53 3.000.000 0.00 2,873 87 1.528.X2 0.00 2,21293 1,177.20 413.27 61,143,20 7,832.80 22,126,67 13,666.67 10,260.53 0.00 0.00 1,349.93 1,69113 0.00 3,598.93 2,080.00 1,123.29 779.53 1.351.7) 592.67 0.00 0.00 2,179.99 1,159,67 413.27 45.443.93 0.00 2.15040 1,144.000 4.3.27 47,050.67 1.351.73 995.00 0.00 3.598.87 2,153.07 1.145.40 596.33 49,213.73 1.316.20 1,669.3 20.00 3,598.87 2,269.27 1.633.87 1.747,67 66,221.87 11.933.33 16,566.67 6,666.67 - 0.00 30,086.67 16,226.67 40,000.00 666,020.00 19.793.80 27,194.07 14,941.50 28.979.27 664,967.87 224,999.40! 119,603,60! 1 10 75,326.01 9.986.97 2001 11,00 9.966.97 50,792.33 0.000 9.986.97 19.SK SE! 0.11 9,986.97 mm 11.00 9,966.93 9.986.92 narro 11.00 9,966.97 0.01 9.966.92 11.00 9.966.97 227,053.31 122,100.00 10 POP 93211 38 Fle 8 8 8 816 8 8 6 8 8 8 8 SOOS 4,528.58 SE li SES NE ir le 888 83 8888888888 7,433.13 12,017.1 8388 ini DZELT S98 IBS ale 2 - si 888 ABS 988 8888888888 88888888 998 S28 5 818 588 298 28 $$ 12,123.56 SubTotal R.. Tax & Ind Total Operating Expense 136 leweciation & Amortization REO Schedule The following information is fictional. The recipient should not share this with any other individuals, including current or future candidates. Aissi Enterprises Real Estate Owned Property Name Property Location Property Type Occupancy % Units 2019 NOI/Cash Flow Mike Scott Tower Shiloh, IL Conventional 85% 15 $ 46,125 St. Luke Apartments Waterloo, IA Affordable Housing 100% 10 $ 27,625 Beakas Nest Cleveland, OH Senior Living 80% 120 $ 1,950,000 Hobbs Cape Yellow Springs, OH Conventional 70% 30 $ 77,760 Kansas Walls St. Charles, MO Conventional 100% 50 $ 16,400 $ Est. Value 562,500 425,000 15,000,000 1,080,000 200,000 $ $ $ $ $ $ $ $ $ $ $ $ | | |-- Value/Unit 37,500 42,500 125,000 36,000 40,000 Current Loan 438,750 306,000 12,300,000 1,188,000 100,000 Loan-To-Value 78.0% 72.0% 82.0% 110.0% 50.0% $ sol
