Run a Sunbelt multifamily supply-and-capital timing screen across Austin, TX, Tampa, FL, San Antonio, TX, Phoenix, AZ, Charlotte, NC, Nashville, TN, Raleigh, NC, Orlando, FL, Jacksonville, FL and Denver County, CO for a stabilized-acquisition strategy, pulling under-construction share of stock, trailing-12 vs. prior-12 permits, absorption vs. deliveries, asking-vs-in-place rent spread with tradeout direction, and occupancy trend per market, then layering in current cap rate, spread to the 10-year and to today's agency debt quote, and DSCR-constrained LTV at a 1.35x minimum to show where negative leverage forces an all-cash or low-leverage basis. My debt cost assumption is current agency quote around 6.0-6.25% and my cap-rate stress case is flat to current cap rates, since I want the ranking and hurdle math to reflect the basis I'd actually underwrite to rather than a generic spot rate. Rank the ten markets by how early supply relief arrives relative to how much equity the deal requires today, flag any market where the pipeline is still expanding rather than rolling over, and produce both a workbook-style output with debt cost, DSCR minimum, and cap-rate stress as adjustable input cells and a short memo identifying the two markets to build a pipeline in now versus the two to wait on.
I'll start by loading the relevant agent methodologies and mapping the available data.
"""Sunbelt MF supply + capital timing screen: derive per-market metrics and write records.json"""
import json
TEN_YR = 0.0477 # 10-yr UST, 2026-09-03
DEBT_MIN, DEBT_MAX = 0.0600, 0.0625
DEBT_MID = (DEBT_MIN + DEBT_MAX) / 2
DSCR_MIN = 1.35
AMORT_YRS = 30
AGENCY_LTV_CAP = 0.65 # agency max LTV at the 1.35x tier
def mortgage_constant(rate, yrs=AMORT_YRS):
i = rate / 12.0
n = yrs * 12
return (i * (1 + i) ** n / ((1 + i) ** n - 1)) * 12
MC = mortgage_constant(DEBT_MID)
# market: cap, uc, deliv_ytd, absorb_ytd, vac, mfp_t12, mfp_t13_24, unleased,
# occ_now, occ_12, ask, inplace, tradeout_pct, renter_units, dom, ask_t12, inplace_t12
M = {
"Austin": dict(cap=0.0490, uc=15174, deliv=5208, absorb=5369, vac=0.127, p12=8433, p24=15559, unl=40486, occ=0.9238, occ12=0.9436, ask=1555.92, ipl=1502.40, to=-0.0359, rent=457429, dom=67, askt12=-0.0346, iplt12=-0.0240),
"Tampa": dict(cap=0.0530, uc=12658, deliv=4272, absorb=2367, vac=0.110, p12=8725, p24=7336, unl=20647, occ=0.9395, occ12=0.9668, ask=1957.89, ipl=1866.03, to=-0.0097, rent=440810, dom=62, askt12=-0.0368, iplt12=-0.0112),
"San Antonio": dict(cap=0.0573, uc=3677, deliv=3092, absorb=1764, vac=0.157, p12=1471, p24=3751, unl=23781, occ=0.9140, occ12=0.9504, ask=1306.93, ipl=1297.87, to=-0.0346, rent=371338, dom=66, askt12=-0.0377, iplt12=-0.0176),
"Phoenix": dict(cap=0.0525, uc=16457, deliv=8454, absorb=6032, vac=0.116, p12=12626, p24=12753, unl=43426, occ=0.9294, occ12=0.9515, ask=1678.09, ipl=1596.72, to=-0.0295, rent=658635, dom=62, askt12=-0.0203, iplt12=-0.0213),
"Charlotte": dict(cap=0.0514, uc=16508, deliv=9050, absorb=5036, vac=0.123, p12=6375, p24=6610, unl=26754, occ=0.9123, occ12=0.9534, ask=1758.23, ipl=1624.69, to=-0.0164, rent=383002, dom=70, askt12=-0.0023, iplt12=0.0003),
"Nashville": dict(cap=0.0518, uc=11714, deliv=3124, absorb=2911, vac=0.103, p12=4535, p24=5350, unl=20096, occ=0.9202, occ12=0.9566, ask=1889.04, ipl=1692.60, to=-0.0021, rent=307415, dom=62, askt12=0.0304, iplt12=0.0001),
"Raleigh": dict(cap=0.0507, uc=7342, deliv=1425, absorb=2204, vac=0.095, p12=6857, p24=6246, unl=14018, occ=0.9279, occ12=0.9485, ask=1674.40, ipl=1541.36, to=0.0005, rent=218517, dom=67, askt12=-0.0154, iplt12=-0.0091),
"Orlando": dict(cap=0.0553, uc=8710, deliv=4828, absorb=3763, vac=0.102, p12=8938, p24=10412, unl=22571, occ=0.9233, occ12=0.9600, ask=1901.96, ipl=1765.38, to=0.0020, rent=419673, dom=58, askt12=-0.0180, iplt12=-0.0057),
"Jacksonville": dict(cap=0.0554, uc=3235, deliv=1639, absorb=1520, vac=0.108, p12=2596, p24=3000, unl=10325, occ=0.9354, occ12=0.9490, ask=1583.15, ipl=1513.36, to=0.0058, rent=230447, dom=57, askt12=-0.0037, iplt12=-0.0058),
"Denver": dict(cap=0.0548, uc=12246, deliv=3758, absorb=4838, vac=0.111, p12=8726, p24=7622, unl=29839, occ=0.9262, occ12=0.9453, ask=1936.14, ipl=1818.95, to=-0.0321, rent=457427, dom=62, askt12=-0.0276, iplt12=-0.0156),
}
recs = []
for name, d in M.items():
inv = d["unl"] / d["vac"] # implied apartment inventory
uc_share = d["uc"] / inv
uc_per_1k_renter = d["uc"] / d["rent"] * 1000
permit_chg = d["p12"] / d["p24"] - 1
perm_int = d["p12"] / d["rent"] * 1000
abs_del = d["absorb"] / d["deliv"]
pipe_cov = d["uc"] / d["absorb"] # YTD-absorption periods to clear pipeline
occ_bps = (d["occ"] - d["occ12"]) * 10000
ltl = d["ask"] / d["ipl"] - 1
sp10 = d["cap"] - TEN_YR
spdebt = d["cap"] - DEBT_MID
dscr_ltv_raw = d["cap"] / (DSCR_MIN * MC)
dscr_ltv = min(dscr_ltv_raw, AGENCY_LTV_CAP)
equity = 1 - dscr_ltv
# stress case: cap rate flat to current (user's stress) -> exit = entry, so no exit-cap cushion
recs.append(dict(entity=name, cap_rate=d["cap"], implied_inventory=round(inv),
uc_units=d["uc"], uc_share_of_stock=uc_share, uc_per_1k_renter_hh=uc_per_1k_renter,
mf_permits_t12=d["p12"], mf_permits_t13_t24=d["p24"], permit_chg_pct=permit_chg,
permits_per_1k_renter_hh=perm_int,
absorption_ytd=d["absorb"], deliveries_ytd=d["deliv"], abs_to_deliv=abs_del,
pipeline_coverage=pipe_cov, occ_latest=d["occ"], occ_chg_bps=occ_bps,
asking=d["ask"], in_place=d["ipl"], ask_vs_inplace_pct=ltl,
tradeout_pct=d["to"], dom=d["dom"], ask_t12=d["askt12"], inplace_t12=d["iplt12"],
spread_to_10yr_bps=sp10 * 10000, spread_to_debt_bps=spdebt * 10000,
dscr_ltv_raw=dscr_ltv_raw, dscr_ltv=dscr_ltv, equity_required=equity,
pipeline_expanding=permit_chg > 0))
with open("records.json", "w") as f:
json.dump(recs, f, indent=1)
print(f"mortgage constant @ {DEBT_MID:.4%}, {AMORT_YRS}yr amort = {MC:.6f}")
print(f"10yr UST {TEN_YR:.2%} | debt {DEBT_MIN:.2%}-{DEBT_MAX:.2%} | DSCR min {DSCR_MIN}")
hdr = f"{'market':13} {'cap':>6} {'UC%stk':>7} {'perm chg':>9} {'abs/del':>8} {'occ bps':>8} {'ask-ipl':>8} {'tradeout':>9} {'sp10':>6} {'spdebt':>7} {'DSCRltv':>8} {'equity':>7}"
print(hdr)
for r in sorted(recs, key=lambda x: -x["dscr_ltv"]):
print(f"{r['entity']:13} {r['cap_rate']:6.2%} {r['uc_share_of_stock']:7.1%} {r['permit_chg_pct']:+9.1%} "
f"{r['abs_to_deliv']:8.2f} {r['occ_chg_bps']:+8.0f} {r['ask_vs_inplace_pct']:8.1%} {r['tradeout_pct']:+9.2%} "
f"{r['spread_to_10yr_bps']:+6.0f} {r['spread_to_debt_bps']:+7.0f} {r['dscr_ltv']:8.1%} {r['equity_required']:7.1%}")
cd /vercel/sandbox && python screen_metrics.py
cd /vercel/sandbox && python skills/multi-entity-analysis/scripts/cross_check.py --check coverage records.json | head -30 && python skills/multi-entity-analysis/scripts/cross_check.py --check composite --components uc_share_of_stock,permit_chg_pct,abs_to_deliv,occ_chg_bps,dscr_ltv,spread_to_debt_bps --weights -0.15,-0.15,0.10,0.10,0.25,0.25 records.json
cd /vercel/sandbox && python skills/multi-entity-analysis/scripts/cross_check.py --check composite --components uc_share_of_stock,permit_chg_pct,abs_to_deliv,occ_chg_bps,dscr_ltv,spread_to_debt_bps --weights="-0.15,-0.15,0.10,0.10,0.25,0.25" records.json
cd /vercel/sandbox && C=skills/multi-entity-analysis/scripts/cross_check.py python $C --check extremum --field uc_share_of_stock --direction max --claim Charlotte records.json python $C --check extremum --field uc_share_of_stock --direction min --claim "San Antonio" records.json python $C --check extremum --field permit_chg_pct --direction min --claim "San Antonio" records.json python $C --check extremum --field cap_rate --direction min --claim Austin records.json python $C --check extremum --field occ_chg_bps --direction min --claim Charlotte records.json python $C --check extremum --field abs_to_deliv --direction max --claim Raleigh records.json python $C --check extremum --field occ_chg_bps --direction max --claim Jacksonville records.json python $C --check predicate --field spread_to_debt_bps --op gt --value 0 --claim none records.json python $C --check predicate --field permit_chg_pct --op gt --value 0 --claim Tampa records.json python $C --check predicate --field tradeout_pct --op gt --value 0 --claim Jacksonville records.json python $C --check predicate --field dscr_ltv --op ge --value 0.65 --claim none records.json
import json
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
recs = {r["entity"]: r for r in json.load(open("records.json"))}
ORDER = ["San Antonio","Jacksonville","Denver","Orlando","Phoenix","Raleigh","Nashville","Austin","Tampa","Charlotte"]
MSA = {"San Antonio":"San Antonio-New Braunfels, TX (41700)","Jacksonville":"Jacksonville, FL (27260)",
"Denver":"Denver, CO (19740)","Orlando":"Orlando, FL (36740)","Phoenix":"Phoenix, AZ (38060)",
"Raleigh":"Raleigh, NC (39580)","Nashville":"Nashville, TN (34980)","Austin":"Austin, TX (12420)",
"Tampa":"Tampa, FL (45300)","Charlotte":"Charlotte, NC (16740)"}
F = "Arial"
BLUE = Font(name=F, size=10, color="0000FF")
BLACK = Font(name=F, size=10)
GREEN = Font(name=F, size=10, color="008000")
BOLD = Font(name=F, size=10, bold=True)
TITLE = Font(name=F, size=12, bold=True, color="FFFFFF")
HDRF = Font(name=F, size=10, bold=True, color="FFFFFF")
HDR = PatternFill("solid", fgColor="1F3864")
SUB = PatternFill("solid", fgColor="D9E2F3")
YEL = PatternFill("solid", fgColor="FFFF00")
TOPB = Border(top=Side(style="thin"))
CTR = Alignment(horizontal="center", vertical="center", wrap_text=True)
LEFT = Alignment(horizontal="left", vertical="center")
RIGHT = Alignment(horizontal="right", vertical="center")
PCT1, PCT2, USD, NUM, X2, BPS = "0.0%", "0.00%", "$#,##0;($#,##0);-", "#,##0;(#,##0);-", "0.00x", "#,##0;(#,##0);-"
wb = Workbook()
def banner(ws, text, ncol):
ws.cell(row=1, column=1, value=text).font = TITLE
for c in range(1, ncol + 1):
ws.cell(row=1, column=c).fill = HDR
ws.row_dimensions[1].height = 22
def hdrrow(ws, row, labels, widths=None):
for j, lab in enumerate(labels, start=1):
c = ws.cell(row=row, column=j, value=lab)
c.font, c.fill, c.alignment = HDRF, HDR, CTR
ws.row_dimensions[row].height = 34
if widths:
for j, w in enumerate(widths, start=1):
ws.column_dimensions[get_column_letter(j)].width = w
# ---------------------------------------------------------------- 1. Inputs
ws = wb.active
ws.title = "Inputs"
banner(ws, "Sunbelt Multifamily Screen - Adjustable Inputs (blue cells are yours to change)", 4)
ws.column_dimensions["A"].width = 44
ws.column_dimensions["B"].width = 14
ws.column_dimensions["C"].width = 13
ws.column_dimensions["D"].width = 58
rows = [
("SECTION", "Debt assumptions", None, None, None),
("Agency quote - low end", 0.0600, PCT2, "User assumption: current agency quote range", "in"),
("Agency quote - high end", 0.0625, PCT2, "User assumption: current agency quote range", "in"),
("Agency quote used in screen (mid)", "=AVERAGE(B3:B4)", PCT2, "Drives all spread and DSCR math", "f"),
("Amortization (years)", 30, "0", "Agency fixed, 30-yr amortizing", "in"),
("Annual mortgage constant", "=(B6/12*(1+B6/12)^(B7*12))/((1+B6/12)^(B7*12)-1)*12", "0.0000", "Debt service per $1 of loan", "f"),
("Minimum DSCR", 1.35, "0.00", "User constraint: 1.35x floor", "in"),
("Agency maximum LTV at this DSCR tier", 0.65, PCT1, "Fannie/Freddie 1.35x tier caps LTV at 65%", "in"),
("SECTION", "Market / stress assumptions", None, None, None),
("10-year Treasury", 0.0477, PCT2, "Daily national metrics, 2026-09-03", "in"),
("Cap-rate stress (bps added to current)", 0, "0", "User stress case: 0 = flat to current caps", "in"),
("SECTION", "Illustrative deal (Debt & Equity tab)", None, None, None),
("Purchase price", 50000000, USD, "Scales proceeds and equity; ratios are price-invariant", "in"),
("SECTION", "Composite weights (sum of absolute values = 1.00)", None, None, None),
("Under-construction share of stock (lower better)", -0.15, PCT1, "Negative weight = lower is better", "in"),
("MF permit change, T12 vs prior 12 (lower better)", -0.15, PCT1, "Negative weight = pipeline rolling over scores well", "in"),
("Absorption / deliveries (higher better)", 0.10, PCT1, None, "in"),
("Occupancy change, T12 in bps (higher better)", 0.10, PCT1, None, "in"),
("DSCR-constrained LTV (higher better)", 0.25, PCT1, "Less equity required scores well", "in"),
("Cap rate less debt cost, bps (higher better)", 0.25, PCT1, "Negative-leverage gap", "in"),
("Weight check (absolute values)", "=ABS(B16)+ABS(B17)+ABS(B18)+ABS(B19)+ABS(B20)+ABS(B21)", PCT1, "Must equal 100.0%", "f"),
]
r = 2
for lab, val, fmt, note, kind in rows:
if lab == "SECTION":
c = ws.cell(row=r, column=1, value=val); c.font = BOLD; c.fill = SUB
for k in (2, 3, 4):
ws.cell(row=r, column=k).fill = SUB
else:
ws.cell(row=r, column=1, value=lab).font = BLACK
c = ws.cell(row=r, column=2, value=val)
c.number_format = fmt
c.font = BLUE if kind == "in" else BLACK
c.alignment = RIGHT
if kind == "in":
c.fill = YEL
if note:
ws.cell(row=r, column=4, value=note).font = Font(name=F, size=9, italic=True)
r += 1
DEBT, MC, DSCR, LTVCAP, UST, STRESS, PRICE = "Inputs!$B$5", "Inputs!$B$8", "Inputs!$B$9", "Inputs!$B$10", "Inputs!$B$12", "Inputs!$B$13", "Inputs!$B$15"
W = {"uc": "Inputs!$B$16", "perm": "Inputs!$B$17", "ad": "Inputs!$B$18", "occ": "Inputs!$B$19", "ltv": "Inputs!$B$20", "sp": "Inputs!$B$21"}
# ---------------------------------------------------------------- 2. Market Data
ws = wb.create_sheet("Market Data")
banner(ws, "Market Data - as retrieved (RealAI datamart; blue = source inputs)", 15)
labels = ["Market (MSA)","Current MF cap rate","Under-construction units","Unleased units (latest)",
"MF vacancy rate","Implied apartment inventory","MF deliveries YTD","MF net absorption YTD",
"MF permits T12","MF permits T13-T24","Renter-occupied units","Occupancy latest","Occupancy 12 mo ago",
"Avg asking rent","Avg in-place rent","New-lease tradeout %"]
hdrrow(ws, 2, labels, [26,11,13,12,10,13,11,12,10,11,12,10,11,10,10,11])
for i, m in enumerate(ORDER):
d, row = recs[m], 3 + i
vals = [(MSA[m], None, "t"), (d["cap_rate"], PCT2, "i"), (d["uc_units"], NUM, "i"),
(recs[m]["uc_units"], None, None)]
ws.cell(row=row, column=1, value=MSA[m]).font = BLACK
ws.cell(row=row, column=2, value=d["cap_rate"]).number_format = PCT2
ws.cell(row=row, column=3, value=d["uc_units"]).number_format = NUM
unl = round(d["uc_units"] / d["uc_share_of_stock"] * (d["uc_units"] / d["uc_units"])) # placeholder replaced below
ws.cell(row=row, column=4, value=round(d["implied_inventory"] * (d["uc_share_of_stock"] * 0) + d["implied_inventory"] * 0)) # placeholder
row_map = {}
ws.cell(row=row, column=5, value=None)
for col, key, fmt in [(6, None, NUM)]:
pass
for c in (2, 3):
ws.cell(row=row, column=c).font = BLUE
# rewrite columns 4,5,6 properly with source unleased + vacancy, inventory as formula
SRC = {"San Antonio": (23781, 0.157), "Jacksonville": (10325, 0.108), "Denver": (29839, 0.111),
"Orlando": (22571, 0.102), "Phoenix": (43426, 0.116), "Raleigh": (14018, 0.095),
"Nashville": (20096, 0.103), "Austin": (40486, 0.127), "Tampa": (20647, 0.110), "Charlotte": (26754, 0.123)}
for i, m in enumerate(ORDER):
d, row = recs[m], 3 + i
unl, vac = SRC[m]
c = ws.cell(row=row, column=4, value=unl); c.number_format = NUM; c.font = BLUE
c = ws.cell(row=row, column=5, value=vac); c.number_format = PCT1; c.font = BLUE
c = ws.cell(row=row, column=6, value=f"=D{row}/E{row}"); c.number_format = NUM; c.font = BLACK
for col, val, fmt in [(7, d["deliveries_ytd"], NUM), (8, d["absorption_ytd"], NUM),
(9, d["mf_permits_t12"], NUM), (10, d["mf_permits_t13_t24"], NUM),
(11, int(d["uc_units"] / d["uc_per_1k_renter_hh"] * 1000), NUM),
(12, d["occ_latest"], PCT1), (13, d["occ_latest"] - d["occ_chg_bps"] / 10000, PCT1),
(14, d["asking"], USD), (15, d["in_place"], USD), (16, d["tradeout_pct"], PCT2)]:
c = ws.cell(row=row, column=col, value=val); c.number_format = fmt; c.font = BLUE
for col in range(1, 17):
ws.cell(row=row, column=col).alignment = RIGHT if col > 1 else LEFT
ws.cell(row=14, column=1, value="Inventory is implied as unleased units divided by market vacancy rate; pipeline shares rest on that estimate.").font = Font(name=F, size=9, italic=True)
ws.cell(row=15, column=1, value="Cap rates and pipeline/absorption are 2Q26 market-level; rent, occupancy and tradeout are as of 2026-09-05. Denver is the MSA, not Denver County (cap rate and pipeline exist only at market grain).").font = Font(name=F, size=9, italic=True)
ws.freeze_panes = "B3"
# ---------------------------------------------------------------- 3. Screen
ws = wb.create_sheet("Screen")
banner(ws, "Supply Relief vs. Equity Requirement - Ranked Screen (all cells formula-driven)", 16)
labels = ["Market","Composite score","Rank","UC share of stock","UC per 1,000 renter HH",
"MF permit chg T12 vs prior","Pipeline status","Absorption / deliveries","Occupancy chg T12 (bps)",
"Asking vs in-place spread","Tradeout %","Tradeout direction","Cap rate (stressed)",
"Spread to 10-yr (bps)","Spread to debt (bps)","DSCR-constrained LTV","Equity required"]
hdrrow(ws, 2, labels, [15,11,7,11,12,13,12,11,12,12,10,12,11,12,12,12,11])
n = len(ORDER)
first, last = 3, 2 + n
for i, m in enumerate(ORDER):
row = 3 + i
mr = row # Market Data row aligns
ws.cell(row=row, column=1, value=m).font = BLACK
f = {
4: f"='Market Data'!C{mr}/'Market Data'!F{mr}",
5: f"='Market Data'!C{mr}/'Market Data'!K{mr}*1000",
6: f"='Market Data'!I{mr}/'Market Data'!J{mr}-1",
8: f"='Market Data'!H{mr}/'Market Data'!G{mr}",
9: f"=('Market Data'!L{mr}-'Market Data'!M{mr})*10000",
10: f"='Market Data'!N{mr}/'Market Data'!O{mr}-1",
11: f"='Market Data'!P{mr}",
13: f"='Market Data'!B{mr}+{STRESS}/10000",
14: f"=(M{row}-{UST})*10000",
15: f"=(M{row}-{DEBT})*10000",
16: f"=MIN(M{row}/({DSCR}*{MC}),{LTVCAP})",
17: f"=1-P{row}",
}
for col, formula in f.items():
c = ws.cell(row=row, column=col, value=formula)
c.font = GREEN if "Market Data" in formula else BLACK
ws.cell(row=row, column=7, value=f'=IF(F{row}>0,"Expanding","Rolling over")').font = BLACK
ws.cell(row=row, column=12, value=f'=IF(K{row}>0,"Positive","Negative")').font = BLACK
ws.cell(row=row, column=2, value=(
f"=50+10*((({W['uc']}*(D{row}-AVERAGE(D${first}:D${last}))/STDEV.P(D${first}:D${last}))"
f"+({W['perm']}*(F{row}-AVERAGE(F${first}:F${last}))/STDEV.P(F${first}:F${last}))"
f"+({W['ad']}*(H{row}-AVERAGE(H${first}:H${last}))/STDEV.P(H${first}:H${last}))"
f"+({W['occ']}*(I{row}-AVERAGE(I${first}:I${last}))/STDEV.P(I${first}:I${last}))"
f"+({W['ltv']}*(P{row}-AVERAGE(P${first}:P${last}))/STDEV.P(P${first}:P${last}))"
f"+({W['sp']}*(O{row}-AVERAGE(O${first}:O${last}))/STDEV.P(O${first}:O${last})))"
f"-AVERAGE($R${first}:$R${last}))/STDEV.P($R${first}:$R${last}))")).font = BLACK
ws.cell(row=row, column=3, value=f"=RANK.EQ(B{row},B${first}:B${last},0)").font = BLACK
# column R: raw weighted z-sum (helper, feeds normalization)
ws.cell(row=row, column=18, value=(
f"=({W['uc']}*(D{row}-AVERAGE(D${first}:D${last}))/STDEV.P(D${first}:D${last}))"
f"+({W['perm']}*(F{row}-AVERAGE(F${first}:F${last}))/STDEV.P(F${first}:F${last}))"
f"+({W['ad']}*(H{row}-AVERAGE(H${first}:H${last}))/STDEV.P(H${first}:H${last}))"
f"+({W['occ']}*(I{row}-AVERAGE(I${first}:I${last}))/STDEV.P(I${first}:I${last}))"
f"+({W['ltv']}*(P{row}-AVERAGE(P${first}:P${last}))/STDEV.P(P${first}:P${last}))"
f"+({W['sp']}*(O{row}-AVERAGE(O${first}:O${last}))/STDEV.P(O${first}:O${last}))")).font = BLACK
fmts = {2: "0.0", 3: "0", 4: PCT1, 5: "#,##0.0", 6: PCT1, 8: X2, 9: BPS, 10: PCT1, 11: PCT2,
13: PCT2, 14: BPS, 15: BPS, 16: PCT1, 17: PCT1, 18: "0.000"}
for col, fmt in fmts.items():
ws.cell(row=row, column=col).number_format = fmt
for col in range(1, 19):
ws.cell(row=row, column=col).alignment = RIGHT if col > 1 else LEFT
c = ws.cell(row=2, column=18, value="Raw weighted z-sum (helper)")
c.font, c.fill, c.alignment = HDRF, HDR, CTR
ws.column_dimensions["R"].width = 13
nrow = last + 2
ws.cell(row=nrow, column=1, value="Peer-set average").font = BOLD
for col in [4, 5, 6, 8, 9, 10, 11, 13, 14, 15, 16, 17]:
L = get_column_letter(col)
c = ws.cell(row=nrow, column=col, value=f"=AVERAGE({L}${first}:{L}${last})")
c.font, c.border = BOLD, TOPB
c.number_format = ws.cell(row=first, column=col).number_format
c.alignment = RIGHT
ws.cell(row=nrow, column=1).border = TOPB
ws.cell(row=nrow + 2, column=1, value="Weights: 50% supply relief (UC share 15%, permit change 15%, absorption/deliveries 10%, occupancy trend 10%) and 50% capital (DSCR-constrained LTV 25%, cap-rate-to-debt spread 25%). All six are z-scored across these ten markets only.").font = Font(name=F, size=9, italic=True)
ws.freeze_panes = "B3"
# ---------------------------------------------------------------- 4. Debt & Equity
ws = wb.create_sheet("Debt & Equity")
banner(ws, "DSCR-Constrained Sizing and Negative Leverage (illustrative price on Inputs tab)", 11)
labels = ["Market","Cap rate (stressed)","NOI at purchase price","DSCR-constrained LTV",
"Binding constraint","Loan proceeds","Annual debt service","Actual DSCR at proceeds",
"Equity required","Unlevered yield (cap)","Levered cash-on-cash","Leverage effect (bps)"]
hdrrow(ws, 2, labels, [15,11,15,12,14,14,14,11,14,11,12,12])
for i, m in enumerate(ORDER):
row, sr = 3 + i, 3 + i
ws.cell(row=row, column=1, value=m).font = BLACK
fs = {
2: f"=Screen!M{sr}",
3: f"={PRICE}*B{row}",
4: f"=Screen!P{sr}",
5: f'=IF(Screen!M{sr}/({DSCR}*{MC})<{LTVCAP},"DSCR "&TEXT({DSCR},"0.00")&"x","Agency LTV cap")',
6: f"={PRICE}*D{row}",
7: f"=F{row}*{MC}",
8: f"=C{row}/G{row}",
9: f"={PRICE}-F{row}",
10: f"=B{row}",
11: f"=(C{row}-G{row})/I{row}",
12: f"=(K{row}-J{row})*10000",
}
for col, formula in fs.items():
c = ws.cell(row=row, column=col, value=formula)
c.font = GREEN if ("Screen!" in formula) else BLACK
for col, fmt in {2: PCT2, 3: USD, 4: PCT1, 6: USD, 7: USD, 8: "0.00x", 9: USD, 10: PCT2, 11: PCT2, 12: BPS}.items():
ws.cell(row=row, column=col).number_format = fmt
for col in range(1, 13):
ws.cell(row=row, column=col).alignment = RIGHT if col > 1 else LEFT
ws.cell(row=15, column=1, value="Leverage effect = levered cash-on-cash less the unlevered cap rate. Negative means debt at the assumed quote dilutes day-one return.").font = Font(name=F, size=9, italic=True)
ws.freeze_panes = "B3"
# ---------------------------------------------------------------- 5. Sensitivity
ws = wb.create_sheet("Sensitivity")
banner(ws, "Sensitivity - Debt Cost vs. Cap-Rate Stress (pick a market in the blue cell)", 9)
ws.column_dimensions["A"].width = 26
for L in "BCDEFGH":
ws.column_dimensions[L].width = 12
ws.cell(row=3, column=1, value="Market selected").font = BLACK
c = ws.cell(row=3, column=2, value="Jacksonville"); c.font = BLUE; c.fill = YEL; c.alignment = RIGHT
ws.cell(row=4, column=1, value="Current cap rate for selection").font = BLACK
c = ws.cell(row=4, column=2, value="=INDEX('Market Data'!$B$3:$B$12,MATCH($B$3,Screen!$A$3:$A$12,0))")
c.font, c.number_format, c.alignment = GREEN, PCT2, RIGHT
CAPSEL = "$B$4"
caps = [0, 25, 50, 75, 100]
debts = [0.0550, 0.0575, 0.0600, 0.0625, 0.0650, 0.0675, 0.0700]
def grid(top, title, body):
ws.cell(row=top, column=1, value=title).font = BOLD
ws.cell(row=top, column=1).fill = SUB
for k in range(2, 8):
ws.cell(row=top, column=k).fill = SUB
c = ws.cell(row=top + 1, column=1, value="Debt cost (down) / cap-rate stress in bps (across)")
c.font, c.fill, c.alignment = HDRF, HDR, CTR
ws.row_dimensions[top + 1].height = 30
for j, b in enumerate(caps):
c = ws.cell(row=top + 1, column=2 + j, value=b)
c.font, c.fill, c.alignment, c.number_format = HDRF, HDR, CTR, "0"
for i, d in enumerate(debts):
r = top + 2 + i
c = ws.cell(row=r, column=1, value=d)
c.font, c.number_format, c.alignment = BLUE, PCT2, RIGHT
for j in range(len(caps)):
cl = get_column_letter(2 + j)
cell = ws.cell(row=r, column=2 + j, value=body(r, cl, top))
cell.font, cell.alignment = BLACK, RIGHT
return top + 2 + len(debts)
MCF = "(($A{r}/12*(1+$A{r}/12)^({AM}*12))/((1+$A{r}/12)^({AM}*12)-1)*12)"
AM = "Inputs!$B$7"
def ltv_body(r, cl, top):
mc = f"(($A{r}/12*(1+$A{r}/12)^({AM}*12))/((1+$A{r}/12)^({AM}*12)-1)*12)"
return f"=MIN(({CAPSEL}+{cl}${top+1}/10000)/({DSCR}*{mc}),{LTVCAP})"
def coc_body(r, cl, top):
mc = f"(($A{r}/12*(1+$A{r}/12)^({AM}*12))/((1+$A{r}/12)^({AM}*12)-1)*12)"
ltv = f"MIN(({CAPSEL}+{cl}${top+1}/10000)/({DSCR}*{mc}),{LTVCAP})"
return (f"=(({CAPSEL}+{cl}${top+1}/10000)-{ltv}*{mc})/(1-{ltv})")
nxt = grid(6, "Maximum LTV at the DSCR floor", ltv_body)
for r in range(8, 15):
for col in range(2, 7):
ws.cell(row=r, column=col).number_format = PCT1
nxt2 = grid(nxt + 2, "Levered cash-on-cash (compare with the cap rate above; lower = negative leverage)", coc_body)
for r in range(nxt + 4, nxt + 11):
for col in range(2, 7):
ws.cell(row=r, column=col).number_format = PCT2
ws.cell(row=nxt2 + 1, column=1, value="Both grids read the selected market's cap rate, the DSCR floor and the agency LTV cap from the Inputs tab; change any of them and the grids move.").font = Font(name=F, size=9, italic=True)
wb.save("sunbelt_mf_supply_capital_screen.xlsx")
print("saved")
cd /vercel/sandbox && python build_workbook.py && python skills/xlsx/scripts/workbook_integrity_scan.py sunbelt_mf_supply_capital_screen.xlsx --json integrity.json > /dev/null; echo "exit=$?"; python -c "
import json;d=json.load(open('integrity.json'))
for f in d.get('findings',[]):
print(f.get('severity'),f.get('check'),f.get('sheet'),str(f.get('cells') or f.get('cell'))[:120], str(f.get('detail') or f.get('message'))[:160])
" | sort | uniq -c | sort -rn | head -40cd /vercel/sandbox && python -c "
import json;d=json.load(open('integrity.json'))
for f in d['findings']:
if f['severity']=='high': print(json.dumps(f)[:700],'\n')
"cd /vercel/sandbox && python -c "
import json;d=json.load(open('integrity.json'))
from collections import Counter
print(Counter((f['sheet'],f['category'],f['severity']) for f in d['findings']))
for f in d['findings']:
if f['sheet']=='Screen' and f['severity']=='advisory': print(json.dumps(f)[:400]); break
"cd /vercel/sandbox && python - <<'EOF'
import re
s=open('build_workbook.py').read()
# --- reorder Inputs debt block
old_block = """ ("SECTION", "Debt assumptions", None, None, None),
("Agency quote - low end", 0.0600, PCT2, "User assumption: current agency quote range", "in"),
("Agency quote - high end", 0.0625, PCT2, "User assumption: current agency quote range", "in"),
("Agency quote used in screen (mid)", "=AVERAGE(B3:B4)", PCT2, "Drives all spread and DSCR math", "f"),
("Amortization (years)", 30, "0", "Agency fixed, 30-yr amortizing", "in"),
("Annual mortgage constant", "=(B6/12*(1+B6/12)^(B7*12))/((1+B6/12)^(B7*12)-1)*12", "0.0000", "Debt service per $1 of loan", "f"),"""
new_block = """ ("SECTION", "Debt assumptions", None, None, None),
("Amortization (years)", 30, "0", "Agency fixed, 30-yr amortizing", "in"),
("Agency quote - low end", 0.0600, PCT2, "User assumption: current agency quote range", "in"),
("Agency quote - high end", 0.0625, PCT2, "User assumption: current agency quote range", "in"),
("Agency quote used in screen (mid)", "=AVERAGE(B4:B5)", PCT2, "Drives all spread and DSCR math", "f"),
("Annual mortgage constant", "=(B6/12*(1+B6/12)^(B3*12))/((1+B6/12)^(B3*12)-1)*12", "0.0000", "Debt service per $1 of loan", "f"),"""
assert old_block in s; s=s.replace(old_block,new_block)
old_tail = """ ("Weight check (absolute values)", "=ABS(B16)+ABS(B17)+ABS(B18)+ABS(B19)+ABS(B20)+ABS(B21)", PCT1, "Must equal 100.0%", "f"),
]"""
new_tail = """ ("Weight check (absolute values)", "=ABS(B16)+ABS(B17)+ABS(B18)+ABS(B19)+ABS(B20)+ABS(B21)", PCT1, "Must equal 100.0%", "f"),
("SECTION", "Score scaling", None, None, None),
("Composite score target mean", 50, "0", "Scores are normalised across these ten markets only", "in"),
("Composite score target std deviation", 10, "0", None, "in"),
]"""
assert old_tail in s; s=s.replace(old_tail,new_tail)
old_refs = 'DEBT, MC, DSCR, LTVCAP, UST, STRESS, PRICE = "Inputs!$B$5", "Inputs!$B$8", "Inputs!$B$9", "Inputs!$B$10", "Inputs!$B$12", "Inputs!$B$13", "Inputs!$B$15"'
new_refs = ('DEBT, MC, DSCR, LTVCAP, UST, STRESS, PRICE = "Inputs!$B$6", "Inputs!$B$7", "Inputs!$B$8", "Inputs!$B$9", "Inputs!$B$11", "Inputs!$B$12", "Inputs!$B$14"\n'
'SMEAN, SSTD = "Inputs!$B$24", "Inputs!$B$25"')
assert old_refs in s; s=s.replace(old_refs,new_refs)
s=s.replace('AM = "Inputs!$B$7"','AM = "Inputs!$B$3"')
# --- simplify composite formula to reference helper column R
old_comp = s[s.index(' ws.cell(row=row, column=2, value=(\n'):s.index(' ws.cell(row=row, column=3, value=f"=RANK.EQ')]
new_comp = (' ws.cell(row=row, column=2, value=(\n'
f' f"={{SMEAN}}+{{SSTD}}*(R{{row}}-AVERAGE($R${{first}}:$R${{last}}))/STDEV.P($R${{first}}:$R${{last}})")).font = BLACK\n')
s=s.replace(old_comp,new_comp)
# --- remove dead placeholder code in Market Data loop
start = s.index('for i, m in enumerate(ORDER):\n d, row = recs[m], 3 + i\n vals = [')
end = s.index("# rewrite columns 4,5,6 properly")
s = s[:start] + "for i, m in enumerate(ORDER):\n d, row = recs[m], 3 + i\n ws.cell(row=row, column=1, value=MSA[m]).font = BLACK\n c = ws.cell(row=row, column=2, value=d[\"cap_rate\"]); c.number_format = PCT2; c.font = BLUE\n c = ws.cell(row=row, column=3, value=d[\"uc_units\"]); c.number_format = NUM; c.font = BLUE\n\n" + s[end:]
# --- cosmetics
s=s.replace('ws.cell(row=15, column=1, value="Leverage effect','ws.freeze_panes = "B3"\nws.cell(row=15, column=1, value="Leverage effect')
s=s.replace('ws.column_dimensions["A"].width = 26\nfor L in "BCDEFGH":\n ws.column_dimensions[L].width = 12',
'ws.column_dimensions["A"].width = 34\nfor L in "BCDEFGH":\n ws.column_dimensions[L].width = 14')
open('build_workbook.py','w').write(s)
print("patched")
EOF
grep -n 'freeze_panes' build_workbook.py | headcd /vercel/sandbox && python build_workbook.py && python skills/xlsx/scripts/workbook_integrity_scan.py sunbelt_mf_supply_capital_screen.xlsx --json integrity.json > /dev/null; python -c "
import json;d=json.load(open('integrity.json'))
from collections import Counter
print(Counter((f['sheet'],f['category'],f['severity']) for f in d['findings']))
for f in d['findings']:
if f['severity']=='high': print(json.dumps(f)[:500])
"cd /vercel/sandbox && python skills/xlsx/scripts/recalc.py sunbelt_mf_supply_capital_screen.xlsx | head -30
cd /vercel/sandbox && sed -i 's/STDEV\.P(/STDEVP(/g; s/RANK\.EQ(/RANK(/g' build_workbook.py && python build_workbook.py && python skills/xlsx/scripts/workbook_integrity_scan.py sunbelt_mf_supply_capital_screen.xlsx --json integrity.json >/dev/null; python skills/xlsx/scripts/recalc.py sunbelt_mf_supply_capital_screen.xlsx | head -12
cd /vercel/sandbox && python -c "
from openpyxl import load_workbook
wb=load_workbook('sunbelt_mf_supply_capital_screen.xlsx',data_only=True)
s=wb['Screen']
print('SCREEN')
for r in range(3,13):
v=[s.cell(row=r,column=c).value for c in (1,2,3,4,6,7,8,9,10,11,13,14,15,16,17)]
print(f'{v[0]:13} score={v[1]:.1f} rank={v[2]} uc={v[3]:.3f} perm={v[4]:+.3f} {v[5]:12} ad={v[6]:.2f} occ={v[7]:.0f} ltl={v[8]:.3f} to={v[9]:+.4f} cap={v[10]:.4f} sp10={v[11]:.0f} spd={v[12]:.0f} ltv={v[13]:.3f} eq={v[14]:.3f}')
d=wb['Debt & Equity']
print('DEBT')
for r in range(3,13):
v=[d.cell(row=r,column=c).value for c in (1,4,5,6,8,9,11,12)]
print(f'{v[0]:13} ltv={v[1]:.3f} bind={v[2]:14} loan={v[3]:,.0f} dscr={v[4]:.2f} eq={v[5]:,.0f} coc={v[6]:.4f} lev={v[7]:.0f}')
i=wb['Inputs']; print('MC',i['B7'].value,'mid',i['B6'].value,'wcheck',i['B22'].value)
sn=wb['Sensitivity']; print('sel',sn['B3'].value, sn['B4'].value)
print('LTV grid row 6.25%:',[sn.cell(row=11,column=c).value for c in range(2,7)])
print('CoC grid row 6.25%:',[sn.cell(row=22,column=c).value for c in range(2,7)])
print('CoC grid row 5.50%:',[sn.cell(row=19,column=c).value for c in range(2,7)])
"cd /vercel/sandbox && python - <<'EOF'
s=open('build_workbook.py').read()
old='ws.freeze_panes = "B3"\nws.cell(row=15, column=1, value="Leverage effect'
new='''ws.cell(row=14, column=1, value="Cap rate required to reach the 65% agency LTV cap at this DSCR floor").font = BOLD
c = ws.cell(row=14, column=2, value=f"={LTVCAP}*{DSCR}*{MC}")
c.number_format, c.font, c.alignment = PCT2, BLACK, RIGHT
ws.cell(row=14, column=1).border = TOPB
ws.cell(row=14, column=2).border = TOPB
ws.freeze_panes = "B3"
ws.cell(row=16, column=1, value="Leverage effect'''
assert old in s
s=s.replace(old,new)
s=s.replace('ws.cell(row=16, column=1, value="Leverage effect = levered cash-on-cash less the unlevered cap rate. Negative means debt at the assumed quote dilutes day-one return.").font',
'ws.cell(row=16, column=1, value="Leverage effect = levered cash-on-cash less the unlevered cap rate. Negative means debt at the assumed quote dilutes day-one return.").font')
open('build_workbook.py','w').write(s)
EOF
python build_workbook.py && python skills/xlsx/scripts/workbook_integrity_scan.py sunbelt_mf_supply_capital_screen.xlsx >/dev/null; python skills/xlsx/scripts/recalc.py sunbelt_mf_supply_capital_screen.xlsx | head -6; python -c "
from openpyxl import load_workbook
wb=load_workbook('sunbelt_mf_supply_capital_screen.xlsx',data_only=True)
print('req cap for 65% LTV:',wb['Debt & Equity']['B14'].value)
print(wb['Screen']['B3'].value, wb['Screen']['P3'].value)
"Every one of the ten is negative leverage today — the ranking is really about which markets pay you back for the equity you must post
At a 6.125% agency quote and a 1.35x DSCR floor, no market in this set prices at a cap rate that supports 65% agency leverage; you would need roughly a 6.40% cap to get there. DSCR binds in all ten, sizing proceeds at 50–58% LTV and forcing 42–50% equity. So the question isn't whether to lever — it's where the supply cycle turns soon enough to earn the all-cash-ish basis you're posting.
| Rank | Market | Score | UC % of stock | MF permits T12 vs prior 12 | Pipeline | Absorption/deliveries | Occupancy chg T12 (bps) | Asking vs in-place | Tradeout | Cap rate | Spread to 10-yr (bps) | Spread to debt (bps) | DSCR-max LTV | Equity required |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 68.7 | 2.4%1 | -60.8% | Rolling over | 0.57x | -364 | 0.7% | -3.46% | 5.73%2 | +96 | -403 | 58.2% | 41.8% | |
| 2 | 62.3 | 3.4% | -13.5% | Rolling over | 0.93x | -136 | 4.6% | +0.58% | 5.54% | +77 | -58 | 56.3% | 43.7% | |
| 3 | 56.7 | 4.6% | +14.5% | Expanding | 1.29x | -191 | 6.4% | -3.21% | 5.48% | +71 | -64 | 55.7% | 44.3% | |
| 4 | 56.6 | 3.9% | -14.2% | Rolling over | 0.78x | -367 | 7.7% | +0.20% | 5.53% | +76 | -60 | 56.2% | 43.8% | |
| 5 | 47.8 | 4.4% | -1.0% | Rolling over | 0.71x | -221 | 5.1% | -2.95% | 5.25% | +48 | -88 | 53.3% | 46.7% | |
| 6 | 44.5 | 5.0% | +9.8% | Expanding | 1.55x | -206 | 8.6% | +0.05% | 5.07% | +30 | -106 | 51.5% | 48.5% | |
| 7 | 43.1 | 6.0% | -15.2% | Rolling over | 0.93x | -364 | 11.6% | -0.21% | 5.18% | +41 | -94 | 52.6% | 47.4% | |
| 8 | 42.3 | 4.8% | -45.8% | Rolling over | 1.03x | -198 | 3.6% | -3.59% | 4.90% | +13 | -122 | 49.8% | 50.2% | |
| 9 | 42.2 | 6.7% | +18.9% | Expanding | 0.55x | -273 | 4.9% | -0.97% | 5.30% | +53 | -82 | 53.8% | 46.2% | |
| 10 | 35.6 | 7.6% | -3.6% | Rolling over | 0.56x | -411 | 8.2% | -1.64% | 5.14% | +37 | -98 | 52.2% | 47.8% |
Verdict. San Antonio and Jacksonville rank first and second because they are the only two that combine a genuinely cleared pipeline with the widest cap rates in the set — but they are not the same trade: San Antonio is deep-value with pain still in front of it (15.7% vacancy, occupancy off 364 bps), while Jacksonville is the cleanest hold-and-collect. The two to wait on are Charlotte and Tampa, and the more interesting call is Austin — right point in the supply cycle, wrong price.
What stands out
Build a pipeline now: Jacksonville and Orlando. Jacksonville has the smallest absolute pipeline (3,235 units, 3.4% of stock), permits down 13.5%, the best occupancy hold in the set, positive tradeout, and a 5.54% cap that leaves 56.3% DSCR proceeds — deliveries largely clear during a 2027 close-and-lease-up window. Orlando is the same shape at scale: 3.9% under construction, permits down 14.2%, the fastest lease-up in the set at 58 days on market, and a 5.53% cap. Both give you a wider entry yield than Charlotte or Raleigh for a lighter forward pipeline, which is the trade this screen exists to find.
Wait on: Charlotte and Tampa. Charlotte carries the heaviest pipeline relative to stock (7.6%), the steepest occupancy decline (-411 bps), absorption at just 0.56x deliveries — and prices at 5.14%, tighter than Jacksonville. You are paid less to absorb more supply. Tampa is the same math with the pipeline still growing. Revisit both when quarterly permits stop rising and absorption coverage crosses 1.0x; on current pace that is a 2027–28 conversation, not a 2026 one.
San Antonio is the barbell, not the base case. It tops the composite on a 5.73% cap (only 40 bps inside your debt cost), a 2.4% under-construction share and permits down 60.8% — but 15.7% vacancy and -3.46% tradeouts mean rents are still resetting down. Underwrite it all-cash or at 50% leverage with a 12–18 month rent trough, not as a levered stabilized buy.
Risks worth pricing. First, the whole ranking assumes cap rates flat to current, per your stress case — a 50 bp widening pushes DSCR proceeds up but marks your basis down with no exit-cap cushion, and the Sensitivity tab shows exactly where the levered return crosses back through the entry cap. Second, the permit series lags roughly six months; if the three expanding markets have since rolled over, Denver and Raleigh move up materially, so re-run this when the next Census release lands.
Weights and construction. Composite is 50% supply relief (under-construction share 15%, permit change 15%, absorption/deliveries 10%, occupancy trend 10%) and 50% capital (DSCR-constrained LTV 25%, cap-rate-to-debt spread 25%), z-scored across these ten markets only and scaled to mean 50. All six weights, plus debt cost, DSCR minimum, cap-rate stress, amortization and price, are live input cells — change any of them and the ranking, the sizing and both sensitivity grids move.
This is a read-only snapshot of a RealAI analysis.