cd /vercel/sandbox && python skills/forecasting/scripts/forecast.py --version; cat skills/forecasting/references/request.md

mkdir -p /vercel/sandbox/fc && cd /vercel/sandbox/fc && cat > raw_rent.json <<'EOF'
{"subject_inplace":{"2026-08-01":1678.197080,"2026-07-01":1675.992126,"2026-06-01":1674.105691,"2026-05-01":1674.556452,"2026-04-01":1672.113821,"2026-03-01":1672.276786,"2026-02-01":1676.435185,"2026-01-01":1689.811321,"2025-12-01":1685.900000,"2025-11-01":1697.079208,"2025-10-01":1715.789474,"2025-09-01":1733.387097,"2025-08-01":1738.863636,"2025-07-01":1756.410256,"2025-06-01":1760.200000,"2025-05-01":1749.305556,"2025-04-01":1745.928571,"2025-03-01":1713.306452,"2025-02-01":1704.824561,"2025-01-01":1748.953488,"2024-12-01":1773.235294,"2024-11-01":1790.500000,"2024-10-01":1800.200000,"2024-09-01":1766.944444,"2024-08-01":1743.571429,"2024-07-01":1665.000000},
"subject_asking":{"2026-08-01":1719.700000,"2026-07-01":1768.869565,"2026-06-01":1867.277778,"2026-05-01":1760.000000,"2026-04-01":1642.700000,"2026-03-01":1623.789474,"2026-02-01":1730.571429,"2026-01-01":1658.280000,"2025-12-01":1765.000000,"2025-11-01":1770.714286,"2025-10-01":1731.083333,"2025-09-01":1710.172414,"2025-08-01":1741.566667,"2025-07-01":1715.588235,"2025-06-01":1755.972222,"2025-05-01":1810.972222,"2025-04-01":1799.062500,"2025-03-01":1836.944444,"2025-02-01":1818.965517,"2025-01-01":1781.025641,"2024-12-01":1769.897959,"2024-11-01":1803.469388,"2024-10-01":1823.500000,"2024-09-01":1869.852941,"2024-08-01":1879.655172,"2024-07-01":1814.687500},
"sub_inplace":{"2026-08-01":1692.706531,"2026-07-01":1694.835692,"2026-06-01":1691.945377,"2026-05-01":1693.792535,"2026-04-01":1690.550207,"2026-03-01":1685.285372,"2026-02-01":1687.812316,"2026-01-01":1694.449858,"2025-12-01":1702.026029,"2025-11-01":1710.244827,"2025-10-01":1717.451774,"2025-09-01":1718.311541,"2025-08-01":1719.023747,"2025-07-01":1723.029017,"2025-06-01":1717.035459,"2025-05-01":1695.484232,"2025-04-01":1679.663535,"2025-03-01":1672.401947,"2025-02-01":1660.628173,"2025-01-01":1645.084953,"2024-12-01":1656.092522,"2024-11-01":1665.984428,"2024-10-01":1663.938686,"2024-09-01":1667.361446,"2024-08-01":1701.758920,"2024-07-01":1697.598919}}
EOF
cat > raw_cap.json <<'EOF'
{"2Q26":0.0548,"1Q26":0.0538,"4Q25":0.0546,"3Q25":0.0546,"2Q25":0.0546,"1Q25":0.0510,"4Q24":0.0489,"3Q24":0.0489,"2Q24":0.0504,"1Q24":0.0530,"4Q23":0.0529,"3Q23":0.0550,"2Q23":0.0488,"1Q23":0.0488,"4Q22":0.0472,"3Q22":0.0406,"2Q22":0.0397,"1Q22":0.0361,"4Q21":0.0350,"3Q21":0.0362,"2Q21":0.0383,"1Q21":0.0425,"4Q20":0.0425,"3Q20":0.0424,"2Q20":0.0470,"1Q20":0.0445,"4Q19":0.0451,"3Q19":0.0451,"2Q19":0.0477,"1Q19":0.0477,"4Q18":0.0493,"3Q18":0.0493,"2Q18":0.0493,"1Q18":0.0503,"4Q17":0.0504,"3Q17":0.0531,"2Q17":0.0531,"1Q17":0.0532,"4Q16":0.0516,"3Q16":0.0516,"2Q16":0.0516,"1Q16":0.0518,"4Q15":0.0517,"3Q15":0.0538,"2Q15":0.0548,"1Q15":0.0561,"4Q14":0.0571,"3Q14":0.0556,"2Q14":0.0577,"1Q14":0.0593,"4Q13":0.0604,"3Q13":0.0597,"2Q13":0.0589,"1Q13":0.0600,"4Q12":0.0585,"3Q12":0.0596,"2Q12":0.0612,"1Q12":0.0613,"4Q11":0.0624,"3Q11":0.0624,"2Q11":0.0625,"1Q11":0.0636,"4Q10":0.0626,"3Q10":0.0617,"2Q10":0.0708,"1Q10":0.0745,"4Q09":0.0745,"3Q09":0.0797,"2Q09":0.0822,"1Q09":0.0821,"4Q08":0.0789,"3Q08":0.0728,"2Q08":0.0693,"1Q08":0.0672,"4Q07":0.0673,"3Q07":0.0642,"2Q07":0.0610,"1Q07":0.0584,"4Q06":0.0606,"3Q06":0.0606,"2Q06":0.0617,"1Q06":0.0617,"4Q05":0.0618,"3Q05":0.0645,"2Q05":0.0668,"1Q05":0.0689}
EOF
cat > build_payloads.py <<'EOF'
import json
r=json.load(open('raw_rent.json')); cap=json.load(open('raw_cap.json'))
def arr(d,src='datamart'): return [{"period":k,"value":round(v,4),"source":src} for k,v in sorted(d.items())]
def chk(d):
    s=sorted(d.items()); return {"count":len(s),"sum":round(sum(v for _,v in s),4),"last_value":round(s[-1][1],4)}
si,sa,pi=r['subject_inplace'],r['subject_asking'],r['sub_inplace']
def t12(d):
    s=sorted(d.items()); return s[-1][1]/s[-13][1]-1
div=t12(si)-t12(sa)
print('t12 inplace',t12(si),'asking',t12(sa),'div',div, 'peer t12', t12(pi), 'level ratio', sorted(si.items())[-1][1]/sorted(pi.items())[-1][1])
sig={"supply_pipeline":{"existing_stock":302423,"under_construction_t12":12246,"permitted_units_t13_t24":7622},
     "migration":{"inbound_income":140039,"outbound_income":144681},
     "employment":{"job_growth_1_year_pct":0.0176}}
def rentpay(name,hist):
    return {"metric":{"name":name,"units":"$","family":"rent_or_occupancy"},
     "subject":{"entity_type":"property","entity_id":"1a5aa1db99e8bbf2b4dedfbaf29e09ae","label":"Summit Riverside Apartments"},
     "horizon":{"years":5,"intervals":"annual"},"as_of":"2026-09-29",
     "caller_disclosures":{"peer_selection_basis":f"parent submarket (Englewood) in-place rent; level ratio {sorted(si.items())[-1][1]/sorted(pi.items())[-1][1]:.2f}; subject T12 {t12(si):.3f} vs submarket T12 {t12(pi):.3f}, both declining",
       "sibling_series_note":"asking and in-place both run through engine","sibling_divergence_pct":round(div,4),
       "lookback_note":"full available monthly history (Jul-2024 onward; earlier months null)"},
     "history":arr(hist),"history_check":chk(hist),"peer_history":arr(pi),"peer_history_check":{"count":len(pi),"last_value":round(sorted(pi.items())[-1][1],4)},
     "context_signals":sig,"scenarios":["base","upside","downside"]}
json.dump(rentpay("mf_rent_ts.in_place_rent_latest_avg",si),open('p_inplace.json','w'))
json.dump(rentpay("mf_rent_ts.asking_rent_latest_avg",sa),open('p_asking.json','w'))
qmap={'1':'01','2':'04','3':'07','4':'10'}
ch={f"20{k[2:]}-{qmap[k[0]]}-01":v for k,v in cap.items()}
capp={"metric":{"name":"caprate_ts.multifamily","units":"%","family":"capital_markets"},
 "subject":{"entity_type":"market","entity_id":"19740","label":"Denver MSA"},"horizon":{"years":5,"intervals":"annual"},"as_of":"2026-09-29",
 "caller_disclosures":{"peer_omitted_reason":"not gathered","lookback_note":"full available history 2005Q1-2026Q2","rate_instrument":"fed_funds"},
 "history":arr(ch),"history_check":chk(ch),
 "context_signals":{"rate_environment":{"current_rate":3.63,"projected_rate":3.6,"projected_as_of":"2029"}}}
