how to input this into an excel sheet with

Market Rent

Property Rent

Vacancy loss

Noi/SF

Noi

Lease Commission

Tenant Improvement

Sale Proceedes

PBTCF

OLB

Debt Service

ebtcf

DCR

BER

Consider the possible purchase of a fully operational 50,000 square foot single-tenant office building. A buyer is seeking a $4,583,500 loan in order to purchase the property for $6,111,000. The buyer wishes to obtain a 10 -year, partially amortizing nonrecourse loan from you, the lender, that would include a $4,083,250 balloon payment due at the end of Year 10. Your job is to decide whether or not the loan satisfies your underwriting guidelines. As a starting point, you took a careful look at what is currently going on in the capital markets to get a sense of what contract interest rates are typically being charged for similar loans. Based on the results of your analysis, you determined that you are willing to lend money out at a rate of 7.67% (interest to be computed monthly at .0767/12, payments to be made monthly). Your underwriting criteria are as follows: Underwriting Criteria 1. Maximum ILTV less than or equal to 75%. 2. Maximum projected terminal LTV ("TLTV") less than or equal to 65%. 3. In computing ILTV and TLTV apply direct capitalization with an initial cap rate of 9% for Year 1. and a terminal cap rate of 10% for Year 10 . 4. Minimum DCR of 1.2 in all years. 5. Maximum BER of 85% for the Year 1. 6. Consider the need for capital improvements and avoid negative EBTCF Flag the property if EBTCF is negative in any given year