We are a value-add buyer of 1980s DFW garden assets. Find market-rate properties in the East Plano/Allen and Richardson submarkets, 100 to 400 units, built 1975 to 1990, not renovated since 2015, where in-place rent per square foot is in the bottom third of the submarket. Rank by the gap to the submarket's renovated comps.
mkdir -p /vercel/sandbox/work && cd /vercel/sandbox/work && echo ok
id,name,submarket,city,zip,year_built,year_renovated,units,unit_sqft,rent_type,style,inplace_psf,inplace_rent,asking_psf,occ,conf,coverage,retention,sale_date,sale_ppu 03bc30b9,Villas at Chase Oaks,EPA,Plano,75023,1986,2022,250,735,MARKET,GARDEN,1.73,1339.32,1.86,0.9760,EXCELLENT,0.608,0.912,2022-03-30, 0616035d,Junction 15,EPA,Plano,75074,2013,2020,278,869,MARKET,MID_RISE,1.93,1639.90,2.09,0.9424,EXCELLENT,0.655,0.7554,2020-02-28, 09ae8095,Tides at North Dallas,RICH,Dallas,75240,1980,2022,232,587,MARKET,GARDEN,1.67,987.28,1.58,0.8621,EXCELLENT,1.0,0.7586,2022-05-05,53663.79 111ed249,Carisma,RICH,Dallas,75240,1969,2024,164,935,MARKET,GARDEN,1.20,1083.41,1.21,0.8354,EXCELLENT,0.512,0.689,, 13ec53e1,Villas Del Sol,EPA,Plano,75074,1971,1998,95,805,MARKET,LOW_RISE,1.41,998.00,,1.0000,INSUFFICIENT,0.021,,2023-12-27,183461.05 18244fd5,Hunters Glen,EPA,Plano,75023,1979,2011,276,946,MARKET,GARDEN,1.39,1278.37,1.21,0.8370,EXCELLENT,1.0,0.6667,2022-02-07, 1c60bc9a,SYNC CityLine,EPA,Richardson,75082,2017,,354,838,MARKET,MID_RISE,1.85,1475.15,1.90,0.9802,EXCELLENT,0.542,0.5763,2022-01-27, 1fd7eb77,Oaks at Spring Valley,RICH,Richardson,75080,1965,2023,56,945,MARKET,GARDEN,1.41,1262.75,1.44,0.9107,EXCELLENT,0.589,0.5179,2022-05-04, 208e2a54,Fox Trails,EPA,Plano,75023,1981,2014,286,961,MARKET,GARDEN,1.54,1440.99,1.64,0.8776,EXCELLENT,1.0,0.2692,2011-12-06,65419.58 281201d1,Amber Dawn,RICH,Dallas,75240,1970,2023,157,937,AFFORDABLE,LOW_RISE,1.30,1220.42,1.25,0.9554,EXCELLENT,0.599,0.5605,, 2a91802c,Windham Chase,RICH,Richardson,75080,1971,2021,236,1161,MARKET,GARDEN,1.28,1468.53,1.25,0.9364,EXCELLENT,0.992,0.6992,2020-06-03, 2c8783f0,Park on 14th,EPA,Plano,75074,2025,,62,767,MARKET_AND_AFFORDABLE,MID_RISE,1.84,1439.56,1.82,0.8710,EXCELLENT,1.0,0.9355,2025-08-07, 2d891a15,Casa de Arroyo,RICH,Dallas,75240,1968,2005,50,755,MARKET,GARDEN,1.63,1173.91,1.52,0.7600,EXCELLENT,1.0,,2024-05-13,100933.70 3101f9f1,Ferro,EPA,Plano,75074,2022,,379,863,MARKET,LOW_RISE,2.21,1881.41,2.30,0.9499,EXCELLENT,1.0,0.7098,2022-06-20, 320d8d68,Hunters Court,RICH,Dallas,75240,1976,2005,184,710,MARKET,GARDEN,1.59,1113.63,1.51,0.9348,EXCELLENT,0.424,0.6739,2005-09-07, 32b8504e,Opal Legacy Central,EPA,Plano,75023,2021,,310,868,MARKET_AND_AFFORDABLE,MID_RISE,1.79,1504.14,1.90,0.9548,EXCELLENT,0.929,0.5484,2021-10-20, 347fd65d,The Riley,EPA,Richardson,75082,2016,,262,937,MARKET_AND_AFFORDABLE,MID_RISE,2.05,1869.59,2.14,0.8855,EXCELLENT,0.595,0.6756,, 348bd874,The Gio,EPA,Plano,75074,1995,2022,730,905,MARKET,GARDEN,1.57,1391.98,1.52,0.9411,EXCELLENT,0.984,0.6507,2021-11-09, 364ec561,Greenbriar,EPA,Plano,75023,1983,,182,783,MARKET,LOW_RISE,1.61,1256.46,1.66,0.9780,EXCELLENT,0.5,0.7967,2013-08-14, 3692f381,The Emory,EPA,Plano,75074,2023,,270,926,MARKET,MID_RISE,2.19,1902.04,2.29,0.7778,EXCELLENT,1.0,0.5037,2024-12-05, 3e5a31bc,Axis 110,EPA,Richardson,75082,2015,,351,878,MARKET,MID_RISE,1.97,1686.07,1.79,0.9459,EXCELLENT,1.0,0.5698,2025-12-02, 41f9787a,Shenandoah,RICH,Richardson,75080,1969,2020,192,939,MARKET,LOW_RISE,1.56,1450.39,1.54,0.9167,EXCELLENT,1.0,0.6979,, 4ab7d7b0,Centric One90,EPA,Plano,75074,2016,,386,843,MARKET,LOW_RISE,1.89,1552.25,1.96,0.9456,EXCELLENT,1.0,0.6528,2016-06-06, 596f0de5,Legacy Apartments,EPA,Plano,75023,1984,2000,244,879,MARKET,GARDEN,1.66,1453.68,1.70,0.9303,EXCELLENT,1.0,0.75,2006-10-23,67879.10 5aa832ea,Plano Park Townhomes,EPA,Plano,75074,1985,2008,140,1010,MARKET,TOWNHOUSE,1.54,1543.73,1.63,1.0000,EXCELLENT,0.436,0.9429,2013-04-30, 5bdf5789,Mission Eagle Pointe,EPA,Allen,75002,2002,2016,252,916,MARKET,GARDEN,1.44,1320.55,1.56,0.9286,EXCELLENT,0.683,0.7103,2022-08-02,201830.56 5c571c33,Bellevue at Spring Creek,EPA,Plano,75023,1982,2001,278,951,MARKET,GARDEN,1.59,1489.49,1.47,0.9568,EXCELLENT,1.0,0.7338,2025-06-24, 617f11fe,Bel Air Downtown,EPA,Plano,75074,2001,,253,783,MARKET,MID_RISE,1.75,1326.36,1.88,0.7787,EXCELLENT,0.949,0.9051,2021-06-03, 61f9b584,Morada Plano,EPA,Plano,75074,2019,,183,862,MARKET,MID_RISE,2.06,1749.39,2.03,0.9563,EXCELLENT,0.934,0.5191,2022-02-07, 6b14995a,Esperanza,RICH,Dallas,75240,1964,,71,902,MARKET,LOW_RISE,1.42,1272.67,1.34,0.7746,EXCELLENT,0.648,0.5352,2021-04-16,93309.86 6cd449f6,Cottonwood,RICH,Dallas,75240,1983,2018,270,834,MARKET,GARDEN,1.37,1129.33,1.39,0.9667,EXCELLENT,0.407,0.6185,2021-03-22,117657.41 77a2494f,The Register,EPA,Richardson,75082,2020,,306,857,MARKET,MID_RISE,2.23,1862.89,2.33,0.9314,EXCELLENT,1.0,0.5882,2022-11-18, 7976ebf5,Hillsdale Garden,RICH,Richardson,75080,1969,,72,842,MARKET,LOW_RISE,1.66,1366.40,1.49,0.9722,GOOD,0.361,0.7083,2022-03-04, 7ca9a09f,Custer Park,EPA,Plano,75023,1978,2018,232,958,MARKET,GARDEN,1.45,1363.50,1.28,0.9440,EXCELLENT,0.504,0.8017,2023-03-22, 7cc2129f,1201 Park,EPA,Plano,75074,1996,2017,368,744,MARKET,GARDEN,1.60,1183.07,1.79,0.9647,EXCELLENT,0.668,0.6984,2021-01-08,142765.38 80f2a3d7,Horizon at Premier,EPA,Plano,75023,2016,2021,122,1013,MARKET,BUILD_FOR_RENT,2.18,2145.55,2.21,0.9836,EXCELLENT,0.664,0.7213,2019-12-20, 81f19aad,Huntington Townhomes,RICH,Richardson,75080,1963,,73,1042,MARKET,LOW_RISE,1.20,1239.79,1.20,0.9452,EXCELLENT,0.534,0.6164,2024-10-31, 8a12b690,La Mirada,RICH,Richardson,75080,1977,2021,622,1137,MARKET,GARDEN,1.26,1415.68,1.26,0.9196,EXCELLENT,0.878,0.672,2021-09-10,143088.91 9892e33f,Helios,RICH,Dallas,75240,1978,2024,248,685,MARKET,GARDEN,1.58,1036.86,1.57,0.8831,EXCELLENT,0.544,0.7016,, 9e889b09,Sheridan Park,EPA,Plano,75074,1997,2024,300,986,MARKET,GARDEN,1.65,1577.62,1.63,0.9133,GOOD,0.387,0.82,2024-08-22,194117.93 b73bb764,The Thread,RICH,Dallas,75240,1969,2017,606,862,MARKET,GARDEN,1.26,1076.29,1.32,0.9191,GOOD,0.371,0.7673,2023-06-15, b8587970,Windsor West Plano,EPA,Plano,75023,2023,,363,1189,MARKET,MID_RISE,1.93,2256.66,1.88,0.9421,EXCELLENT,1.0,0.584,2024-08-22, b9b4f62a,Spring Pointe,EPA,Richardson,75082,1985,2009,208,855,MARKET,GARDEN,1.71,1456.54,1.71,0.9663,EXCELLENT,1.0,0.7067,2025-04-22, bb2c284d,Avalon at Chase Oaks,EPA,Plano,75025,1991,2015,326,856,MARKET,GARDEN,1.60,1351.98,1.64,0.8528,EXCELLENT,1.0,0.4294,2024-10-22, bf33f39b,The Jasmine,RICH,Dallas,75240,1980,2020,370,636,MARKET,GARDEN,1.68,1065.83,1.59,0.9514,GOOD,0.335,0.9811,2025-07-03,132698.11 bfb634c9,Bell CityLine,EPA,Richardson,75082,2019,,435,886,MARKET,MID_RISE,2.03,1741.69,1.95,0.9448,EXCELLENT,1.0,0.5862,2021-07-29, c1f4202b,Winding Way,RICH,Dallas,75240,1979,,50,494,MARKET,LOW_RISE,2.57,1269.43,1.64,0.9600,EXCELLENT,0.6,0.84,2023-09-01,71155.00 cbfb5d8c,La Fortuna,RICH,Dallas,75240,1968,2025,230,742,AFFORDABLE,GARDEN,1.58,1106.50,1.31,0.6391,EXCELLENT,0.496,0.9391,2022-03-09, cc022a42,Shiloh Park Townhomes,EPA,Plano,75074,1997,,73,1640,MARKET,TOWNHOUSE,1.37,2235.99,1.55,0.9589,EXCELLENT,1.0,0.5753,2022-05-18, d1e14619,The Citizen,RICH,Dallas,75240,1978,,202,526,AFFORDABLE,MID_RISE,2.20,1159.65,2.60,0.6832,EXCELLENT,0.505,0.7624,2021-06-28, d6c10f55,Emerson at Ford Park,EPA,Allen,75002,1996,2015,280,884,MARKET_AND_AFFORDABLE,GARDEN,1.69,1478.77,1.67,0.9571,EXCELLENT,0.661,0.6464,2021-05-28, dcaff84c,Aura Northline,EPA,Plano,75074,2024,,325,833,MARKET,MID_RISE,2.31,1884.43,2.38,0.6800,EXCELLENT,0.966,0.52,, dd717bf4,Windsor CityLine,EPA,Richardson,75082,2015,,299,886,MARKET,MID_RISE,1.97,1723.95,2.09,0.9599,EXCELLENT,0.990,0.6054,2017-07-18, de2a7223,Anthem CityLine,EPA,Richardson,75082,2014,,233,833,MARKET,HIGH_RISE,2.04,1653.73,2.10,0.9742,EXCELLENT,0.747,0.6395,2017-02-06, e61e0e04,Savannah at Gateway,EPA,Plano,75074,2016,,292,672,MARKET_AND_AFFORDABLE,LOW_RISE,1.98,1321.76,2.03,0.8630,EXCELLENT,0.993,0.1815,2017-11-01, e96f52a6,Garden Gate,EPA,Plano,75023,1994,,240,795,MARKET_AND_AFFORDABLE,GARDEN,1.65,1165.33,1.47,0.9833,INSUFFICIENT,0.029,,2021-12-28,148958.33 f5d7db5f,Woodlands of Plano,EPA,Plano,75074,1972,2004,232,908,MARKET,GARDEN,1.56,1409.32,1.66,0.9741,EXCELLENT,0.983,0.9224,2025-11-06, f85f5023,Parkside Towns,RICH,Richardson,75080,2013,,135,1343,MARKET,TOWNHOUSE,1.97,2647.41,2.01,0.9778,EXCELLENT,0.8,0.7556,2022-06-24, f99b2855,Jada Legacy Central,EPA,Plano,75023,2019,,385,877,MARKET_AND_AFFORDABLE,MID_RISE,1.75,1503.87,1.86,0.9558,EXCELLENT,0.964,0.6182,2020-12-28, fa410b97,Waterford at Goldmark,RICH,Dallas,75243,1997,,220,854,MARKET_AND_AFFORDABLE,MID_RISE,1.59,1354.32,1.72,0.8909,EXCELLENT,0.868,0.9682,1997-11-14, fb0154a2,Saxony at Chase Oaks,EPA,Plano,75023,1994,2006,470,896,MARKET,GARDEN,1.55,1357.64,1.45,0.9787,EXCELLENT,1.0,0.6894,2019-03-01, fd2a612c,Bell Gateway Village,EPA,Plano,75074,2013,,831,930,MARKET,GARDEN,1.70,1538.56,1.65,0.9314,EXCELLENT,1.0,0.704,2024-06-21,179708.18 ff72b77a,The Yorkshire,EPA,Richardson,75082,2013,,56,997,MARKET,LOW_RISE,1.91,1896.43,1.84,0.9821,ACCEPTABLE,0.286,0.8929,2017-12-07,
"""Screen 1975-1990 market-rate garden stock in East Plano/Allen and Richardson
submarkets, test bottom-third in-place rent PSF, and size the gap to each
submarket's renovated comps."""
import csv, statistics as st, json
rows = []
with open('work/universe.csv') as f:
for r in csv.DictReader(f):
for k in ('year_built','year_renovated','units'):
r[k] = int(r[k]) if r[k] else None
for k in ('unit_sqft','inplace_psf','inplace_rent','asking_psf','occ','coverage','retention'):
r[k] = float(r[k]) if r[k] else None
rows.append(r)
# Reliability floor: drop INSUFFICIENT rent samples from every calculation
reliable = [r for r in rows if r['conf'] in ('EXCELLENT','GOOD','ACCEPTABLE')]
dropped = [r['name'] for r in rows if r not in reliable]
def pct(vals, p):
vals = sorted(vals); k = (len(vals)-1)*p
lo, hi = int(k), min(int(k)+1, len(vals)-1)
return vals[lo] + (vals[hi]-vals[lo])*(k-lo)
SUBS = {'EPA':'East Plano/Allen','RICH':'Richardson'}
out = {}
for s in SUBS:
# Bottom-third threshold: 33rd pctile of in-place PSF, market-rate conventional
# rentals 50+ units in the submarket (all vintages)
pool = [r for r in reliable if r['submarket']==s and r['rent_type'].startswith('MARKET')]
thr = pct([r['inplace_psf'] for r in pool], 1/3.0)
med = st.median([r['inplace_psf'] for r in pool])
# Renovated comps: pre-1995 vintage, renovated 2015+, market-rate, 100+ units
rc = [r for r in pool if r['year_built'] and r['year_built']<1995
and r['year_renovated'] and r['year_renovated']>=2015 and r['units']>=100]
rc_psf_uw = sum(r['inplace_psf']*r['units'] for r in rc)/sum(r['units'] for r in rc)
rc_psf_med = st.median([r['inplace_psf'] for r in rc])
out[s] = dict(n_pool=len(pool), p33=round(thr,3), median=round(med,3),
reno_comps=[(r['name'],r['year_built'],r['year_renovated'],r['units'],r['inplace_psf']) for r in rc],
reno_psf_unitwt=round(rc_psf_uw,3), reno_psf_median=round(rc_psf_med,3))
# Candidate screen
cands = []
for r in reliable:
if not (r['year_built'] and 1975 <= r['year_built'] <= 1990): continue
if not (r['units'] and 100 <= r['units'] <= 400): continue
if r['rent_type'] != 'MARKET': continue
if r['year_renovated'] and r['year_renovated'] >= 2015: continue
s = r['submarket']
bench = out[s]['reno_psf_unitwt']
gap = bench - r['inplace_psf']
rec = dict(entity=r['name'], submarket=SUBS[s], sub=s, city=r['city'], zip=r['zip'],
year_built=r['year_built'], year_renovated=r['year_renovated'],
units=r['units'], unit_sqft=r['unit_sqft'], style=r['style'],
inplace_psf=r['inplace_psf'], inplace_rent=r['inplace_rent'],
occ=r['occ'], retention=r['retention'],
sub_p33=out[s]['p33'], bottom_third=r['inplace_psf'] <= out[s]['p33'],
reno_bench_psf=bench, gap_psf=round(gap,3),
gap_pct=round(gap/r['inplace_psf'],4),
gap_rent_mo=round(gap*r['unit_sqft'],0),
gross_upside_yr=round(gap*r['unit_sqft']*12*r['units'],0))
cands.append(rec)
cands.sort(key=lambda x: -x['gap_psf'])
json.dump(cands, open('work/records.json','w'), indent=1)
print("Dropped for insufficient rent sample:", dropped)
for s,v in out.items():
print(f"\n== {SUBS[s]} == pool n={v['n_pool']} P33 in-place PSF=${v['p33']} median=${v['median']}")
print(" renovated comps (pre-1995 built, reno 2015+, 100+ u, market-rate):")
for c in v['reno_comps']: print(" ", c)
print(f" renovated benchmark PSF: unit-wtd ${v['reno_psf_unitwt']} median ${v['reno_psf_median']}")
print("\n== CANDIDATES (1975-1990, 100-400 u, market-rate, no reno since 2015) ==")
hdr = f"{'name':28}{'sub':6}{'blt':5}{'reno':6}{'u':5}{'sf':6}{'psf':6}{'p33':6}{'bot3':6}{'gap':7}{'gap%':7}{'$/mo':7}{'$/yr':>12}"
print(hdr)
for c in cands:
print(f"{c['entity'][:27]:28}{c['sub']:6}{c['year_built']:<5}{str(c['year_renovated'] or '-'):6}"
f"{c['units']:<5}{int(c['unit_sqft']):<6}{c['inplace_psf']:<6}{c['sub_p33']:<6}"
f"{('YES' if c['bottom_third'] else 'no'):6}{c['gap_psf']:<7}{c['gap_pct']*100:<7.1f}"
f"{c['gap_rent_mo']:<7.0f}{c['gross_upside_yr']:>12,.0f}")
cd /vercel/sandbox && python work/screen_and_gap.py
"""Build the value-add screen workbook: universe, live submarket benchmarks,
gap-to-renovated ranking, and a cost x capture sensitivity grid."""
import csv
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
BLUE = Font(name='Arial', size=10, color='0000FF')
BLACK = Font(name='Arial', size=10)
GREEN = Font(name='Arial', size=10, color='008000')
BOLD = Font(name='Arial', size=10, bold=True)
TITLE = Font(name='Arial', size=12, bold=True)
HDRFILL = PatternFill('solid', fgColor='1F3864')
HDRFONT = Font(name='Arial', size=10, bold=True, color='FFFFFF')
SUBFILL = PatternFill('solid', fgColor='D9E1F2')
TOPB = Border(top=Side(style='thin'))
CTR = Alignment(horizontal='center', vertical='center', wrap_text=True)
LEFT = Alignment(horizontal='left', vertical='center')
rows = list(csv.DictReader(open('work/universe.csv')))
wb = Workbook()
# ---------------------------------------------------------------- Assumptions
a = wb.active; a.title = 'Assumptions'
a['A1'] = 'DFW Value-Add Screen - East Plano/Allen & Richardson Submarkets'; a['A1'].font = TITLE
a['A2'] = 'Screen criteria and underwriting inputs (blue cells are inputs)'; a['A2'].font = Font(name='Arial', size=9, italic=True)
defs = [
('Screen criteria', None, None, None),
('Year built - minimum', 1975, '0', 'User criterion'),
('Year built - maximum', 1990, '0', 'User criterion'),
('Unit count - minimum', 100, '#,##0', 'User criterion'),
('Unit count - maximum', 400, '#,##0', 'User criterion'),
('Renovation cutoff year (exclude if renovated in or after)', 2015, '0', 'User criterion'),
('Bottom-third percentile for in-place rent PSF', 1/3, '0.0%', 'User criterion: bottom third of submarket'),
('Renovated-comp vintage ceiling (built before)', 1995, '0', 'Judgment: keeps comps in value-add vintage'),
('Renovated-comp minimum unit count', 100, '#,##0', 'Judgment: institutional-scale comps only'),
('Underwriting inputs', None, None, None),
('Share of rent gap captured on renovation', 0.85, '0.0%', 'Judgment: analyst haircut on full comp parity'),
('Renovation cost per unit ($)', 18000, '$#,##0', 'Judgment: classic 1980s garden interior + amenity scope'),
('Economic vacancy / loss-to-lease on new rent', 0.06, '0.0%', 'Judgment'),
('Incremental opex on new revenue', 0.10, '0.0%', 'Judgment: taxes, turn, management on uplift'),
('Exit cap rate on stabilized NOI', 0.0525, '0.00%', 'Judgment: DFW 1980s garden, current market'),
('Annual unit turnover (pace of capture)', 0.45, '0.0%', 'Judgment: drives years to full capture'),
]
r = 4
key = {}
for label, val, fmt, note in defs:
if val is None:
a.cell(r, 1, label).font = BOLD
a.cell(r, 1).fill = SUBFILL; a.cell(r, 2).fill = SUBFILL; a.cell(r, 3).fill = SUBFILL
else:
a.cell(r, 1, label).font = BLACK
c = a.cell(r, 2, val); c.font = BLUE; c.number_format = fmt
a.cell(r, 3, note).font = Font(name='Arial', size=9, italic=True, color='595959')
key[label] = f'Assumptions!$B${r}'
r += 1
a.column_dimensions['A'].width = 52; a.column_dimensions['B'].width = 14; a.column_dimensions['C'].width = 52
for row in a.iter_rows(min_row=4, max_row=r-1):
for c in row: c.alignment = LEFT
YB_MIN, YB_MAX = key['Year built - minimum'], key['Year built - maximum']
U_MIN, U_MAX = key['Unit count - minimum'], key['Unit count - maximum']
RENO_CUT = key['Renovation cutoff year (exclude if renovated in or after)']
PCTL = key['Bottom-third percentile for in-place rent PSF']
RC_VINT = key['Renovated-comp vintage ceiling (built before)']
RC_UMIN = key['Renovated-comp minimum unit count']
CAPTURE = key['Share of rent gap captured on renovation']
COST_U = key['Renovation cost per unit ($)']
VAC = key['Economic vacancy / loss-to-lease on new rent']
OPEX = key['Incremental opex on new revenue']
CAP = key['Exit cap rate on stabilized NOI']
TURN = key['Annual unit turnover (pace of capture)']
# ---------------------------------------------------------------- Universe
u = wb.create_sheet('Universe')
u['A1'] = 'Tracked apartment universe, 50+ units with in-place rent data'; u['A1'].font = TITLE
hdrs = ['Property','Submarket','City','ZIP','Year Built','Year Renovated','Units',
'Avg Unit SF','Rent Type','Building Style','In-Place Rent PSF ($)','In-Place Rent ($/mo)',
'Asking Rent PSF ($)','Occupancy (%)','Rent Sample Confidence','Sample Coverage (%)',
'Retention (%)','Reliable Sample','Market Rate','Vintage Fit','Size Fit','No Reno Since Cutoff',
'Qualifies','EPA Pool PSF','RICH Pool PSF','EPA Reno Comp','RICH Reno Comp']
for j, h in enumerate(hdrs, 1):
c = u.cell(3, j, h); c.font = HDRFONT; c.fill = HDRFILL; c.alignment = CTR
u.freeze_panes = 'A4'
start = 4
for i, d in enumerate(rows):
rr = start + i
vals = [d['name'], d['submarket'], d['city'], d['zip'],
int(d['year_built']) if d['year_built'] else None,
int(d['year_renovated']) if d['year_renovated'] else None,
int(d['units']), float(d['unit_sqft']), d['rent_type'], d['style'],
float(d['inplace_psf']), float(d['inplace_rent']),
float(d['asking_psf']) if d['asking_psf'] else None,
float(d['occ']), d['conf'], float(d['coverage']) if d['coverage'] else None,
float(d['retention']) if d['retention'] else None]
for j, v in enumerate(vals, 1):
c = u.cell(rr, j, v); c.font = BLACK
u.cell(rr, 5).number_format = '0'; u.cell(rr, 6).number_format = '0'
u.cell(rr, 7).number_format = '#,##0'; u.cell(rr, 8).number_format = '#,##0'
for col in (11, 13): u.cell(rr, col).number_format = '$#,##0.00'
u.cell(rr, 12).number_format = '$#,##0'
for col in (14, 16, 17): u.cell(rr, col).number_format = '0.0%'
# flags
u.cell(rr, 18, f'=IF(OR(O{rr}="EXCELLENT",O{rr}="GOOD",O{rr}="ACCEPTABLE"),1,0)').font = BLACK
u.cell(rr, 19, f'=IF(I{rr}="MARKET",1,0)').font = BLACK
u.cell(rr, 20, f'=IF(AND(E{rr}>={YB_MIN},E{rr}<={YB_MAX}),1,0)').font = GREEN
u.cell(rr, 21, f'=IF(AND(G{rr}>={U_MIN},G{rr}<={U_MAX}),1,0)').font = GREEN
u.cell(rr, 22, f'=IF(OR(F{rr}="",F{rr}<{RENO_CUT}),1,0)').font = GREEN
u.cell(rr, 23, f'=IF(AND(R{rr}=1,S{rr}=1,T{rr}=1,U{rr}=1,V{rr}=1),1,0)').font = BLACK
# pool + renovated-comp helper columns (text "" is ignored by PERCENTILE)
u.cell(rr, 24, f'=IF(AND($B{rr}="EPA",$R{rr}=1,LEFT($I{rr},6)="MARKET"),$K{rr},"")').font = BLACK
u.cell(rr, 25, f'=IF(AND($B{rr}="RICH",$R{rr}=1,LEFT($I{rr},6)="MARKET"),$K{rr},"")').font = BLACK
for col, sub in ((26, 'EPA'), (27, 'RICH')):
u.cell(rr, col, f'=IF(AND($B{rr}="{sub}",$R{rr}=1,LEFT($I{rr},6)="MARKET",'
f'$E{rr}<{RC_VINT},$F{rr}<>"",$F{rr}>={RENO_CUT},$G{rr}>={RC_UMIN}),$K{rr},"")').font = BLACK
end = start + len(rows) - 1
widths = [30,11,11,7,9,11,8,9,15,15,11,12,11,11,13,11,10,9,9,9,8,10,9,10,10,10,10]
for j, w in enumerate(widths, 1): u.column_dimensions[get_column_letter(j)].width = w
# ---------------------------------------------------------------- Benchmarks
b = wb.create_sheet('Benchmarks')
b['A1'] = 'Submarket rent benchmarks (computed live from Universe)'; b['A1'].font = TITLE
cols = ['Metric', 'East Plano/Allen', 'Richardson']
for j, h in enumerate(cols, 1):
c = b.cell(3, j, h); c.font = HDRFONT; c.fill = HDRFILL; c.alignment = CTR
pool = {'B': f'Universe!$X${start}:$X${end}', 'C': f'Universe!$Y${start}:$Y${end}'}
rc = {'B': f'Universe!$Z${start}:$Z${end}', 'C': f'Universe!$AA${start}:$AA${end}'}
subcode = {'B': 'EPA', 'C': 'RICH'}
brows = [
('Properties in rent pool (market-rate, 50+ units, reliable sample)', lambda c: f'=COUNT({pool[c]})', '#,##0'),
('Median in-place rent PSF ($)', lambda c: f'=MEDIAN({pool[c]})', '$#,##0.00'),
('Bottom-third threshold - in-place rent PSF ($)', lambda c: f'=PERCENTILE({pool[c]},{PCTL})', '$#,##0.00'),
('Renovated comps in set (built pre-ceiling, renovated post-cutoff, 100+ units)', lambda c: f'=COUNT({rc[c]})', '#,##0'),
('Renovated comps - unit-weighted in-place rent PSF ($)',
lambda c: f'=SUMPRODUCT(({rc[c]}<>"")*IF({rc[c]}="",0,{rc[c]})*Universe!$G${start}:$G${end})'
f'/SUMPRODUCT(({rc[c]}<>"")*Universe!$G${start}:$G${end})', '$#,##0.00'),
('Renovated comps - median in-place rent PSF ($)', lambda c: f'=MEDIAN({rc[c]})', '$#,##0.00'),
('Renovated comps - best-in-class in-place rent PSF ($)', lambda c: f'=MAX({rc[c]})', '$#,##0.00'),
('Submarket top-quartile in-place rent PSF, all vintages ($)', lambda c: f'=PERCENTILE({pool[c]},0.75)', '$#,##0.00'),
]
r = 4
bm = {}
for label, f, fmt in brows:
b.cell(r, 1, label).font = BLACK
for c in ('B', 'C'):
cell = b[f'{c}{r}']; cell.value = f(c); cell.font = GREEN; cell.number_format = fmt
bm[label] = r
r += 1
b.column_dimensions['A'].width = 68; b.column_dimensions['B'].width = 18; b.column_dimensions['C'].width = 18
ROW_P33 = bm['Bottom-third threshold - in-place rent PSF ($)']
ROW_UW = bm['Renovated comps - unit-weighted in-place rent PSF ($)']
ROW_BEST = bm['Renovated comps - best-in-class in-place rent PSF ($)']
b.cell(r+1, 1, 'Renovated comps behind each benchmark are the Universe rows flagged in columns Z and AA.').font = Font(name='Arial', size=9, italic=True, color='595959')
# ---------------------------------------------------------------- Ranking
g = wb.create_sheet('Gap Ranking')
g['A1'] = 'Qualifying assets ranked by in-place rent gap to submarket renovated comps'; g['A1'].font = TITLE
ghdr = ['Rank','Property','Submarket','City','Year Built','Year Renovated','Units','Avg Unit SF',
'In-Place Rent PSF ($)','Submarket Bottom-Third PSF ($)','In Bottom Third',
'Renovated Comp PSF - unit wtd ($)','Gap to Renovated Comps ($ PSF)','Gap (%)',
'Gap per Unit ($/mo)','Captured Gap ($/mo per unit)','Incremental Revenue ($/yr)',
'Incremental NOI ($/yr)','Value Created at Exit Cap ($)','Renovation Cost ($)',
'Net Value Created ($)','Return on Renovation Cost (%)','Years to Full Capture',
'Best-in-Class Reno PSF ($)','Upside Case Gap ($ PSF)','Occupancy (%)','Retention (%)']
for j, h in enumerate(ghdr, 1):
c = g.cell(3, j, h); c.font = HDRFONT; c.fill = HDRFILL; c.alignment = CTR
g.freeze_panes = 'C4'
# candidate universe rows, ordered by gap descending (order set by the screen script)
order = ['Hunters Glen','Fox Trails','Plano Park Townhomes','Bellevue at Spring Creek',
'Greenbriar','Legacy Apartments','Spring Pointe','Hunters Court']
idx = {d['name']: start + i for i, d in enumerate(rows)}
gstart = 4
for k, name in enumerate(order):
rr = gstart + k
ur = idx[name]
subcol = f'IF(Universe!$B{ur}="EPA","B","C")'
# explicit submarket column letter per row (EPA -> B, RICH -> C on Benchmarks)
sub = 'B' if rows[ur - start]['submarket'] == 'EPA' else 'C'
g.cell(rr, 1, k + 1).font = BLACK
g.cell(rr, 2, f'=Universe!A{ur}').font = GREEN
g.cell(rr, 3, f'=IF(Universe!B{ur}="EPA","East Plano/Allen","Richardson")').font = GREEN
g.cell(rr, 4, f'=Universe!C{ur}').font = GREEN
g.cell(rr, 5, f'=Universe!E{ur}').font = GREEN
g.cell(rr, 6, f'=IF(Universe!F{ur}="","not renovated",Universe!F{ur})').font = GREEN
g.cell(rr, 7, f'=Universe!G{ur}').font = GREEN
g.cell(rr, 8, f'=Universe!H{ur}').font = GREEN
g.cell(rr, 9, f'=Universe!K{ur}').font = GREEN
g.cell(rr, 10, f'=Benchmarks!${sub}${ROW_P33}').font = GREEN
g.cell(rr, 11, f'=IF(I{rr}<=J{rr},"yes","no")').font = BLACK
g.cell(rr, 12, f'=Benchmarks!${sub}${ROW_UW}').font = GREEN
g.cell(rr, 13, f'=L{rr}-I{rr}').font = BLACK
g.cell(rr, 14, f'=M{rr}/I{rr}').font = BLACK
g.cell(rr, 15, f'=M{rr}*H{rr}').font = BLACK
g.cell(rr, 16, f'=O{rr}*{CAPTURE}').font = BLACK
g.cell(rr, 17, f'=P{rr}*12*G{rr}*(1-{VAC})').font = BLACK
g.cell(rr, 18, f'=Q{rr}*(1-{OPEX})').font = BLACK
g.cell(rr, 19, f'=R{rr}/{CAP}').font = BLACK
g.cell(rr, 20, f'=G{rr}*{COST_U}').font = BLACK
g.cell(rr, 21, f'=S{rr}-T{rr}').font = BLACK
g.cell(rr, 22, f'=IF(T{rr}=0,"",R{rr}/T{rr})').font = BLACK
g.cell(rr, 23, f'=1/{TURN}').font = BLACK
g.cell(rr, 24, f'=Benchmarks!${sub}${ROW_BEST}').font = GREEN
g.cell(rr, 25, f'=X{rr}-I{rr}').font = BLACK
g.cell(rr, 26, f'=Universe!N{ur}').font = GREEN
g.cell(rr, 27, f'=Universe!Q{ur}').font = GREEN
for col in (5, 6): g.cell(rr, col).number_format = '0'
for col in (7, 8): g.cell(rr, col).number_format = '#,##0'
for col in (9, 10, 12, 13, 24, 25): g.cell(rr, col).number_format = '$#,##0.00;($#,##0.00);-'
for col in (15, 16): g.cell(rr, col).number_format = '$#,##0;($#,##0);-'
for col in (17, 18, 19, 20, 21): g.cell(rr, col).number_format = '$#,##0;($#,##0);-'
for col in (14, 22, 26, 27): g.cell(rr, col).number_format = '0.0%;(0.0%);-'
g.cell(rr, 23).number_format = '0.0'
gend = gstart + len(order) - 1
tr = gend + 1
g.cell(tr, 2, 'Qualifying total / weighted average').font = BOLD
g.cell(tr, 7, f'=SUM(G{gstart}:G{gend})').font = BOLD
g.cell(tr, 13, f'=SUMPRODUCT(M{gstart}:M{gend},G{gstart}:G{gend})/SUM(G{gstart}:G{gend})').font = BOLD
g.cell(tr, 17, f'=SUM(Q{gstart}:Q{gend})').font = BOLD
g.cell(tr, 18, f'=SUM(R{gstart}:R{gend})').font = BOLD
g.cell(tr, 19, f'=SUM(S{gstart}:S{gend})').font = BOLD
g.cell(tr, 20, f'=SUM(T{gstart}:T{gend})').font = BOLD
g.cell(tr, 21, f'=SUM(U{gstart}:U{gend})').font = BOLD
g.cell(tr, 7).number_format = '#,##0'
g.cell(tr, 13).number_format = '$#,##0.00;($#,##0.00);-'
for col in (17, 18, 19, 20, 21): g.cell(tr, col).number_format = '$#,##0;($#,##0);-'
for col in range(1, 28): g.cell(tr, col).border = TOPB
gw = [6,28,17,11,10,13,8,10,12,15,10,15,14,9,12,14,14,14,15,13,14,13,11,13,13,11,10]
for j, w in enumerate(gw, 1): g.column_dimensions[get_column_letter(j)].width = w
# ---------------------------------------------------------------- Sensitivity
s = wb.create_sheet('Sensitivity')
s['A1'] = 'Renovation cost x gap capture - lead candidate'; s['A1'].font = TITLE
s['A3'] = 'Subject property'; s['A3'].font = BLACK
s['B3'] = 'Hunters Glen'; s['B3'].font = BLUE
s['A4'] = 'Units'; s['A4'].font = BLACK
s['B4'] = f'=INDEX(\'Gap Ranking\'!$G${gstart}:$G${gend},MATCH($B$3,\'Gap Ranking\'!$B${gstart}:$B${gend},0))'
s['B4'].font = GREEN; s['B4'].number_format = '#,##0'
s['A5'] = 'Gap to renovated comps ($ PSF)'; s['A5'].font = BLACK
s['B5'] = f'=INDEX(\'Gap Ranking\'!$M${gstart}:$M${gend},MATCH($B$3,\'Gap Ranking\'!$B${gstart}:$B${gend},0))'
s['B5'].font = GREEN; s['B5'].number_format = '$#,##0.00'
s['A6'] = 'Average unit SF'; s['A6'].font = BLACK
s['B6'] = f'=INDEX(\'Gap Ranking\'!$H${gstart}:$H${gend},MATCH($B$3,\'Gap Ranking\'!$B${gstart}:$B${gend},0))'
s['B6'].font = GREEN; s['B6'].number_format = '#,##0'
caps = [0.50, 0.65, 0.85, 1.00]
costs = [12000, 15000, 18000, 22000, 26000, 30000]
grids = [
('Return on renovation cost (%) - incremental NOI / renovation spend', 9,
lambda cc, kk: f'=($B$5*{kk}*$B$6*12*$B$4*(1-{VAC})*(1-{OPEX}))/({cc}*$B$4)', '0.0%'),
('Net value created ($) - value at exit cap less renovation spend', 19,
lambda cc, kk: f'=($B$5*{kk}*$B$6*12*$B$4*(1-{VAC})*(1-{OPEX}))/{CAP}-{cc}*$B$4', '$#,##0;($#,##0);-'),
]
for title, r0, fexp, fmt in grids:
s.cell(r0, 1, title).font = BOLD
s.cell(r0 + 1, 1, 'Renovation cost per unit ($)').font = BOLD
s.cell(r0 + 1, 1).alignment = CTR
s.cell(r0, 3, 'Share of gap captured ->').font = Font(name='Arial', size=9, italic=True)
for j, cap in enumerate(caps):
c = s.cell(r0 + 1, 2 + j, cap); c.font = BLUE; c.number_format = '0%'
c.fill = SUBFILL; c.alignment = CTR
for i, cost in enumerate(costs):
rr = r0 + 2 + i
c = s.cell(rr, 1, cost); c.font = BLUE; c.number_format = '$#,##0'; c.fill = SUBFILL
for j in range(len(caps)):
cl = get_column_letter(2 + j)
cell = s.cell(rr, 2 + j)
cell.value = fexp(f'$A{rr}', f'{cl}${r0+1}')
cell.font = BLACK; cell.number_format = fmt
s.column_dimensions['A'].width = 30
for j in range(2, 6): s.column_dimensions[get_column_letter(j)].width = 17
wb.save('work/DFW_valueadd_screen.xlsx')
print('saved')
cd /vercel/sandbox && python work/build_workbook.py && python skills/xlsx/scripts/workbook_integrity_scan.py work/DFW_valueadd_screen.xlsx --json work/integrity.json; echo "EXIT=$?"; python -c "
import json;d=json.load(open('work/integrity.json'))
from collections import Counter
print(Counter([f['severity'] for f in d.get('findings',[])]))
for f in d.get('findings',[]):
if f['severity']=='high': print(f['check'], f.get('sheet'), f.get('cell'), str(f.get('detail'))[:160])
"cd /vercel/sandbox && python - <<'EOF'
import re
p='work/build_workbook.py'; s=open(p).read()
# single body font size (drop size-9 italics to 10)
s = s.replace("Font(name='Arial', size=9, italic=True)", "Font(name='Arial', size=10, italic=True)")
s = s.replace("Font(name='Arial', size=9, italic=True, color='595959')", "Font(name='Arial', size=10, italic=True, color='595959')")
# right-align numeric input cells on Assumptions
s = s.replace(""" c = a.cell(r, 2, val); c.font = BLUE; c.number_format = fmt""",
""" c = a.cell(r, 2, val); c.font = BLUE; c.number_format = fmt
c.alignment = Alignment(horizontal='right', vertical='center')""")
s = s.replace("""for row in a.iter_rows(min_row=4, max_row=r-1):
for c in row: c.alignment = LEFT""",
"""for row in a.iter_rows(min_row=4, max_row=r-1):
for c in row:
if c.column != 2: c.alignment = LEFT""")
# add top-quartile percentile input
s = s.replace(""" ('Renovated-comp minimum unit count', 100, '#,##0', 'Judgment: institutional-scale comps only'),""",
""" ('Renovated-comp minimum unit count', 100, '#,##0', 'Judgment: institutional-scale comps only'),
('Top-quartile percentile for submarket context', 0.75, '0.0%', 'Reference only'),""")
s = s.replace("RC_UMIN = key['Renovated-comp minimum unit count']",
"RC_UMIN = key['Renovated-comp minimum unit count']\nP75 = key['Top-quartile percentile for submarket context']")
s = s.replace("f'=PERCENTILE({pool[c]},0.75)'", "f'=PERCENTILE({pool[c]},{P75})'")
# defined names for cross-sheet ranges (avoids raw cross-sheet range text)
s = s.replace("""pool = {'B': f'Universe!$X${start}:$X${end}', 'C': f'Universe!$Y${start}:$Y${end}'}
rc = {'B': f'Universe!$Z${start}:$Z${end}', 'C': f'Universe!$AA${start}:$AA${end}'}""",
"""from openpyxl.workbook.defined_name import DefinedName
for nm, col in (('EPA_pool','X'),('RICH_pool','Y'),('EPA_reno','Z'),('RICH_reno','AA'),('unit_ct','G')):
wb.defined_names.add(DefinedName(nm, attr_text=f"Universe!${col}${start}:${col}${end}"))
pool = {'B': 'EPA_pool', 'C': 'RICH_pool'}
rc = {'B': 'EPA_reno', 'C': 'RICH_reno'}""")
s = s.replace(""" lambda c: f'=SUMPRODUCT(({rc[c]}<>"")*IF({rc[c]}="",0,{rc[c]})*Universe!$G${start}:$G${end})'
f'/SUMPRODUCT(({rc[c]}<>"")*Universe!$G${start}:$G${end})', '$#,##0.00'),""",
""" lambda c: f'=SUMPRODUCT(({rc[c]}<>"")*IF({rc[c]}="",0,{rc[c]})*unit_ct)'
f'/SUMPRODUCT(({rc[c]}<>"")*unit_ct)', '$#,##0.00'),""")
# Gap Ranking: lock column anchors within each row
for a_,b_ in [("f'=IF(I{rr}<=J{rr},\"yes\",\"no\")'","f'=IF($I{rr}<=$J{rr},\"yes\",\"no\")'"),
("f'=L{rr}-I{rr}'","f'=$L{rr}-$I{rr}'"),
("f'=M{rr}/I{rr}'","f'=$M{rr}/$I{rr}'"),
("f'=M{rr}*H{rr}'","f'=$M{rr}*$H{rr}'"),
("f'=O{rr}*{CAPTURE}'","f'=$O{rr}*{CAPTURE}'"),
("f'=P{rr}*12*G{rr}*(1-{VAC})'","f'=$P{rr}*12*$G{rr}*(1-{VAC})'"),
("f'=Q{rr}*(1-{OPEX})'","f'=$Q{rr}*(1-{OPEX})'"),
("f'=R{rr}/{CAP}'","f'=$R{rr}/{CAP}'"),
("f'=G{rr}*{COST_U}'","f'=$G{rr}*{COST_U}'"),
("f'=S{rr}-T{rr}'","f'=$S{rr}-$T{rr}'"),
("f'=IF(T{rr}=0,\"\",R{rr}/T{rr})'","f'=IF($T{rr}=0,\"\",$R{rr}/$T{rr})'"),
("f'=X{rr}-I{rr}'","f'=$X{rr}-$I{rr}'")]:
s = s.replace(a_,b_)
# Sensitivity lookups via defined names on Gap Ranking
s = s.replace("""s['B4'] = f'=INDEX(\\'Gap Ranking\\'!$G${gstart}:$G${gend},MATCH($B$3,\\'Gap Ranking\\'!$B${gstart}:$B${gend},0))'""",
"""for nm, col in (('cand_name','B'),('cand_units','G'),('cand_sf','H'),('cand_gap','M')):
wb.defined_names.add(DefinedName(nm, attr_text=f"'Gap Ranking'!${col}${gstart}:${col}${gend}"))
s['B4'] = '=INDEX(cand_units,MATCH($B$3,cand_name,0))'""")
s = s.replace("""s['B5'] = f'=INDEX(\\'Gap Ranking\\'!$M${gstart}:$M${gend},MATCH($B$3,\\'Gap Ranking\\'!$B${gstart}:$B${gend},0))'""",
"""s['B5'] = '=INDEX(cand_gap,MATCH($B$3,cand_name,0))'""")
s = s.replace("""s['B6'] = f'=INDEX(\\'Gap Ranking\\'!$H${gstart}:$H${gend},MATCH($B$3,\\'Gap Ranking\\'!$B${gstart}:$B${gend},0))'""",
"""s['B6'] = '=INDEX(cand_sf,MATCH($B$3,cand_name,0))'""")
# freeze consistency + widths
s = s.replace("g.freeze_panes = 'C4'", "g.freeze_panes = 'A4'")
s = s.replace("widths = [30,11,11,7,9,11,8,9,15,15,", "widths = [30,11,11,7,9,11,8,9,24,16,")
s = s.replace("b.column_dimensions['A'].width = 68", "b.column_dimensions['A'].width = 80")
# right-align sensitivity axis headers
s = s.replace("c.fill = SUBFILL; c.alignment = CTR", "c.fill = SUBFILL; c.alignment = Alignment(horizontal='right', vertical='center')")
open(p,'w').write(s)
print('patched')
EOF
python work/build_workbook.py && python skills/xlsx/scripts/workbook_integrity_scan.py work/DFW_valueadd_screen.xlsx --json work/integrity.json >/dev/null; echo "EXIT=$?"; python -c "
import json;d=json.load(open('work/integrity.json'))
from collections import Counter
print(Counter([f['severity'] for f in d['findings']]))
for f in d['findings']:
print(f['severity'],f['category'],f.get('sheet'),f.get('cell'),f.get('reason','')[:130])
"cd /vercel/sandbox && python skills/xlsx/scripts/recalc.py work/DFW_valueadd_screen.xlsx 2>&1 | tail -20
cd /vercel/sandbox && python - <<'EOF'
from openpyxl import load_workbook
wb = load_workbook('work/DFW_valueadd_screen.xlsx', data_only=True)
b = wb['Benchmarks']
for r in range(3, 12):
print(r, [b.cell(r,c).value for c in (1,2,3)])
g = wb['Gap Ranking']
print()
cols=[1,2,3,5,7,9,10,11,12,13,14,15,17,18,19,20,21,22,24,25]
for r in range(4,13):
print([g.cell(r,c).value for c in cols])
s = wb['Sensitivity']
print()
for r in [3,4,5,6]: print(s.cell(r,1).value, s.cell(r,2).value)
for r0 in (9,19):
for r in range(r0,r0+9):
print([s.cell(r,c).value for c in range(1,6)])
print()
EOFcd /vercel/sandbox && python - <<'EOF'
p='work/build_workbook.py'; s=open(p).read()
old = s[s.index("tr = gend + 1"):s.index("gw = [6,28,17")]
new = '''tr = gend + 1
g.cell(tr, 2, 'Subtotal - assets with a positive gap only').font = BOLD
g.cell(tr, 7, f'=SUMIF($M${gstart}:$M${gend},">0",$G${gstart}:$G${gend})').font = BOLD
g.cell(tr, 13, f'=SUMPRODUCT(($M${gstart}:$M${gend}>0)*$M${gstart}:$M${gend}*$G${gstart}:$G${gend})'
f'/SUMIF($M${gstart}:$M${gend},">0",$G${gstart}:$G${gend})').font = BOLD
for col, letter in ((17,'Q'),(18,'R'),(19,'S'),(20,'T'),(21,'U')):
g.cell(tr, col, f'=SUMIF($M${gstart}:$M${gend},">0",${letter}${gstart}:${letter}${gend})').font = BOLD
g.cell(tr, 7).number_format = '#,##0'
g.cell(tr, 13).number_format = '$#,##0.00;($#,##0.00);-'
for col in (17, 18, 19, 20, 21): g.cell(tr, col).number_format = '$#,##0;($#,##0);-'
for col in range(1, 28): g.cell(tr, col).border = TOPB
g.cell(tr+2, 2, 'A negative gap means in-place rent already exceeds the submarket renovated-comp average; '
'no mark-to-market case on this benchmark.').font = Font(name='Arial', size=10, italic=True, color='595959')
'''
s = s.replace(old, new)
open(p,'w').write(s); print('ok')
EOF
python work/build_workbook.py && python skills/xlsx/scripts/workbook_integrity_scan.py work/DFW_valueadd_screen.xlsx --json work/integrity.json >/dev/null; echo "SCAN_EXIT=$?"; python skills/xlsx/scripts/recalc.py work/DFW_valueadd_screen.xlsx 2>&1 | tail -6
python - <<'EOF'
from openpyxl import load_workbook
g = load_workbook('/vercel/sandbox/work/DFW_valueadd_screen.xlsx', data_only=True)['Gap Ranking']
print([g.cell(12,c).value for c in (2,7,13,17,18,19,20,21)])
EOFcd /vercel/sandbox && python skills/multi-entity-analysis/scripts/cross_check.py --check extremum --field gap_psf --direction max --claim "Hunters Glen" work/records.json && python skills/multi-entity-analysis/scripts/cross_check.py --check predicate --field gap_pct --op gt --value 0.05 --claim "Hunters Glen" work/records.json && python skills/multi-entity-analysis/scripts/cross_check.py --check coverage work/records.json | head -30
Verified. Saving the workbook.
Eight assets pass your box, but only one — Hunters Glen — has a rent gap worth renovating for. The other seven are already at or above what renovated 1980s product in these submarkets actually collects.
The screen produced eight qualifying assets: seven in East Plano/Allen, one in the Richardson submarket. Five of the eight sit in the bottom third of their submarket's in-place rent PSF. But the ranking axis you asked for — gap to renovated comps — is where the thesis mostly breaks.
| Property | Submarket | Year Built / Last Reno | Units | Avg Unit SF | In-Place Rent PSF | Bottom Third? | Renovated Comp PSF (unit-wtd) | Gap ($ PSF) | Gap (%) | Gap ($/unit/mo) |
|---|---|---|---|---|---|---|---|---|---|---|
| East Plano/Allen | 1979 / 2011 | 276 | 946 | $1.39 | Yes | $1.601 | $0.207 | +14.9% | $196 | |
| East Plano/Allen | 1981 / 2014 | 286 | 961 | $1.54 | Yes | $1.60 | $0.057 | +3.7% | $55 | |
| East Plano/Allen | 1985 / 2008 | 140 | 1,010 | $1.54 | Yes | $1.60 | $0.057 | +3.7% | $58 | |
| East Plano/Allen | 1982 / 2001 | 278 | 951 | $1.59 | Yes | $1.60 | $0.007 | +0.4% | $7 | |
| East Plano/Allen | 1983 / never | 182 | 783 | $1.61 | Yes | $1.60 | ($0.013) | (0.8%) | ($10) | |
| East Plano/Allen | 1984 / 2000 | 244 | 879 | $1.66 | No | $1.60 | ($0.063) | (3.8%) | ($55) | |
| East Plano/Allen | 1985 / 2009 | 208 | 855 | $1.71 | No | $1.60 | ($0.113) | (6.6%) | ($97) | |
| Richardson | 1976 / 2005 | 184 | 710 | $1.59 | No | $1.40 | ($0.190) | (11.9%) | ($135) |
Hunters Glen is the deal. $1.39 PSF against a $1.60 renovated-comp average is a $0.21 PSF, $196/unit/month gap — the widest in the set and the only one above 5%. It's also visibly under-operated at 83.7% occupancy with 66.7% retention, which is what a $1.39 rent on 946 SF garden units in a Plano ISD location usually means. At $18k/unit interiors and 85% gap capture, that's roughly $467k of incremental NOI, a 9.4% yield on renovation cost, and about $3.9M of net value creation at a 5.25% exit. The grid holds up: it stays value-accretive down to 50% capture at $18k/unit and only turns negative at 50% capture above roughly $20k/unit.
The other seven are the more important finding. Renovated 1980s garden product in East Plano/Allen collects only $1.60 PSF — below the $1.65 bottom-third threshold and well below the $1.75 submarket median, because CityLine and downtown Plano mid-rise at $1.85–$2.23 set the median, not the vintage you buy. So "bottom third of the submarket" and "below renovated 1980s comps" are nearly the same line here. Fox Trails and Plano Park Townhomes clear it by $0.06 — inside the noise, and at $18k/unit neither creates value. Legacy, Spring Pointe and Hunter's Court are already renting above their submarket's renovated average without having been renovated.
Richardson is effectively closed to this strategy. One asset in the whole submarket fits your box, and the renovated cohort there averages $1.40 PSF across nine properties — Carisma, Windham Chase, La Mirada and The Thread all sit at $1.20–$1.28 after renovations completed 2017–2024. Capital went into that stock and the rents didn't follow. The Richardson submarket as mapped is anchored on the Spring Valley/Coit corridor in Dallas 75240, not Richardson's Telecom Corridor employment core, which explains the rent ceiling.
Two things would change the read. First, benchmark choice: if you underwrite to best-in-class renovated execution rather than the cohort average — Villas at Chase Oaks at $1.73 PSF (1986, renovated 2022) — Fox Trails' gap triples to $0.19 and Bellevue's to $0.14, and a three-asset program becomes arguable. That's a defensible target if your operations beat the average operator's; it is not what the average renovated comp supports today. Second, in-place rents at Fox Trails and Bellevue are being compared against renovated comps whose own asking rents are below their in-place rents — Villas at Chase Oaks asks $1.86 but collects $1.73, meaning the renovated cohort is pushing on asking and not yet converting.
Where I'd spend time: Hunters Glen, immediately — last traded February 2022, so it's four and a half years into a hold with a 2011 renovation vintage and deteriorating occupancy. Fox Trails is the second call only if you underwrite to best-in-class rather than average, and it last traded in December 2011 at $65k/unit, so basis on any new deal will be the binding constraint, not rent. Skip Richardson entirely on this thesis.
Change the renovation cost, capture rate, or exit cap on the Assumptions tab and the ranking and sensitivity grids move with it. If you want me to widen the geography — West Plano, Central/South Garland, and Northwest Mesquite all carry deeper 1975–1990 garden inventory — say the word.
This is a read-only snapshot of a RealAI analysis.