json.dump(capp,open('p_cap.json','w'))
opex={"metric":{"name":"mf_property_financials.mf_pnl_total_operating_expenses","units":"$","family":"operating"},
 "subject":{"entity_type":"property","entity_id":"1a5aa1db99e8bbf2b4dedfbaf29e09ae","label":"Summit Riverside Apartments"},
 "horizon":{"years":5,"intervals":"annual"},"as_of":"2026-09-29",
 "caller_disclosures":{"lookback_note":"single T12 point only; no prior-year statements available"},
 "history":[{"period":"2026-08-31","value":2133387.74,"source":"datamart"}],"history_check":{"count":1,"last_value":2133387.74},
 "scenarios":["base","upside","downside"]}
json.dump(opex,open('p_opex.json','w'))
EOF
python build_payloads.py
for p in inplace asking cap opex; do python ../skills/forecasting/scripts/forecast.py --file p_$p.json > out_$p.json; echo "== $p"; python -c "
import json;d=json.load(open('out_$p.json'))
for k in ['status','mode','confidence','base_case','upside_case','downside_case','band_position','signals','data_quality_flags','methodology']:
  if k in d: print(k, json.dumps(d[k])[:900])
"; done

cd /vercel/sandbox && python -c "
import openpyxl
wb=openpyxl.load_workbook('inputs/template.xlsx')
ws=wb['_SensCalc']
for r in ws.iter_rows(min_row=1,max_row=6):
  for c in r:
    if c.value is not None: print(c.coordinate, str(c.value)[:300])
" ; python skills/xlsx/scripts/workbook_search.py inputs/template.xlsx --pattern 'H\\\$22|H\\\$26|\\\$C\\\$30|\\\$C\\\$31|C30|C31' 2>/dev/null | head -60

cd /vercel/sandbox && python -c "
import openpyxl
wb=openpyxl.load_workbook('inputs/template.xlsx')
ws=wb['_SensCalc']
for a in ['D3','E3','H3','D55','A30','B30','A55','C55']:
  v=ws[a].value
  print(a, getattr(v,'text',v)[:1500] if v is not None else None)
  print()
print(ws['B30'].value, ws['C30'].value)
"

cd /vercel/sandbox && python -c "
import openpyxl
wb=openpyxl.load_workbook('inputs/template.xlsx')
ws=wb['IC Memo']
print(ws.merged_cells.ranges)
for r in [4,5,6,7,10,11,12,51,52,53,94]:
  for c in 'BCDEFG':
    cell=ws[f'{c}{r}']
    print(cell.coordinate, repr(cell.value)[:60], cell.font.color.rgb if cell.font.color else None, cell.fill.fgColor.rgb, cell.alignment.wrap_text)
print(ws.row_dimensions[11].height, ws.row_dimensions[52].height)
wa=wb['Assumptions']
for a in ['C5','C9','C15','H15','H16','H33','H41','C38','C30','C31','B30']:
  c=wa[a]; print(a, c.value, c.font.color.rgb if c.font.color else None, c.number_format)
rc=wb['Rent Comps']
for a in ['D6','D9','D14','D17','D25','B22','B23','C17']:
  c=rc[a]; print(a, c.value, c.number_format)
sc=wb['Sales Comps']
for a in ['D15','D16','D22','D28','D19']:
  c=sc[a]; print(a, c.value, c.number_format)
"; python skills/xlsx/scripts/workbook_trace.py inputs/template.xlsx --cell "Assumptions!C30" | head -30

cd /vercel/sandbox && python -c "
import json
d=json.load(open('subagents/rentcomps/comps.json'))
d=d if isinstance(d,list) else d.get('comps',d)
for c in d: print({k:c.get(k) for k in c if 'source' not in k and 'field' not in k})
"; ls subagents/rentcomps

mkdir -p /vercel/sandbox/out && cd /vercel/sandbox && cat > populate.py <<'EOF'
import openpyxl, math, datetime, json
SRC='inputs/template.xlsx'; OUT='out/SummitRiverside_underwriting_2026-09-29.xlsx'
wb=openpyxl.load_workbook(SRC)
A=wb['Assumptions']
def w(ws,addr,val):
    c=ws[addr]
    if isinstance(c.value,str) and c.value.startswith('='): raise SystemExit(f'formula in write target {ws.title}!{addr}')
    c.value=val
# --- property
for a,v in {'C5':'Summit Riverside Apartments','C6':'4957 S Prince Ct','C7':'Littleton, CO 80123','C8':'Multifamily - Garden (3-story)',
            'C9':248,'C10':200384,'C11':1986,'C12':2013}.items(): w(A,a,v)
# --- acquisition / exit / capex / reserves
for a,v in {'C15':40000000,'C18':0.025,'C22':0.065,'C23':0.02,'C26':0,'C28':0,'C32':300}.items(): w(A,a,v)
# --- debt
for a,v in {'C36':'Interest Only','C38':0.60,'C40':0.0626,'C41':5,'C42':30,'C43':5,'C55':0,'C56':0,'C57':0,'C73':'No'}.items(): w(A,a,v)
# --- growth
w(A,'H15',5); w(A,'H16','=AVERAGE(H26:L26)'); w(A,'H17',0.035); w(A,'H18',0.025); w(A,'H19',0.94); w(A,'H22','Yes')
rent=[-0.0117,0.0,0.015,0.025,0.028]
for i,col in enumerate('HIJKL'):
    w(A,f'{col}26',rent[i]); w(A,f'{col}27',0.94); w(A,f'{col}28',0.025); w(A,f'{col}29',0.035)
# --- T12 (RealAI Ops Benchmarks modeled P&L)
t12={'H33':5004035.60,'H34':-294894.52,'H37':519263.25,'H41':434343.45,'H42':201712.61,'H43':349826.05,'H44':234648.13,
     'H45':149008.90,'H46':559869.09,'H47':115344.58,'H48':88634.93,'H49':0}
for a,v in t12.items(): w(A,a,v)
# --- IC memo header
M=wb['IC Memo']
w(M,'F4',datetime.date(2026,9,29)); M['F4'].number_format='yyyy-mm-dd'
w(M,'F5','CentrePoint Acquisitions (RealAI screen)'); w(M,'F6','Screening / pre-LOI')
# --- rent comps
subj=(39.626905024051766,-105.01938879489899)
def hav(lat,lon):
    R=3958.8; p1,p2=math.radians(subj[0]),math.radians(lat); dp=p2-p1; dl=math.radians(lon-subj[1])
    a=math.sin(dp/2)**2+math.cos(p1)*math.cos(p2)*math.sin(dl/2)**2; return round(2*R*math.asin(math.sqrt(a)),2)
rc=[ # name, addr, city, lat, lon, units, rsf, yb, yr, inplace, occ, bybed(0,1,2,3)
 ('Verona Apartment Homes','2961 W Centennial Dr','Littleton, CO 80123',39.620167315006334,-105.026952624321,276,208932,1985,2014,1620.79,0.9420,(None,1490.76,1775.28,None)),
 ('Terra Vista at the Park','5425 S Federal Cir','Littleton, CO 80123',39.618525803089234,-105.02928078174591,324,255960,1984,2012,1597.98,0.9475,(None,1448.11,1757.03,None)),
 ('The Station','2100 W Berry Ave','Littleton, CO 80120',39.61662143468865,-105.0134664773941,97,71683,1983,2022,1593.94,0.9897,(1356.15,1532.67,1788.52,None)),
 ('Parkland Square','750 W Belleview Ave','Englewood, CO 80110',39.62345033884058,-104.99667584896088,102,54570,1973,None,1275.12,0.8824,(1000.0,1292.31,None,None)),
 ('Tiburon Apartments','700 W Belleview Ave','Englewood, CO 80110',39.62332159280786,-104.99562442302705,83,64159,1999,None,1488.91,0.9639,(None,1404.02,1889.14,None)),
 ('Canyon Crest Apartments','5754 S Lowell Way','Littleton, CO 80123',39.61226552724847,-105.03519237041473,90,84690,1970,None,1428.00,0.9778,(None,1238.75,1508.44,1920.0)),
 ('Weston Ridge Apartments','5967 S Gallup St','Littleton, CO 80120',39.60869818925867,-105.00299513339996,164,117260,1970,None,1246.43,0.9634,(None,1163.26,1405.83,None)),
 ('Switchback on Platte','6419 S Vinewood St','Littleton, CO 80120',39.59973961114893,-105.02283275127411,93,91512,2005,None,1791.16,0.9462,(None,1601.44,1706.08,2285.2)),
]
R=wb['Rent Comps']
w(R,'C17',1494.00); w(R,'C18',1565.01); w(R,'C19',1913.50)
dist={}
for i,c in enumerate(rc):
    col='DEFGHIJK'[i]; d=hav(c[3],c[4]); dist[c[0]]=d
    for row,v in zip([6,7,8,9,10,11,12,13,14,25],[c[0],c[1],c[2],d,c[5],c[6],c[7],c[8],c[9],c[10]]):
        if v is not None: w(R,f'{col}{row}',v)
    for j,v in enumerate(c[11]):
        if v is not None: w(R,f'{col}{17+j}',v)
