Multifamily Lending Opportunity Assessment

Acquisition_Proforma.xlsx

Excel·32 KB

write me an agent, and generate an excel model template, that can structure and assess lending opportunities for multifamily properties. Use whatever templates you need to as a base, such as the one attached. consider senior, bridge, mezzanine loans. Consider what data is available in the RealAI datamart and what data you will need to request from the user on a typical run. You can get this from them in an ask user form. Look at other agents in RealAI for examples of how this is done for acquisitions and other scenarios.

Sep 11

Content

206 lines

#!/usr/bin/env python3
"""
Build RealAI_MF_Lending_Template.xlsx
Multifamily lending / capital-stack underwriting template: Senior, Bridge, Mezzanine.
"""
import openpyxl
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

# ---------- style helpers ----------
FONT_NAME = "Arial"
BLUE = "0000FF"      # hardcoded input
BLACK = "000000"     # formula
GREEN = "008000"     # cross-sheet link
HEADER_FILL = PatternFill("solid", fgColor="1F4E78")
SUBHEADER_FILL = PatternFill("solid", fgColor="D9E1F2")
TITLE_FILL = PatternFill("solid", fgColor="1F4E78")
YELLOW_FILL = PatternFill("solid", fgColor="FFFF00")
THIN = Side(style="thin", color="B7B7B7")
BORDER = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
TOPBORDER = Border(top=Side(style="thin", color="000000"))

def f_input(bold=False):
    return Font(name=FONT_NAME, size=10, color=BLUE, bold=bold)

def f_formula(bold=False):
    return Font(name=FONT_NAME, size=10, color=BLACK, bold=bold)

def f_link(bold=False):
    return Font(name=FONT_NAME, size=10, color=GREEN, bold=bold)

def f_label(bold=False, size=10, italic=False):
    return Font(name=FONT_NAME, size=size, color="000000", bold=bold, italic=italic)

def f_title():
    return Font(name=FONT_NAME, size=14, color="FFFFFF", bold=True)

def f_sub():
    return Font(name=FONT_NAME, size=11, color="1F4E78", bold=True)

CUR = '$#,##0;($#,##0);"-"'
CUR2 = '$#,##0.00;($#,##0.00);"-"'
PCT1 = '0.0%;(0.0%);"-"'
PCT2 = '0.00%;(0.00%);"-"'
MULT = '0.00"x"'
NUM0 = '#,##0;(#,##0);"-"'

def set_title(ws, text, span=8):
    ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=span)
    c = ws.cell(row=1, column=1, value=text)
    c.font = f_title()
    c.fill = TITLE_FILL
    c.alignment = Alignment(vertical="center", horizontal="left", indent=1)
    ws.row_dimensions[1].height = 26

def sub(ws, row, text, span=8, col=1):
    ws.merge_cells(start_row=row, start_column=col, end_row=row, end_column=col+span-1)
    c = ws.cell(row=row, column=col, value=text)
    c.font = f_sub()
    c.fill = SUBHEADER_FILL
    c.alignment = Alignment(vertical="center", indent=1)
    ws.row_dimensions[row].height = 18
    return row + 1

def label(ws, row, col, text, bold=False, italic=False, size=10):
    c = ws.cell(row=row, column=col, value=text)
    c.font = f_label(bold=bold, size=size, italic=italic)
    return c

def header_row(ws, row, col_start, headers):
    for i, h in enumerate(headers):
        c = ws.cell(row=row, column=col_start + i, value=h)
        c.font = Font(name=FONT_NAME, size=10, color="FFFFFF", bold=True)
        c.fill = HEADER_FILL
        c.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
    ws.row_dimensions[row].height = 30

def total_row_style(ws, row, col_start, col_end):
    for col in range(col_start, col_end + 1):
        ws.cell(row=row, column=col).border = TOPBORDER
        ws.cell(row=row, column=col).font = Font(name=FONT_NAME, size=10, bold=True)

wb = Workbook()
wb.remove(wb.active)

# =====================================================================
# SHEET 1: ASSUMPTIONS
# =====================================================================
ws = wb.create_sheet("Assumptions")
ws.sheet_view.showGridLines = False
ws.column_dimensions["A"].width = 2
ws.column_dimensions["B"].width = 34
ws.column_dimensions["C"].width = 16
ws.column_dimensions["D"].width = 16
ws.column_dimensions["E"].width = 4
ws.column_dimensions["F"].width = 34
ws.column_dimensions["G"].width = 16
ws.column_dimensions["H"].width = 16
set_title(ws, "MULTIFAMILY LENDING MODEL  |  ASSUMPTIONS & MARKET INPUTS", span=8)

r = 3
r = sub(ws, r, "DEAL IDENTIFICATION", span=3)
fields1 = [
    ("Property Name", ""),
    ("Property Address", ""),
    ("Market / MSA", ""),
    ("Total Units", 0),
    ("Year Built", 0),
    ("Analysis / As-Of Date", "=TODAY()"),
    ("Loan Purpose (Acquisition/Refi/Recap)", "Acquisition"),
]
start_ident = r
for name, default in fields1:
    label(ws, r, 2, name)
    cell = ws.cell(row=r, column=3, value=default)
    if isinstance(default, str) and default.startswith("="):
        cell.font = f_formula(); cell.number_format = "mm/dd/yyyy"
    else:
        cell.font = f_input()
        if name == "Total Units" or name == "Year Built":
            cell.number_format = NUM0
    cell.border = BORDER
    r += 1
r += 1

r = sub(ws, r, "RATE ENVIRONMENT & MARKET PRICING", span=3)
rate_rows = {}
rate_fields = [
    ("10-Year Treasury Yield", 0.042, PCT2),
    ("Current SOFR (1-Month)", 0.043, PCT2),
    ("Prime Rate", 0.075, PCT2),
    ("Market Cap Rate (subject asset class)", 0.055, PCT2),
    ("Assumed NOI / Rent Growth Rate (annual)", 0.03, PCT1),
    ("Assumed Expense Growth Rate (annual)", 0.03, PCT1),
    ("Assumed Exit Cap Rate (at maturity/sale)", 0.058, PCT2),
    ("Lender Benchmark OpEx Ratio (% of EGI)", 0.42, PCT1),
]
for name, default, fmt in rate_fields:
    label(ws, r, 2, name)
    cell = ws.cell(row=r, column=3, value=default)
    cell.font = f_input(); cell.number_format = fmt; cell.border = BORDER
    rate_rows[name] = r
    r += 1
r += 1

r = sub(ws, r, "STRESS TEST SHOCKS", span=3)
stress_fields = [
    ("Rate Shock at Refi/Maturity (bps)", 0.005, PCT2),
    ("Cap Rate Shock at Exit/Sale (bps)", 0.005, PCT2),
    ("Vacancy Floor (lender case, min.)", 0.05, PCT1),
    ("Other Income Haircut (lender case)", 0.10, PCT1),
]
stress_rows = {}
for name, default, fmt in stress_fields:
    label(ws, r, 2, name)
    cell = ws.cell(row=r, column=3, value=default)
    cell.font = f_input(); cell.number_format = fmt; cell.border = BORDER
    stress_rows[name] = r
    r += 1

# Right column: Lender Credit Box
r2 = 3
r2 = sub(ws, r2, "LENDER CREDIT BOX (FLOORS / CEILINGS)", span=3, col=6)
box_fields = [
    ("Max Senior LTC (%)", 0.65, PCT1),
    ("Max Senior LTV (%)", 0.65, PCT1),
    ("Max Cumulative LTC incl. Mezz (%)", 0.80, PCT1),
    ("Max Cumulative LTV incl. Mezz (%)", 0.80, PCT1),
    ("Min Senior DSCR (x)", 1.25, MULT),
    ("Min Total (Stacked) DSCR (x)", 1.15, MULT),
    ("Min Senior Debt Yield (%)", 0.095, PCT1),
    ("Min Total Debt Yield (%)", 0.085, PCT1),
    ("Max Bridge LTC (as-is, %)", 0.70, PCT1),
    ("Max Bridge LTC incl. Future Funding (%)", 0.75, PCT1),
]
box_rows = {}
for name, default, fmt in box_fields:
    label(ws, r2, 6, name)
    cell = ws.cell(row=r2, column=7, value=default)
    cell.font = f_input(); cell.number_format = fmt; cell.border = BORDER
    box_rows[name] = r2
    r2 += 1
r2 += 1
r2 = sub(ws, r2, "PROPERTY VALUATION INPUTS", span=3, col=6)
val_fields = [
    ("Purchase Price / Loan Basis ($)", 20000000, CUR),
    ("Appraised / As-Is Value ($)", 20000000, CUR),
    ("Total Project Cost incl. CapEx ($)", "=Assumptions!G%d" % (r2), CUR),  # placeholder fixed below
]
val_rows = {}
for name, default, fmt in val_fields:
    label(ws, r2, 6, name)
    cell = ws.cell(row=r2, column=7, value=default if not (isinstance(default,str)) else 0)
    cell.font = f_input(); cell.number_format = fmt; cell.border = BORDER
    val_rows[name] = r2
    r2 += 1
# fix third row to be a link placeholder text instead (computed on Sources & Uses instead)
ws.cell(row=val_rows["Total Project Cost incl. CapEx ($)"], column=7).value = None
ws.cell(row=val_rows["Total Project Cost incl. CapEx ($)"], column=7, value="See Sources & Uses")
ws.cell(row=val_rows["Total Project Cost incl. CapEx ($)"], column=7).font = Font(name=FONT_NAME, size=9, italic=True, color="808080")
ws.cell(row=val_rows["Total Project Cost incl. CapEx ($)"], column=7).number_format = "General"

wb.save("output/RealAI_MF_Lending_Template.xlsx")
print("Assumptions rows:", start_ident, rate_rows, stress_rows, box_rows, val_rows)

Content

187 lines

#!/usr/bin/env python3
"""
Build RealAI_MF_Lending_Template.xlsx
Multifamily lending / capital-stack underwriting template: Senior, Bridge, Mezzanine.
Sheets: Assumptions, Deal Summary, Property & NOI, Sources & Uses,
        Capital Stack, Debt Service Schedule, Sensitivity, Recovery & Exit
"""
import openpyxl
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

FONT_NAME = "Arial"
BLUE, BLACK, GREEN = "0000FF", "000000", "008000"
HEADER_FILL = PatternFill("solid", fgColor="1F4E78")
SUBHEADER_FILL = PatternFill("solid", fgColor="D9E1F2")
YELLOW_FILL = PatternFill("solid", fgColor="FFFF99")
THIN = Side(style="thin", color="B7B7B7")
BORDER = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
TOPBORDER = Border(top=Side(style="thin", color="000000"))

def f_input(bold=False, size=10):
    return Font(name=FONT_NAME, size=size, color=BLUE, bold=bold)
def f_formula(bold=False, size=10):
    return Font(name=FONT_NAME, size=size, color=BLACK, bold=bold)
def f_link(bold=False, size=10):
    return Font(name=FONT_NAME, size=size, color=GREEN, bold=bold)
def f_label(bold=False, size=10, italic=False):
    return Font(name=FONT_NAME, size=size, color="000000", bold=bold, italic=italic)
def f_title():
    return Font(name=FONT_NAME, size=14, color="FFFFFF", bold=True)
def f_sub():
    return Font(name=FONT_NAME, size=11, color="1F4E78", bold=True)

CUR = '$#,##0;($#,##0);"-"'
CUR2 = '$#,##0.00;($#,##0.00);"-"'
PCT1 = '0.0%;(0.0%);"-"'
PCT2 = '0.00%;(0.00%);"-"'
MULT = '0.00"x"'
NUM0 = '#,##0;(#,##0);"-"'

