Underwrite Signature at Sugarloaf, Duluth GA as a value-add acquisition, deriving a going-in price range per unit from the Atlanta multifamily cap rate and from comparable sales of 1995–2008 vintage, 200–400 unit assets in the Atlanta market over the trailing 12 months. From the datamart T12, compute the comp-supported market rent gap by bedroom type and a stabilized NOI assuming a $15,000-per-unit renovation program; my renovation scope assumption is full interior/exterior repositioning and my expected renovation-to-stabilization timeline is 12 months. At the midpoint of the derived price range, calculate the stabilized unlevered yield on cost and test it against a 6.5% hurdle, and run a sensitivity showing which single assumption — cap rate, rent gap capture, or renovation cost — moves the answer most.
cat agents/019eb33d-d26e-739e-91bf-0c2b1fe4a447/RealAI_Pro_Forma_Template_manifest_v5.1.md
sed -n '/^## 7. Runner Workflow/,$p' agents/019eb33d-d26e-739e-91bf-0c2b1fe4a447/RealAI_Pro_Forma_Template_manifest_v5.1.md
Now the cap rates, market supply, rent comps pool, and subject rent roll (unit mix).
Goal: produce engine-sourced underwriting growth assumptions for a value-add acquisition screen of Signature at Sugarloaf (multifamily, 268 units, built 2000, 2255 Satellite Blvd, Duluth GA 30097). Datamart IDs: property_mfr id = 04e92f71da4316ba14a89924b82cec4b; submarket id = efb38c416bdf62954a437082a2054ef7; county id = 13135 (Gwinnett); market id = 12060 (Atlanta, GA). Today is 2026-09-28. These outputs feed an Excel pro forma (annual rent growth, annual expense growth, exit cap rate view), so I need 10 annual values for trend metrics.
Rules (mandatory):
forecasting skill via sandbox_skills and follow it exactly. scripts/forecast.py (run as python skills/forecasting/scripts/forecast.py --file payload.json) is the SOLE math authority. Run --version first. Never compute, adjust or estimate a forecast in prose, never write your own forecasting code, never port the script. Read references/request.md for payload schema before building payloads.Runs required: A. Rent growth — metric mf_rent_ts.asking_rent_latest (family rent_or_occupancy), request scenarios ["base","upside","downside"], 10-year annual horizon. History: the submarket (efb38c416bdf62954a437082a2054ef7) monthly asking-rent series (use the property's own series only if the submarket series is unavailable or too short); peer_history: Atlanta market 12060 monthly series. Also check the in-place sibling series per the skill's sibling rule. Context signals you may source: Atlanta market supply_snapshot (mf_units_permitted_t12, mf_under_construction_units, mf_vacancy_rate_pct, mf_deliveries_ytd_units, mf_net_absorption_units) and market migration topic if available. B. Expense growth — metric mf_property_financials.mf_pnl_total_operating_expenses, family operating, scenarios base/upside/downside, 10-year horizon; follow the skill's guidance for what history to pass for this family (if no expense time series exists in the datamart, follow the skill's documented behavior, e.g. inflation anchor, and report exactly what the engine did). C. Cap rate — Atlanta market 12060 caprate_ts.multifamily, family capital_markets (directional mode). Pass the full quarterly history. Return band min / structural mean / max, current level, band position, and the rate-environment signal (source rate context from national_metrics_daily or national_metrics_monthly treasury fields if the skill asks for it).
Workspace: write everything to subagents/forecast/ — raw query JSON, a single build script subagents/forecast/build_and_run.py that parses raw results, writes payload_rent.json, payload_opex.json, payload_cap.json, runs the engine on each, and saves out_rent.json, out_opex.json, out_cap.json. The script must be re-runnable end to end from the saved raw files (no network).
Return: (1) for rent: base/upside/downside annual growth arrays (years 1-10), confidence tier, and every data_quality_flags entry verbatim, plus boom_extrapolation_suspected / cyclical_drawdown_suspected status; (2) same for expense growth; (3) cap rate band min/mean/max, current value and quarter, band position label, rate-environment signal, and any flags verbatim; (4) the history source used for each (entity, id, field, number of periods, date range); (5) file paths of all outputs; (6) any gaps or conflicts. Do not provide an underwriting opinion.
All figures below come verbatim from the forecasting engine's stdout (skills/forecasting/scripts/forecast.py, params_version 2026Q3.5, sha256 517fce0a...e37fa9, verified via --version). No forecast math was computed by hand. Full payloads and outputs are saved under subagents/forecast/ (see Files) and are re-runnable end-to-end via python subagents/forecast/build_and_run.py with no network calls.
=== (1) RENT GROWTH — mf_rent_ts.asking_rent_latest_avg, family rent_or_occupancy, mode=trend === Subject: Duluth submarket (efb38c416bdf62954a437082a2054ef7), Atlanta MSA. Peer: Atlanta market 12060. Confidence: LOW. Base case (flat after Year 1 — see cyclical-drawdown flag): Yr1 -1.07%, Yr2-10: 0.00% each year (level held at $1,653.18). Upside case annual pct_change, Yr1→Yr10: 0.93%, 2.30%, 2.64%, 3.04%, 3.50%, 4.02%, 4.63%, 5.32%, 6.12%, 7.04%. Downside case annual pct_change, Yr1→Yr10: -3.07%, -2.30%, -2.64%, -3.04%, -3.50%, -4.02%, -4.63%, -5.32%, -6.12%, -7.04%. boom_extrapolation_suspected: false. cyclical_drawdown_suspected: TRUE. data_quality_flags (verbatim):
=== (2) EXPENSE GROWTH — mf_property_financials.mf_pnl_total_operating_expenses, family operating, mode=trend === Subject: Signature at Sugarloaf (property_mfr id 04e92f71da4316ba14a89924b82cec4b). Confidence: LOW. Base case annual pct_change, Yr1→Yr10: flat 3.50% every year (structural terminal rate fallback). Upside case: 5.00%, 5.23%, 5.48%, 5.78%, 6.12%, 6.52%, 6.97%, 7.49%, 8.09%, 8.78%. Downside case: 2.00%, 1.78%, 1.52%, 1.22%, 0.88%, 0.48%, 0.03%, -0.49%, -1.09%, -1.78%. data_quality_flags (verbatim):
=== (3) CAP RATE — caprate_ts.multifamily, Atlanta market 12060, family capital_markets, mode=directional (no projection) === Confidence: HIGH. Full-history band (1Q05–2Q26, 86 valid quarterly points, 21.25 years): min 3.67%, structural mean 5.90%, max 8.58%. Current level (2Q26): 5.33%. Band position: 0.3381 → label "cheap" (i.e., cap rate sits in the lower/tighter third of its full historical range, cheap = trading rich to that wide-history mean... per engine's own label, "cheap" here is the band-position label the engine assigns, not an economic judgment). Trailing-10-year band: min 3.67%, structural mean 5.11%, max 5.79%. Current 5.33% vs. this narrower band → position 0.783 → label "rich". Rate-environment signal: "stable" (current fed funds 3.63% [Aug-2026, national_metrics_monthly] vs. projected fed funds median 3.60% for 2029 [national_metrics_annual_projection, furthest available point, matching as_of 2026 + capital_markets' 3-year horizon window] — both on the fed_funds_rate instrument). data_quality_flags (verbatim):
=== (4) HISTORY SOURCES USED ===
=== (5) FILE PATHS ===
python subagents/forecast/build_and_run.py.=== (6) GAPS / CONFLICTS / CAVEATS ===
cd /vercel/sandbox && python subagents/forecast/build_and_run.py >/dev/null 2>&1; python -c "
import json
for f in ['out_rent','out_opex','out_cap']:
d=json.load(open(f'subagents/forecast/{f}.json'))
print(f, d.get('confidence'), {k:d[k] for k in d if k in ('base_case','band_position','signals')} if f!='out_cap' else {k:d.get(k) for k in ('band_position','signals','current_level')})
" | cut -c1-1500
T=agents/019eb33d-d26e-739e-91bf-0c2b1fe4a447/RealAI_Pro_Forma_Template_v5.1.xlsx
python - <<'EOF'
import openpyxl
wb=openpyxl.load_workbook('agents/019eb33d-d26e-739e-91bf-0c2b1fe4a447/RealAI_Pro_Forma_Template_v5.1.xlsx')
ws=wb['Pro Forma']
for r in range(1,44):
row=[(c.coordinate,c.value) for c in ws[r][:6] if c.value is not None]
if row: print(row)
EOF# Raw datamart values transcribed from query_data results (2026-09-28).
SUBJECT = dict(id="04e92f71da4316ba14a89924b82cec4b", name="Signature at Sugarloaf", address="2255 Satellite Blvd",
csz="Duluth, GA 30097", style="GARDEN", units=268, sf=285420, yb=2000, lat=33.981305658817384, lon=-84.0942531824112,
gpr=4639715.37, vac=133269.18, oi=582151.21, tax=624297.71, ins=190306.62, util=431959.16, rm=322271.12,
mgmt=127215.01, payroll=615485.48, ga=161372.61, adv=69172.39, noi=2546517.28, opex=2542080.11,
occ=0.9664, ip_avg=1440.29, ip={1:1205.34, 2:1399.43, 3:1636.59, 4:1807.43},
ask={1:1268.33, 2:1538.00, 3:1907.00})
# Rent roll bedroom sequence (rent_roll_latest WEEK, 144 tracked units, page1 then page2)
RR_BEDS = [4,3,3,1,4,1,1,2,1,2,2,3,1,1,2,2,2,4,3,3,3,2,2,1,2,2,1,3,2,2,2,3,3,1,2,2,1,3,2,2,2,3,2,1,3,2,2,1,3,2,
3,3,2,3,1,3,3,1,2,2,2,1,3,3,1,2,3,3,2,2,2,1,3,2,2,3,3,3,2,1,2,1,1,1,1,3,3,4,1,2,3,4,4,3,3,1,3,4,2,2,
2,3,2,1,3,3,3,1,1,2,1,2,2,2,1,3,2,1,1,1,3,3,2,1,4,3,2,1,2,3,1,1,3,2,1,1,2,2,2,2,2,3,3,1]
# Rent comps: renovated 1995-2008 garden/low-rise, Gwinnett, near subject
RENT_COMPS = [
dict(name="The Quinn Sugarloaf", address="100 Woodiron Dr", csz="Duluth, GA 30097", lat=33.98663252592095, lon=-84.09457504749298, units=386, sf=382526, yb=1998, yr=2016, ip=1540.40, ask=1767.68, occ=0.9352, b={1:1296.73,2:1627.07,3:2039.76,4:None}),
dict(name="The Reserve at Sugarloaf", address="2605 Meadow Church Rd", csz="Duluth, GA 30097", lat=33.992603123188104, lon=-84.09742891788483, units=333, sf=407925, yb=2001, yr=2021, ip=1789.50, ask=1792.85, occ=0.9640, b={1:1462.46,2:1863.51,3:2148.66,4:2928.67}),
dict(name="Oaks at Sugarloaf", address="5375 Sugarloaf Pkwy", csz="Lawrenceville, GA 30043", lat=33.96831303834924, lon=-84.06514585018158, units=406, sf=441322, yb=2001, yr=2024, ip=1688.37, ask=1591.44, occ=0.9557, b={1:1464.35,2:1780.60,3:2154.58,4:None}),
dict(name="Astor Place", address="1435 Boggs Rd", csz="Duluth, GA 30096", lat=33.95391494035729, lon=-84.09018695354463, units=308, sf=319088, yb=1999, yr=2014, ip=1536.46, ask=1613.40, occ=0.9123, b={1:1337.05,2:1578.82,3:1822.07,4:None}),
dict(name="The Hartley at Sweetwater Creek", address="1500 Boggs Rd NW", csz="Duluth, GA 30096", lat=33.95592659711846, lon=-84.09696757793427, units=280, sf=299600, yb=1996, yr=2016, ip=1511.31, ask=1514.71, occ=0.9036, b={1:1255.49,2:1540.70,3:1791.96,4:None}),
dict(name="Everly", address="2800 Herrington Woods Ct", csz="Lawrenceville, GA 30044", lat=33.9444413781167, lon=-84.08480107784271, units=324, sf=324648, yb=1997, yr=2017, ip=1533.80, ask=1545.18, occ=0.9784, b={1:1257.94,2:1498.21,3:1802.54,4:2055.71}),
dict(name="The Veranda", address="100 Veranda Chase Dr", csz="Lawrenceville, GA 30044", lat=33.94768148660668, lon=-84.08746182918549, units=250, sf=329000, yb=2002, yr=2011, ip=1729.10, ask=1786.07, occ=0.9360, b={1:1400.74,2:1747.51,3:2076.95,4:None}),
dict(name="The Asher at Sugarloaf", address="4975 Sugarloaf Pkwy", csz="Lawrenceville, GA 30044", lat=33.958018720150086, lon=-84.05592978000641, units=260, sf=291720, yb=2007, yr=2016, ip=1631.76, ask=1650.72, occ=0.9500, b={1:1388.52,2:1702.23,3:1993.74,4:None}),
]
# Sales comps: Atlanta MSA, 1995-2008 vintage, 200-400 units, sold since 2025-09-28, priced, garden/low-rise
# (excluded: The Eva - high-rise; Battle Creek Village Townhomes - $81k/unit outlier; unpriced records)
# adj = (time, size, condition, location, market) ; AI-estimated, disclosed
SALE_COMPS = [
dict(name="Preserve at Mill Creek", address="1400 Mall Of Georgia Blvd", csz="Buford, GA 30519", lat=34.059513509273614, lon=-83.99918496608736, units=400, yb=2001, yr=2015, date="2026-05-26", price=83200000, ip=1530.35, adj=(0,0,-0.05,0,0)),
dict(name="The Aubrey at Sweetwater", address="2222 E W Connector", csz="Austell, GA 30106", lat=33.86234968900689, lon=-84.61951553821564, units=324, yb=1999, yr=None, date="2026-04-16", price=63242987, ip=1417.68, adj=(0,0,0,0.05,0)),
dict(name="The Glen at Lanier Crossing", address="10 Glenwood Way", csz="Stockbridge, GA 30281", lat=33.538118898868646, lon=-84.19611275196075, units=264, yb=2003, yr=None, date="2026-03-30", price=43012188, ip=1461.01, adj=(0,0,0,0.05,0)),
dict(name="Village at Almand Creek", address="1825 Parker Rd SE", csz="Conyers, GA 30094", lat=33.64277869462976, lon=-84.0248590707779, units=236, yb=2002, yr=None, date="2026-03-20", price=40000000, ip=None, adj=(0,0,0,0.05,0)),
dict(name="The Grove at Greenleaf", address="50 Greenleaf Rd", csz="Conyers, GA 30013", lat=33.60523849725732, lon=-83.96953046321869, units=240, yb=1999, yr=None, date="2026-03-04", price=33075000, ip=None, adj=(0,0,0,0.05,0)),
dict(name="Perimeter Gardens at Georgetown", address="100 Azalea Garden Dr", csz="Dunwoody, GA 30338", lat=33.9255478978158, lon=-84.31019246578218, units=245, yb=2007, yr=None, date="2025-11-05", price=53500000, ip=1551.48, adj=(0,0,0,-0.10,0)),
dict(name="Park at The Hill", address="5385 Peachtree Dunwoody Rd", csz="Atlanta, GA 30342", lat=33.901713788509454, lon=-84.35133755207062, units=296, yb=1996, yr=2015, date="2025-10-01", price=69500000, ip=1851.14, adj=(0,0,-0.05,-0.10,0)),
]
ATL_MF_CAP = 0.0533 # caprate_ts 2Q26, market 12060
"""Comp-supported rent gap by bedroom and weighted rent-lift % for Signature at Sugarloaf."""
import json, math, statistics as st
from collections import Counter
from data import SUBJECT as S, RR_BEDS, RENT_COMPS
def miles(a, b, c, d):
R = 3958.8
p1, p2 = math.radians(a), math.radians(c)
dp, dl = p2 - p1, math.radians(d - b)
h = math.sin(dp/2)**2 + math.cos(p1)*math.cos(p2)*math.sin(dl/2)**2
return 2*R*math.asin(math.sqrt(h))
for c in RENT_COMPS:
c["dist"] = round(miles(S["lat"], S["lon"], c["lat"], c["lon"]), 2)
# Unit mix: rent-roll sample proportions scaled to 268 units
cnt = Counter(RR_BEDS); n = len(RR_BEDS)
mix = {b: cnt[b]/n for b in (1, 2, 3, 4)}
units = {b: round(mix[b]*S["units"]) for b in mix}
units[2] += S["units"] - sum(units.values()) # rounding remainder to largest type
rows = {}
for b in (1, 2, 3):
vals = [c["b"][b] for c in RENT_COMPS if c["b"][b] is not None]
comp = st.mean(vals)
rows[b] = dict(subject=S["ip"][b], comp_avg=round(comp, 2), comp_n=len(vals),
comp_min=min(vals), comp_max=max(vals),
gap=round(comp - S["ip"][b], 2), gap_pct=round(comp/S["ip"][b]-1, 4))
# 4BR: only 2 comps report 4BR rents -> too thin; apply the 3BR % premium (conservative)
v4 = [c["b"][4] for c in RENT_COMPS if c["b"][4] is not None]
p3 = rows[3]["gap_pct"]
rows[4] = dict(subject=S["ip"][4], comp_avg=round(S["ip"][4]*(1+p3), 2), comp_n=len(v4),
comp_raw_avg=round(st.mean(v4), 2), gap=round(S["ip"][4]*p3, 2), gap_pct=p3,
note="3BR % premium applied; raw 2-comp 4BR average not used")
cur = sum(units[b]*rows[b]["subject"] for b in rows)
tgt = sum(units[b]*rows[b]["comp_avg"] for b in rows)
lift_pct = tgt/cur - 1
wtd_gap = (tgt - cur)/S["units"]
out = dict(sample_units=n, mix_pct={b: round(mix[b], 4) for b in mix}, units=units, by_bed=rows,
wtd_current=round(cur/S["units"], 2), wtd_target=round(tgt/S["units"], 2),
wtd_gap_per_unit_mo=round(wtd_gap, 2), annual_gap_gross=round(wtd_gap*12*S["units"]),
lift_pct=round(lift_pct, 4),
comp_dist={c["name"]: c["dist"] for c in RENT_COMPS},
comp_avg_occ=round(st.mean(c["occ"] for c in RENT_COMPS), 4))
json.dump(out, open("rent_gap.json", "w"), indent=1)
print(json.dumps(out, indent=1))
cd /vercel/sandbox/sugarloaf && python rent_gap.py
cd /vercel/sandbox && grep -n -i "clear_policy\|required_runtime_input\|source_policy" agents/019eb33d-d26e-739e-91bf-0c2b1fe4a447/RealAI_Pro_Forma_Template_manifest_v5.1.md | head; python - <<'EOF'
import openpyxl
wb=openpyxl.load_workbook('agents/019eb33d-d26e-739e-91bf-0c2b1fe4a447/RealAI_Pro_Forma_Template_v5.1.xlsx')
ws=wb['Assumptions']
for rng in ['B4:I20','B21:Q29','B31:I61','B62:D70']:
for row in ws[rng]:
for c in row:
if c.value is not None and not (isinstance(c.value,str) and c.value.startswith('=')): print(c.coordinate,repr(c.value)[:40], end=' | ')
print()
EOF"""Populate RealAI Pro Forma Template v5.1 per manifest, recalc, read outputs.
Usage: python populate.py -> runs base + one-at-a-time sensitivity cases, writes results.json
"""
import json, shutil, subprocess, datetime, sys, os
import openpyxl
from data import SUBJECT as S, RENT_COMPS, SALE_COMPS, ATL_MF_CAP
TEMPLATE = "../agents/019eb33d-d26e-739e-91bf-0c2b1fe4a447/RealAI_Pro_Forma_Template_v5.1.xlsx"
RECALC = "../skills/xlsx/scripts/recalc.py"
GAP = json.load(open("rent_gap.json"))
FC_RENT = json.load(open("../subagents/forecast/out_rent.json"))
FC_OPEX = json.load(open("../subagents/forecast/out_opex.json"))
HOLD = 5
# Market rent path: engine Yr1 base; engine flagged cyclical drawdown and held flat after Yr1, so
# Yrs 2+ use an analyst recovery path (JUDGMENT): 1.5% in Yr2, then the engine's 2.8% terminal rate.
MKT = [FC_RENT["base_case"][0]["pct_change"], 0.015, 0.028, 0.028, 0.028]
OPEX_G = [FC_OPEX["base_case"][i]["pct_change"] for i in range(HOLD)]
OCC = [0.89, 0.94, 0.94, 0.94, 0.94] # Yr1 renovation downtime (~22 units offline); 94% stabilized default (comp avg 94.2%)
EXIT_CAP, DISP, CLOSE, RES = 0.0575, 0.02, 0.025, 250
LTV, RATE, TERM = 0.60, 0.0575, 10 # default financing (not sourced; unlevered metrics unaffected)
INPUT_CELLS = ["C5","C6","C7","C8","C9","C10","C11","C12","C15","C18","C22","C23","C26","C28","C32",
"H15","H16","H17","H18","H19","H33","H34","H37","H41","H42","H43","H44","H45","H46","H47","H48","H49",
"C38","C40","C41","C42","C43","C56","C57","C75","C79","C80","C81","C82","C83","C84"]
def staged(capture, lift=GAP["lift_pct"]):
L = lift*capture
y1 = (1+MKT[0])*(1+0.5*L) - 1 # half the lift captured on average during the 12-mo program
y2 = (1+MKT[1])*(1+L)/(1+0.5*L) - 1 # remainder captured at stabilization
return [y1, y2] + MKT[2:]
def write(path, price, capex_per_unit, capture):
shutil.copy(TEMPLATE, path)
wb = openpyxl.load_workbook(path)
A = wb["Assumptions"]; R = wb["Rent Comps"]; SC = wb["Sales Comps"]
# clearing pass over all runtime write surfaces
for c in INPUT_CELLS: A[c].value = None
for rng in ["H26:Q29"]:
for row in A[rng]:
for c in row: c.value = None
for ws, rngs in ((R, ["D6:K14","C17:K21","D25:K25"]), (SC, ["D6:K12","D15:K16","D18:K19","D22:K26","D28:K28"])):
for rng in rngs:
for row in ws[rng]:
for c in row:
if isinstance(c.value, str) and c.value.startswith("="): raise SystemExit(f"formula in write target {c.coordinate}")
c.value = None
# Assumptions
A["C5"], A["C6"], A["C7"], A["C8"] = S["name"], S["address"], S["csz"], S["style"]
A["C9"], A["C10"], A["C11"] = S["units"], S["sf"], S["yb"]
A["C15"], A["C18"], A["C22"], A["C23"] = round(price), CLOSE, EXIT_CAP, DISP
A["C26"], A["C28"], A["C32"] = capex_per_unit*S["units"], 1, RES
A["H15"], A["H16"], A["H17"], A["H18"], A["H19"] = HOLD, MKT[2], OPEX_G[0], MKT[2], OCC[1]
A["H22"] = "Yes"
rg = staged(capture)
cols = "HIJKL"
for i in range(HOLD):
A[f"{cols[i]}26"] = round(rg[i], 6); A[f"{cols[i]}27"] = OCC[i]
A[f"{cols[i]}28"] = MKT[i]; A[f"{cols[i]}29"] = OPEX_G[i]
A["H33"], A["H34"], A["H37"] = S["gpr"], -abs(S["vac"]), S["oi"]
for cell, k in zip(["H41","H42","H43","H44","H45","H46","H47","H48"],
["tax","ins","util","rm","mgmt","payroll","ga","adv"]):
A[cell] = S[k]
A["C38"], A["C40"], A["C43"] = LTV, RATE, TERM
# Rent comps (D:K) + subject by-bed
for j, c in enumerate(RENT_COMPS):
col = "DEFGHIJK"[j]
for r, v in zip(range(6, 15), [c["name"], c["address"], c["csz"], GAP["comp_dist"][c["name"]], c["units"], c["sf"], c["yb"], c["yr"], c["ip"]]):
R[f"{col}{r}"] = v
for b in (1, 2, 3, 4):
R[f"{col}{17+b}"] = c["b"][b]
R[f"{col}25"] = c["occ"]
for b in (1, 2, 3, 4): R[f"C{17+b}"] = S["ip"][b]
# Sales comps
from rent_gap import miles
w = round(1/len(SALE_COMPS), 6)
for j, c in enumerate(SALE_COMPS):
col = "DEFGHIJK"[j]
for r, v in zip(range(6, 13), [c["name"], c["address"], c["csz"], round(miles(S["lat"], S["lon"], c["lat"], c["lon"]), 1), c["units"], c["yb"], c["yr"]]):
SC[f"{col}{r}"] = v
SC[f"{col}15"] = datetime.datetime.fromisoformat(c["date"]); SC[f"{col}15"].number_format = "yyyy-mm-dd"
SC[f"{col}16"] = c["price"]; SC[f"{col}18"] = c["ip"]
for r, v in zip(range(22, 27), c["adj"]): SC[f"{col}{r}"] = v
SC[f"{col}28"] = w
SC[f"{'DEFGHIJK'[len(SALE_COMPS)-1]}28"] = round(1 - w*(len(SALE_COMPS)-1), 6)
wb.save(path)
r = subprocess.run([sys.executable, RECALC, path], capture_output=True, text=True)
return read(path)
def read(path):
wb = openpyxl.load_workbook(path, data_only=True)
A, P, RS, SC, RC, SU = wb["Assumptions"], wb["Pro Forma"], wb["Returns Summary"], wb["Sales Comps"], wb["Rent Comps"], wb["Sources & Uses"]
g = lambda ws, c: ws[c].value
out = dict(price=g(A,"C15"), price_unit=g(A,"C16"), basis=g(A,"C19"), t12_noi=g(A,"H52"), t12_cap=g(A,"H5"),
yr1_noi=g(P,"D26"), yr2_noi=g(P,"E26"), yr2_egi=g(P,"E11"), yr2_opex=g(P,"E23"), yr1_egi=g(P,"D11"),
yoc_yr1=g(A,"H7"), unlev_irr=g(A,"H8"), lev_irr=g(A,"H9"), em=g(A,"H11"), dscr_yr2=g(P,"E42"),
t12_egi=g(A,"H38"), t12_opex=g(A,"H50"), opex_ratio=g(A,"H60"), occ_t12=g(A,"H36"),
comps_wtd=g(SC,"C32"), comps_unadj=g(SC,"C34"), rent_comp_avg=g(RC,"C29"), rent_comp_occ=g(RC,"C33"),
su_diff=g(SU,"C11"), su=g(SU,"G11"))
out["yoc_stab"] = out["yr2_noi"]/out["basis"] if out["basis"] else None
errs = [f"{ws.title}!{c.coordinate}" for ws in wb for row in ws.iter_rows() for c in row
if isinstance(c.value, str) and c.value.startswith("#") and c.value.rstrip("!?0/").upper() in ("#REF","#VALUE","#DIV","#NAME","#N/A")]
out["errors"] = errs[:10]
return out
def price_mid(cap, comps_wtd):
cap_val = S["noi"]/cap
comp_val = comps_wtd*S["units"]
return dict(cap=cap, cap_value=cap_val, cap_value_unit=cap_val/S["units"], comp_value=comp_val, comp_value_unit=comps_wtd,
low=min(cap_val, comp_val), high=max(cap_val, comp_val), mid=(cap_val+comp_val)/2)
if __name__ == "__main__":
os.makedirs("runs", exist_ok=True)
probe = write("runs/probe.xlsx", 45_000_000, 15000, 1.0) # pass 1: read comps-derived $/unit
comps_wtd = probe["comps_wtd"]
cases = {"base": (ATL_MF_CAP, 15000, 1.00),
"cap -50bp": (ATL_MF_CAP-0.005, 15000, 1.00), "cap +50bp": (ATL_MF_CAP+0.005, 15000, 1.00),
"capture 75%": (ATL_MF_CAP, 15000, 0.75), "capture 125%": (ATL_MF_CAP, 15000, 1.25),
"reno $10k": (ATL_MF_CAP, 10000, 1.00), "reno $20k": (ATL_MF_CAP, 20000, 1.00)}
res = {"comps_wtd_unit": comps_wtd, "lift_pct": GAP["lift_pct"], "staged_base": staged(1.0), "cases": {}}
for name, (cap, reno, capt) in cases.items():
pr = price_mid(cap, comps_wtd)
fn = "runs/" + name.replace(" ", "_").replace("$", "").replace("%", "pct") + ".xlsx"
o = write(fn, pr["mid"], reno, capt)
res["cases"][name] = dict(inputs=dict(cap=cap, reno=reno, capture=capt), price=pr, out=o)
print(name, round(pr["mid"]/S["units"]), round(o["yr2_noi"]), round(o["yoc_stab"], 4), o["errors"], o["su"])
json.dump(res, open("results.json", "w"), indent=1, default=str)
cd /vercel/sandbox/sugarloaf && time python populate.py 2>&1 | tail -20
cd /vercel/sandbox/sugarloaf && python -c "
import json;r=json.load(open('results.json'))
print('comps',r['comps_wtd_unit'],'staged',[round(x,4) for x in r['staged_base']])
b=r['cases']['base'];print(json.dumps(b['price'],indent=0));print(json.dumps(b['out'],indent=0))
for k,v in r['cases'].items(): print(k, round(v['price']['mid']), round(v['out']['basis']), round(v['out']['yoc_stab'],4), round(v['out']['unlev_irr'],4))
""""Breakeven purchase price for a 6.5% stabilized YoC, and worst plausible combination - both run through the workbook."""
import json
from populate import write, price_mid, S, ATL_MF_CAP
r = json.load(open("results.json")); base = r["cases"]["base"]["out"]
HURDLE = 0.065
capex, res = 15000*S["units"], 250*S["units"]
be_price = (base["yr2_noi"]/HURDLE - capex - res)/1.025 # Total Basis C19 = C15*(1+C18)+C26+C33
o_be = write("runs/breakeven_price.xlsx", be_price, 15000, 1.0)
pr = price_mid(ATL_MF_CAP-0.005, r["comps_wtd_unit"])
o_worst = write("runs/worst_combo.xlsx", pr["mid"], 20000, 0.75)
out = dict(breakeven_price=round(be_price), breakeven_unit=round(be_price/S["units"]), be_check_yoc=round(o_be["yoc_stab"], 4),
discount_to_mid=round(1-be_price/r["cases"]["base"]["price"]["mid"], 4),
worst=dict(price_unit=round(pr["mid"]/S["units"]), yoc=round(o_worst["yoc_stab"], 4), noi=round(o_worst["yr2_noi"])),
reno_only_yield=round((base["yr2_noi"]-base["t12_noi"])/capex, 4))
json.dump(out, open("sens_extra.json", "w"), indent=1); print(out)
cd /vercel/sandbox/sugarloaf && python sens_extra.py 2>&1 | tail -2
cd /vercel/sandbox/sugarloaf && ls && python sens_extra.py 2>&1 | tail -2
cd /vercel/sandbox/sugarloaf && cp runs/base.xlsx SignatureAtSugarloaf_underwriting_2026-09-28.xlsx && python ../skills/xlsx/scripts/workbook_integrity_scan.py SignatureAtSugarloaf_underwriting_2026-09-28.xlsx --json integ.json >/dev/null; echo scan_exit=$?; python ../skills/xlsx/scripts/recalc.py SignatureAtSugarloaf_underwriting_2026-09-28.xlsx | head -c 400; python - <<'EOF'
import openpyxl,json
wb=openpyxl.load_workbook('SignatureAtSugarloaf_underwriting_2026-09-28.xlsx',data_only=True)
W=wb['Waterfall'];Se=wb['Sensitivity'];RS=wb['Returns Summary'];A=wb['Assumptions']
print('wf',W['C71'].value,[W.cell(71,c).value for c in range(4,14)])
print('sens tie',Se['F8'].value,RS['C21'].value,Se['F17'].value,RS['C22'].value,Se['F26'].value)
print('H34',A['H34'].value,'H36',A['H36'].value,'H60',A['H60'].value,'C50',A['C50'].value, 'H5',A['H5'].value,'C64',A['C64'].value,A['C77'].value,A['C78'].value)
j=json.load(open('integ.json'));print([ (f.get('severity'),f.get('check'),f.get('cell')) for f in j.get('findings',[]) if f.get('severity')=='high'][:10])
EOFcd /vercel/sandbox/sugarloaf && python - <<'EOF'
import json,collections
j=json.load(open('integ.json'))
f=[x for x in j.get('findings',[]) if x.get('severity')=='high']
print(collections.Counter((x.get('category') or x.get('type') or x.get('kind'),x.get('sheet')) for x in f))
print(json.dumps(f[:4],indent=0)[:1500])
EOF
# baseline: scan raw template
python ../skills/xlsx/scripts/workbook_integrity_scan.py ../agents/019eb33d-d26e-739e-91bf-0c2b1fe4a447/RealAI_Pro_Forma_Template_v5.1.xlsx --json tmpl.json >/dev/null 2>tmpl.err; tail -1 tmpl.errcd /vercel/sandbox/sugarloaf && python -c "
import openpyxl
wb=openpyxl.load_workbook('SignatureAtSugarloaf_underwriting_2026-09-28.xlsx',data_only=True);S=wb['Sales Comps']
for c in 'DEFGHIJ': print(c,S[c+'6'].value,S[c+'9'].value,S[c+'17'].value and round(S[c+'17'].value),S[c+'27'].value, S[c+'29'].value and round(S[c+'29'].value))
A=wb['Assumptions'];P=wb['Pro Forma']
print(A['C15'].value,A['C16'].value,A['C19'].value,A['H5'].value,A['H8'].value,P['E26'].value,P['E11'].value,P['E23'].value,P['E42'].value,S['C32'].value,S['C34'].value)
"NO-GO at the price range I derived. It becomes CONDITIONAL only at about $156k/unit or less. At the midpoint ($181k/unit, $48.6M), the renovation takes NOI from $2.55M to $3.05M. That's a stabilized unlevered yield on cost of 5.65%, about 85 bps short of your 6.5% hurdle. The renovation itself works: it returns 12.4% on the $4.0M spent. The problem is that the purchase price is already at full market value, so the upside doesn't raise the yield on the total cost enough.
| Method | Value | $/unit |
|---|---|---|
| Cap rate: T12 NOI $2.55M ÷ Atlanta MF cap 5.33% (2Q26) | $47.8M | $178.3k |
| Sales comps, weighted after adjustments (7 trades) | $49.5M | $184.6k |
| Midpoint (used) | $48.6M | $181.5k |
The midpoint puts the entry cap on T12 NOI at 5.24% . That's within 25 bps of the market cap, so this is market pricing, not a discount.
Sale comps. I found 14 Atlanta trades in the last 12 months that fit your screen (1995–2008, 200–400 units) . Seven are usable. I dropped five with no recorded price, The Eva (a high-rise) and Battle Creek Townhomes (an outlier at $81k/unit). The datamart doesn't record cap rates for these sales.
| Property | Sold | Mi | Units | Built | $/unit | Adj. $/unit |
|---|---|---|---|---|---|---|
| Preserve at Mill Creek | May-26 | 7.7 | 400 | 2001 | $208,000 | $197,600 |
| Aubrey at Sweetwater | Apr-26 | 31.2 | 324 | 1999 | $195,194 | $204,954 |
| Glen at Lanier Crossing | Mar-26 | 31.2 | 264 | 2003 | $162,925 | $171,071 |
| Village at Almand Creek | Mar-26 | 23.7 | 236 | 2002 | $169,492 | $177,966 |
| Grove at Greenleaf | Mar-26 | 27.0 | 240 | 1999 | $137,812 | $144,703 |
| Perimeter Gardens | Nov-25 | 13.0 | 245 | 2007 | $218,367 | $196,531 |
| Park at The Hill | Oct-25 | 15.7 | 296 | 1996 | $234,797 | $199,578 |
I compared the subject to 8 nearby comps, all built 1996–2007, all renovated 2011–2024, 0.4 to 2.7 miles away . Their average occupancy is 94.2% .
| Type | Est. units | Subject in-place | Renovated comp avg | Gap |
|---|---|---|---|---|
| 1BR | 73 | $1,205 | $1,358 | +$153 (12.7%) |
| 2BR | 98 | $1,399 | $1,667 | +$268 (19.1%) |
| 3BR | 82 | $1,637 | $1,979 | +$342 (20.9%) |
| 4BR* | 15 | $1,807 | $2,185 | +$378 (20.9%) |
| Weighted | 268 | $1,442 | $1,707 | +$265/mo (18.4%), $853k/yr gross |
*Only two comps report 4BR rents, so I applied the 3BR percentage instead. The unit mix is estimated from 144 units tracked in the rent roll.
| Line | T12 (current) | Stabilized (Year 2) |
|---|---|---|
| Effective gross income | $5.09M | $5.77M |
| Operating expenses | $2.54M | $2.72M |
| NOI | $2.55M | $3.05M |
| Total cost (price, 2.5% closing, $4.02M renovation, reserves) | $53.9M | |
| Unlevered yield on cost | 5.24% | 5.65% vs 6.5% hurdle |
Each row changes one input and re-runs the workbook :
| Assumption | Downside | Base | Upside | Spread |
|---|---|---|---|---|
| Rent-gap capture (75% / 125%) | 5.28% | 5.65% | 6.02% | 74 bps |
| Cap rate (+50 / −50 bps), which moves the price midpoint | 5.88% at +50 bps | 5.65% | 5.40% at −50 bps | 48 bps |
| Renovation cost ($20k / $10k per unit) | 5.51% | 5.65% | 5.79% | 28 bps |
Rent-gap capture moves the answer most. Each 25 points of capture is worth about 37 bps of yield.
Forecast caveats. These are the forecasting engine's own warnings, which I've carried into the assumptions:
The financing (60% loan-to-value, interest-only, 5.75% rate) is a default I assumed, not a sourced rate. It gives a Year-2 debt service coverage of 1.82x and doesn't affect any of the unlevered figures above.
Bottom line: The rent gap is supported by comps, and a full interior/exterior repositioning is the right scope to close it. But at current market pricing, the deal can't reach 6.5% on any single assumption that's defensible. Pursue it only at about $156k/unit or below. If you do, the next step is a phased pro forma with unit-by-unit turn timing and a test-unit program to confirm the 2BR and 3BR premiums, which are about 80% of the lift.
The workbook has the full model, including the comp grids and the 12-month staged renovation.
This is a read-only snapshot of a RealAI analysis.