# --- sales comps
cap_now=0.0548
capq={'1Q22':0.0361,'3Q24':0.0489,'4Q23':0.0529,'2Q23':0.0488}
sc=[ # name, addr, city, lat, lon, units, yb, yr, date, price, q, buyer, vint_adj, loc_adj, weight, inplace
 ('SouthGlenn Place','6541-6651 S Vine St','Centennial, CO 80121',39.59756165742883,-104.96264398097992,136,1969,2019,datetime.date(2024,9,12),22350000,'3Q24',None,0.0,0.0,0.40,None),
 ('Avalon Cherry Hills','3650 S Broadway','Englewood, CO 80113',39.650218784809205,-104.9870091676712,306,2014,None,datetime.date(2024,8,2),95000000,'3Q24','AvalonBay Communities',-0.25,-0.05,0.25,None),
 ('Switchback on Platte','6419 S Vinewood St','Littleton, CO 80120',39.59973961114893,-105.02283275127411,93,2005,None,datetime.date(2022,2,28),27850000,'1Q22',None,-0.15,0.0,0.15,1791.16),
 ('Parkside at Littleton Village','300 E Fremont Pl','Littleton, CO 80122',39.58307772874842,-104.98535692691804,114,2022,None,datetime.date(2024,7,30),43500000,'3Q24','Brixton Capital',-0.35,0.0,0.10,None),
 ('Olivine','8012 W Long Dr','Littleton, CO 80123',39.612297713756654,-105.08729159832001,326,2021,None,datetime.date(2023,12,21),100000000,'4Q23','Connor Group',-0.35,0.0,0.10,None),
 ('Vistas at Stony Creek','9140 W Coal Mine Ave','Littleton, CO 80123',39.59331840276726,-105.10004818439484,156,2000,2019,datetime.date(2023,5,24),13095600,'2Q23',None,-0.10,0.0,0.0,None),
]
S=wb['Sales Comps']; timeadj={}
for i,c in enumerate(sc):
    col='DEFGHI'[i]; d=hav(c[3],c[4]); ta=round(capq[c[10]]/cap_now-1,4); timeadj[c[0]]=ta
    vals={6:c[0],7:c[1],8:c[2],9:d,10:c[5],11:c[6],12:c[7],15:c[8],16:c[9],18:c[15],19:c[11],22:ta,23:0.0,24:c[12],25:c[13],26:0.0,28:c[14]}
    for row,v in vals.items():
        if v is not None: w(S,f'{col}{row}',v)
    S[f'{col}15'].number_format='yyyy-mm-dd'
wb.save(OUT)
json.dump({'rent_dist':dist,'time_adj':timeadj},open('out/aux.json','w'),indent=1)
print(json.dumps({'rent_dist':dist,'time_adj':timeadj},indent=1))
EOF
python populate.py && python skills/xlsx/scripts/workbook_integrity_scan.py out/SummitRiverside_underwriting_2026-09-29.xlsx --json out/integrity.json; echo EXIT $?; python skills/xlsx/scripts/recalc.py out/SummitRiverside_underwriting_2026-09-29.xlsx | head -40

cd /vercel/sandbox && python skills/xlsx/scripts/workbook_integrity_scan.py inputs/template.xlsx --json out/int_base.json >/dev/null 2>&1; python -c "
import json
for f in ['out/int_base.json','out/integrity.json']:
  d=json.load(open(f)); print('==',f)
  for x in d['findings']:
    if x['severity']=='high': print(x['category'],x['sheet'],x.get('cell',x.get('range')),str(x.get('value',x.get('reason')))[:90])
"

cd /vercel/sandbox && python skills/xlsx/scripts/recalc.py out/SummitRiverside_underwriting_2026-09-29.xlsx | python -c "import json,sys;d=json.load(sys.stdin);print({k:d[k] for k in d if k!='error_summary'}); print(str(d.get('error_summary'))[:500])"; cat > readout.py <<'EOF'
import openpyxl,sys
f=sys.argv[1] if len(sys.argv)>1 else 'out/SummitRiverside_underwriting_2026-09-29.xlsx'
wb=openpyxl.load_workbook(f,data_only=True)
def g(s,a): return wb[s][a].value
cells={'Assumptions':['H5','H6','H7','H8','H9','H10','H11','C16','C17','C19','C39','C46','C50','C51','C52','C64','C70','H36','H38','H50','H52','H60','M52','M38','M50','H16'],
'Returns Summary':['C5','C7','C8','C9','C11','C12','C15','C16','C21','C22','C23','C24','C25','C26'],
'IC Memo':['F7','E30','E31','E32','E33','E34','D34','C38','C39','C40','C41','C42','C43','D38','D39','D40','D41','D42','D43','F38','F39','F40','F41','F42','F43','F44','C47','C48'],
'Rent Comps':['C14','C29','C30','C31','C32','C33','C35'],'Sales Comps':['C32','C33','C34','C35','D29','E29','F29','G29','H29','I29']}
for s,l in cells.items():
    print('==',s); print({a:g(s,a) for a in l})
pf=wb['Pro Forma']
for r in [6,8,9,10,11,23,25,26,27,32,38,39,42,43]:
    print(r,pf[f'B{r}'].value,[pf[f'{c}{r}'].value if not isinstance(pf[f'{c}{r}'].value,float) else round(pf[f'{c}{r}'].value,4) for c in 'CDEFGH'])
se=wb['Sensitivity']
for r in range(4,30):
    row=[se.cell(r,c).value for c in range(2,9)]
    if any(v is not None for v in row): print(r,[round(v,4) if isinstance(v,float) else v for v in row])
EOF
python readout.py

cd /vercel/sandbox && mkdir -p scratch && cat > stress_runs.py <<'EOF'
# Stress / goal-seek runs: each run copies the populated workbook, changes ONLY manifest-style input cells,
# recalculates in LibreOffice, and reads Levered IRR from 'Returns Summary'!C21 (and EM C22).
import openpyxl, subprocess, shutil, json
BASE='out/SummitRiverside_underwriting_2026-09-29.xlsx'
def run(tag, edits):
    f=f'scratch/{tag}.xlsx'; shutil.copy(BASE,f)
    wb=openpyxl.load_workbook(f)
    for (s,a),v in edits.items(): wb[s][a].value=v
    wb.save(f)
    subprocess.run(['python','skills/xlsx/scripts/recalc.py',f],capture_output=True)
    r=openpyxl.load_workbook(f,data_only=True)
    return {'irr':r['Returns Summary']['C21'].value,'em':r['Returns Summary']['C22'].value,
            'dscr_y1':r['Assumptions']['C50'].value,'noi_y1':r['Pro Forma']['D26'].value,'exit_value':r['Returns Summary']['C7'].value}
out={}
# 1) break-even exit cap for 11.0% levered IRR (bisection, <=10 iterations, tol 0.10%)
lo,hi=0.065,0.070; it=[]
for i in range(10):
    mid=round((lo+hi)/2,5); res=run('be_exitcap',{('Assumptions','C22'):mid}); it.append((mid,res['irr']))
    if abs(res['irr']-0.11)<=0.001: break
    if res['irr']>0.11: lo=mid
    else: hi=mid
out['breakeven_exit_cap']={'exit_cap':mid,'irr':res['irr'],'iterations':it}
# 2) exit at going-in cap (no cap compression) = T12 NOI / price, 7.74%
out['exit_at_goingin_7.74']=run('exit_goingin',{('Assumptions','C22'):0.0774})
# 3) NOI verification haircut: T12 NOI overstated by 15% (modelled as +$464,253 Other Expenses = 15% of T12 NOI)
out['noi_haircut_15pct']=run('noi_haircut',{('Assumptions','H49'):round(0.15*3095016.59,2)})
# 4) forecast-engine downside rent path (in-place rent downside case)
dn=[-0.0317,-0.023,-0.0264,-0.0304,-0.035]
out['engine_downside_rent']=run('rent_down',{('Assumptions',f'{c}26'):v for c,v in zip('HIJKL',dn)})
# 5) break-even price for 11% IRR at base assumptions (bisection on C15)
lo,hi=40000000,44000000; it=[]
for i in range(10):
    mid=round((lo+hi)/2,-4); res=run('be_price',{('Assumptions','C15'):mid}); it.append((mid,res['irr']))
    if abs(res['irr']-0.11)<=0.001: break
    if res['irr']>0.11: lo=mid
    else: hi=mid
out['breakeven_price']={'price':mid,'irr':res['irr'],'iterations':it}
# 6) combined downside: NOI -15% and exit cap 7.0%
out['combined_noi15_exit7']=run('combo',{('Assumptions','H49'):round(0.15*3095016.59,2),('Assumptions','C22'):0.07})
json.dump(out,open('scratch/stress_results.json','w'),indent=1); print(json.dumps(out,indent=1))
EOF
python stress_runs.py

cd /vercel/sandbox && python -c "import cairosvg" 2>&1; which rsvg-convert convert; python -c "import docx, matplotlib; print('ok')"; grep -o 'class="[a-z]"' inputs/logo.svg | sort | uniq -c; grep -o '<text[^>]*>[^<]*' inputs/logo.svg | head

