Shared analysis

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.

8:38 PM

Instructions

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):

Load the 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.
Build every history/peer array programmatically from raw query results you save to disk (save the raw query JSON first, then parse it in a Python script). Include history_check / peer_history_check in every payload. No hand-typed arrays.
Every context_signals leaf must trace to a tool result; otherwise omit the sub-block and log it in caller_disclosures.signals_omitted.
Use explore_data before referencing any field name in query_data. Time-series topics (mf_rent_ts, caprate_ts, permit_ts) must each be queried in their own call; mf_rent_ts requires filter mf_rent_ts.period_type (use MONTH).

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.

Results

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):

"cyclical drawdown suspected: history is short (<= 30mo) and trailing trend is negative. The engine held flat after year 1 rather than extrapolating the correction. Supply a longer history that spans a full cycle, or have the calling agent apply structural context and a defended recovery path. Do NOT use this base case as a multi-year assumption without that judgment."
"wide scenario divergence relative to point estimate"
"signals not triaged: [employment] - each family-relevant signal must be populated from a real source or logged in caller_disclosures.signals_omitted with a reason; a silent absence is indistinguishable from a signal never considered" Note: item 3 fired despite employment being logged in caller_disclosures.signals_omitted (reason: "not gathered") — no employment/job-growth field was located at market/submarket grain this session; this is a caller gap, disclosed, not fabricated. Signals returned: supply_pressure = "moderate", migration_signal = "neutral", employment_signal = "unknown". Methodology notes carried in output: trend_r2 0.3955, recent_annualized_growth -1.35%, long_run_mean_growth -1.34%, terminal_rate_applied 2.8%, peer_level_ratio 1.065 (peer richer), history depth 26 valid months.

=== (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):

"insufficient history for a trend fit (1 point(s), minimum 6): the structural terminal rate (0.0350) was applied as a flat growth path with no trend component. This is the expected case for operating series (a single T12), not an error, but the path carries no subject-specific signal - confidence is low." Methodology: primary_method "Structural terminal rate fallback (insufficient history; no trend fit)"; terminal_rate_applied 3.5%; signals.inflation_anchor = 0.025 (engine-applied, not caller-supplied). No boom/drawdown flags fired (both false; family has no supply/demand signal path per skill). Cause: the datamart carries no expense time series at any grain for mf_pnl_total_operating_expenses — only the current-period RealAI Ops Benchmarks figure per property. Only 1 point (subject property, $2,542,080.11 total opex as of 2026-08-31) was available; no prior-year T12s/tax bills were found to reach the 6-point minimum, so the engine applied the documented 1-5-point operating fallback exactly as the skill describes.

=== (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):

"scenarios ignored: capital_markets is directional, not projected; no scenario envelope is produced"
"band position differs between the full history (cheap) and the trailing 10 years (rich): the window spans multiple regimes - report both reads, do not silently pick one" No projected cap-rate values were produced — directional mode never projects, by design.

=== (4) HISTORY SOURCES USED ===

Rent subject history: submarket efb38c416bdf62954a437082a2054ef7 (Duluth), mf_rent_ts, period_type=MONTH, field asking_rent_latest_avg. 26 valid monthly points, Jul-2024 through Aug-2026 (Jan–Jun 2024 dropped: null/no coverage, logged in caller_disclosures.excluded_periods).
Rent peer history: market 12060 (Atlanta, GA), same field/period_type, 26 valid points, same window.
Sibling check: submarket in_place_rent_latest_avg checked in the same fetch; trailing-12-month growth gap (asking vs. in-place) = 0.66 percentage points — below the 100bps materiality threshold, so only the note was required (both series were not separately run).
Expense history: property_mfr 04e92f71da4316ba14a89924b82cec4b (Signature at Sugarloaf), mf_property_financials.mf_pnl_total_operating_expenses, 1 point, as_of 2026-08-31, $2,542,080.11.
Cap rate history: market 12060, caprate_ts.multifamily, period_type=QUARTERLY, 86 valid points, 1Q05–2Q26 (4Q04 dropped: null).
Rate-environment context: national_metrics_monthly fed_funds_rate = 3.63% (period 2026-08-01); national_metrics_annual_projection fed_funds_rate_median_projection = 3.60% for period 2029-01-01 (furthest available annual projection).
Rent context signals: Atlanta market supply_snapshot — mf_under_construction_units 13,609, mf_units_permitted_t13_t24 11,551, existing_stock (unit_count) 495,715 units (mf_rent_ts). Migration: market 12060 migration topic — household_income_avg_in $198,716 vs. household_income_avg_out $211,679 (outbound cohort earns more; contributed to "neutral" migration_signal).
mf_deliveries_ytd_units / mf_net_absorption_units / mf_vacancy_rate_pct were retrieved (supply_snapshot: 7,104 / 6,230 / 11.2%) but not mapped into the engine's schema leaves (supply_pipeline only accepts existing_stock/under_construction_t12/permitted_units_t13_t24) — not a gap, just not applicable fields for this schema.

=== (5) FILE PATHS ===

