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: MealTime Enterprises ACCOUNT: Operating and Maintenance Checking Account JAN. APR. MAY JULY AUG. OCT. NOV. DEC LTM BUDGET 96.00% 100.00% 100.00% 96.00% 92.00% 92.00% 96.00% 100.00% 100.00% # of Units Vacant 20 20 #4010-Rent 217,640.00 217,020.00 213,340.00 211,573.331 214,226.67 204, 193.33 207,520.00 203, 140.00 213,000.00 215,413.33 2,532,980.00 2,501,420.00 5,786.73 2,610.00 2,641.67 1,885.87 3,441.67 982.53 2,250.07 2, 450.87 3,211.67 2,575.87 3,680.07 36,357.001 #4580-SD Fee Income 0.00 521.471 226.67 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.001 2,666.67 #4605-Misc. 0.00 0.00 0.00 .00 0.00 213,454.73 206,443.40 219,093.40 2,569,858.471 22, 606.60 22,097.60 20,851.60 19,311.87 22, 135.13 32,936.73 21, 177.07 280, 111.001 169,759.26 5,752.67 9,035.60 1,654.20 7,409.07 3,548.13 8,184.80 22,985.93 5,918.87 3,965.40 115,268.131 61, 111.11 #506X-Maint. Contract 10.00 0.00 0.00 0.00 0.00 714.60 6,000.00 714.60 0.00 7,407.41 #510X-Painting 0.00 #548X-Snow Removal 4,320.00 .00 0.00 0.00 0/00 0.001 11,993.07 0.00 0.00 0.00 0.00 9, 195.13 32,274.401 #508X-Grounds 534.27 17,594.07 23,906.071 8,518.52 38, 138.67 12, 198.53 16,041.67 Sub-Tot Oper. & Maint. 45, 125.00 82,365.33 48,007.20 45,441.53 542, 782.671 520,824.07 #501X-Electric Utility 3,599.53 3,227.40 3,710.73 1,749.40 1,381.60 ,500.33 50,066.67 #5030-Water Utility 7,501.47 7,281.27 7,215.07 7,773.60 7,212.40 7,292.13 7,001.40 7,517.47 89,547.07 7,137.80 6,950.40 6,870.20 7,232.80 6,897.53 80,000.00 #502X-Gas Utility 0.00 0.00 0.00 0.00 0.00 0.00 .00 0.00 0.00 .00 0.00 #551X-Trash Utility 4,236.07 55,050.20 45,413.33 22,471.67 22,074.47 21,203.40 22,970.60 19,238.93 19,877.93 9,959.20 20,938.40 254,944.33 284,406.67 #5181-Site Office Payroll 7,832.80 7,832.80 7,832.80 7,832.80 7,832.80 7,832.80 7,832.80 7,832.80 7,832.80 7,832.80 7,832.80 7,832.80 93,993.33 #5430-Management Fee 22,026.67 22,026.67 22,026.67, 21,653.33 21,280.00 22,026.67 261, 706.67, 264,320.00 #5365-Project Auditing 13,666.67 13,666.67 0.00 0.00 0.00 0.00 0.00 13,666.67 13,666.67 54,666.67, #5364-Project Bkping. 10,260.53 #5366-Project Legal 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00, 0.00 #529X-Advertising 1,674.67, 0.00 8,333.33 1,335.47 1,352.00 1,350.00 1,350.00, 1,351.73 #540X-Office Supplies 858.60 2,011.07 1,057.20, 592.67 995.00 2,667.27 1, 691.13 16,666.67 #545X-Training 166.67 0.00 0.00 0.00 0.00 0.00 0.00 #525X-Emplenefits 0.00 0.00 0.00 0.00 3,598.93 3,598.93 6, 666.67 #5182-Maint. Payrl. Taxes 2,212.93 2, 150.40 2,082.07 27, 194.07, #5220-Maint. Wkrs. Comp. 1, 144.00 1,096.20 1,022.40, 1, 107.67 1, 125.27 16,226.67 #541X-Other Admin. Exp. 413.27 28,979.27, 40,000.00 61, 143.20 664,967.87, 666,020.00 #547X-Real Estate Taxes 75,326.80 0.00 227,053.33 9,966.97 9,966.97 9,966.97 9,966.97 119,603.60#506X-Maint. Contract 0.00 0.00 0.00 2,483.27 0.00 0.00 6,000.00 714.60 0.00 10, 722.471 0.00 #548X-Snow Removal 0.00 0.00 0.00 0.00 6,296.30 0.0 9.195.13 Pay37,037.04 0.00 17,594.07 0.00 1,685.60 493.33 #11XX-Cap. Exp. Oper. 0.00 0.00 0.00 38, 138.67 12, 198.53 0.00 16,041.67 0.00 80,500.60! 230,694.44 Sub-Tot Oper. & Maint 48,007.20 45,441.53 #501X-Electric Utility 3,710.73 1,749.40 7,215.07 7,773.60 1,381.60 1,928.73 1,004.80 1,500.33 2, 129.00 1,702.53 2, 152.67 8,426.33 7,485.40 7,212.40 50,066.67 7,001.40 7,911.27 7,517.47 26,805.13 7,137.80 6,950.40 6,894.00 7,436.13 6,870.20 6,396.60 6, 705.60 6,458.07 89,547.07 #502X-Gas Utility 6,637.73 6,897.53 0.00 83,541.93 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 4,236.67 22,074.47 4,236.07 7,716.00 4,384.00 4,379.87 4,369.67 Sub-Total Utility 22,471.67 21,695.7. 23,324.47 45,413.33 21,203.40 22,970.60 19,959.20 20,938.4 254,944.33 #5181-Site Office Payroll #5430-Management Fee 22, 026.67 22,026.67 22,026.67 22,026.67 21,280.00 21,653.33 #5365-Project Auditing 22,026.67 261, 706.67 0.00 264,320.00 0.00 0.00 0.00 0.00 0.00 0.00 #5364-Project Bkping. 10,260.53 10,260.53 10,260.53 10,260.53 10,260.53, 10,260.53 10,260.53 10,260.53 #5366-Project Legal 10,260.53 123, 126.40, 0.OC 0.00 ).00 0.00 0.00 0.00 0.00 .00 0.00 ).0 0.00 #529X-Advertising 0.00 0.00 0.00 #546X-Project Phones 1,352.00 1,674.67, 1,335.47 1,471.33 1,350.00 1,350.00 1,351.73 1,351.73 8,333.33 1,351.73 1,356.53 1,349.93 #540X-Office Supplies 2,011.07 1,316.20 592.67 16,268.20 2,667.27 1,691.13 11,933.33 1,869.33 #545X-Training 166.67 18,089.67, 0.00 766.67 0.00 0.00 #525X-Emplenefits 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 5,398.20 #5182-Maint. Payrl. Taxes 3,598.87 2,212.93 2, 150.40 1,921.87, #5220-Maint. Wkrs. Comp. 1, 177.20 2, 153.07 2,082.07 1, 145.40 3,243.67 27, 194.07, , 096.20 1,528.87 1, 107.67 2,080.00 1,754.80 2,269.27 30,086.67 #541X-Other Admin. Exp. , 633.87 413.27 413.27 8,518.67 Sub-Total Administration 61, 143.20 47,050.67 50, 792.53 49,213.73 50,594.27 64,411.47 28,979.27 #547X-Real Estate Taxes 0.00 89,588.53! 0.00 0.00 #5455-Property Insurance 227,053.33 9,966.97 Sub-Total R.E. Tax & Ins. 9,966.97 9,966.97 9,555.50! 23, 100.00 ,966.97 72,051.03 9,966.97 9,966.97 ,966.97 346,603.00! 350,153.33 Tenant Utility & Overage Total Operating Expense 22,833.30 130,604.17 212,559.83 160,784.97 190,530.43 1,811,984.531 13821,404.07 #1308-Capital expenditures 7,433.33 7,433.33 89,200.00 Net Operating Inc. 4,476.43 90,644.03 73,690.83 -7,087.30 106, 754.90 39,182.50 67,019.63 74,441.10 668,673.93 628,375.93 12,000.00 12,247.12 12,370.68 12,494.24 12,617.80 12,864.92 12,988.48 13, 112.04 13,359.16 152, 154.96, 152, 154.96 6,959.07 6,711.95 6,588.39 6,464.83 6,341.27, 6,094.15 5,970.59 5,599.91 75,353.84, 75,353.84 43,568.36 Net Income 67,497.94 56,850.81 68, 158.61 51,205.41 30,574.95 34,269.47 14,737.45 -10,478.33 15,694.85 44,534.21 357,298.77