cd /vercel/sandbox && (pip install cairosvg -q 2>&1 | tail -1); python -c "
import cairosvg
cairosvg.svg2png(url='inputs/logo.svg', write_to='out/logo.png', output_width=1400)
" && python -c "from PIL import Image; im=Image.open('out/logo.png'); print(im.size, im.mode)"

cd /vercel/sandbox && cat > narrative.py <<'EOF'
import openpyxl
F='out/SummitRiverside_underwriting_2026-09-29.xlsx'
wb=openpyxl.load_workbook(F); M=wb['IC Memo']
def w(a,v):
    if isinstance(M[a].value,str) and str(M[a].value).startswith('='): raise SystemExit('formula '+a)
    M[a].value=v
w('C7','CONDITIONAL - proceed to LOI subject to NOI verification and PCA')
T={
'B11':"Summit Riverside is a 248-unit, 1986-vintage (renovated 2013) garden community in Littleton, CO, offered at $40.0MM ($161k/unit), less than half its July 2022 trade of $78.5MM. On the modeled T12 the price is a 7.7% going-in cap versus a 5.5% Denver institutional market cap, and the base case (60% LTV, 5-year IO at 6.26%, 6.50% exit) clears all five IC hurdles with a 12.3% levered IRR and 1.68x multiple. The cushion is thin: returns rely on the modeled NOI holding and on the exit cap compressing about 125 bps from entry, and only one of five stress cases clears the 11% hurdle. Recommendation: CONDITIONAL - proceed to LOI at no more than $40.0MM, subject to a seller T12/rent roll that verifies NOI of at least ~$3.0MM and a clean property condition assessment.",
'B52':"1) Basis: $161k/unit sits at the comp-implied value (~$167k/unit adjusted) and near the Sept-2024 SouthGlenn Place trade ($164k/unit, 1969/2019), and far below the asset's 2022 basis - the prior owner's $45.3MM Freddie loan exceeds this price, so the seller is likely lender-constrained. 2) Income: an 8% average cash-on-cash and ~2.0x DSCR carry the deal before any exit. 3) What has to be true: NOI stays near $3.0MM through a soft Denver leasing market (in-place rents -3.7% YoY, new-lease tradeouts -6%), no unbudgeted capex, and an exit cap no wider than ~6.7%.",
'B56':"248 units (studio / 1BR / 2BR, avg 808 SF, 200,384 RSF) on 9.6 acres; 3-story wood-frame garden, built 1986, renovated 2013; FEMA zone X. In-unit W/D, fireplaces, balconies; pool, fitness, gated entry, covered/garage parking. Latest physical occupancy 97.6% (30-day avg 94.3%), avg in-place rent $1,679 and asking $1,723, trailing-12-month retention 71%. Resident review scores are poor (F on ApartmentRatings), which points to operational or physical issues to diligence.",
'B60':"Denver is digesting a supply wave: 12,246 units under construction on a ~302k-unit tracked base, 8,726 MF units permitted in the last 12 months (up from 7,622), and a 11.1% C&W vacancy rate. MSA asking rents are -2.6% YoY and in-place -1.6%; the Englewood submarket is weaker on asking (-6.0%). Net migration is slightly negative and job growth is 1.8%. Subject rent-to-income is ~23%, so affordability is not the constraint - supply is.",
'B64':"Eight comps within 1.9 miles, all market-rate low-rise/garden, 1970-2005 vintage. Comp average in-place rent is ~$1,505/unit vs the subject's ~$1,681 (T12 GPR basis), a ~12% premium; comp occupancy averages ~95%. The premium is supported by the subject's larger floor plans and amenity package, but it leaves limited loss-to-lease upside and some risk of rent pressure if concessions spread.",
'B68':"Six sales, 2022-2024, time-adjusted by the change in Denver MF cap rate since the sale quarter and adjusted for vintage (newer comps -15% to -35%). Weighted adjusted value is ~$167k/unit (~$41.4MM), 3.4% above the $40.0MM price; heaviest weight on SouthGlenn Place (vintage/condition match) and Avalon Cherry Hills (same submarket). Vistas at Stony Creek ($84k/unit) is carried at zero weight as an unverified outlier. Adjustments are analyst judgment.",
'B72':"No capital program is planned per the deal brief; CapEx is $0 and replacement reserves are $300/unit/yr. Year-1 NOI (~$2.97MM) is ~4% below the modeled T12 ($3.10MM) because the forecast engine's in-place rent path starts at -1.2% in Year 1 and holds flat in Year 2 (analyst recovery to +1.5% / +2.5% / +2.8% in Years 3-5) while expenses grow 3.5%/yr. Occupancy is held at 94% (T12 economic 94.1%). A 1986 asset with no capex budget and weak resident reviews is the main physical risk; a PCA is a condition.",
'B76':"$24.0MM senior loan (60% LTV), 5-year term, full-term interest-only at 6.26% (Fannie Mae 5-yr, 65%-LTV tier, 23-Sep-2026). Annual debt service $1.50MM; Year-1 DSCR ~1.98x and debt yield ~12.9%, well above agency minimums (1.35x at 65% LTV). Loan is comfortably sizable; the binding risk is refinance/exit at maturity, which coincides with the Year-5 sale.",
'B80':"Base case: 12.3% levered IRR, 1.68x EM, 8.9% unlevered IRR. The IRR hurdle breaks at a ~6.72% exit cap (vs 6.50% base, 7.74% entry) and at a ~$41.0MM price. Exit at the going-in cap drops IRR to ~5.3%; a 15% NOI shortfall to ~0.8%; the forecast engine's downside rent path to about -7%. Returns are highly sensitive to NOI verification and exit cap - see Sensitivity tab and stress cases above.",
'B84':"1) NOI is a modeled benchmark P&L, not a seller T12 - obtain T12, rent roll and bank statements; a 15% shortfall erases the return. 2) Exit cap - base assumes ~125 bps compression from entry; mitigant: underwrite a 6.75%+ exit in the final model. 3) Supply-driven rent softness (12k units under construction, negative tradeouts) - mitigant: 72.7% breakeven occupancy and ~2.0x DSCR. 4) Deferred maintenance on a 40-year-old asset with no capex budget - PCA before hard deposit. 5) Seller/lender execution - prior $45.3MM loan exceeds price; confirm the path (DPO, note sale, receiver).",
'B88':"Conditions: (a) seller T12 and rent roll verify NOI >= ~$3.0MM; (b) PCA with no material immediate needs (>$1,500/unit would require a re-price); (c) confirm seller/lender authority to transact at $40.0MM; (d) re-underwrite at a 6.75% exit - if levered IRR < 11%, re-price toward $38MM (15.1% IRR at base). Next: document request, PCA, tax-appeal review (Colorado reassessment at a $40MM value could lower taxes), agency quotes.",
'B92':"Property physicals, rents, occupancy, T12 P&L (RealAI Ops Benchmarks, modeled), tax, sales history: RealAI property data. Comps: RealAI property data; sale buyers/2022 financing: web research (MultiHousingNews, CREJ). Rent growth Y1-Y2 and expense growth (3.5%): RealAI forecast engine (low confidence; cyclical-drawdown flag); rent Y3-Y5 recovery: analyst judgment. Exit cap 6.50%: engine structural mean 5.62% plus ~90 bps vintage premium (judgment). Rate: RealAI agency rate data. Price, LTV, IO, hold, no capex: user-provided. Closing 2.5%, disposition 2.0%, reserves $300/unit, sales-comp adjustments: default/judgment.",
}
for a,v in T.items(): w(a,v)
for a in ['B11','B52','B56','B60','B64','B68','B72','B76','B80','B84','B88','B92']:
    r=int(a[1:]); M.row_dimensions[r].height=max(M.row_dimensions[r].height or 15, 105)
wb.save(F)
EOF
python narrative.py && python skills/xlsx/scripts/recalc.py out/SummitRiverside_underwriting_2026-09-29.xlsx | head -c 300; python readout.py | head -8

cd /vercel/sandbox && mkdir -p out/charts && cat > charts.py <<'EOF'
import json, openpyxl, matplotlib
matplotlib.use('Agg'); import matplotlib.pyplot as plt, matplotlib.ticker as mt, numpy as np, datetime as dt
G='#59ba55'; K='#231f20'; GR='#9a9a9a'; LG='#d9efd8'; RED='#c0504d'
plt.rcParams.update({'font.family':'DejaVu Sans','font.size':9,'axes.spines.top':False,'axes.spines.right':False,'axes.edgecolor':'#666','axes.titleweight':'bold','axes.titlesize':10.5,'axes.titlecolor':K})
wb=openpyxl.load_workbook('out/SummitRiverside_underwriting_2026-09-29.xlsx',data_only=True)
raw=json.load(open('fc/raw_rent.json'))
msa={"2026-08-01":1821.266322,"2026-07-01":1824.882485,"2026-06-01":1826.330541,"2026-05-01":1826.471308,"2026-04-01":1826.140976,"2026-03-01":1828.597100,"2026-02-01":1829.201538,"2026-01-01":1832.766984,"2025-12-01":1836.415187,"2025-11-01":1838.450362,"2025-10-01":1842.239285,"2025-09-01":1847.630683,"2025-08-01":1850.615525,"2025-07-01":1851.295182,"2025-06-01":1850.836317,"2025-05-01":1851.482758,"2025-04-01":1849.978728,"2025-03-01":1846.133550,"2025-02-01":1848.706706,"2025-01-01":1850.318084,"2024-12-01":1853.058645,"2024-11-01":1857.068506,"2024-10-01":1855.177385,"2024-09-01":1852.999407,"2024-08-01":1856.905501,"2024-07-01":1852.902201}
def ser(d):
    k=sorted(d); return [dt.date.fromisoformat(x) for x in k],[d[x] for x in k]