subagents/forecast/raw_submarket_rent_ts.json, raw_market_rent_ts.json, raw_market_caprate_ts.json, raw_market_supply_snapshot.json, raw_market_migration.json, raw_property_financials.json, raw_national_metrics.json — retained raw query results.
subagents/forecast/build_and_run.py — single build script; parses raw files, writes payloads, invokes engine, writes outputs. Re-run with python subagents/forecast/build_and_run.py.
subagents/forecast/payload_rent.json, payload_opex.json, payload_cap.json — assembled engine payloads.
subagents/forecast/out_rent.json, out_opex.json, out_cap.json — full verbatim engine responses.

=== (6) GAPS / CONFLICTS / CAVEATS ===

Rent base case is a defect-guarded flat line after Year 1 because the drawdown guard fired on a short (26-month), declining-trend history; the engine explicitly instructs NOT to use it as a multi-year underwriting assumption without applying structural judgment — that judgment (e.g., a defended recovery path) belongs to the domain analysis consuming this output, not to this sub-agent.
The submarket/market mf_rent_ts series only has usable coverage starting Jul-2024 (25–26 months); this falls short of the skill's 24-month "high confidence" bar only marginally, and well short of the 30-month cyclical-guard threshold — hence low confidence and the drawdown flag.
employment.job_growth_1_year_pct was not located at market or submarket grain in the topics explored this session; logged as "not gathered" rather than fabricated, but the "signals not triaged" flag still fired because the engine treats it as an unresolved gap regardless of the disclosure.
Expense growth carries essentially zero subject-specific signal (single T12 point, structural fallback only) — this is expected per the skill's documented operating-family behavior, not an error, but it means the expense growth path is effectively an inflation-anchored placeholder (2.5% inflation anchor informing a 3.5% base) rather than a Signature-at-Sugarloaf-specific trend.
Cap rate: full-history and trailing-10-year bands disagree on label ("cheap" vs. "rich") because the 21-year window spans multiple regimes (GFC, ZIRP, 2022 repricing); both reads are reported per the engine's own flag — no single reconciled view was picked, by design.
No underwriting opinion, workbook, or recommendation was produced — out of scope per the brief and the forecasting skill's boundary.

Content

40 lines

# 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

Content

48 lines

"""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))

Content

128 lines

"""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)

SignatureAtSugarloaf_underwriting_2026-09-28.xlsxAI Created

Excel·104 KB·Sep 28, 2026

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.

Price range

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

The unadjusted average is $189.5k .
My adjustments are my own estimates: −5% on comps that were recently renovated, −10% on Buckhead/Dunwoody locations, +5% on west and south-side Atlanta locations. All seven are equally weighted.
No time adjustment was needed; the market cap rate stayed at 5.33–5.34% over the period .

Rent gap by bedroom

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.

Renovated comps lease $150–$380 above Sugarloaf by bedroom

How it underwrites (at the midpoint price)

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

The renovation adds about $0.5M of NOI on $4.02M spent, a 12.4% return .
Only about $500k of the $853k gross rent gap reaches NOI. Two things eat the rest:
- Occupancy: I stabilize at 94%, in line with the comps, versus the subject's current 97.1% economic occupancy.
- Expenses: they grow 3.5% a year for two years.
Over a 5-year hold with a 5.75% exit cap, the unlevered IRR is 6.0% .

Sensitivity: which single assumption moves the answer most

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.

None of the single changes gets to 6.5%, not even 125% capture.
The worst combination (cap −50 bps, 75% capture, $20k/unit) gives 4.92%.
To hit 6.5% at base assumptions, the price has to be about $41.7M ($155.7k/unit), 14% below the midpoint .

Risks

Whether the rent gap is real. The comp average includes The Reserve at $1,790, which was renovated in 2021. Test-renovate units before closing to prove $265/mo.
Supply. Atlanta vacancy is 11.2%, with 13,609 units under construction and 12,042 permitted in the last 12 months . Market asking rents are down 1.7% year over year . The renovated target of $1,707 is still below nearby newer buildings, which helps.
How solid the T12 is. Other income is $582k, 11.4% of EGI , near the top of the normal range. The site is in FEMA flood zone AE , so check insurance before relying on the $190k expense line.
Operations are already good. Occupancy is 96.6%, retention is 80% against a 66% market average, and new leases are signing 25.6% above the prior rent . That means the upside is almost entirely physical renovation, not fixing a poorly run property.

Forecast caveats. These are the forecasting engine's own warnings, which I've carried into the assumptions:

Rent growth (low confidence): "cyclical drawdown suspected… Do NOT use this base case as a multi-year assumption without that judgment." The engine also flagged "wide scenario divergence relative to point estimate" and "signals not triaged: [employment]". I used its −1.1% for Year 1 , then my own recovery assumption: 1.5% in Year 2 and 2.8% after that.
Expenses: a flat 3.5% a year, low confidence, because there's only one T12 to work from.
Cap rates: 5.33% is "cheap" against the full-history band (3.67–8.58%, mean 5.90%) but "rich" against the last 10 years. The engine's note: "report both reads, do not silently pick one" .

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.