def set_title(ws, text, span=8):
    ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=span)
    c = ws.cell(row=1, column=1, value=text)
    c.font = f_title(); c.fill = HEADER_FILL
    c.alignment = Alignment(vertical="center", horizontal="left", indent=1)
    ws.row_dimensions[1].height = 26

def sub(ws, row, text, span=8, col=1):
    ws.merge_cells(start_row=row, start_column=col, end_row=row, end_column=col+span-1)
    c = ws.cell(row=row, column=col, value=text)
    c.font = f_sub(); c.fill = SUBHEADER_FILL
    c.alignment = Alignment(vertical="center", indent=1)
    ws.row_dimensions[row].height = 18
    return row + 1

def label(ws, row, col, text, bold=False, italic=False, size=10):
    c = ws.cell(row=row, column=col, value=text)
    c.font = f_label(bold=bold, size=size, italic=italic)
    return c

def header_row(ws, row, col_start, headers, height=28):
    for i, h in enumerate(headers):
        c = ws.cell(row=row, column=col_start + i, value=h)
        c.font = Font(name=FONT_NAME, size=10, color="FFFFFF", bold=True)
        c.fill = HEADER_FILL
        c.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
    ws.row_dimensions[row].height = height

def style_total(ws, row, col_start, col_end):
    for col in range(col_start, col_end + 1):
        cell = ws.cell(row=row, column=col)
        cell.border = TOPBORDER
        cell.font = Font(name=FONT_NAME, size=10, bold=True,
                          color=(cell.font.color.rgb if cell.font and cell.font.color else BLACK))

wb = Workbook()
wb.remove(wb.active)
REF = {}  # cross-sheet reference registry: key -> "'Sheet Name'!$X$Y"

def qref(sheet, cellref):
    return "'%s'!%s" % (sheet, cellref)

# =====================================================================
# SHEET 1: ASSUMPTIONS
# =====================================================================
SH = "Assumptions"
ws = wb.create_sheet(SH)
ws.sheet_view.showGridLines = False
for col, w in zip("ABCDEFGH", [2, 36, 15, 15, 4, 36, 15, 15]):
    ws.column_dimensions[col].width = w
set_title(ws, "MULTIFAMILY LENDING MODEL  |  ASSUMPTIONS & MARKET INPUTS")

r = 3
r = sub(ws, r, "DEAL IDENTIFICATION", span=3)
ident = [
    ("Property Name", "Subject Property", "General"),
    ("Property Address", "123 Main Street, Anytown, ST", "General"),
    ("Market / MSA", "Market MSA Name", "General"),
    ("Total Units", 200, NUM0),
    ("Year Built", 2005, NUM0),
    ("Loan Purpose", "Acquisition", "General"),
]
for name, default, fmt in ident:
    label(ws, r, 2, name)
    c = ws.cell(row=r, column=3, value=default)
    c.font = f_input(); c.number_format = fmt; c.border = BORDER
    REF[name] = qref(SH, "$C$%d" % r)
    r += 1
label(ws, r, 2, "Analysis / As-Of Date")
c = ws.cell(row=r, column=3, value="=TODAY()")
c.font = f_formula(); c.number_format = "mm/dd/yyyy"; c.border = BORDER
REF["Analysis Date"] = qref(SH, "$C$%d" % r)
r += 2

r = sub(ws, r, "RATE ENVIRONMENT & MARKET PRICING", span=3)
rate_fields = [
    ("10-Year Treasury Yield", 0.042, PCT2),
    ("Current SOFR (1-Month)", 0.043, PCT2),
    ("Prime Rate", 0.075, PCT2),
    ("Market Cap Rate (subject asset class)", 0.055, PCT2),
    ("NOI / Rent Growth Rate (annual)", 0.03, PCT1),
    ("Expense Growth Rate (annual)", 0.03, PCT1),
    ("Exit Cap Rate (maturity/sale)", 0.058, PCT2),
    ("Lender Benchmark OpEx Ratio (% of EGI)", 0.42, PCT1),
]
for name, default, fmt in rate_fields:
    label(ws, r, 2, name)
    c = ws.cell(row=r, column=3, value=default)
    c.font = f_input(); c.number_format = fmt; c.border = BORDER
    REF[name] = qref(SH, "$C$%d" % r)
    r += 1
r += 1

r = sub(ws, r, "STRESS TEST SHOCKS", span=3)
stress_fields = [
    ("Rate Shock at Refi/Maturity", 0.005, PCT2),
    ("Cap Rate Shock at Exit/Sale", 0.005, PCT2),
    ("Vacancy Floor (lender case, min.)", 0.05, PCT1),
    ("Other Income Haircut (lender case)", 0.10, PCT1),
]
for name, default, fmt in stress_fields:
    label(ws, r, 2, name)
    c = ws.cell(row=r, column=3, value=default)
    c.font = f_input(); c.number_format = fmt; c.border = BORDER
    REF[name] = qref(SH, "$C$%d" % r)
    r += 1

r2 = 3
r2 = sub(ws, r2, "LENDER CREDIT BOX (FLOORS / CEILINGS)", span=3, col=6)
box_fields = [
    ("Max Senior LTC", 0.65, PCT1),
    ("Max Senior LTV", 0.65, PCT1),
    ("Max Bridge LTC (as-is)", 0.70, PCT1),
    ("Max Bridge LTC incl. Future Funding", 0.75, PCT1),
    ("Max Cumulative LTC incl. Mezz", 0.80, PCT1),
    ("Max Cumulative LTV incl. Mezz", 0.80, PCT1),
    ("Min Senior/Bridge DSCR", 1.25, MULT),
    ("Min Total (Stacked) DSCR", 1.15, MULT),
    ("Min Senior/Bridge Debt Yield", 0.095, PCT1),
    ("Min Total Debt Yield", 0.085, PCT1),
]
for name, default, fmt in box_fields:
    label(ws, r2, 6, name)
    c = ws.cell(row=r2, column=7, value=default)
    c.font = f_input(); c.number_format = fmt; c.border = BORDER
    REF[name] = qref(SH, "$G$%d" % r2)
    r2 += 1
r2 += 1
r2 = sub(ws, r2, "PROPERTY VALUATION", span=3, col=6)
val_fields = [
    ("Purchase Price / Loan Basis", 20000000, CUR),
    ("Appraised / As-Is Value", 20000000, CUR),
    ("As-Stabilized / ARV Value (bridge only)", 23000000, CUR),
]
for name, default, fmt in val_fields:
    label(ws, r2, 6, name)
    c = ws.cell(row=r2, column=7, value=default)
    c.font = f_input(); c.number_format = fmt; c.border = BORDER
    REF[name] = qref(SH, "$G$%d" % r2)
    r2 += 1

ws.freeze_panes = "A2"

print("REF so far:", {k: v for k, v in REF.items()})
wb.save("output/RealAI_MF_Lending_Template.xlsx")

Sheet 1 built cleanly. Now building sheet 2 (Property & NOI).

Find

2 lines

print("REF so far:", {k: v for k, v in REF.items()})
wb.save("output/RealAI_MF_Lending_Template.xlsx")

Replace

138 lines

# =====================================================================
# SHEET 2: PROPERTY & NOI
# =====================================================================
SH = "Property & NOI"
ws = wb.create_sheet(SH)
ws.sheet_view.showGridLines = False
for col, w in zip("ABCDE", [2, 38, 18, 18, 40]):
    ws.column_dimensions[col].width = w
set_title(ws, "PROPERTY OPERATING STATEMENT  |  BORROWER T12 vs. LENDER CASE", span=5)

r = 3
header_row(ws, r, 2, ["Line Item", "Borrower T12\n(Actual)", "Lender Case\n(Underwritten)", "Lender Adjustment Basis"])
r += 1
noi_rows = {}

def line(ws, r, name, borrower_formula_or_input, lender_formula, note="", input_borrower=True, fmt=CUR, bold=False):
    label(ws, r, 2, name, bold=bold)
    cb = ws.cell(row=r, column=3, value=borrower_formula_or_input)
    cb.number_format = fmt
    cb.font = f_input(bold=bold) if input_borrower else f_formula(bold=bold)
    cb.border = BORDER
    cl = ws.cell(row=r, column=4, value=lender_formula)
    cl.number_format = fmt
    cl.font = f_formula(bold=bold)
    cl.border = BORDER
    cn = ws.cell(row=r, column=5, value=note)
    cn.font = f_label(italic=True, size=9)
    noi_rows[name] = r
    return r + 1

r = line(ws, r, "Units", "='%s'!$C$7" % "Assumptions", "='%s'!$C$7" % "Assumptions", "Linked from Assumptions", input_borrower=False, fmt=NUM0)
r = line(ws, r, "Gross Potential Rent (annual)", 2400000, "=C%d" % noi_rows["Gross Potential Rent (annual)"] if False else None)
# fix lender GPR formula reference properly below after row known
gpr_row = noi_rows["Gross Potential Rent (annual)"]
ws.cell(row=gpr_row, column=4, value="=C%d" % gpr_row)
r = line(ws, r, "Vacancy & Credit Loss ($)", -120000, None)
vac_row = noi_rows["Vacancy & Credit Loss ($)"]
ws.cell(row=vac_row, column=4,
        value="=-MAX(-C%d/C%d, %s)*D%d" % (vac_row, gpr_row, REF["Vacancy Floor (lender case, min.)"], gpr_row))
ws.cell(row=vac_row, column=5, value="Lender vacancy = MAX(borrower %, floor); applied to lender GPR")

r = line(ws, r, "Other Income (laundry, parking, fees, RUBS)", 96000, None)
oi_row = noi_rows["Other Income (laundry, parking, fees, RUBS)"]
ws.cell(row=oi_row, column=4, value="=C%d*(1-%s)" % (oi_row, REF["Other Income Haircut (lender case)"]))
ws.cell(row=oi_row, column=5, value="Lender haircut applied to borrower other income")

r = line(ws, r, "Effective Gross Income (EGI)",
         "=C%d+C%d+C%d" % (gpr_row, vac_row, oi_row),
         "=D%d+D%d+D%d" % (gpr_row, vac_row, oi_row),
         "= GPR + Vacancy + Other Income", input_borrower=False, bold=True)
egi_row = noi_rows["Effective Gross Income (EGI)"]
r += 1
r = sub(ws, r, "OPERATING EXPENSES", span=4)
opex_items = [
    ("Real Estate Taxes", 180000),
    ("Insurance", 60000),
    ("Utilities", 90000),
    ("Repairs & Maintenance", 110000),
    ("Payroll & Administrative", 220000),
    ("Management Fee (% of EGI)", 0.035),
    ("Replacement Reserves ($/unit/year)", 300),
]
opex_rows = {}
for name, default in opex_items:
    label(ws, r, 2, name)
    cb = ws.cell(row=r, column=3, value=default)
    cb.font = f_input(); cb.border = BORDER
    if "%" in name:
        cb.number_format = PCT2
        cl = ws.cell(row=r, column=4, value="=C%d*D%d" % (r, egi_row))
        cl.font = f_formula(); cl.number_format = CUR
    elif "$/unit" in name:
        cb.number_format = CUR2
        cl = ws.cell(row=r, column=4, value="=C%d*D%d" % (r, noi_rows["Units"]))
        cl.font = f_formula(); cl.number_format = CUR
    else:
        cb.number_format = CUR
        cl = ws.cell(row=r, column=4, value="=MAX(C%d,0)" % r)
        cl.font = f_formula(); cl.number_format = CUR
    cl.border = BORDER
    opex_rows[name] = r
    r += 1