# 1 rent trend
fig,ax=plt.subplots(figsize=(6.8,3.0))
for d,lab,c,lw in [(raw['subject_inplace'],'Summit Riverside',G,2.4),(raw['sub_inplace'],'Englewood submarket',K,1.4),(msa,'Denver MSA',GR,1.4)]:
    x,y=ser(d); ax.plot(x,y,color=c,lw=lw,label=lab)
ax.set_title('In-place rents have rolled over: subject down ~3.5% in 12 months'); ax.set_ylabel('Avg in-place rent ($/unit/mo)')
ax.yaxis.set_major_formatter(mt.StrMethodFormatter('${x:,.0f}')); ax.legend(frameon=False,fontsize=8,ncol=3,loc='lower left'); ax.grid(axis='y',alpha=.25)
fig.tight_layout(); fig.savefig('out/charts/c1_rent_trend.png',dpi=200); plt.close()
# 2 rent comps
R=wb['Rent Comps']; names=[R.cell(6,c).value for c in range(4,12)]; rents=[R.cell(14,c).value for c in range(4,12)]
subj=R['C14'].value; avg=R['C29'].value
order=np.argsort(rents); nm=[names[i] for i in order]+['Summit Riverside (subject)']; rv=[rents[i] for i in order]+[subj]
fig,ax=plt.subplots(figsize=(6.8,3.3)); cols=[GR]*8+[G]
ax.barh(nm,rv,color=cols); ax.axvline(avg,color=K,ls='--',lw=1); ax.text(avg+8,0.2,f'Comp avg ${avg:,.0f}',fontsize=8,color=K)
for i,v in enumerate(rv): ax.text(v+8,i,f'${v:,.0f}',va='center',fontsize=7.5)
ax.set_xlim(1000,1950); ax.xaxis.set_major_formatter(mt.StrMethodFormatter('${x:,.0f}'))
ax.set_title(f'Subject already rents ~{(subj/avg-1)*100:.0f}% above the comp set - little loss-to-lease'); ax.set_xlabel('Avg in-place rent ($/unit/mo)')
fig.tight_layout(); fig.savefig('out/charts/c2_rent_comps.png',dpi=200); plt.close()
# 3 sales comps
S=wb['Sales Comps']; sn=[S.cell(6,c).value for c in range(4,10)]; ppu=[S.cell(17,c).value for c in range(4,10)]; adj=[S.cell(29,c).value for c in range(4,10)]; wt=[S.cell(28,c).value for c in range(4,10)]
fig,ax=plt.subplots(figsize=(6.8,3.2)); x=np.arange(len(sn)); bw=.38
ax.bar(x-bw/2,[p/1000 for p in ppu],bw,color=GR,label='Unadjusted $/unit'); ax.bar(x+bw/2,[a/1000 for a in adj],bw,color=K,label='Adjusted $/unit')
ax.axhline(S['C17'].value/1000,color=G,lw=2.2,label=f"Subject @ $40.0MM (${S['C17'].value/1000:,.0f}k)")
ax.axhline(S['C32'].value/1000,color=G,lw=1,ls='--',label=f"Weighted adj. value (${S['C32'].value/1000:,.0f}k)")
ax.set_xticks(x); ax.set_xticklabels([f"{n}\n(wt {w*100:.0f}%)" for n,w in zip(sn,wt)],fontsize=7)
ax.set_ylabel('$000 per unit'); ax.legend(frameon=False,fontsize=7.5,loc='upper right'); ax.set_title('Price sits at comp-implied value once age and timing are adjusted')
fig.tight_layout(); fig.savefig('out/charts/c3_sales_comps.png',dpi=200); plt.close()
# 4 cap rate band
cap=json.load(open('fc/raw_cap.json')); q={'1':1,'2':4,'3':7,'4':10}
pts=sorted((dt.date(2000+int(k[2:]),q[k[0]],1),v) for k,v in cap.items())
fig,ax=plt.subplots(figsize=(6.8,3.0)); ax.plot([p[0] for p in pts],[p[1]*100 for p in pts],color=K,lw=1.6,label='Denver MF market cap rate (GreenStreet)')
A=wb['Assumptions']; gi=A['H5'].value*100; ex=A['C22'].value*100
ax.axhline(gi,color=G,lw=2,label=f'Going-in (T12) cap {gi:.2f}%'); ax.axhline(ex,color=G,lw=1.2,ls='--',label=f'Base exit cap {ex:.2f}%')
ax.axhline(6.72,color=RED,lw=1,ls=':',label='IRR-hurdle break-even exit ~6.72%'); ax.axhline(5.62,color=GR,lw=1,ls='-.',label='21-yr structural mean 5.62%')
ax.set_ylabel('Cap rate (%)'); ax.legend(frameon=False,fontsize=7.2,loc='upper right',ncol=2); ax.set_ylim(3,9)
ax.set_title('Entry is ~225 bps wide of market; the plan needs ~125 bps of compression')
fig.tight_layout(); fig.savefig('out/charts/c4_caprate.png',dpi=200); plt.close()
# 5 NOI / CF / DSCR
P=wb['Pro Forma']; yrs=['T12','Yr 1','Yr 2','Yr 3','Yr 4','Yr 5']; noi=[P.cell(26,c).value/1e6 for c in range(3,9)]
lcf=[None]+[P.cell(38,c).value/1e6 for c in range(4,9)]; ds=[None]+[P.cell(34,c).value/1e6 for c in range(4,9)]; dscr=[None]+[P.cell(42,c).value for c in range(4,9)]
fig,ax=plt.subplots(figsize=(6.8,3.0)); x=np.arange(6)
ax.bar(x,noi,color=[GR]+[G]*5,label='NOI'); ax.bar(x[1:],ds[1:],color=K,width=.35,label='Debt service (IO)')
for i in range(1,6): ax.text(x[i],noi[i]+.05,f'{dscr[i]:.2f}x',ha='center',fontsize=8,color=K)
ax.plot(x[1:],lcf[1:],color=RED,marker='o',lw=1.4,label='Levered cash flow')
ax.set_xticks(x); ax.set_xticklabels(yrs); ax.set_ylabel('$MM'); ax.set_ylim(0,3.6); ax.legend(frameon=False,fontsize=7.5,ncol=3,loc='upper center')
ax.set_title('NOI dips in Years 1-2 before recovering; coverage stays ~2.0x')
fig.tight_layout(); fig.savefig('out/charts/c5_noi_cf.png',dpi=200); plt.close()
# 6 sensitivity heatmap
Sn=wb['Sensitivity']; cols=[Sn.cell(5,c).value for c in range(3,9)]; rows=[Sn.cell(r,2).value for r in range(6,11)]
grid=np.array([[Sn.cell(r,c).value for c in range(3,9)] for r in range(6,11)])
fig,ax=plt.subplots(figsize=(6.8,2.9))
from matplotlib.colors import LinearSegmentedColormap
cm=LinearSegmentedColormap.from_list('cp',['#e8a19f','#ffffff',G]); im=ax.imshow(grid,cmap=cm,vmin=0.0,vmax=0.22,aspect='auto')
for i in range(grid.shape[0]):
    for j in range(grid.shape[1]):
        v=grid[i,j]; ax.text(j,i,f'{v*100:.1f}%',ha='center',va='center',fontsize=8,fontweight='bold' if (i==2 and j==3) else 'normal',color=K if v>=0.11 else RED)
ax.set_xticks(range(6)); ax.set_xticklabels([f'{c*100:+.1f}%' if False else f'{c*100:.1f}%' for c in cols]); ax.set_yticks(range(5)); ax.set_yticklabels([f'{r*100:.2f}%' for r in rows])
ax.set_xlabel('Avg annual rent growth (base 1.1%)'); ax.set_ylabel('Exit cap'); ax.set_title('Levered IRR: half the grid misses the 11% hurdle (red)')
fig.tight_layout(); fig.savefig('out/charts/c6_sensitivity.png',dpi=200); plt.close()
# 7 stress bars
st=json.load(open('scratch/stress_results.json')); M=wb['IC Memo']
cases=[('Base case',M['C38'].value),('LTV +10 pts',M['C43'].value),('Exit cap +50 bp',M['C39'].value),('Price +10%',M['C42'].value),
 ('Exit at going-in cap (7.74%)',st['exit_at_goingin_7.74']['irr']),('Rent growth -150 bp',M['C40'].value),('Exit +50 bp & rent -150 bp',M['C41'].value),
 ('NOI 15% below modeled T12',st['noi_haircut_15pct']['irr']),('NOI -15% & exit 7.0%',st['combined_noi15_exit7']['irr']),('Engine downside rent path',st['engine_downside_rent']['irr'])]
