Please answer all the question and calculate in excel and show me the formula. This is the assignment and will also provide the tamplate in how it should look. Bridge Loan$/$Transition Loans Earnout$/$Re$-$margin Examples

ABC Borrower can borrow up to $$50$ million under a bridge loan facility from your fund that pays interest$-$only at LIBOR $+450$ bps $($current coupon of $9\mathrm{.}9\%)$ to fund the remaining lease$-$up of a $250,000$sf office building that they recently acquired. Answer the questions below following each assumption.

A$.$ The project was $60\%$ leased at closing and the lender funded $$30$ million with a DSCR of $1\mathrm{.}40$X$.$ The lender will fund the remaining loan on a pro rata basis as space is leased provided coverage remains the same. The borrower anticipates leasing up the remaining space evenly over the next eight months while maintaining the same coverage.

$1.$ How many square feet per month does the developer need to lease to reach full occupancy in eight months?

$2.$ What are the draws assuming the borrower$$s projections?

$3.$ What is the NOI$/$square foot at closing for the space leased?

$4.$ What is the NOI upon full lease up assuming the same coverage?

B$.$ The project was $60\%$ leased at closing and the lender funded $$30$ million with a DSCR of $1\mathrm{.}10$X$.$ The lender has agreed to fund the remaining loan in increments of $$5,000,000$ provided DSCR increases as additional space is leased. At $70\%$ leased, the lender is requiring DSCR of $1\mathrm{.}20$X; at $80\%$ leased, the lender is requiring DSCR of $1\mathrm{.}30$X; and, for $90\%$ leased, the lender is requiring DSCR of $1\mathrm{.}40$X$.$ The lender will fund the remaining loan as each additional $25,000$ square feet is leased. The lender will disburse all funds at $95\%$ leased with DSCR of $1\mathrm{.}45$X$.$ In order to expedite leasing, the borrower is leasing the space at a lower rental rate. Therefore, DSCR at $70\%,80\%$ and $90\%$ leased is projected to be $1\mathrm{.}15$X$,1\mathrm{.}20$X and $1\mathrm{.}25$X$,$ respectively.

$1.$ What is the required NOI assuming the loan is funded pro rata and the lender$$s required DSCR is obtained?

$2.$ Given the loan is funded in increments of $$5,000,000,$ what is the actual NOI given the DSCR$?$

$3.$ Assuming a shortfall in NOI, how much would the loan be adjusted?