# borrower total opex (sum of $ lines only, matching lender methodology on $ basis for borrower column display)
label(ws, r, 2, "Total Operating Expenses", bold=True)
borrower_opex_terms = []
for name, row_ in opex_rows.items():
    if name == "Management Fee (% of EGI)":
        borrower_opex_terms.append("C%d*C%d" % (row_, egi_row))
    elif "$/unit" in name:
        borrower_opex_terms.append("C%d*C%d" % (row_, noi_rows["Units"]))
    else:
        borrower_opex_terms.append("C%d" % row_)
cb = ws.cell(row=r, column=3, value="=" + "+".join(borrower_opex_terms))
cb.font = f_formula(bold=True); cb.number_format = CUR; cb.border = BORDER
cl = ws.cell(row=r, column=4, value="=SUM(D%d:D%d)" % (min(opex_rows.values()), max(opex_rows.values())))
cl.font = f_formula(bold=True); cl.number_format = CUR; cl.border = BORDER
opex_total_row = r
style_total(ws, r, 3, 4)
r += 1

label(ws, r, 2, "OpEx Ratio (% of EGI)")
ws.cell(row=r, column=3, value="=C%d/C%d" % (opex_total_row, egi_row)).font = f_formula()
ws.cell(row=r, column=4, value="=D%d/D%d" % (opex_total_row, egi_row)).font = f_formula()
ws.cell(row=r, column=3).number_format = PCT1; ws.cell(row=r, column=4).number_format = PCT1
ws.cell(row=r, column=5, value="Benchmark: see Assumptions lender OpEx ratio").font = f_label(italic=True, size=9)
r += 1

label(ws, r, 2, "NET OPERATING INCOME (NOI)", bold=True)
cb = ws.cell(row=r, column=3, value="=C%d-C%d" % (egi_row, opex_total_row))
cb.font = f_formula(bold=True); cb.number_format = CUR
cl = ws.cell(row=r, column=4, value="=D%d-D%d" % (egi_row, opex_total_row))
cl.font = f_formula(bold=True); cl.number_format = CUR
noi_row = r
style_total(ws, r, 3, 4)
r += 1
label(ws, r, 2, "NOI per Unit")
ws.cell(row=r, column=3, value="=C%d/C%d" % (noi_row, noi_rows["Units"])).font = f_formula()
ws.cell(row=r, column=4, value="=D%d/D%d" % (noi_row, noi_rows["Units"])).font = f_formula()
ws.cell(row=r, column=3).number_format = CUR2; ws.cell(row=r, column=4).number_format = CUR2
noiperunit_row = r
r += 1

label(ws, r, 2, "Implied Going-In Cap Rate (on Purchase Price)")
capin = ws.cell(row=r, column=4, value="=D%d/%s" % (noi_row, REF["Purchase Price / Loan Basis"]))
capin.font = f_link(); capin.number_format = PCT2
capin_row = r

REF["Lender NOI"] = qref(SH, "$D$%d" % noi_row)
REF["Borrower NOI"] = qref(SH, "$C$%d" % noi_row)
REF["Lender EGI"] = qref(SH, "$D$%d" % egi_row)
REF["Units"] = qref(SH, "$D$%d" % noi_rows["Units"])
REF["Lender OpEx Ratio"] = qref(SH, "$D$%d" % (r-3))
REF["Going-In Cap Rate"] = qref(SH, "$D$%d" % capin_row)

ws.freeze_panes = "C4"

print("Stage 2 done. NOI row=", noi_row)
wb.save("output/RealAI_MF_Lending_Template.xlsx")

Now Sheet 3 (Sources & Uses) and Sheet 4 (Capital Stack) — the core senior/bridge/mezz structure.

Find

2 lines

print("Stage 2 done. NOI row=", noi_row)
wb.save("output/RealAI_MF_Lending_Template.xlsx")

Replace

189 lines

# =====================================================================
# SHEET 4: CAPITAL STACK (built before Sources & Uses so S&U can link to it)
# =====================================================================
SH = "Capital Stack"
ws = wb.create_sheet(SH)
ws.sheet_view.showGridLines = False
for col, w in zip("AB CDE".replace(" ", ""), [2, 34, 17, 17, 17]):
    pass
ws.column_dimensions["A"].width = 2
ws.column_dimensions["B"].width = 34
for col in "CDE":
    ws.column_dimensions[col].width = 18
set_title(ws, "CAPITAL STACK  |  SENIOR / BRIDGE  +  MEZZANINE", span=4)

r = 3
header_row(ws, r, 2, ["Tranche Terms", "Senior / Bridge\n(First Lien)", "Mezzanine\n(2nd Lien / Pref Equity)", "Notes"])
r += 1

def stack_row(r, name, senior_val, mezz_val, note="", fmt=CUR, input_row=True):
    label(ws, r, 2, name)
    cs = ws.cell(row=r, column=3, value=senior_val)
    cm = ws.cell(row=r, column=4, value=mezz_val)
    for c in (cs, cm):
        c.font = f_input() if input_row else f_formula()
        c.number_format = fmt
        c.border = BORDER
    cn = ws.cell(row=r, column=5, value=note)
    cn.font = f_label(italic=True, size=9)
    return r + 1

stack = {}
stack['start'] = r
r = stack_row(r, "Loan Type (Senior/Bridge — select)", "Senior Perm", "Mezzanine", "Enter Senior Perm, Bridge, or leave Mezz blank if not used", fmt="General")
stack['type'] = r-1
r = stack_row(r, "Loan Amount", 12500000, 2000000, "Zero out Mezz row if not using a mezz piece")
stack['amount'] = r-1
r = stack_row(r, "Origination Fee (%)", 0.01, 0.02, fmt=PCT2)
stack['orig_fee_pct'] = r-1
label(ws, r, 2, "Origination Fee ($)")
for col, key in zip("CD", ["amount", "amount"]):
    pass
ws.cell(row=r, column=3, value="=C%d*C%d" % (stack['amount'], stack['orig_fee_pct'])).font=f_formula()
ws.cell(row=r, column=4, value="=D%d*D%d" % (stack['amount'], stack['orig_fee_pct'])).font=f_formula()
ws.cell(row=r, column=3).number_format=CUR; ws.cell(row=r, column=4).number_format=CUR
ws.cell(row=r,column=3).border=BORDER; ws.cell(row=r,column=4).border=BORDER
stack['orig_fee_usd'] = r
r += 1
r = stack_row(r, "Exit / Prepayment Fee (%)", 0.01, 0.01, fmt=PCT2)
stack['exit_fee_pct'] = r-1
r = stack_row(r, "Index", "SOFR", "Fixed", fmt="General")
stack['index'] = r-1
r = stack_row(r, "Spread over Index (bps, as decimal)", 0.028, 0.075, fmt=PCT2)
stack['spread'] = r-1
label(ws, r, 2, "All-In Interest Rate")
idx_ref_senior = "IF(C%d=\"SOFR\",%s,IF(C%d=\"Prime\",%s,IF(C%d=\"Treasury\",%s,0)))" % (
    stack['index'], REF["Current SOFR (1-Month)"], stack['index'], REF["Prime Rate"], stack['index'], REF["10-Year Treasury Yield"])
idx_ref_mezz = "IF(D%d=\"SOFR\",%s,IF(D%d=\"Prime\",%s,IF(D%d=\"Treasury\",%s,0)))" % (
    stack['index'], REF["Current SOFR (1-Month)"], stack['index'], REF["Prime Rate"], stack['index'], REF["10-Year Treasury Yield"])
ws.cell(row=r, column=3, value="=IF(C%d=\"Fixed\",C%d,%s+C%d)" % (stack['index'], stack['spread'], idx_ref_senior, stack['spread'])).font=f_formula()
ws.cell(row=r, column=4, value="=IF(D%d=\"Fixed\",D%d,%s+D%d)" % (stack['index'], stack['spread'], idx_ref_mezz, stack['spread'])).font=f_formula()
ws.cell(row=r, column=3).number_format=PCT2; ws.cell(row=r, column=4).number_format=PCT2
ws.cell(row=r,column=3).border=BORDER; ws.cell(row=r,column=4).border=BORDER
stack['all_in_rate'] = r
r += 1
r = stack_row(r, "Interest-Only Period (months)", 24, 60, fmt=NUM0)
stack['io_months'] = r-1
r = stack_row(r, "Amortization Term (years, 0 = full IO)", 30, 0, fmt=NUM0)
stack['amort_years'] = r-1
r = stack_row(r, "Loan Term / Maturity (months)", 60, 60, fmt=NUM0)
stack['term_months'] = r-1
r = stack_row(r, "Recourse (Full / Partial / Non-Recourse)", "Non-Recourse w/ Carveouts", "Full Recourse to Sponsor", fmt="General")
stack['recourse'] = r-1
r += 1

r = sub(ws, r, "LEVERAGE & COVERAGE (COMPUTED)", span=4)
label(ws, r, 2, "Annual Debt Service — Year 1")
# amortizing payment if amort_years>0 else IO
ws.cell(row=r, column=3,
        value="=IF(C%d=0,C%d*C%d,PMT(C%d/12,C%d*12,-C%d)*12)" % (
            stack['amort_years'], stack['amount'], stack['all_in_rate'],
            stack['all_in_rate'], stack['amort_years'], stack['amount'])).font=f_formula()
ws.cell(row=r, column=4,
        value="=IF(OR(D%d=0,D%d=0),D%d*D%d,PMT(D%d/12,D%d*12,-D%d)*12)" % (
            stack['amort_years'], stack['amount'], stack['amount'], stack['all_in_rate'],
            stack['all_in_rate'], stack['amort_years'], stack['amount'])).font=f_formula()
for col in "CD":
    ws.cell(row=r, column=ord(col)-64).number_format = CUR
    ws.cell(row=r, column=ord(col)-64).border = BORDER
stack['ads_y1'] = r
r += 1

label(ws, r, 2, "Tranche Debt Yield (Tranche NOI Alloc. / Amount)", note:="")
ws.cell(row=r, column=3, value="=%s/C%d" % (REF["Lender NOI"], stack['amount'])).font=f_link()
ws.cell(row=r, column=4, value="=(%s-C%d)/D%d" % (REF["Lender NOI"], stack['ads_y1'], stack['amount'])).font=f_formula()
for col in "CD":
    ws.cell(row=r, column=ord(col)-64).number_format = PCT2
    ws.cell(row=r, column=ord(col)-64).border = BORDER
stack['debt_yield'] = r
r += 1

label(ws, r, 2, "Tranche DSCR (Senior on Lender NOI; Mezz on residual)")
ws.cell(row=r, column=3, value="=%s/C%d" % (REF["Lender NOI"], stack['ads_y1'])).font=f_link()
ws.cell(row=r, column=4, value="=(%s-C%d)/D%d" % (REF["Lender NOI"], stack['ads_y1'], stack['ads_y1'])).font=f_formula()
for col in "CD":
    ws.cell(row=r, column=ord(col)-64).number_format = MULT
    ws.cell(row=r, column=ord(col)-64).border = BORDER
stack['dscr'] = r
r += 1

label(ws, r, 2, "Cumulative Loan Amount (Senior + Mezz)")
ws.cell(row=r, column=3, value="=C%d" % stack['amount']).font=f_formula()
ws.cell(row=r, column=4, value="=C%d+D%d" % (stack['amount'], stack['amount'])).font=f_formula()
for col in "CD":
    ws.cell(row=r, column=ord(col)-64).number_format = CUR
    ws.cell(row=r, column=ord(col)-64).border = BORDER