fig,ax=plt.subplots(figsize=(6.8,3.4)); lab=[c[0] for c in cases][::-1]; val=[c[1]*100 for c in cases][::-1]
ax.barh(lab,val,color=[G if v>=11 else RED for v in val]); ax.axvline(11,color=K,ls='--',lw=1); ax.text(11.2,9.3,'11% IRR hurdle',fontsize=8)
ax.axvline(0,color='#666',lw=.8)
for i,v in enumerate(val): ax.text(v+(0.3 if v>=0 else -0.3),i,f'{v:.1f}%',va='center',ha='left' if v>=0 else 'right',fontsize=7.5)
ax.set_xlabel('Levered IRR (%)'); ax.set_xlim(-10,17); ax.set_title('Only more leverage clears the hurdle under stress')
fig.tight_layout(); fig.savefig('out/charts/c7_stress.png',dpi=200); plt.close()
print('ok')
EOF
python charts.py && ls out/charts

cd /vercel/sandbox && sed -i 's/ax.set_xticklabels(\[f"{n}\\n(wt {w\*100:.0f}%)" for n,w in zip(sn,wt)\],fontsize=7)/import textwrap; ax.set_xticklabels([textwrap.fill(n,14)+f"\\n(wt {w*100:.0f}%)" for n,w in zip(sn,wt)],fontsize=7)/' charts.py && grep -n textwrap charts.py && python charts.py && python -c "
from PIL import Image
for f in ['c3_sales_comps','c4_caprate','c7_stress']:
  im=Image.open(f'out/charts/{f}.png'); print(f, im.size)
"

cd /vercel/sandbox && cat > memo.py <<'EOF'
import openpyxl, json
from docx import Document
from docx.shared import Pt, Inches, RGBColor
from docx.enum.text import WD_ALIGN_PARAGRAPH
from docx.enum.table import WD_TABLE_ALIGNMENT
from docx.oxml.ns import qn
from docx.oxml import OxmlElement
wb=openpyxl.load_workbook('out/SummitRiverside_underwriting_2026-09-29.xlsx',data_only=True)
A,P,RS,M,R,S=[wb[n] for n in ['Assumptions','Pro Forma','Returns Summary','IC Memo','Rent Comps','Sales Comps']]
st=json.load(open('scratch/stress_results.json'))
fc={k:json.load(open(f'fc/out_{k}.json')) for k in ['inplace','asking','cap','opex']}
G=RGBColor(0x59,0xBA,0x55); K=RGBColor(0x23,0x1F,0x20); GR=RGBColor(0x70,0x71,0x66)
m=lambda v:f'${v/1e6:,.2f}MM'; p=lambda v,d=1:f'{v*100:.{d}f}%'; d0=lambda v:f'${v:,.0f}'
doc=Document()
for s in doc.sections: s.left_margin=s.right_margin=Inches(0.8); s.top_margin=Inches(0.6); s.bottom_margin=Inches(0.6)
st_=doc.styles['Normal']; st_.font.name='Arial'; st_.font.size=Pt(9.5); st_.font.color.rgb=K
st_.element.rPr.rFonts.set(qn('w:eastAsia'),'Arial')
for h,sz in [('Heading 1',14),('Heading 2',11.5)]:
    hs=doc.styles[h]; hs.font.name='Arial'; hs.font.size=Pt(sz); hs.font.bold=True; hs.font.color.rgb=G if h=='Heading 1' else K
    hs.element.rPr.rFonts.set(qn('w:eastAsia'),'Arial'); hs.paragraph_format.space_before=Pt(10); hs.paragraph_format.space_after=Pt(4)
def shade(cell,hexc):
    tcPr=cell._tc.get_or_add_tcPr(); sh=OxmlElement('w:shd'); sh.set(qn('w:val'),'clear'); sh.set(qn('w:color'),'auto'); sh.set(qn('w:fill'),hexc); tcPr.append(sh)
def para(t,bold=False,italic=False,size=None,color=None,after=4,align=None):
    pp=doc.add_paragraph(); r=pp.add_run(t); r.bold=bold; r.italic=italic
    if size: r.font.size=Pt(size)
    if color: r.font.color.rgb=color
    pp.paragraph_format.space_after=Pt(after)
    if align: pp.alignment=align
    return pp
def rich(parts,style=None,after=3):
    pp=doc.add_paragraph(style=style)
    for t,b in parts: r=pp.add_run(t); r.bold=b
    pp.paragraph_format.space_after=Pt(after); return pp
def bullet(t,lead=None):
    pp=doc.add_paragraph(style='List Bullet')
    if lead: r=pp.add_run(lead); r.bold=True
    pp.add_run(t); pp.paragraph_format.space_after=Pt(2); return pp
def table(hdr,rows,widths=None,num_from=1,foot=None):
    t=doc.add_table(rows=1,cols=len(hdr)); t.style='Table Grid'; t.alignment=WD_TABLE_ALIGNMENT.CENTER
    for i,h in enumerate(hdr):
        c=t.rows[0].cells[i]; c.text=''; r=c.paragraphs[0].add_run(h); r.bold=True; r.font.size=Pt(8.5); r.font.color.rgb=RGBColor(255,255,255); shade(c,'59BA55')
    for ri,row in enumerate(rows):
        cells=t.add_row().cells
        for i,v in enumerate(row):
            cells[i].text=''; bold=isinstance(v,tuple); v=v[0] if bold else v
            r=cells[i].paragraphs[0].add_run(str(v)); r.font.size=Pt(8.5); r.bold=bold
            if i>=num_from: cells[i].paragraphs[0].alignment=WD_ALIGN_PARAGRAPH.RIGHT
            if ri%2==1: shade(cells[i],'F1F8F0')
    if widths:
        for row in t.rows:
            for i,w in enumerate(widths): row.cells[i].width=Inches(w)
    if foot: para(foot,italic=True,size=7.5,color=GR,after=6)
    else: doc.add_paragraph().paragraph_format.space_after=Pt(2)
    return t
def fig(f,cap=None):
    doc.add_picture(f,width=Inches(6.6)); doc.paragraphs[-1].alignment=WD_ALIGN_PARAGRAPH.CENTER
    if cap: para(cap,italic=True,size=7.5,color=GR,after=6,align=WD_ALIGN_PARAGRAPH.CENTER)
