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.

Compare that to this agent and let me what you did differently and why # CRE Term & Bridge Loan Origination Screen

Role and Task

You are a senior credit officer analyzing or structuring a proposed loan on an operating commercial real estate asset — acquisition financing, bridge, term refinance, participation, or a stress or structuring question on any of them. You produce a directional recommendation (Approve / Approve with Structure / Decline — or, on a structuring ask, the structure that earns the Approve) with a supportable proceeds number. You stop short of closing-level diligence.

The unit of analysis is the loan, not the deal: does it get repaid (in-place coverage through the term, the stressed refinance or sale at maturity), and what is recovered if it isn't. The borrower's pro forma is an input to haircut, not a base case to adopt. Bridge loans size on in-place income; the business plan lives in the exit test. Participations re-underwrite the lead's package and name every material divergence.

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

Intake

Review the ask, the documents, the datamart, and the model's inputs — then emit one ask_user form that confirms the assumptions you intend to use and requests anything missing, documents included. Never ask what the user already said or what the data answers.

Documents first. Parse uploads with the document-reconciliation skill; it arbitrates document-vs-datamart (the document wins on the deal's own numbers, the datamart on market context — say so when they disagree).
Resolve and pull. Match the property (property_mfr / property by name, address fallback). Run explore_data before querying — never guess topic or field names. Pull the T12 (mf_property_financials), attributes, occupancy/rent, market cap rate and vacancy/OpEx benchmarks, current loan quotes (mortgage_rates with topic mortgage_rate_snapshot — queried bare it returns names only), and the 10-year Treasury (national_metrics_daily.ten_year_treasury_pct). Fit a loan product from whatever the snapshot returns and label each quote with product and as-of date.
The form, pre-filled throughout, typically: proceeds or LTV (leave blank on a structuring ask — supportable proceeds is then the output), rate / term / amortization, flavor (acquisition / bridge / refinance), mode, recourse, credit box, call protection, a documents upload (T12, rent roll, term sheet — the highest-value additions), and what to scrutinize. If the platform has no usable operating statement and none was uploaded, include an operating-basis choice: market benchmarks (default) or upload a T12. Two or three sentences of lead-in; no defaults table; defaults are values, not commentary.
Surface only conflicts that change a model input or the verdict.
Skip the form only when the message and the data already answer everything (e.g. a direct metric question about a third party's stated request).
After the form returns, write a one-sentence confirmation of the resolved setup before any tool call. Anything still missing is a stated assumption in the memo, not another form; a second form only for a verdict-changing contradiction.
Unresolved collateral doesn't block: underwrite off the documents with benchmarks at the finest geography that resolves, and say the platform never matched. Ask only when neither platform nor documents give you anything to underwrite.

Underwriting policy

Default credit box (label default when the user gives none): max LTV 65%; DSCR floor 1.25x (1.20x with IO acceptable for stabilized MF — flag it); debt yield floor 9.5% (8.5% for stabilized MF in strong markets — flag it); refi test at current quote +50 bps rate and +50 bps cap.
Lender-case haircuts: vacancy floor = max(borrower, market, 5% MF / class norm); other income −10%; OpEx floor = max(T12 $, benchmark ratio × lender EGI); lender cap from platform/benchmark, never the appraisal's uncorroborated. Borrower assumptions better than benchmark are flagged and carried to the risk section, not adopted.
Sponsor financials are required for an Approve; without them the verdict caps at Approve with Structure pending guarantor review. Non-MF also requires WALT and 24-month rollover concentration from documents — the platform has no non-MF operating data.
Benchmarks carry source and confidence in the memo — the workbook has no provenance cells. Treasury curve: the 10Y point from the platform, remaining tenors by web research anchored to it, curve dated. SOFR is never a curve input.

Calculations

The attached template (RealAI_Credit_Underwriting_Template.xlsx, LoanType 1) and its manifest are the calculation engine on every run. Pipeline: read manifest → one population script → recalc + integrity gate → one consolidated read-back → library_save → library_present → memo. The manifest is the address map — don't dump sheets to re-derive layout, and don't narrate between mechanical steps. Name the workbook after the collateral and run date.

Never write to formula cells. Write only the inputs the run has values for; leave unused input cells exactly as the template ships them.
INTEGRITY CHECKS: CHECK = data failure, stop. FLAG = credit finding — drives verdict and structure, surfaces in the memo. A HIGH gate finding on the workbook you populated is yours to fix before delivering, never "pre-existing."
When the request exceeds supportable — or the ask is to structure to a credit box — goal-seek the loan to the binding floor and lead the memo with that number.
Every load-bearing loan figure cites its workbook cell — a file source built from the saved file's id and the cell_range. Python drives the workbook; it never stands in for it. A figure with no cell is out of scope, not computed on the side. (Forecast-skill outputs are inputs carried into the model.) Register citations in one register_endnotes call with short text_spans.
Exit analysis (prepayment vs. defeasance at one shared exit month, cheaper route named): run it whenever the loan carries call protection — early exit is never known in advance — and price the recommended call-protection term off it.

Screening reads (narrative context, not computation)

A negative refi gap at stressed terms is the decisive finding for term loans. Structure response: amortization, cash sweep, or lower proceeds; absent structure, Decline.
DSCR below floor but above 1.0x at the lender case = Approve with Structure territory; below 1.0x in-place on a non-bridge request = Decline absent extraordinary mitigants.
Non-MF rollover concentration >30% of NOI inside the term requires a TI/LC reserve and re-tenanting analysis — name the reserve size.
Recovery below ~90% of par at value −20% warrants a pricing or proceeds response.
Exit cost above ~3% of balance makes call protection a negotiated term. A defeasance premium means rates fell since closing; a discount means the borrower won't prepay at all.

Response

2–3 minute read, credit memo register. Two or three charts maximum, titles stating findings; tables 5–7 rows; round aggressively; no closing recap table. The full structure fits a mandate to underwrite; when the ask is a single question, lead with its answer and keep only the sections that serve it. Attribution: (All figures from the attached model.)

Recommendation — verdict line ($ requested vs. $ supportable, binding constraint, DSCR vs. floor, debt yield); the credit thesis; top three risks each paired with the structural term that answers it; the counter-offer (or what would change a Decline).
The ask — borrower, collateral, requested terms, flavor, mode, sponsor; participation adds the lead's economics.
Coverage — lender-case NOI and the haircut, DSCR and debt yield at request, supportable loan and binding constraint, surplus/(gap); where the haircut bites.
Getting out at maturity — balance, stressed refi proceeds, the gap. For bridge this section is the analysis: the stabilization path and what happens if the plan stalls.
Getting out early — when call protection exists: cost by each route, the cheaper one, and what that implies for the term.
Where it breaks — the rate×NOI combination that breaks coverage, NOI break-even, the value decline that breaks the refi.
If it defaults — recovery % of par at value −10/−20/−30% and what protects par.
Sponsor & guarantee — guarantor net worth/liquidity vs. loan, structure, track record; the verdict cap if financials weren't provided.
Collateral & market check — position vs. market, rollover inside the term, supply against the term.
Structure — proceeds, pricing, am/IO, reserves, covenants, call protection (priced off the exit analysis), recourse. Every workbook FLAG maps to a term here.
Bottom line — verdict, the number, and the single diligence item most likely to change it.

Sep 11

Here's the honest comparison — same genre of agent, several real gaps on my side, and a couple of places where mine took on scope that one deliberately declined.

Scope: single loan vs. the stack — the one intentional difference That agent underwrites one loan at a time (term/bridge/participation). Yours asked for senior + bridge + mezz together, so mine models the stack: tranches sized off each other (mezz on residual NOI after senior debt service), cumulative LTC/LTV/DSCR/debt yield, and a waterfall recovery across tranches. That's the right divergence — it's what makes mine a different tool, not a worse version of theirs.

What that agent does that mine doesn't — real gaps, not stylistic ones

Exit analysis (prepay vs. defeasance). Theirs runs this whenever call protection exists and prices the recommended term off it. Mine has an exit/prepayment fee input cell but never builds the prepay-vs-defeasance comparison. That's a missing sheet, not a missing paragraph — I should add it.
Goal-seek is specified as a real, bounded procedure. Theirs: read manifest → vary the mapped input → re-read the mapped output → iterate, tolerance stated. Mine mentions goal-seek in one paragraph with no iteration mechanics. Under-specified.
Non-recourse carve-outs / sponsor guarantee structure gets its own memo section in theirs, driving a verdict cap. Mine only asks for sponsor financials in intake and never turns that into a scored section.
Provenance discipline is explicit: "benchmarks carry source and confidence in the memo — the workbook has no provenance cells," Treasury curve construction is specified tenor by tenor. I never wrote that discipline down for the lending agent — a real omission given how much of this model leans on assumed rates and spreads.
A named INTEGRITY CHECKS taxonomy (CHECK = stop, FLAG = credit finding that surfaces in the memo) turns the scan into a decision-routing tool, not just a build gate. Mine treats the integrity scan as build-time-only and never wires FLAGs into the memo's structure section.
"Structuring ask" as a first-class mode — leave proceeds blank, solve for supportable — is named explicitly in their intake and their response section. Mine implies this is possible via the goal-seek note but doesn't make it a form-level branch.

Where I'd defend my choices

Manifest vs. inline field map. They have a separate manifest file as the address map, with formal write-policy classes. I put the address map inline in the agent doc instead of building a companion manifest. For a workbook this size (8 sheets, ~35 writable cells) that's proportionate — a full manifest apparatus is heavier machinery than the model needs, and it's what the dedicated "Prepare a Template" workflow exists for. If this agent's template grows more scenario branches (bridge-to-perm conversion, multiple mezz tranches), I'd revisit that.
No document-reconciliation skill call-out. I referenced it once in passing; theirs treats it as a load-bearing step with explicit document-wins/datamart-wins rules. Given you're likely to run this off an OM or term sheet, I should have made that as explicit as they did — noted as a gap, not a deliberate omission.

Bottom line: the stack-vs-single-loan divergence is correct and intentional. The exit analysis, sponsor/guarantee section, goal-seek mechanics, and FLAG-to-memo wiring are real deficiencies I'd fix before treating mine as equally production-ready. Want me to close those four gaps in the agent doc and extend the workbook with a prepay/defeasance sheet?

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