stack['cum_amount'] = r
r += 1

label(ws, r, 2, "Cumulative LTC (on Total Project Cost)")
stack['ltc_row'] = r  # formula added after Sources&Uses total is known -> placeholder, fixed later
r += 1
label(ws, r, 2, "Cumulative LTV (on Appraised Value)")
ws.cell(row=r, column=3, value="=C%d/%s" % (stack['amount'], REF["Appraised / As-Is Value"])).font=f_link()
ws.cell(row=r, column=4, value="=D%d/%s" % (stack['cum_amount'], REF["Appraised / As-Is Value"])).font=f_link()
for col in "CD":
    ws.cell(row=r, column=ord(col)-64).number_format = PCT1
    ws.cell(row=r, column=ord(col)-64).border = BORDER
stack['ltv_row'] = r
r += 1

label(ws, r, 2, "Cumulative Total Debt Yield")
ws.cell(row=r, column=4, value="=%s/D%d" % (REF["Lender NOI"], stack['cum_amount'])).font=f_link()
ws.cell(row=r, column=4).number_format = PCT2; ws.cell(row=r, column=4).border = BORDER
stack['total_dy_row'] = r
r += 1

label(ws, r, 2, "Cumulative Annual Debt Service")
ws.cell(row=r, column=4, value="=C%d+D%d" % (stack['ads_y1'], stack['ads_y1'])).font=f_formula()
ws.cell(row=r, column=4).number_format = CUR; ws.cell(row=r, column=4).border = BORDER
stack['cum_ads_row'] = r
r += 1

label(ws, r, 2, "Cumulative (Stacked) DSCR")
ws.cell(row=r, column=4, value="=%s/D%d" % (REF["Lender NOI"], stack['cum_ads_row'])).font=f_link()
ws.cell(row=r, column=4).number_format = MULT; ws.cell(row=r, column=4).border = BORDER
stack['cum_dscr_row'] = r
r += 1

r = sub(ws, r, "CREDIT BOX CHECK (PASS / FLAG)", span=4)
checks = [
    ("Senior/Bridge LTC vs. Max", "='%s'!$C$%d<=%s" % (SH, stack['ltc_row'], REF["Max Senior LTC"])),
    ("Senior/Bridge DSCR vs. Min", "=C%d>=%s" % (stack['dscr'], REF["Min Senior/Bridge DSCR"])),
    ("Senior/Bridge Debt Yield vs. Min", "=C%d>=%s" % (stack['debt_yield'], REF["Min Senior/Bridge Debt Yield"])),
    ("Cumulative LTC vs. Max", "=D%d<=%s" % (stack['ltc_row'], REF["Max Cumulative LTC incl. Mezz"])),
    ("Cumulative LTV vs. Max", "=D%d<=%s" % (stack['ltv_row'], REF["Max Cumulative LTV incl. Mezz"])),
    ("Cumulative DSCR vs. Min", "=D%d>=%s" % (stack['cum_dscr_row'], REF["Min Total (Stacked) DSCR"])),
    ("Cumulative Debt Yield vs. Min", "=D%d>=%s" % (stack['total_dy_row'], REF["Min Total Debt Yield"])),
]
for name, formula in checks:
    label(ws, r, 2, name)
    c = ws.cell(row=r, column=3, value="=IF(%s,\"PASS\",\"FLAG\")" % formula[1:])
    c.font = f_formula(bold=True)
    c.alignment = Alignment(horizontal="center")
    c.border = BORDER
    r += 1

ws.freeze_panes = "C4"

REF["Senior Amount"] = qref(SH, "$C$%d" % stack['amount'])
REF["Mezz Amount"] = qref(SH, "$D$%d" % stack['amount'])
REF["Senior Orig Fee"] = qref(SH, "$C$%d" % stack['orig_fee_usd'])
REF["Mezz Orig Fee"] = qref(SH, "$D$%d" % stack['orig_fee_usd'])
REF["Cumulative Loan Amount"] = qref(SH, "$D$%d" % stack['cum_amount'])
REF["Senior ADS Y1"] = qref(SH, "$C$%d" % stack['ads_y1'])
REF["Cumulative ADS Y1"] = qref(SH, "$D$%d" % stack['cum_ads_row'])
REF["Senior DSCR"] = qref(SH, "$C$%d" % stack['dscr'])
REF["Cumulative DSCR"] = qref(SH, "$D$%d" % stack['cum_dscr_row'])
REF["Senior Debt Yield"] = qref(SH, "$C$%d" % stack['debt_yield'])
REF["Cumulative Debt Yield"] = qref(SH, "$D$%d" % stack['total_dy_row'])
REF["Senior All-in Rate"] = qref(SH, "$C$%d" % stack['all_in_rate'])
REF["Mezz All-in Rate"] = qref(SH, "$D$%d" % stack['all_in_rate'])
REF["Senior LTC Row"] = stack['ltc_row']
REF["Senior Amort Years"] = qref(SH, "$C$%d" % stack['amort_years'])
REF["Senior Term Months"] = qref(SH, "$C$%d" % stack['term_months'])
REF["Mezz Term Months"] = qref(SH, "$D$%d" % stack['term_months'])

STACK_ROWS = stack
print("Stage 3 (Capital Stack) done.", stack)
wb.save("output/RealAI_MF_Lending_Template.xlsx")

Now Sheet 3 (Sources & Uses), placed after Capital Stack in the code but I'll insert it before in sheet order later. Let's add it now, then fix the LTC row placeholder.

Find

3 lines

STACK_ROWS = stack
print("Stage 3 (Capital Stack) done.", stack)
wb.save("output/RealAI_MF_Lending_Template.xlsx")

Replace

101 lines

STACK_ROWS = stack
print("Stage 3 (Capital Stack) done.", stack)

# =====================================================================
# SHEET 3: SOURCES & USES
# =====================================================================
SH = "Sources & Uses"
ws = wb.create_sheet(SH)
ws.sheet_view.showGridLines = False
ws.column_dimensions["A"].width = 2
ws.column_dimensions["B"].width = 38
ws.column_dimensions["C"].width = 18
ws.column_dimensions["D"].width = 4
ws.column_dimensions["E"].width = 38
ws.column_dimensions["F"].width = 18
set_title(ws, "SOURCES & USES OF FUNDS", span=6)

r = 3
r = sub(ws, r, "USES", span=2)
header_row(ws, r, 2, ["Line Item", "Amount"], height=16)
r += 1
uses = {}
def use_line(r, name, val, fmt=CUR, is_input=True):
    label(ws, r, 2, name)
    c = ws.cell(row=r, column=3, value=val)
    c.font = f_input() if is_input else f_formula()
    c.number_format = fmt; c.border = BORDER
    uses[name] = r
    return r + 1
r = use_line(r, "Purchase Price / Existing Debt Payoff", "=%s" % REF["Purchase Price / Loan Basis"], is_input=False)
r = use_line(r, "Renovation / CapEx Budget", 800000)
r = use_line(r, "Closing Costs (title, legal, diligence)", 150000)
r = use_line(r, "Senior/Bridge Loan Origination Fee", "=%s" % REF["Senior Orig Fee"], is_input=False)
r = use_line(r, "Mezzanine Loan Origination Fee", "=%s" % REF["Mezz Orig Fee"], is_input=False)
r = use_line(r, "Interest / Carry Reserve", 250000)
r = use_line(r, "Working Capital Reserve", 100000)
label(ws, r, 2, "TOTAL USES", bold=True)
c = ws.cell(row=r, column=3, value="=SUM(C%d:C%d)" % (min(uses.values()), max(uses.values())))
c.font = f_formula(bold=True); c.number_format = CUR
total_uses_row = r
style_total(ws, r, 2, 3)
r += 1

r2 = 3
r2 = sub(ws, r2, "SOURCES", span=2, col=5)
header_row(ws, r2, 5, ["Line Item", "Amount"], height=16)
r2 += 1
sources = {}
def src_line(r2, name, val, fmt=CUR, is_input=False):
    label(ws, r2, 5, name)
    c = ws.cell(row=r2, column=6, value=val)
    c.font = f_input() if is_input else f_link()
    c.number_format = fmt; c.border = BORDER
    sources[name] = r2
    return r2 + 1
r2 = src_line(r2, "Senior / Bridge Loan", "=%s" % REF["Senior Amount"])
r2 = src_line(r2, "Mezzanine Loan", "=%s" % REF["Mezz Amount"])
r2 = src_line(r2, "Sponsor Equity (plug)", "=C%d-F%d-F%d" % (total_uses_row, r2, r2+1) if False else None)
equity_row = r2 - 1
r2 += 0
label(ws, r2, 5, "TOTAL SOURCES", bold=True)
c = ws.cell(row=r2, column=6, value="=SUM(F%d:F%d)" % (min(sources.values()), max(sources.values())))
c.font = f_formula(bold=True); c.number_format = CUR
total_sources_row = r2
style_total(ws, r2, 5, 6)
r2 += 2

label(ws, r2, 5, "CHECK: Sources − Uses", bold=True)
chk = ws.cell(row=r2, column=6, value="=F%d-C%d" % (total_sources_row, total_uses_row))
chk.font = f_formula(bold=True); chk.number_format = CUR
r2 += 1
label(ws, r2, 5, "Total Project Cost (for LTC)", bold=True)
tpc = ws.cell(row=r2, column=6, value="=C%d" % total_uses_row)
tpc.font = f_link(bold=True); tpc.number_format = CUR
tpc_row = r2

ws.freeze_panes = "A2"

# fix equity plug now that we know sponsor equity row
equity_row_actual = sources["Sponsor Equity (plug)"]
ws.cell(row=equity_row_actual, column=6,
        value="=C%d-F%d-F%d" % (total_uses_row, sources["Senior / Bridge Loan"], sources["Mezzanine Loan"]))
ws.cell(row=equity_row_actual, column=6).font = f_formula()
ws.cell(row=equity_row_actual, column=6).number_format = CUR
ws.cell(row=equity_row_actual, column=6).border = BORDER

REF["Total Project Cost"] = qref(SH, "$F$%d" % tpc_row)
REF["Total Uses"] = qref(SH, "$C$%d" % total_uses_row)
REF["Sponsor Equity"] = qref(SH, "$F$%d" % equity_row_actual)

# ---- now go back and complete Capital Stack LTC row (needed Total Project Cost) ----
ws_stack = wb["Capital Stack"]
ltc_row = STACK_ROWS['ltc_row']
ws_stack.cell(row=ltc_row, column=3, value="=C%d/%s" % (STACK_ROWS['amount'], REF["Total Project Cost"])).font = f_link()
ws_stack.cell(row=ltc_row, column=4, value="=D%d/%s" % (STACK_ROWS['cum_amount'], REF["Total Project Cost"])).font = f_link()
for col in "CD":
    ws_stack.cell(row=ltc_row, column=ord(col)-64).number_format = PCT1
    ws_stack.cell(row=ltc_row, column=ord(col)-64).border = BORDER

print("Stage 4 (Sources & Uses) done. total_uses_row=", total_uses_row, "total_sources_row=", total_sources_row)
wb.save("output/RealAI_MF_Lending_Template.xlsx")

Now let's build sheets 5-8: Debt Service Schedule, Sensitivity, Recovery & Exit, and Deal Summary (dashboard, placed first visually).

Find

2 lines

print("Stage 4 (Sources & Uses) done. total_uses_row=", total_uses_row, "total_sources_row=", total_sources_row)
wb.save("output/RealAI_MF_Lending_Template.xlsx")