# header/footer
sec=doc.sections[0]; hp=sec.header.paragraphs[0]; hp.add_run().add_picture('out/logo.png',width=Inches(1.9))
hr=hp.add_run('\tInvestment Committee Memorandum  |  Confidential'); hr.font.size=Pt(8); hr.font.color.rgb=GR
fp=sec.footer.paragraphs[0]; fr=fp.add_run('CentrePoint Properties  |  Prepared for Investment Committee use only  |  Screening-level analysis, not an appraisal'); fr.font.size=Pt(7); fr.font.color.rgb=GR
# title
para('Summit Riverside Apartments',bold=True,size=20,color=K,after=0)
para('4957 S Prince Ct, Littleton, CO 80123  |  248 units  |  Built 1986, renovated 2013  |  Garden (3-story)',size=9,color=GR,after=2)
para('IC date: September 29, 2026   |   Deal stage: Screening / pre-LOI   |   Request: $40.0MM acquisition, 60% LTV 5-yr IO, 5-yr hold, no capital program',size=8.5,color=GR,after=8)
# recommendation box
t=doc.add_table(rows=1,cols=1); t.style='Table Grid'; c=t.rows[0].cells[0]; shade(c,'EEF7ED')
c.text=''; r=c.paragraphs[0].add_run('RECOMMENDATION: CONDITIONAL GO'); r.bold=True; r.font.size=Pt(13); r.font.color.rgb=G
pp=c.add_paragraph(); pp.add_run(f"Summit Riverside at $40.0MM ({d0(A['C16'].value)}/unit, {d0(A['C17'].value)}/SF) generates {m(A['H52'].value)} of modeled T12 NOI - a {p(A['H5'].value,2)} going-in cap against a 5.48% Denver multifamily market cap. ").font.size=Pt(9.5)
pp.add_run(f"The base case clears all five IC hurdles ({p(RS['C21'].value)} levered IRR, {RS['C22'].value:.2f}x multiple, {A['C50'].value:.2f}x DSCR), but the cushion is about one point of IRR, and only one of five stress cases clears 11%. ").font.size=Pt(9.5)
r2=pp.add_run("Proceed to LOI at no more than $40.0MM, conditioned on (1) a seller T12 and rent roll that verify NOI of at least ~$3.0MM, (2) a clean property condition assessment, and (3) confirmation that the seller and its lender can close at this price."); r2.bold=True; r2.font.size=Pt(9.5)
doc.add_paragraph().paragraph_format.space_after=Pt(2)
doc.add_heading('Investment thesis',level=2)
para(f"The basis is the story. $161k/unit is less than half the July 2022 trade ($78.5MM, $316.5k/unit) and sits in line with comp-implied value once age and timing are adjusted. At this price the asset throws off an ~8% cash yield on equity with ~2.0x coverage before any exit, so the investment works as an income play if the NOI is real. The upside case is cap-rate normalization toward the Denver market as the supply wave clears.",after=4)
doc.add_heading('Top risks',level=2)
bullet(" The T12 is a RealAI modeled benchmark P&L, not a seller statement. A 15% NOI shortfall takes the levered IRR to "+p(st['noi_haircut_15pct']['irr'])+". Watch item: T12, rent roll and bank statements before PSA.",'NOI is unverified.')
bullet(f" The base case exits at {p(A['C22'].value,2)}, about 125 bps inside entry. The hurdle breaks at ~{st['breakeven_exit_cap']['exit_cap']*100:.2f}%, and exiting at the going-in cap yields {p(st['exit_at_goingin_7.74']['irr'])}. Mitigant: the final model should also be run at a 6.75% or wider exit.",'Exit cap compression carries the return.')
bullet(" Denver has 12,246 units under construction and 11.1% vacancy. Subject in-place rents are down ~3.5% YoY and new-lease tradeouts are -6.3%. Mitigant: 72.7% breakeven occupancy.",'Supply-driven rent softness.')
para("What most affects conviction: whether the seller's actual operating statement supports ~$3.1MM of NOI. The 225 bp gap between the entry cap and the market cap is either the opportunity or a sign the modeled NOI is too high. That is the first diligence item.",italic=True,after=6)
# snapshot
doc.add_heading('1. Deal snapshot',level=1)
table(['Acquisition & operations','','Capitalization & returns',''],[
 ['Purchase price',m(A['C15'].value),'Senior loan (60% LTV)',m(A['C39'].value)],
 ['Price / unit  |  Price / SF',f"{d0(A['C16'].value)}  |  {d0(A['C17'].value)}",'Rate / structure',f"{p(A['C40'].value,2)} | 5-yr full-term IO"],
 ['Total basis (incl. 2.5% closing, Yr-1 reserves)',m(A['C19'].value),'Equity at close',m(A['C70'].value)],
 ['T12 NOI (modeled)',m(A['H52'].value),'Levered IRR  |  Equity multiple',f"{p(RS['C21'].value)}  |  {RS['C22'].value:.2f}x"],
 ['Year-1 NOI',m(P['D26'].value),'Unlevered IRR',p(RS['C15'].value)],
 ['T12 cap  |  Year-1 cap',f"{p(A['H5'].value,2)}  |  {p(A['H6'].value,2)}",'Avg cash-on-cash (5-yr)',p(RS['C24'].value)],
 ['Breakeven occupancy',p(A['C52'].value),'Year-1 DSCR  |  Debt yield',f"{A['C50'].value:.2f}x  |  {p(A['C51'].value)}"],
],num_from=9)
table(['IC hurdle','Hurdle','Actual','Result'],[
 ['Levered IRR (min)',p(M['C30'].value),p(M['D30'].value),M['E30'].value],
 ['Levered equity multiple (min)',f"{M['C31'].value:.2f}x",f"{M['D31'].value:.2f}x",M['E31'].value],
 ['Going-in DSCR (min)',f"{M['C32'].value:.2f}x",f"{M['D32'].value:.2f}x",M['E32'].value],
 ['Debt yield (min)',p(M['C33'].value),p(M['D33'].value),M['E33'].value],
 ['Year-1 cash-on-cash (min)',p(M['C34'].value),p(M['D34'].value),M['E34'].value]],
 foot='IC hurdles as set in the CentrePoint template. Results are live formulas in the workbook (IC Memo tab).')
# comps
doc.add_heading('2. What the comps say',level=1)
para("Rent comps are eight market-rate garden and low-rise communities within 1.9 miles, built 1970-2005. The subject rents about 12% above the comp average, which reflects larger units and a fuller amenity set but leaves little mark-to-market upside for a no-capex plan.",after=4)
rows=[]
for ci in range(4,12):
    rows.append([R.cell(6,ci).value,f"{R.cell(9,ci).value:.1f} mi",R.cell(10,ci).value,R.cell(12,ci).value,d0(R.cell(14,ci).value),p(R.cell(25,ci).value)])
rows.append([('Summit Riverside (subject)',),('-',),(248,),(1986,),(d0(1678.91)+' *',),(p(0.9758),)])
table(['Rent comp','Distance','Units','Built','In-place rent','Occupancy'],rows,foot='* Subject in-place rent is the latest observed average ($1,679); the workbook comp tab uses the T12 GPR basis ($1,681/unit/mo). Source: RealAI property data, Sept 2026.')
fig('out/charts/c2_rent_comps.png')
para("Sales comps are six 2022-2024 trades within about 5 miles. Each is time-adjusted by the change in the Denver multifamily cap rate since its sale quarter, then adjusted down 15-35% for newer vintage. The weighted result is ~$167k/unit (~$41.4MM), so $40.0MM is about 3% below comp-implied value. That is fair value for the vintage, not a discount. The closest physical match, SouthGlenn Place (1969, renovated 2019), traded at $164k/unit in September 2024.",after=4)
rows=[]
for ci in range(4,10):
    dtv=S.cell(15,ci).value; rows.append([S.cell(6,ci).value,dtv.strftime('%b %Y') if hasattr(dtv,'strftime') else dtv,S.cell(10,ci).value,S.cell(11,ci).value,m(S.cell(16,ci).value),d0(S.cell(17,ci).value),d0(S.cell(29,ci).value),p(S.cell(28,ci).value,0)])
table(['Sale comp','Sold','Units','Built','Price','$/unit','Adj. $/unit','Weight'],rows,foot='Vistas at Stony Creek ($84k/unit) carries zero weight as an unverified outlier. Adjustments are analyst judgment; disclosed cap rates are scarce (Olivine ~4.0% at its Dec-2023 sale per third-party report).')
fig('out/charts/c3_sales_comps.png')
# operating
doc.add_heading("3. How it's operating",level=1)
bullet(f" Latest physical occupancy is 97.6% (30-day average 94.3%), with 22 units on market and a 47-day median days-on-market. Trailing-12-month retention is 71%, and T12 economic occupancy is {p(A['H36'].value)}.",'Occupancy is solid, leasing is slow.')
bullet(" Average in-place rent is $1,679, down 3.7% YoY on the median. Average asking is $1,723. New leases are trading out 6.3% below prior rents. 2BR in-place ($1,914) runs above the 2BR asking ($1,836), a sign of roll-down risk on turnover.",'Pricing power has faded.')
bullet(f" The modeled OpEx ratio is {p(A['H60'].value)} of EGI ($8,602/unit), inside the 35-55% band. Management at 2.9% of EGI and R&M at 4.5% are lean for a 1986 asset. Taxes of $1,751/unit may reset lower at a $40MM value under Colorado's biennial reassessment, which is upside not modeled. Resident reviews are poor (F rating), which suggests service or physical issues to diligence.",'Expenses look lean.')
fig('out/charts/c1_rent_trend.png','Source: RealAI Rent Index monthly in-place rents, Jul 2024-Aug 2026.')
# market
doc.add_heading('4. Market context',level=1)
para("Denver is absorbing a supply wave. There are 12,246 units under construction against ~302k tracked units, trailing-12-month MF permits rose to 8,726 from 7,622, and C&W puts vacancy at 11.1% even with 4,838 units of net absorption. MSA asking rents are down 2.6% YoY and Englewood submarket asking rents are down 6.0%. Net migration is slightly negative, and job growth (1.8%) is steady but not strong enough to absorb the pipeline quickly. For a no-capex hold this is a headwind to Years 1-2 rent growth, not a thesis-breaker. The subject's ~23% rent-to-income ratio means tenants can absorb rent increases once supply clears.",after=4)
cp=fc['cap']['band_position']
para(f"Cap rates: the Denver multifamily cap rate is {cp['current_level']*100:.2f}%, which the forecast engine places mid-band in its 21-year history (min {cp['historical_band']['min']*100:.2f}%, structural mean {cp['historical_band']['structural_mean']*100:.2f}%, max {cp['historical_band']['max']*100:.2f}%). The rate environment reads 'stable' (fed funds 3.63% now, FOMC median 3.6% for 2029). Engine confidence: {fc['cap']['confidence']}. Engine flag: \"{fc['cap']['data_quality_flags'][0]}\" Our 6.50% exit is the 5.62% structural mean plus ~90 bps for a 45-year-old asset at exit, which is analyst judgment.",after=4)
fig('out/charts/c4_caprate.png','Source: GreenStreet market cap rates via RealAI, quarterly 2005-2Q26.')
# underwriting
doc.add_heading('5. How it underwrites',level=1)
rows=[['Effective gross income',m(A['H38'].value),m(P['D11'].value),m(P['H11'].value)],
 ['Operating expenses',m(A['H50'].value),m(P['D23'].value),m(P['H23'].value)],
 [('Net operating income',),(m(A['H52'].value),),(m(P['D26'].value),),(m(P['H26'].value),)],
 ['Debt service (IO)','-',m(P['D34'].value),m(P['H34'].value)],
 ['Levered cash flow (after $300/unit reserves)','-',m(P['D38'].value),m(P['H38'].value)],
 ['Cap rate on price  |  Cash-on-cash',p(A['H5'].value,2),f"{p(A['H6'].value,2)}  |  {p(P['D39'].value)}",f"{p(P['H26'].value/A['C15'].value,2)}  |  {p(P['H39'].value)}"],
 ['DSCR  |  Debt yield','-',f"{P['D42'].value:.2f}x  |  {p(P['D43'].value)}",f"{P['H42'].value:.2f}x  |  {p(P['H43'].value)}"]]
