How would you implement this into an excel file?
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. Property Description 1. 50,000 square feet (sf) fully leased single-tenant office building. 2. A good-credit tenant (not a publicly traded corporation) signed a 10-year net lease three years ago (meaning this lease terminates at the end of Year 7) and the assumption is that said tenant, if they renew will renew at the projected market rent level seen in the market at that time. 3. Existing Lease: The lease has current net rent of $11/ sf per year (annual payments at ends of years). with step-up provisions to $11.50 in Year 2 through 4 , and $12.00 in Years 5 through 7. Beginning in Year 8 , since a new lease will be in place (either from the existing tenant or a new tenant), rent is adjusted to the level of the market which in Year 8 then stays at that level through Year 10. 4. Market Rent: Current rents in the relevant space market are $12/sf for new 10 -year leases (with no concessions), and are expected to grow at a rate of 2% per year (e.g., they will be $12.24/ sf per \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|} \hline rear & \begin{tabular}{l} Market Rent \\ (net)/st \end{tabular} & \begin{tabular}{l} Property reat \\ (net) / sf \end{tabular} & \begin{tabular}{l} Vacancy \\ lets/st \end{tabular} & NOI/SF & NO1 & \begin{tabular}{l} Lease \\ Commision \end{tabular} & \begin{tabular}{l} Tenant \\ Improvemert \end{tabular} & \begin{tabular}{l} Sale \\ Proceedt \end{tabular} & parce & on & Debtsentice & cerce. & & BER \\ \hline 1 & & 1100 & 5 & & & 5 & s & 5 & & 5 & & & & \\ \hline 2 & & 1150 & s & & & 5 & 5 & 5 & & 8= & & & & \\ \hline 3 & & & 5 & & & & 5 & 3 & & s. & & & & \\ \hline 4 & & & & & & 5 & 5 & 5 & & 5 . & & & & \\ \hline 5 & & & = & & & 5 & 5 & 5 & & s. & & & & \\ \hline 6. & & & s. & & & 5 & 5 & 5 & & 5. & & & & \\ \hline 7 & & & 5 & & & 5 & 5 & s & & 5 . & & & & \\ \hline a & & & & & & & & 5 & & 5 & & & & \\ \hline 9 & & & - & & & 5 & 5 & 5 & & 5. & & & & \\ \hline 10 & & & 5. & & & s & 5 & & & & & & & \\ \hline \end{tabular} 4. Market Rent: Current rents in the relevant space market are $12/sf for new 10 -year leases (with no concessions), and are expected to grow at a rate of 2% per year (e.g., they will be $12.24/sf per year in Year 1 which equals 1.0212. 5. Vacancy: There is a 75% chance the tenant renews and a 25% chance the tenant does not renew. In the case that the tenant does not renew, you expect 3 month vacancy period occurring within Year 8 before a new tenant arrives implying a vacancy loss per sf for the year equal to (.25)(3/12)(X) where X denotes Property rent (net)/sf per year in Year 8. 6. Lease Commission: You expect there to be a $2/sf leasing commission fee associated with the tenant renewing in Year 8 (which is paid for in entirety Year 8 which thus decreases Year 8 NOI) and $5/sf in leasing commission fees if the tenant does not renew. Hence, there is an expected [.75(2)+ .25(5)]50,000=$137,500 cost expected to be incurred in Year 8. 7. Tenant Improvements: You also project tenant improvement expenditures associated with the lease renewal in the amount of $10/sf if the tenant renews but $20/sf if a new tenant moves in Year 8. Since a new tenant moving in happens with a 25% chance, expected tenant improvement expenditures are [.75(10)+.25(20)]50,000=$625,000; a cost expected to be incurred in Year 8