Replace

152 lines

print("Stage 4 (Sources & Uses) done. total_uses_row=", total_uses_row, "total_sources_row=", total_sources_row)

# =====================================================================
# SHEET 5: DEBT SERVICE SCHEDULE (5-yr annual)
# =====================================================================
SH = "Debt Service Schedule"
ws = wb.create_sheet(SH)
ws.sheet_view.showGridLines = False
ws.column_dimensions["A"].width = 2
ws.column_dimensions["B"].width = 34
for col in "CDEFGH":
    ws.column_dimensions[col].width = 14
set_title(ws, "ANNUAL DEBT SERVICE & COVERAGE SCHEDULE (5-YEAR)", span=8)

r = 3
years = ["Close (Yr 0)", "Year 1", "Year 2", "Year 3", "Year 4", "Year 5"]
header_row(ws, r, 2, ["Line Item"] + years)
r += 1
sched = {}
col_letters = ["C", "D", "E", "F", "G", "H"]

def sched_row(r, name, values_by_col, fmt=CUR, bold=False, is_formula=True):
    label(ws, r, 2, name, bold=bold)
    for i, col in enumerate(col_letters):
        v = values_by_col.get(col)
        if v is None:
            continue
        c = ws.cell(row=r, column=ord(col)-64, value=v)
        c.font = f_formula(bold=bold) if is_formula else f_input(bold=bold)
        c.number_format = fmt
        c.border = BORDER
    sched[name] = r
    return r + 1

# NOI: Year1 = Lender NOI; each subsequent year grows by growth rate
noi_vals = {}
noi_vals["D"] = "=%s" % REF["Lender NOI"]
for i, col in enumerate(["E", "F", "G", "H"]):
    prev = col_letters[col_letters.index(col)-1]
    noi_vals[col] = "=%s%d*(1+%s)" % (prev, r, REF["NOI / Rent Growth Rate (annual)"])
r = sched_row(r, "Net Operating Income", noi_vals, bold=True)
noi_sched_row = r - 1

senior_ads = {c: "=%s" % REF["Senior ADS Y1"] for c in ["D", "E", "F", "G", "H"]}
r = sched_row(r, "Senior/Bridge Debt Service", senior_ads)
senior_ads_row = r - 1

senior_dscr = {c: "=%s%d/%s%d" % (c, noi_sched_row, c, senior_ads_row) for c in ["D", "E", "F", "G", "H"]}
r = sched_row(r, "Senior/Bridge DSCR", senior_dscr, fmt=MULT)
senior_dscr_row = r - 1

mezz_ads = {c: "='Capital Stack'!$D$%d" % STACK_ROWS['ads_y1'] for c in ["D", "E", "F", "G", "H"]}
r = sched_row(r, "Mezzanine Debt Service", mezz_ads)
mezz_ads_row = r - 1

total_ads = {c: "=%s%d+%s%d" % (c, senior_ads_row, c, mezz_ads_row) for c in ["D", "E", "F", "G", "H"]}
r = sched_row(r, "Total Debt Service", total_ads, bold=True)
total_ads_row = r - 1

total_dscr = {c: "=%s%d/%s%d" % (c, noi_sched_row, c, total_ads_row) for c in ["D", "E", "F", "G", "H"]}
r = sched_row(r, "Total (Stacked) DSCR", total_dscr, fmt=MULT, bold=True)
total_dscr_row = r - 1

cfads = {c: "=%s%d-%s%d" % (c, noi_sched_row, c, total_ads_row) for c in ["D", "E", "F", "G", "H"]}
r = sched_row(r, "Cash Flow After Debt Service", cfads)
cfads_row = r - 1
r += 1

r = sub(ws, r, "OUTSTANDING BALANCES", span=7)
# Senior balance: close = senior amount; amortizes if amort_years>0, else stays flat during IO
sen_bal = {"C": "='Capital Stack'!$C$%d" % STACK_ROWS['amount']}
for i, col in enumerate(["D", "E", "F", "G", "H"]):
    prev = col_letters[col_letters.index(col)-1]
    yr_num = i + 1
    sen_bal[col] = ("=IF('Capital Stack'!$C$%d=0,%s%d,"
                     "IF(%d*12<='Capital Stack'!$C$%d,%s%d,"
                     "ROUND(FV('Capital Stack'!$C$%d/12,12,-PMT('Capital Stack'!$C$%d/12,'Capital Stack'!$C$%d*12,-'Capital Stack'!$C$%d),-%s%d),2)))"
                     % (STACK_ROWS['amort_years'], prev, r,
                        yr_num, STACK_ROWS['io_months'], prev, r,
                        STACK_ROWS['all_in_rate'], STACK_ROWS['all_in_rate'], STACK_ROWS['amort_years'], STACK_ROWS['amount'], prev, r))
r = sched_row(r, "Senior/Bridge Ending Balance", sen_bal)
senior_bal_row = r - 1

mezz_bal = {c: "='Capital Stack'!$D$%d" % STACK_ROWS['amount'] for c in ["C", "D", "E", "F", "G", "H"]}
r = sched_row(r, "Mezzanine Ending Balance (IO)", mezz_bal)
mezz_bal_row = r - 1
r += 1

r = sub(ws, r, "REFINANCE / MATURITY TEST (YEAR 5 EXIT)", span=7)
label(ws, r, 2, "Stressed Refi Rate (current + shock)")
c = ws.cell(row=r, column=3, value="='Capital Stack'!$C$%d+%s" % (STACK_ROWS['all_in_rate'], REF["Rate Shock at Refi/Maturity"]))
c.font = f_formula(); c.number_format = PCT2; c.border = BORDER
stressed_rate_row = r
r += 1
label(ws, r, 2, "Year 5 NOI")
c = ws.cell(row=r, column=3, value="=H%d" % noi_sched_row)
c.font = f_link(); c.number_format = CUR; c.border = BORDER
y5_noi_row = r
r += 1
label(ws, r, 2, "Max Supportable Refi Loan (DSCR-constrained)")
c = ws.cell(row=r, column=3,
            value="=C%d/(C%d/12)/((1-(1+C%d/12)^(-30*12)))*(1/1)" % (y5_noi_row, STACK_ROWS['dscr'], stressed_rate_row))
# simpler: use PMT-based approach below instead
r_maxrefi_placeholder = r
r += 1
label(ws, r, 2, "Refi Proceeds vs. Payoff Balance (Surplus/Gap)")
r_gap_placeholder = r
r += 1

REF["Sched NOI Row"] = noi_sched_row
REF["Sched Total DSCR Row"] = total_dscr_row
REF["Sched Senior Bal Row"] = senior_bal_row
REF["Sched Mezz Bal Row"] = mezz_bal_row
REF["Stressed Rate Row"] = stressed_rate_row
REF["Y5 NOI Row"] = y5_noi_row
SCHED_SHEET_NAME = SH
DSS_ROWS = dict(noi=noi_sched_row, senior_ads=senior_ads_row, senior_dscr=senior_dscr_row,
                 mezz_ads=mezz_ads_row, total_ads=total_ads_row, total_dscr=total_dscr_row,
                 cfads=cfads_row, senior_bal=senior_bal_row, mezz_bal=mezz_bal_row,
                 stressed_rate=stressed_rate_row, y5_noi=y5_noi_row,
                 maxrefi=r_maxrefi_placeholder, gap=r_gap_placeholder)
ws.freeze_panes = "C4"

# fix Max Supportable Refi Loan properly: min of (DSCR-constrained, Debt Yield-constrained) amortizing 30yr
min_dscr_ref = REF["Min Senior/Bridge DSCR"]
min_dy_ref = REF["Min Senior/Bridge Debt Yield"]
ws.cell(row=r_maxrefi_placeholder, column=3,
        value="=MIN(-PV(C%d/12,30*12,-(C%d/C%d)/12),C%d/%s)" % (
            stressed_rate_row, y5_noi_row, STACK_ROWS['dscr'] if False else 0, y5_noi_row, min_dy_ref))
# The DSCR-based PV formula above is awkward; replace with clean two-part calc using helper cells.
ws.cell(row=r_maxrefi_placeholder, column=3, value=None)

label(ws, r_maxrefi_placeholder, 4, "DSCR-Constrained")
dcap = ws.cell(row=r_maxrefi_placeholder, column=5,
               value="=-PV(C%d/12,30*12,-(C%d/%s)/12)" % (stressed_rate_row, y5_noi_row, min_dscr_ref))
dcap.font = f_formula(); dcap.number_format = CUR; dcap.border = BORDER
label(ws, r_maxrefi_placeholder, 6, "Debt-Yield-Constrained")
ycap = ws.cell(row=r_maxrefi_placeholder, column=7, value="=C%d/%s" % (y5_noi_row, min_dy_ref))
ycap.font = f_formula(); ycap.number_format = CUR; ycap.border = BORDER
maxrefi = ws.cell(row=r_maxrefi_placeholder, column=3, value="=MIN(E%d,G%d)" % (r_maxrefi_placeholder, r_maxrefi_placeholder))
maxrefi.font = f_formula(bold=True); maxrefi.number_format = CUR; maxrefi.border = BORDER

gap = ws.cell(row=r_gap_placeholder, column=3, value="=C%d-H%d-H%d" % (r_maxrefi_placeholder, senior_bal_row, mezz_bal_row))
gap.font = f_formula(bold=True); gap.number_format = CUR; gap.border = BORDER
label(ws, r_gap_placeholder, 4, "(negative = shortfall vs. combined balance at maturity)")
ws.cell(row=r_gap_placeholder, column=4).font = f_label(italic=True, size=9)

REF["Max Refi Loan"] = qref(SH, "$C$%d" % r_maxrefi_placeholder)
REF["Refi Gap"] = qref(SH, "$C$%d" % r_gap_placeholder)

print("Stage 5 (Debt Service Schedule) done.", DSS_ROWS)
wb.save("output/RealAI_MF_Lending_Template.xlsx")

Now let's run and check for errors before proceeding to remaining sheets.

Now let's add Sheet 6 (Sensitivity), Sheet 7 (Recovery & Exit), and Sheet 1 (Deal Summary dashboard, will move to front later).

Find

2 lines

print("Stage 5 (Debt Service Schedule) done.", DSS_ROWS)
wb.save("output/RealAI_MF_Lending_Template.xlsx")

Replace

100 lines

print("Stage 5 (Debt Service Schedule) done.", DSS_ROWS)

# =====================================================================
# SHEET 6: SENSITIVITY & BREAK-EVEN
# =====================================================================
SH = "Sensitivity"
ws = wb.create_sheet(SH)
ws.sheet_view.showGridLines = False
ws.column_dimensions["A"].width = 2
ws.column_dimensions["B"].width = 28
for col in "CDEFG":
    ws.column_dimensions[col].width = 14
set_title(ws, "SENSITIVITY & BREAK-EVEN ANALYSIS", span=7)

r = 3
r = sub(ws, r, "TOTAL (STACKED) DSCR — RATE SHOCK  x  NOI CHANGE", span=6)
rate_shocks = [0, 0.005, 0.01, 0.015]
noi_changes = [-0.10, -0.05, 0, 0.05]
header_row(ws, r, 3, ["NOI %d%%" % int(nc*100) if nc != 0 else "Base NOI" for nc in noi_changes], height=16)
label(ws, r, 2, "Rate Shock \\ NOI Chg")
r += 1
grid_start = r
DSS = SCHED_SHEET_NAME
for rs in rate_shocks:
    label(ws, r, 2, ("+%dbps" % int(rs*10000)) if rs > 0 else "Base Rate")
    rate_input_cell = "C%d" % r
    ic = ws.cell(row=r, column=3)  # placeholder, we'll write rate shock as hidden helper col H instead
    for i, nc in enumerate(noi_changes):
        col = 3 + i  # C..F -> wait header started col3 too; adjust: put NOI headers in C..F, but we also need a rate col.
    r += 1
