Create a spreadsheet that answers the questions below. Use the template that I have created. Make sure that you include all the formulas used in the analysis. Conduct all your analysis in a single Excel file. Submit the Excel file for grading. Make sure you name your file with your name. To complete the project, upload your files under the "Excel Project 5 folder in OAKs. Screening Memo - Medical Office in Goodtown, USA The loan request is for a $5 million loan to refinance existing debt on the two properties. The two properties are a 10,000 sf medical office building and a 22,875 sf medical office building. Both properties are located in Gogdtown, USA. The market information is in the CoStar reports attached. The borrower will be an LLC owned by Joseph Smith. The sponsor has owned the properties since 1995. The loan request is for a 10 -year loan with a 25 -year amortization schedule. The loan will have a 6.5% rate. Prepayment protection will be yield maintenance. The loan will be structured to the release of the smaller property subject to a paydown. The properties are currently 100% occupled by four tenants. The sponsor is one of the tenants and occupies 4,647 sf. See the rent roll below for the other three tenants. The properties historical operating statements are shown in the tables below. The property will be self-managed by the sponsor, Mr. Smith. Your firm has the following underwriting guidelines. Minimum NCF DSCR of 1.5 based on a 30 year amortization schedule and maximum LTV of 70%. The sponsor is attorney. His net worth (exclusive of these properties) and liquidity are $5.5 million and $500,000, respectively. Questions: 1. The loan rate is currently quoted at 6.5%. Should we adjust the rate? What factors should you consider when setting the rate? 2. Develop normalized capital expenditures for the property, that is, calculate the normalized Tis and LCs. 3. Should we make the $5 million loan? If not, how much should we lend? Discuss your reasoning. 4. Are there any conditions that you would impose to reduce the risk of the transaction? Explain your answer. 5. How much should we require to be paid off related to the release of the smaller property? Are there any other conditions that you would impose to reduce our risk associated with the release of the smaller property. Market data Rental comps - The properties are located in Goodtown, The Goodtown MSA medical office market includes 1.7 million square feet across 97 buildings. The market is currently 91% occupied, and the occupancy has ranged from 92% to 80% over the past 10 years. - Based on discussions with market participants and appraisers, you have determined the market rent on medical office is currently \$18 NNN. Rental comps - The properties are located in Ggedtown. The Goedtown MSA medical office market includes 1.7 million square feet across 97 buildings. The market is currently 91% occupied, and the occupancy has ranged from 92% to 80% over the past 10 years. - Based on discussions with market participants and appraisers, you have determined the market rent on medical office is currently $18NNN. - With respect to capital expenditures, appraisers are-assuming 2% leasing commissions for new leases and 1% for renewals. They are also assuming $30 in Tis for new leases and $10 for renewals. New leases are assumed to be 10 -year leases. Also, the renewal probability is assumed to be 70%. Sales comps - Recent discussions with appraisers and other market participants suggest a capitalization rate of 8.5% on comparable properties. Underwriting Assumptions - The property condition report suggests using $0.35 pst for replacement reserves. - Discussions with market participants suggest that the standard management fee in this market is 5% of EGI for small medical office buildings. - The current tax bill is $110,000. The current insurance bill is $15,000. Other expenses are estimated from the actual expenses of 2022 plus 3%. Araner the quesion belos. Create a spreadsheet that answers the questions below. Use the template that I have created. Make sure that you include all the formulas used in the analysis. Conduct all your analysis in a single Excel file. Submit the Excel file for grading. Make sure you name your file with your name. To complete the project, upload your files under the "Excel Project 5 folder in OAKs. Screening Memo - Medical Office in Goodtown, USA The loan request is for a $5 million loan to refinance existing debt on the two properties. The two properties are a 10,000 sf medical office building and a 22,875 sf medical office building. Both properties are located in Gogdtown, USA. The market information is in the CoStar reports attached. The borrower will be an LLC owned by Joseph Smith. The sponsor has owned the properties since 1995. The loan request is for a 10 -year loan with a 25 -year amortization schedule. The loan will have a 6.5% rate. Prepayment protection will be yield maintenance. The loan will be structured to the release of the smaller property subject to a paydown. The properties are currently 100% occupled by four tenants. The sponsor is one of the tenants and occupies 4,647 sf. See the rent roll below for the other three tenants. The properties historical operating statements are shown in the tables below. The property will be self-managed by the sponsor, Mr. Smith. Your firm has the following underwriting guidelines. Minimum NCF DSCR of 1.5 based on a 30 year amortization schedule and maximum LTV of 70%. The sponsor is attorney. His net worth (exclusive of these properties) and liquidity are $5.5 million and $500,000, respectively. Questions: 1. The loan rate is currently quoted at 6.5%. Should we adjust the rate? What factors should you consider when setting the rate? 2. Develop normalized capital expenditures for the property, that is, calculate the normalized Tis and LCs. 3. Should we make the $5 million loan? If not, how much should we lend? Discuss your reasoning. 4. Are there any conditions that you would impose to reduce the risk of the transaction? Explain your answer. 5. How much should we require to be paid off related to the release of the smaller property? Are there any other conditions that you would impose to reduce our risk associated with the release of the smaller property. Market data Rental comps - The properties are located in Goodtown, The Goodtown MSA medical office market includes 1.7 million square feet across 97 buildings. The market is currently 91% occupied, and the occupancy has ranged from 92% to 80% over the past 10 years. - Based on discussions with market participants and appraisers, you have determined the market rent on medical office is currently \$18 NNN. Rental comps - The properties are located in Ggedtown. The Goedtown MSA medical office market includes 1.7 million square feet across 97 buildings. The market is currently 91% occupied, and the occupancy has ranged from 92% to 80% over the past 10 years. - Based on discussions with market participants and appraisers, you have determined the market rent on medical office is currently $18NNN. - With respect to capital expenditures, appraisers are-assuming 2% leasing commissions for new leases and 1% for renewals. They are also assuming $30 in Tis for new leases and $10 for renewals. New leases are assumed to be 10 -year leases. Also, the renewal probability is assumed to be 70%. Sales comps - Recent discussions with appraisers and other market participants suggest a capitalization rate of 8.5% on comparable properties. Underwriting Assumptions - The property condition report suggests using $0.35 pst for replacement reserves. - Discussions with market participants suggest that the standard management fee in this market is 5% of EGI for small medical office buildings. - The current tax bill is $110,000. The current insurance bill is $15,000. Other expenses are estimated from the actual expenses of 2022 plus 3%. Araner the quesion belos