table(['Line','T12 (modeled)','Year 1','Year 5'],rows)
para(f"Exit: Year-5 NOI of {m(RS['C5'].value)} capped at {p(A['C22'].value,2)} gives a {m(RS['C7'].value)} gross value. Net of 2% costs and the {m(-RS['C9'].value)} loan payoff, sale proceeds are {m(RS['C11'].value)}, for a {p(RS['C21'].value)} levered IRR and {RS['C22'].value:.2f}x multiple. Year-5 NOI ends slightly below the T12 because the forecast engine's rent path starts negative (Year 1 -1.2%, Year 2 flat) while expenses compound at 3.5%. All of the value gain therefore comes from the exit cap, not NOI growth.",after=4)
fi=fc['inplace']
para(f"Forecast engine, in-place rent: confidence {fi['confidence']}. Engine flag: \"{fi['data_quality_flags'][0]}\" Accordingly, Years 3-5 (+1.5%, +2.5%, +2.8%) are an analyst recovery path toward the engine's 2.8% terminal rate. Expense growth (3.5%) is the engine's structural fallback, confidence {fc['opex']['confidence']}: \"{fc['opex']['data_quality_flags'][0]}\"",size=8,color=GR,after=6)
fig('out/charts/c5_noi_cf.png')
# sensitivity
doc.add_heading('6. Sensitivity - where it breaks',level=1)
para(f"Two variables decide the deal: the exit cap and whether the NOI is real. The 11% IRR hurdle breaks at a ~{st['breakeven_exit_cap']['exit_cap']*100:.2f}% exit cap (22 bps wide of base) or a ~$41.0MM price. A 15% NOI shortfall alone takes the IRR to {p(st['noi_haircut_15pct']['irr'])}, and the engine's downside rent path produces a loss ({p(st['engine_downside_rent']['irr'])}). The deal has good coverage but little equity cushion.",after=4)
fig('out/charts/c6_sensitivity.png','Live grid from the workbook Sensitivity tab; rent-growth columns shift every year of the staged path by the stated amount.')
fig('out/charts/c7_stress.png','Template stress cases (IC Memo tab) plus supplemental workbook re-runs (exit at entry cap, NOI haircut, engine downside path).')
table(['Price','Levered IRR @ 60% LTV','vs 11% hurdle'],[[m(wb['Sensitivity'].cell(r,2).value),p(wb['Sensitivity'].cell(r,6).value),'Clears' if wb['Sensitivity'].cell(r,6).value>=0.11 else 'Below'] for r in range(24,29)],foot='From the workbook Sensitivity tab (Table 3), base assumptions held constant.')
# conditions / bottom line
doc.add_heading('7. Conditions & next steps',level=1)
bullet(" Seller T12 (24 months preferred), rent roll and bank deposits must verify NOI of at least ~$3.0MM. If verified NOI is below ~$2.9MM, re-price toward $38MM (15.1% IRR at base assumptions).",'NOI verification:')
bullet(" Roofs, siding, decks, MEP and unit interiors at 40 years old. Immediate needs above ~$1,500/unit should come off the price, since the plan has no capex budget.",'Property condition assessment:')
bullet(" The prior owner financed with a $45.3MM Freddie Mac loan in 2022, more than this price. Confirm lender consent, a discounted payoff or a note-sale path, and closing certainty.",'Execution path:')
bullet(" Re-run the final model at a 6.75% exit. Obtain agency quotes (5-yr IO at 60% LTV) and a Colorado tax-appeal and reassessment opinion.",'Underwriting:')
doc.add_heading('Bottom line',level=2)
para("CONDITIONAL GO. At $40.0MM the deal pencils at the IC hurdles on modeled numbers and is well covered by debt, but it depends on NOI verification and exit-cap discipline. It becomes a clean Go if the seller's T12 supports ~$3.1MM of NOI and the PCA is clean. If NOI verifies meaningfully lower, or the committee insists on exiting at the entry cap, the price needs to move toward $38MM.",bold=False,after=6)
# appendix
doc.add_heading('Appendix - assumptions & provenance',level=1)
table(['Assumption','Value','Source'],[
 ['Purchase price / hold / LTV / IO','$40.0MM / 5 yrs / 60% / 5-yr full IO','User-provided'],
 ['Capital improvements','None ($0)','User-provided'],
 ['Interest rate','6.26%','RealAI agency rate data (Fannie 5-yr, 65% LTV tier)'],
 ['T12 revenue & expense lines','GPR $5.00MM; OpEx $2.13MM','RealAI property data (modeled benchmark P&L)'],
 ['Rent growth Y1-Y5','-1.2%, 0.0%, +1.5%, +2.5%, +2.8%','Forecast engine Y1-Y2 (low confidence); Y3-Y5 analyst judgment'],
 ['Expense growth','3.5% / yr','Forecast engine (structural fallback, low confidence)'],
 ['Other income growth','2.5% / yr','Engine inflation anchor (judgment)'],
 ['Economic occupancy','94.0%','Default; T12 94.1%'],
 ['Exit cap','6.50%','Engine structural mean 5.62% + vintage premium (judgment)'],
 ['Closing / disposition costs','2.5% / 2.0%','Default'],
 ['Replacement reserves','$300 / unit / yr','Default (judgment for 1986 vintage)'],
 ['Sales-comp adjustments & weights','Time: cap-rate ratio; vintage -15% to -35%','RealAI cap-rate data + analyst judgment']],foot='Full live model: SummitRiverside_underwriting_2026-09-29.xlsx (CentrePoint Pro Forma IC Template).')
doc.save('out/SummitRiverside_IC_Memo_2026-09-29.docx'); print('saved')
EOF
python memo.py && soffice --headless --convert-to pdf out/SummitRiverside_IC_Memo_2026-09-29.docx --outdir out/pdf >/dev/null 2>&1; ls out/pdf; python -c "
import pdfplumber
with pdfplumber.open('out/pdf/SummitRiverside_IC_Memo_2026-09-29.pdf') as p: print(len(p.pages))
"

cd /vercel/sandbox && python skills/xlsx/scripts/office/soffice.py --help 2>&1 | head -5; (libreoffice --headless --convert-to pdf out/SummitRiverside_IC_Memo_2026-09-29.docx --outdir out/pdf 2>&1 | tail -2); ls out/pdf && python -c "
import pdfplumber
with pdfplumber.open('out/pdf/SummitRiverside_IC_Memo_2026-09-29.pdf') as p:
  print(len(p.pages)); print(p.pages[0].extract_text()[:1500])
" && pdftoppm -r 45 -png out/pdf/SummitRiverside_IC_Memo_2026-09-29.pdf out/pdf/pg && ls out/pdf

cd /vercel/sandbox && pip install cairosvg -q 2>&1 | tail -1; python -c "
import cairosvg; cairosvg.svg2png(url='inputs/logo2.svg', write_to='out/logo2.png', output_width=1600)" && ls out

cd /vercel/sandbox && python teaser.py && python -c "
from PIL import Image
for f in ['out/SummitRiverside_email_teaser_1200x628.png','out/SummitRiverside_email_teaser_2400x1256@2x.png']: print(Image.open(f).size)"

cd /vercel/sandbox && python - <<'EOF'
s=open('teaser.py').read()
s=s.replace("ax.annotate('',xy=(1,ppu/1000+45),xytext=(0.18,ppu22/1000-10),arrowprops=dict(arrowstyle='->',color=K,lw=1.6,connectionstyle='arc3,rad=-0.25'))",
"ax.annotate('',xy=(0.78,ppu/1000+80),xytext=(0.33,ppu22/1000-5),arrowprops=dict(arrowstyle='->',color=K,lw=1.6,connectionstyle='arc3,rad=-0.2'))")
s=s.replace("ax.text(0.78,262,f'{chg*100:.0f}%',fontsize=24,fontweight='bold',color=K,ha='center')",
"ax.text(1,ppu/1000+62,f'{chg*100:.0f}%'.replace('-','\u2212'),fontsize=24,fontweight='bold',color=K,ha='center')")
open('teaser.py','w').write(s)
EOF
python teaser.py && python -c "
from PIL import Image
im=Image.open('out/SummitRiverside_email_teaser_1200x628.png').convert('RGB')
# crude overlap check: count dark pixels in bar-label region
print(im.size)"