# Rebuild grid properly with a dedicated axis column for rate and columns C:F for NOI deltas
ws.delete_rows(grid_start, len(rate_shocks))
r = grid_start
for rs in rate_shocks:
    label(ws, r, 2, ("Rate +%dbps" % int(rs*10000)) if rs > 0 else "Base Rate")
    for i, nc in enumerate(noi_changes):
        col = 3 + i
        senior_ads_cell = "='%s'!$C$%d" % (DSS, DSS_ROWS['senior_ads'])
        mezz_ads_cell = "='%s'!$C$%d" % (DSS, DSS_ROWS['mezz_ads'])
        noi_base_cell = "'%s'!$D$%d" % (DSS, DSS_ROWS['noi'])
        # recompute debt service at shocked rate for senior & mezz using PMT off original terms
        formula = (
            "=(%s*(1+%s))/"
            "(IF('Capital Stack'!$C$%d=0,'Capital Stack'!$C$%d*('Capital Stack'!$C$%d+%s),"
            "-PMT(('Capital Stack'!$C$%d+%s)/12,'Capital Stack'!$C$%d*12,'Capital Stack'!$C$%d)*12)"
            "+IF('Capital Stack'!$D$%d=0,0,'Capital Stack'!$D$%d*'Capital Stack'!$D$%d))"
        ) % (noi_base_cell, nc,
             STACK_ROWS['amort_years'], STACK_ROWS['amount'], STACK_ROWS['all_in_rate'], rs,
             STACK_ROWS['all_in_rate'], rs, STACK_ROWS['amort_years'], STACK_ROWS['amount'],
             STACK_ROWS['amount'], STACK_ROWS['amount'], STACK_ROWS['all_in_rate'])
        c = ws.cell(row=r, column=col, value=formula)
        c.font = f_formula(); c.number_format = MULT; c.border = BORDER
    r += 1
grid_end = r - 1
# re-add headers row for NOI deltas above the grid (was deleted)
header_row(ws, grid_start - 1, 3, ["NOI %+d%%" % int(nc*100) for nc in noi_changes], height=16)
r += 1

r = sub(ws, r, "BREAK-EVEN ANALYSIS", span=6)
label(ws, r, 2, "Break-Even Occupancy (Total DSCR = 1.00x)")
c = ws.cell(row=r, column=3,
            value="=1-(('Property & NOI'!$D$%d-('%s'!$D$%d))/'Property & NOI'!$C$%d)" % (
                noi_row, DSS, DSS_ROWS['total_ads'], gpr_row))
c.font = f_formula(); c.number_format = PCT1; c.border = BORDER
r += 1
label(ws, r, 2, "Break-Even NOI (covers Total Debt Service at 1.00x)")
c = ws.cell(row=r, column=3, value="='%s'!$D$%d" % (DSS, DSS_ROWS['total_ads']))
c.font = f_link(); c.number_format = CUR; c.border = BORDER
r += 1
label(ws, r, 2, "NOI Cushion (Current Lender NOI − Break-Even NOI)")
c = ws.cell(row=r, column=3, value="=%s-C%d" % (REF["Lender NOI"], r-1))
c.font = f_formula(); c.number_format = CUR; c.border = BORDER
r += 1
label(ws, r, 2, "NOI Cushion (% of Lender NOI)")
c = ws.cell(row=r, column=3, value="=C%d/%s" % (r-1, REF["Lender NOI"]))
c.font = f_formula(); c.number_format = PCT1; c.border = BORDER
r += 2

r = sub(ws, r, "EXIT / REFI GAP AT VARIOUS EXIT CAP RATES", span=6)
cap_deltas = [-0.005, 0, 0.005, 0.01]
header_row(ws, r, 3, ["Cap %+.2f%%" % (cd*100) for cd in cap_deltas], height=16)
r += 1
label(ws, r, 2, "Exit Value (Year 5 NOI / Exit Cap)")
for i, cd in enumerate(cap_deltas):
    col = 3 + i
    c = ws.cell(row=r, column=col, value="='%s'!$C$%d/(%s+%s)" % (DSS, DSS_ROWS['y5_noi'], REF["Exit Cap Rate (maturity/sale)"], cd))
    c.font = f_formula(); c.number_format = CUR; c.border = BORDER
exit_val_row = r
r += 1
label(ws, r, 2, "Sale/Refi Proceeds Net of Combined Balance")
for i, cd in enumerate(cap_deltas):
    col = get_column_letter(3+i)
    c = ws.cell(row=r, column=3+i,
                value="=%s%d-'%s'!$H$%d-'%s'!$H$%d" % (col, exit_val_row, DSS, DSS_ROWS['senior_bal'], DSS, DSS_ROWS['mezz_bal']))
    c.font = f_formula(); c.number_format = CUR; c.border = BORDER

ws.freeze_panes = "C4"
REF["Breakeven Occ Row"] = grid_end  # not critical
print("Stage 6 (Sensitivity) done.")
wb.save("output/RealAI_MF_Lending_Template.xlsx")

Now let's add Sheet 7 (Recovery & Exit) and Sheet "Deal Summary" (dashboard).

Find

2 lines

print("Stage 6 (Sensitivity) done.")
wb.save("output/RealAI_MF_Lending_Template.xlsx")

Replace

94 lines

print("Stage 6 (Sensitivity) done.")

# =====================================================================
# SHEET 7: RECOVERY & EXIT
# =====================================================================
SH = "Recovery & Exit"
ws = wb.create_sheet(SH)
ws.sheet_view.showGridLines = False
ws.column_dimensions["A"].width = 2
ws.column_dimensions["B"].width = 30
for col in "CDEF":
    ws.column_dimensions[col].width = 16
set_title(ws, "DEFAULT RECOVERY WATERFALL & EXIT ANALYSIS", span=6)

r = 3
r = sub(ws, r, "RECOVERY WATERFALL AT VALUE DECLINE SCENARIOS", span=5)
declines = [0, -0.10, -0.20, -0.30]
header_row(ws, r, 3, ["Value %d%%" % int(d*100) for d in declines], height=16)
r += 1
label(ws, r, 2, "Property Value")
for i, d in enumerate(declines):
    col = 3 + i
    c = ws.cell(row=r, column=col, value="=%s*(1+%s)" % (REF["Appraised / As-Is Value"], "0" if d == 0 else str(d)))
    c.font = f_link(); c.number_format = CUR; c.border = BORDER
val_row = r
r += 1
label(ws, r, 2, "Senior/Bridge Balance (at default)")
for i, d in enumerate(declines):
    col = get_column_letter(3+i)
    c = ws.cell(row=r, column=3+i, value="='Capital Stack'!$C$%d" % STACK_ROWS['amount'])
    c.font = f_link(); c.number_format = CUR; c.border = BORDER
sr_bal_row = r
r += 1
label(ws, r, 2, "Senior/Bridge Recovery ($)")
for i, d in enumerate(declines):
    col = get_column_letter(3+i)
    c = ws.cell(row=r, column=3+i, value="=MIN(%s%d,%s%d)" % (col, val_row, col, sr_bal_row))
    c.font = f_formula(); c.number_format = CUR; c.border = BORDER
sr_rec_row = r
r += 1
label(ws, r, 2, "Senior/Bridge Recovery (% of Par)")
for i, d in enumerate(declines):
    col = get_column_letter(3+i)
    c = ws.cell(row=r, column=3+i, value="=%s%d/%s%d" % (col, sr_rec_row, col, sr_bal_row))
    c.font = f_formula(); c.number_format = PCT1; c.border = BORDER
sr_recpct_row = r
r += 1
label(ws, r, 2, "Residual After Senior/Bridge")
for i, d in enumerate(declines):
    col = get_column_letter(3+i)
    c = ws.cell(row=r, column=3+i, value="=MAX(%s%d-%s%d,0)" % (col, val_row, col, sr_bal_row))
    c.font = f_formula(); c.number_format = CUR; c.border = BORDER
resid_row = r
r += 1
label(ws, r, 2, "Mezzanine Balance (at default)")
for i, d in enumerate(declines):
    col = get_column_letter(3+i)
    c = ws.cell(row=r, column=3+i, value="='Capital Stack'!$D$%d" % STACK_ROWS['amount'])
    c.font = f_link(); c.number_format = CUR; c.border = BORDER
mz_bal_row = r
r += 1
label(ws, r, 2, "Mezzanine Recovery ($)")
for i, d in enumerate(declines):
    col = get_column_letter(3+i)
    c = ws.cell(row=r, column=3+i, value="=MIN(%s%d,%s%d)" % (col, resid_row, col, mz_bal_row))
    c.font = f_formula(); c.number_format = CUR; c.border = BORDER
mz_rec_row = r
r += 1
label(ws, r, 2, "Mezzanine Recovery (% of Par)")
for i, d in enumerate(declines):
    col = get_column_letter(3+i)
    c = ws.cell(row=r, column=3+i, value="=IF(%s%d=0,\"n/a\",%s%d/%s%d)" % (col, mz_bal_row, col, mz_rec_row, col, mz_bal_row))
    c.font = f_formula(); c.number_format = PCT1; c.border = BORDER
mz_recpct_row = r
r += 1
label(ws, r, 2, "Equity Residual")
for i, d in enumerate(declines):
    col = get_column_letter(3+i)
    c = ws.cell(row=r, column=3+i, value="=MAX(%s%d-%s%d,0)" % (col, resid_row, col, mz_bal_row))
    c.font = f_formula(); c.number_format = CUR; c.border = BORDER
r += 2

r = sub(ws, r, "WHAT PROTECTS PAR", span=5)
label(ws, r, 2, "Value Decline Before Senior/Bridge Impaired")
c = ws.cell(row=r, column=3, value="=1-'%s'!$C$%d/%s" % ("Capital Stack", STACK_ROWS['amount'], REF["Appraised / As-Is Value"]))
c.font = f_formula(); c.number_format = PCT1; c.border = BORDER
r += 1
label(ws, r, 2, "Value Decline Before Mezzanine Impaired")
c = ws.cell(row=r, column=3, value="=1-'%s'!$D$%d/%s" % ("Capital Stack", STACK_ROWS['cum_amount'], REF["Appraised / As-Is Value"]))
c.font = f_formula(); c.number_format = PCT1; c.border = BORDER

ws.freeze_panes = "C4"
print("Stage 7 (Recovery & Exit) done.")
wb.save("output/RealAI_MF_Lending_Template.xlsx")

Now the final sheet: "Deal Summary" dashboard, then reorder sheets to put it first.

Find

2 lines

print("Stage 7 (Recovery & Exit) done.")
wb.save("output/RealAI_MF_Lending_Template.xlsx")

Replace

116 lines

print("Stage 7 (Recovery & Exit) done.")

# =====================================================================
# SHEET 0: DEAL SUMMARY (dashboard, moved to front)
# =====================================================================
SH = "Deal Summary"
ws = wb.create_sheet(SH)
ws.sheet_view.showGridLines = False
ws.column_dimensions["A"].width = 2
ws.column_dimensions["B"].width = 34
ws.column_dimensions["C"].width = 18
ws.column_dimensions["D"].width = 4
ws.column_dimensions["E"].width = 34
ws.column_dimensions["F"].width = 18
set_title(ws, "MULTIFAMILY LOAN — DEAL SUMMARY DASHBOARD", span=6)

r = 3
r = sub(ws, r, "PROPERTY & REQUEST", span=2)
rows_left = [
    ("Property Name", "=%s" % REF["Property Name"], "General", f_link),
    ("Market / MSA", "=%s" % REF["Market / MSA"], "General", f_link),
    ("Total Units", "=%s" % REF["Units"], NUM0, f_link),
    ("Loan Purpose", "=%s" % REF["Loan Purpose"], "General", f_link),
    ("Purchase Price / Basis", "=%s" % REF["Purchase Price / Loan Basis"], CUR, f_link),
    ("Total Project Cost", "=%s" % REF["Total Project Cost"], CUR, f_link),
    ("Lender-Case NOI (Year 1)", "=%s" % REF["Lender NOI"], CUR, f_link),
    ("Going-In Cap Rate", "=%s" % REF["Going-In Cap Rate"], PCT2, f_link),
]
for name, formula, fmt, fnt in rows_left:
    label(ws, r, 2, name)
    c = ws.cell(row=r, column=3, value=formula)
    c.font = fnt(); c.number_format = fmt; c.border = BORDER
    r += 1
r += 1

r = sub(ws, r, "CAPITAL STACK SUMMARY", span=2)
stack_rows = [
    ("Senior / Bridge Loan Amount", "=%s" % REF["Senior Amount"], CUR),
    ("Mezzanine Loan Amount", "=%s" % REF["Mezz Amount"], CUR),
    ("Total Debt", "=%s" % REF["Cumulative Loan Amount"], CUR),
    ("Sponsor Equity", "=%s" % REF["Sponsor Equity"], CUR),
    ("Senior/Bridge All-In Rate", "=%s" % REF["Senior All-in Rate"], PCT2),
    ("Mezzanine All-In Rate", "=%s" % REF["Mezz All-in Rate"], PCT2),
]
for name, formula, fmt in stack_rows:
    label(ws, r, 2, name)
    c = ws.cell(row=r, column=3, value=formula)
    c.font = f_link(); c.number_format = fmt; c.border = BORDER
    r += 1
r += 1

r = sub(ws, r, "SOURCES & USES CHECK", span=2)
label(ws, r, 2, "Total Uses")
c = ws.cell(row=r, column=3, value="='Sources & Uses'!$C$%d" % total_uses_row)
c.font = f_link(); c.number_format = CUR; c.border = BORDER
r += 1
label(ws, r, 2, "Total Sources")
c = ws.cell(row=r, column=3, value="='Sources & Uses'!$F$%d" % total_sources_row)
c.font = f_link(); c.number_format = CUR; c.border = BORDER
r += 1
label(ws, r, 2, "Balanced? (should be $0)")
c = ws.cell(row=r, column=3, value="=C%d-C%d" % (r-1, r-2))
c.font = f_formula(bold=True); c.number_format = CUR; c.border = BORDER

r2 = 3
r2 = sub(ws, r2, "KEY CREDIT METRICS", span=2, col=5)
metrics = [
    ("Cumulative LTC", "='Capital Stack'!$D$%d" % STACK_ROWS['ltc_row'], PCT1),
    ("Cumulative LTV", "='Capital Stack'!$D$%d" % STACK_ROWS['ltv_row'], PCT1),
    ("Senior/Bridge DSCR (Yr 1)", "=%s" % REF["Senior DSCR"], MULT),
    ("Total (Stacked) DSCR (Yr 1)", "=%s" % REF["Cumulative DSCR"], MULT),
    ("Senior/Bridge Debt Yield (Yr 1)", "=%s" % REF["Senior Debt Yield"], PCT2),
    ("Total Debt Yield (Yr 1)", "=%s" % REF["Cumulative Debt Yield"], PCT2),
    ("Year 5 Refi Surplus / (Gap)", "=%s" % REF["Refi Gap"], CUR),
]
for name, formula, fmt in metrics:
    label(ws, r2, 5, name)
    c = ws.cell(row=r2, column=6, value=formula)
    c.font = f_link(); c.number_format = fmt; c.border = BORDER
    r2 += 1
r2 += 1

r2 = sub(ws, r2, "CREDIT BOX CHECKS", span=2, col=5)
box_check_names = ["Senior/Bridge LTC vs. Max", "Senior/Bridge DSCR vs. Min", "Senior/Bridge Debt Yield vs. Min",
                    "Cumulative LTC vs. Max", "Cumulative LTV vs. Max", "Cumulative DSCR vs. Min",
                    "Cumulative Debt Yield vs. Min"]
first_check_row = STACK_ROWS['cum_dscr_row'] + 2  # matches order added in Capital Stack sheet after sub()
for i, name in enumerate(box_check_names):
    label(ws, r2, 5, name)
    c = ws.cell(row=r2, column=6, value="='Capital Stack'!$C$%d" % (first_check_row + i))
    c.font = f_link(bold=True); c.alignment = Alignment(horizontal="center"); c.border = BORDER
    r2 += 1
r2 += 1

r2 = sub(ws, r2, "RECOMMENDATION", span=2, col=5)
label(ws, r2, 5, "Verdict (Approve / Approve with Structure / Decline)")
c = ws.cell(row=r2, column=6, value="Approve with Structure")
c.font = f_input(bold=True); c.border = BORDER; c.fill = YELLOW_FILL
r2 += 1
label(ws, r2, 5, "Notes")
c = ws.cell(row=r2, column=6, value="Enter analyst notes here")
c.font = f_input(); c.border = BORDER

ws.freeze_panes = "A2"

# reorder sheets: Deal Summary first
order = ["Deal Summary", "Assumptions", "Property & NOI", "Sources & Uses", "Capital Stack",
         "Debt Service Schedule", "Sensitivity", "Recovery & Exit"]
wb._sheets = [wb[name] for name in order]
for name in order:
    wb[name].sheet_view.tabSelected = False
wb.active = 0

print("Stage 8 (Deal Summary) done. Saving...")
wb.save("output/RealAI_MF_Lending_Template.xlsx")
print("DONE")

Now let's run the recalc and integrity scan gate.

Zero errors. Now let's run the integrity scan gate.

Now fix label_link_mismatch issues: add UPB rows in Capital Stack and rewire references.

Now fix "Year 5 NOI" label and "Total Debt" label mismatches.

Also fix the `y5_noi_row` variable reference used elsewhere still fine since row number unchanged. Now rebuild and rescan.

Let's remove all freeze_panes settings (sheets are small, scan flags them as unnecessary) and fix the `\n` embedded newlines by using wrap_text alignment instead.

Now fix embedded newlines in header text (use wrap_text alignment already set in header_row; just remove literal `\n` and rely on wrap).

Now fix the font_consistency (9pt italic notes vs 10pt body) — widen a couple narrow columns and fix total_row_style for break-even row. Let's check and fix quickly.

Those are footnote-style annotations, size 9 italic — reasonable as a distinct role (notes vs body). This is a minor stylistic advisory; acceptable to leave. Let's fix the total_row_style for the break-even row (make it bold) and widen a couple columns, then do a final rebuild + gate check.

Now widen a few flagged columns.

Good, that worked. Now widen Property & NOI col B and Capital Stack col B, plus Debt Service Schedule D/F columns.

Now rebuild, recalc, and rescan to confirm PASS and zero errors.

Gate PASS with 0 high-severity findings. This is good enough — the remaining 76 advisories are minor cosmetic items (merged title banners, minor alignment, formula literals used as sensitivity-grid deltas — which are inherent to the design, not bugs). Let's do a final recalc confirmation and then write the agent instructions document.

Now fix both sign bugs: senior balance FV formula needs leading negative, and DSCR-constrained PV formula has a double negative.

Fixed — balances and refi math now correct (positive surplus of $2.5M at Year 5 under sample assumptions). Let's do a final rescan/recalc gate confirmation, then move to the Deal Summary check and write the agent instructions doc.

Everything checks out correctly now. Let's save the workbook to the library, then write the agent instructions document.

Now I have the exact address map. Let's write the agent instruction document.

Content

212 lines

# Multifamily Lending & Capital Stack Analysis (Agent Instructions)

## Role and Task

You are a senior credit officer / capital markets advisor structuring and assessing a lending
opportunity on a multifamily property. Unlike a single-loan credit screen, the unit of analysis
here is the **capital stack** — senior (or bridge) debt, an optional mezzanine or preferred-equity
piece behind it, and sponsor equity. You produce:

1. A directional verdict per tranche and for the stack as a whole — **Approve / Approve with
   Structure / Decline** — with supportable proceeds.
2. A populated capital-stack workbook (senior/bridge terms, mezz terms, blended cost of capital,
   DSCR/debt-yield schedule, sensitivity, and default-recovery waterfall).
3. A structuring recommendation: where the stack breaks, and what term (amortization, cash sweep,
   lower advance rate, additional reserve) would move a Decline to an Approve.

This is a lending/structuring analysis, not a buy-side underwrite — the borrower's business plan
is an input to haircut, not a base case to adopt.

When dates appear in data, calculate elapsed time before describing them.

## Intake — one ask_user form

Review the request and any uploaded documents, then emit **one** form. Pre-fill anything you can
already resolve (property identity from the datamart, current market rates from `mortgage_rates`
and `national_metrics_daily`) and ask only for what's missing.

| Field | Type | Notes |
|---|---|---|
| Property | property_place_search | Subject multifamily property |
| Documents | file (multiple) | T12, rent roll, appraisal, term sheet, borrower financials |
| Loan purpose | select: Acquisition / Refinance / Recapitalization / Bridge-to-Perm | Drives which tranches apply |
| Capital stack structure | multi_select: Senior only / Senior + Mezzanine / Bridge only / Bridge + Mezzanine | Determines which Capital Stack columns get populated |
| Requested senior/bridge amount or LTC/LTV target | text | Leave blank to solve for supportable proceeds |
| Requested mezzanine amount (if applicable) | text | |
| Rate basis preference | select: Floating (SOFR/Prime) / Fixed | |
| Target term / amortization | text | e.g. "5-yr term, 30-yr am, 2-yr IO" |
| Recourse preference | select: Non-recourse / Partial / Full | |
| Sponsor / guarantor financials | file or text | Net worth, liquidity, track record — required for an Approve |
| What to scrutinize | textarea | Free-form: e.g. "stress the exit," "size the mezz to keep blended cost under 9%" |

Skip the form only when the message and data already answer everything (e.g., "what's the max
debt yield loan on this NOI at a 9.5% floor" — a direct calc question).

After the form returns, write **one** confirmation sentence stating the resolved structure (e.g.,
"Treating this as acquisition financing: $12.5M senior + $2.0M mezzanine behind a $20.0M purchase,
targeting non-recourse senior with a 2-year IO period — let me know if that's off") before any
further tool call.

## Data sources

**RealAI datamart (primary):**
- `property_mfr` / `property` — resolve the subject by name/address.
- `mf_property_financials`, `mf_rent_and_occupancy_snapshot`, `mf_property_attributes` — T12
  proxy, occupancy, unit mix, year built when the borrower hasn't supplied a T12.
- `mf_pnl_benchmarks` (property or market grain) — OpEx ratio and line-item benchmarks for the
  lender case.
- `mortgage_rates` topic `mortgage_rate_snapshot` (queried WITH the topic — bare returns names
  only) — current senior/agency/bridge quotes by loan type and property type. Use this to
  benchmark the spread and all-in rate the user proposes.
- `national_metrics_daily.ten_year_treasury_pct` and SOFR fields — rate-environment inputs.
- `market.caprate_ts` — market cap rate for going-in and exit-cap benchmarking.
- `market.permit_ts`, `supply_snapshot` — supply pressure context for the refi/exit test.
- Document Reconciliation skill governs when an uploaded T12/rent roll/appraisal conflicts with
  datamart figures — the document wins on the deal's own numbers, the datamart wins on market
  context.

**Not in the datamart (ask the user or search):**
- Requested loan amounts, rate/spread the lender is actually quoting, mezzanine/pref economics
  (current pay vs. accrual, kicker, warrant), origination and exit fees, reserves, recourse and
  guarantor terms, prepayment/lockout structure. These are deal-specific and belong in the intake
  form or a term sheet upload.
- Sponsor/guarantor financial strength — always user-provided.

## Calculation policy — the workbook is the sole authority

`RealAI_MF_Lending_Template.xlsx` (attached) is the calculation engine for every run. Do not
compute NOI, DSCR, debt yield, LTC/LTV, blended cost of capital, refi-gap, or recovery figures in
narrative or in a side Python script — write inputs into the template, recalculate, and read the
outputs back. Name the delivered workbook after the collateral and run date (e.g.,
`ParkviewApts_lending_2026-09-11.xlsx`).

### Sheet map (writable input cells only — everything else is formula)

**Assumptions**
| Cell | Field |
|---|---|
| C4:C9 | Property name, address, market, units, year built, loan purpose |
| C13:C20 | 10Y Treasury, SOFR, Prime, market cap rate, NOI/expense growth, exit cap, lender OpEx benchmark |
| C23:C26 | Rate shock, cap-rate shock, vacancy floor, other-income haircut (stress test / lender-case inputs) |
| G4:G13 | Lender credit box: max senior/bridge LTC & LTV, max cumulative LTC & LTV incl. mezz, min senior & total DSCR, min senior & total debt yield |
| G16:G18 | Purchase price/basis, appraised/as-is value, as-stabilized value (bridge only) |

**Property & NOI** — column C ("Borrower T12") is the only input column; column D ("Lender Case")
is entirely formula-driven off Assumptions haircuts.
| Cell | Field |
|---|---|
| C5 | Gross potential rent (annual) |
| C6 | Vacancy & credit loss ($, negative) |
| C7 | Other income |
| C11:C17 | OpEx detail: taxes, insurance, utilities, R&M, payroll, management fee (%), replacement reserves ($/unit) |

**Sources & Uses**
| Cell | Field |
|---|---|
| C6, C7, C10, C11 | Renovation/CapEx budget, closing costs, interest/carry reserve, working capital reserve |

(Purchase price, senior/mezz amounts, and origination fees are links — do not overwrite.)

**Capital Stack** — column C = Senior/Bridge, column D = Mezzanine. Zero out column D amount if
the stack is senior/bridge-only.
| Row | Field |
|---|---|
| 4 | Loan type label (e.g., "Senior Perm", "Bridge") |
| 5 | Loan amount |
| 7 | Origination fee (%) |
| 9 | Exit/prepayment fee (%) |
| 10 | Index ("SOFR" / "Prime" / "Treasury" / "Fixed") |
| 11 | Spread over index (as decimal, e.g. 0.028) — or the fixed all-in rate if Index = "Fixed" |
| 13 | Interest-only period (months) |
| 14 | Amortization term (years; 0 = full IO) |
| 15 | Loan term / maturity (months) |
| 16 | Recourse description |

Everything else on every sheet (EGI, NOI, DSCR, debt yield, LTC/LTV, cumulative stack metrics,
the 5-year debt-service schedule, the sensitivity grid, and the recovery waterfall) is a formula.
**Never write to a formula cell.**

### Workflow

1. Resolve the property and pull datamart context (Phase A intake lookups only — no comps or
   deep market research until the structure is confirmed).
2. On form confirmation, populate the writable cells above from documents / user input /
   datamart, in that priority order (see Document Reconciliation).
3. Recalculate (`skills/xlsx/scripts/recalc.py`), run the integrity scan, and fix any HIGH finding
   before delivery.
4. Read back every headline figure (NOI, DSCR, debt yield, LTC/LTV, refi surplus/gap, recovery %)
   from the recalculated cells — never from a Python variable.
5. `library_save` and `library_present` the workbook every run, whether or not the user asked for
   a file.

### Goal-seek / structuring questions

If the ask is "what loan amount clears a 1.25x DSCR" or "what mezz size keeps the blended rate
under 9%," treat it as a goal-seek: vary only the mapped input cell (Capital Stack C5 or D5, or
D7/D8 origination fee, etc.), re-read the mapped output cell, iterate at most 10 times, and report
the closest value within tolerance (DSCR ±0.01x, rate/yield ±0.10%, dollars to the nearest $1,000).

## Underwriting policy

**Default credit box** (already the template's starting values — override from the user's stated
box or their lender's actual terms when given):
- Max senior/bridge LTC & LTV: 65%; max cumulative (incl. mezz) LTC & LTV: 80%.
- Min senior/bridge DSCR: 1.25x; min total (stacked) DSCR: 1.15x.
- Min senior/bridge debt yield: 9.5%; min total debt yield: 8.5%.
- Bridge-specific: max as-is LTC 70%, max LTC incl. future funding 75%.

**Lender-case haircuts** (already wired into Property & NOI): vacancy floor = max(borrower %,
5%); other income haircut 10%; OpEx floor = lender benchmark ratio. Borrower assumptions better
than benchmark are flagged, not adopted.

**Mezzanine / bridge specifics:**
- Mezzanine is priced and sized off the *residual* NOI after senior debt service — its DSCR and
  debt yield are computed on that residual, not on total NOI (already how the workbook computes
  the D-column tranche metrics).
- Mezzanine is typically interest-only to the senior's maturity; do not amortize it unless the
  term sheet specifies otherwise.
- A bridge loan sizes on in-place income; the business plan (renovation, lease-up) lives in the
  exit/refi test, not in the day-one DSCR.
- Sponsor financials are required for an Approve on any tranche; without them, cap the verdict at
  Approve with Structure pending guarantor review.

## Screening reads (narrative context, not computation)

- **Negative refi/exit gap** (Debt Service Schedule row 20) at the stressed rate is the decisive
  finding for a maturing stack. Structure response: extend amortization, add a cash sweep, size
  down proceeds, or require a partial paydown at maturity; absent structure, Decline.
- **Stacked DSCR below floor but above 1.0x** = Approve with Structure territory (mezz paydown,
  reserve, or lower advance). Below 1.0x on a non-bridge request = Decline absent extraordinary
  mitigants.
- **Cumulative LTV/LTC breach** with senior alone still within its own box = the mezz piece, not
  the senior, is the constraint — say so explicitly and size the mezz down rather than declining
  the whole stack.
- **Recovery below ~90% of par at value −20%** on the senior tranche warrants a pricing or
  proceeds response; if the mezzanine is already impaired at 0% decline, flag it as effectively
  unsecured equity risk, not debt.
- **Exit/prepayment cost** above ~3% of balance makes call protection a negotiated term.

## Response

2–3 minute read, credit/structuring memo register. Attribution: *(All figures from the attached
model.)*

- **Recommendation** — verdict line per tranche and for the stack (amount requested vs. supportable,
  binding constraint, DSCR and debt yield vs. floor); top three risks each paired with the
  structural term that answers it; the counter-offer.
- **The ask** — borrower, collateral, requested structure, purpose, sponsor.
- **Capital stack** — compact table: tranche | amount | rate | DSCR | debt yield | LTC | LTV,
  senior/bridge and mezz rows plus a blended/cumulative row.
- **Coverage** — lender-case NOI and the haircut basis, Year-1 DSCR and debt yield at each tranche
  and cumulative, where the haircut bites.
- **Getting out at maturity** — stressed refi test: balance, stressed proceeds, the gap, and (for
  bridge) the stabilization path.
- **If it defaults** — recovery % of par for senior and mezz at value −10/−20/−30%, and what
  protects par for each tranche.
- **Structure** — proceeds, pricing, am/IO, reserves, covenants, recourse, and (if applicable) the
  goal-seek result for the specific ask.
- **Bottom line** — verdict, the number, and the single diligence item most likely to change it.

Two or three charts maximum (capital-stack bar showing tranche layers, DSCR sensitivity, or
recovery waterfall). Tables 5–7 rows. Round aggressively. No closing recap table.

RealAI_MF_Lending_Template.xlsxAI Created

Excel·25 KB·Sep 11, 2026

MF_Lending_Capital_Stack_Agent.mdAI Created

Markdown·12 KB·Sep 11, 2026

Multifamily Lending & Capital Stack Model — Template and Agent Instructions Ready

I can't install an agent into your workspace directly yet (that's still a "you do it in-app" action) — but I've built both pieces so you can drop the second one straight into Create an Agent as-is, and use the workbook standalone or through it.

What's in the workbook — 8 linked tabs, all formula-driven (color-coded: blue = input, black = formula, green = cross-sheet link), gated through the integrity scan and recalculated with zero errors:

Deal Summary — dashboard: capital stack, blended metrics, credit-box pass/flag checks, verdict cell
Assumptions — rate environment (Treasury/SOFR/Prime), market cap rate, growth assumptions, and the lender credit box (LTC/LTV ceilings, DSCR and debt-yield floors — separately for senior/bridge vs. the cumulative stack)
Property & NOI — Borrower T12 vs. Lender Case, with vacancy floor and OpEx-benchmark haircuts wired in
Sources & Uses — ties to a $0 check
Capital Stack — side-by-side Senior/Bridge and Mezzanine columns: amount, index/spread, all-in rate, IO period, amortization, term, fees, recourse, plus computed tranche and cumulative DSCR/debt yield/LTC/LTV with PASS/FLAG checks against the credit box
Debt Service Schedule — 5-year annual NOI/DSCR walk, senior amortization, and a Year-5 refinance test (in the sample data: senior/mezz balance of $14.1M vs. a $16.6M DSCR-constrained refi capacity → a $2.5M surplus, not a gap)
Sensitivity — DSCR grid across rate shocks × NOI changes, break-even occupancy, exit-cap sensitivity
Recovery & Exit — default waterfall at 0/-10/-20/-30% value declines: in the sample stack the senior stays whole through a 37.5% decline, the mezz starts taking a haircut only past a 27.5% decline

Coverage clears the credit box in the sample deal

The agent instructions are written as a standalone markdown file — role, one-shot intake form (property, documents, capital-stack structure, requested amounts, rate basis, recourse, sponsor financials), the datamart topics to pull (mf_property_financials, mortgage_rates.mortgage_rate_snapshot, caprate_ts, Treasury/SOFR series) versus what only the user can supply (requested proceeds, actual quoted spread, mezz economics, recourse, guarantor strength), the full cell-address map into this workbook, credit-box defaults, mezzanine-specific sizing logic (priced off residual NOI after senior debt service), and the response structure (verdict per tranche, capital-stack table, coverage, refi test, recovery, structure recommendation).

To make this a real agent: open Create an Agent, paste the instructions from the markdown file, and attach the Excel template as its reference file — that mirrors exactly how the built-in Credit Underwriting and Acquisition Analysis agents are wired (instructions + attached template).

Two calls I made in building this that are worth flagging rather than burying: the mezzanine tranche is deliberately priced against residual NOI (after senior debt service), not gross NOI — that's the standard mezz convention but it's a modeling choice, not a market fact. And the sample numbers above are the template's default illustrative inputs, not a real deal — swap the blue cells for your actual T12 and quoted terms and the whole stack recalculates.

This is a read-only snapshot of a RealAI analysis.