GO — Conditional: Preserve at Ridgeville pencils to a 12% levered IRR only near $49.7M ($207K/unit), a discount to both the OM's framing and its own comps

cd /vercel/sandbox && mkdir -p assets/allen_harrison && cp scratch/inputs/library/01a0f846-303d-74da-b733-0c2193dab5e5/full.png assets/allen_harrison/full.png && cp scratch/inputs/library/01a0f846-c155-76f4-bc08-7c0bcec5b620/footer.png assets/allen_harrison/footer.png && python3 -c "
from PIL import Image
from collections import Counter
for f in ['full','footer']:
    im=Image.open(f'assets/allen_harrison/{f}.png').convert('RGBA'); print(f, im.size)
    c=Counter(p[:3] for p in im.getdata() if p[3]>200)
    print(c.most_common(6))
"; ls; ls extracts 2>/dev/null

cd /vercel/sandbox && mkdir -p scratch/qa && python3 -c "
from PIL import Image
im=Image.open('assets/allen_harrison/full.png').convert('RGBA')
bgs=[(0,57,90),(30,42,56),(74,90,106),(205,214,222)]
out=Image.new('RGB',(400*4,270))
for i,b in enumerate(bgs):
    bg=Image.new('RGBA',im.size,b+(255,)); bg.alpha_composite(im); out.paste(bg.convert('RGB'),(400*i,0))
out.save('scratch/qa/bgtest.png')
"

cd /vercel/sandbox && mkdir -p extracts scripts && python3 -c "
import openpyxl, json
wb=openpyxl.load_workbook('scratch/inputs/library/01a0f844-8530-7385-936a-0b65ad392fa0/uw.xlsx',data_only=True)
a=wb['Assumptions']; out={}
for c in ['C9','C10','C15','C18','C19','C22','C23','C32','C38','C40','C43','C50','C51','C52','C64','H5','H6','H7','H8','H9','H10','H11','H15','H16','H17','H19','H33','H34','H35','H36','H37','H38','H41','H42','H43','H44','H45','H46','H47','H48','H49','H50','H52','H60','M52']:
    out['A_'+c]=a[c].value
su=wb['Sources & Uses']; out['SU_C4']=su['C4'].value; out['SU_C7']=su['C7'].value; out['SU_G5']=su['G5'].value; out['SU_G7']=su['G7'].value; out['SU_G9']=su['G9'].value
rc=wb['Rent Comps']
out['rent_comps']=[[rc.cell(row=r,column=c).value for r in (6,9,10,12,14,26,27,28,34)] for c in range(4,9)]
for r in (38,46,47,48): out[f'RC_C{r}']=rc.cell(row=r,column=3).value
sc=wb['Sales Comps']
out['sale_comps']=[[str(sc.cell(row=r,column=c).value) if r==15 else sc.cell(row=r,column=c).value for r in (6,9,10,11,15,16,17)] for c in range(4,8)]
out['SC_C36']=sc['C36'].value; out['SC_C35']=sc['C35'].value
s=wb['Sensitivity']
out['sens_hdr']=[s.cell(row=5,column=c).value for c in range(3,9)]
out['sens']=[[s.cell(row=r,column=c).value for c in range(2,9)] for r in range(6,11)]
out['em']=[[s.cell(row=r,column=c).value for c in range(2,9)] for r in range(15,20)]
pf=wb['Pro Forma']
out['pf']=[[pf.cell(row=r,column=c).value for c in range(2,10)] for r in range(1,44)]
json.dump(out,open('extracts/uw_readback.json','w'),indent=1,default=str)
print(json.dumps({k:v for k,v in out.items() if k not in ('pf',)},default=str)[:4000])
"

cd /vercel/sandbox && python3 -c "
import json; d=json.load(open('extracts/uw_readback.json'))
for i,r in enumerate(d['pf'],1):
  if any(x not in (None,'') for x in r): print(i, r)
"

{
 "source": "RealAI datamart mf_rent_ts MONTH, subject property 5a69c440a0085cd6b55f5a8d5c3a703f and Charleston MSA 16700, pulled 2026-09 (tool calls toolu_bdrk_01QenCcNZntp9HV1wUKK2DTt / toolu_bdrk_013PzMWBVQTn7UW1mxGWwkLT)",
 "months": ["2024-09","2024-10","2024-11","2024-12","2025-01","2025-02","2025-03","2025-04","2025-05","2025-06","2025-07","2025-08","2025-09","2025-10","2025-11","2025-12","2026-01","2026-02","2026-03","2026-04","2026-05","2026-06","2026-07","2026-08"],
 "subject_occ": [0.9875,0.920833,0.9,0.883333,0.879167,0.879167,0.845833,0.841667,0.8375,0.854167,0.895833,0.9125,0.933333,0.954167,0.941667,0.979167,0.983333,0.991667,0.995833,0.979167,0.929167,0.904167,0.904167,0.9],
 "subject_inplace": [1525.49,1647.18,1645.78,1642.55,1636.50,1633.60,1627.95,1632.39,1603.42,1583.17,1568.58,1560.02,1557.43,1551.43,1549.67,1550.76,1549.45,1550.10,1548.49,1547.28,1555.16,1556.84,1556.75,1547.75],
 "msa_inplace": [1804.60,1809.80,1812.17,1812.18,1810.16,1821.07,1824.46,1828.49,1837.43,1839.63,1837.27,1834.98,1834.95,1833.29,1830.32,1830.84,1839.90,1839.89,1848.60,1850.19,1854.09,1861.01,1863.59,1861.57]
}

"""Allen Harrison-branded IC memo for Preserve at Ridgeville.
Inputs: extracts/uw_readback.json (recalculated workbook read-back, file 01a0f844-8530-7385-936a-0b65ad392fa0),
        extracts/series.json (datamart monthly series), assets/allen_harrison/*.png (client logos).
Output: scratch/ic_memo.docx (+ charts in scratch/charts)."""
import json, os
import matplotlib
matplotlib.use("Agg")
import matplotlib.pyplot as plt
from docx import Document
from docx.shared import Inches, Pt, RGBColor, Emu
from docx.enum.text import WD_ALIGN_PARAGRAPH, WD_BREAK
from docx.enum.table import WD_TABLE_ALIGNMENT, WD_CELL_VERTICAL_ALIGNMENT
from docx.enum.section import WD_SECTION
from docx.oxml.ns import qn
from docx.oxml import OxmlElement

D = json.load(open("extracts/uw_readback.json"))
S = json.load(open("extracts/series.json"))
os.makedirs("scratch/charts", exist_ok=True)

NAVY = RGBColor(0x00, 0x39, 0x5A); NAVY_HEX = "00395A"
STEEL = RGBColor(0x67, 0x9A, 0xC9); STEEL_HEX = "679AC9"
TINT_HEX = "EEF3F8"; GREY = RGBColor(0x55, 0x5F, 0x6B)
HEAD_FONT, BODY_FONT = "Georgia", "Arial"

def money(x, d=0): return f"${x:,.{d}f}"
def pct(x, d=1): return f"{x*100:.{d}f}%"

# ---------------- derived figures (computed from workbook read-back) ----------------
price = D["A_C15"]; units = D["A_C9"]; ppu = price / units
tax_om_y1 = 785683
tax_model_y1 = D["pf"][13][3]          # Pro Forma Year 1 RE taxes
noi_y1 = D["A_M52"]; ds = D["pf"][31][3]
tax_gap = tax_om_y1 - tax_model_y1
noi_y1_taxadj = noi_y1 - tax_gap
dscr_taxadj = noi_y1_taxadj / ds
cap_y1_taxadj = noi_y1_taxadj / price
oi_share = D["A_H37"] / D["A_H35"]
comp_ppu = D["SC_C36"]; disc_to_comps = ppu / comp_ppu - 1

# ---------------- charts ----------------
plt.rcParams.update({"font.family": "DejaVu Sans", "font.size": 9})
labels = [m[2:].replace("-", "/") for m in S["months"]]
fig, ax = plt.subplots(figsize=(6.25, 2.6), dpi=200)
ax.plot(range(24), [o*100 for o in S["subject_occ"]], color="#00395A", lw=2, label="Preserve at Ridgeville occupancy")
ax.axhline(94, color="#679AC9", ls="--", lw=1.2, label="OM-stated occupancy (94%, Apr-26)")
ax.axhline(92, color="#9AA5B1", ls=":", lw=1.2, label="Underwritten stabilized (92%)")
ax.set_xticks(range(0, 24, 3)); ax.set_xticklabels(labels[::3])
ax.set_ylim(80, 101); ax.set_ylabel("Physical occupancy (%)")
for s in ("top", "right"): ax.spines[s].set_visible(False)
ax.legend(frameon=False, fontsize=7.5, loc="lower right")
ax.grid(axis="y", color="#E3E8EE", lw=0.6)
fig.tight_layout(); fig.savefig("scratch/charts/occ.png"); plt.close(fig)

fig, ax = plt.subplots(figsize=(6.25, 2.6), dpi=200)
ax.plot(range(24), S["subject_inplace"], color="#00395A", lw=2, label="Preserve at Ridgeville")
ax.plot(range(24), S["msa_inplace"], color="#679AC9", lw=2, label="Charleston MSA average")
ax.set_xticks(range(0, 24, 3)); ax.set_xticklabels(labels[::3])
ax.set_ylabel("Avg in-place rent ($/mo)")
for s in ("top", "right"): ax.spines[s].set_visible(False)
ax.legend(frameon=False, fontsize=7.5, loc="center right")
ax.grid(axis="y", color="#E3E8EE", lw=0.6)
fig.tight_layout(); fig.savefig("scratch/charts/rent.png"); plt.close(fig)

# ---------------- doc helpers ----------------
doc = Document()
for name in ("Eyebrow", "Stat", "Body Small", "Disclaimer"):
    from docx.enum.style import WD_STYLE_TYPE
    doc.styles.add_style(name, WD_STYLE_TYPE.PARAGRAPH)

sec = doc.sections[0]
sec.page_width, sec.page_height = Inches(8.5), Inches(11)
sec.top_margin = Inches(1.15); sec.bottom_margin = Inches(0.9)
sec.left_margin = sec.right_margin = Inches(1.0)
sec.header_distance = Inches(0.35); sec.footer_distance = Inches(0.4)
TEXT_W = 6.5

st = doc.styles
st["Normal"].font.name = BODY_FONT; st["Normal"].font.size = Pt(10)
st["Normal"].element.rPr.rFonts.set(qn("w:eastAsia"), BODY_FONT)
st["Normal"].paragraph_format.space_after = Pt(6); st["Normal"].paragraph_format.line_spacing = 1.12
for nm, sz, col, before, after in (("Title", 26, NAVY, 0, 4), ("Heading 1", 15, NAVY, 16, 6),
                                   ("Heading 2", 12, NAVY, 12, 4), ("Heading 3", 10.5, NAVY, 8, 2)):
    s = st[nm]; s.font.name = HEAD_FONT; s.font.size = Pt(sz); s.font.color.rgb = col; s.font.bold = nm != "Title"
    s.element.rPr.rFonts.set(qn("w:asciiTheme"), "") if False else None
    rf = s.element.get_or_add_rPr().get_or_add_rFonts()
    for k in ("w:ascii", "w:hAnsi", "w:cs", "w:eastAsia"): rf.set(qn(k), HEAD_FONT)
    for k in ("w:asciiTheme", "w:hAnsiTheme", "w:eastAsiaTheme", "w:cstheme"):
        if rf.get(qn(k)) is not None: del rf.attrib[qn(k)]
    s.paragraph_format.space_before = Pt(before); s.paragraph_format.space_after = Pt(after)
    s.paragraph_format.keep_with_next = True
st["Heading 1"].font.italic = False
e = st["Eyebrow"]; e.font.name = BODY_FONT; e.font.size = Pt(8.5); e.font.bold = True; e.font.color.rgb = STEEL; e.font.all_caps = True
e.paragraph_format.space_after = Pt(2); e.paragraph_format.keep_with_next = True
s = st["Stat"]; s.font.name = HEAD_FONT; s.font.size = Pt(18); s.font.color.rgb = NAVY; s.paragraph_format.space_after = Pt(0)
s = st["Body Small"]; s.font.name = BODY_FONT; s.font.size = Pt(8.5); s.font.color.rgb = GREY; s.paragraph_format.space_after = Pt(4)
s = st["Disclaimer"]; s.font.name = BODY_FONT; s.font.size = Pt(7.5); s.font.color.rgb = GREY
s = st["Caption"]; s.font.name = BODY_FONT; s.font.size = Pt(8); s.font.italic = False; s.font.bold = True; s.font.color.rgb = NAVY
s.paragraph_format.space_before = Pt(8); s.paragraph_format.space_after = Pt(3); s.paragraph_format.keep_with_next = True
for ls in ("List Bullet",):
    st[ls].font.name = BODY_FONT; st[ls].font.size = Pt(10); st[ls].paragraph_format.space_after = Pt(3)

def shade(cell, hexcol):
    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"), hexcol)
    tcPr.append(sh)

def cell_margins(cell, top=60, bottom=60, left=90, right=90):
    tcPr = cell._tc.get_or_add_tcPr(); m = OxmlElement("w:tcMar")
    for k, v in (("top", top), ("bottom", bottom), ("start", left), ("end", right), ("left", left), ("right", right)):
        el = OxmlElement(f"w:{k}"); el.set(qn("w:w"), str(v)); el.set(qn("w:type"), "dxa"); m.append(el)
    tcPr.append(m)

def table_borders(tbl, inside_h=True, color="C9D3DD", outer_top=STEEL_HEX):
    tblPr = tbl._tbl.tblPr; b = OxmlElement("w:tblBorders")
    spec = {"top": ("single", 12, outer_top), "bottom": ("single", 8, NAVY_HEX), "left": ("nil", 0, "auto"),
            "right": ("nil", 0, "auto"), "insideH": ("single", 4, color) if inside_h else ("nil", 0, "auto"),
            "insideV": ("nil", 0, "auto")}
    for k, (v, sz, c) in spec.items():
        el = OxmlElement(f"w:{k}"); el.set(qn("w:val"), v); el.set(qn("w:sz"), str(sz)); el.set(qn("w:color"), c); b.append(el)
    tblPr.append(b)

def no_borders(tbl):
    tblPr = tbl._tbl.tblPr; b = OxmlElement("w:tblBorders")
    for k in ("top", "left", "bottom", "right", "insideH", "insideV"):
        el = OxmlElement(f"w:{k}"); el.set(qn("w:val"), "nil"); b.append(el)
    tblPr.append(b)

def set_widths(tbl, widths):
    grid = tbl._tbl.tblGrid
    for i, gc in enumerate(grid.findall(qn("w:gridCol"))):
        gc.set(qn("w:w"), str(int(widths[i] * 1440)))
    for row in tbl.rows:
        for i, c in enumerate(row.cells): c.width = Inches(widths[i])
    tblPr = tbl._tbl.tblPr; lay = OxmlElement("w:tblLayout"); lay.set(qn("w:type"), "fixed"); tblPr.append(lay)

def write(cell, text, bold=False, color=None, size=9, align=None, font=BODY_FONT, italic=False):
    p = cell.paragraphs[0]; p.paragraph_format.space_after = Pt(0); p.paragraph_format.space_before = Pt(0)
    p.paragraph_format.line_spacing = 1.0
    if align: p.alignment = align
    r = p.add_run(text); r.bold = bold; r.italic = italic; r.font.size = Pt(size); r.font.name = font
    if color is not None: r.font.color.rgb = color
    return p

def data_table(header, rows, widths, num_cols=None, bold_rows=(), caption=None, note=None, size=8.5):
    if caption: doc.add_paragraph(caption, style="Caption")
    t = doc.add_table(rows=1 + len(rows), cols=len(header)); t.alignment = WD_TABLE_ALIGNMENT.CENTER
    table_borders(t); set_widths(t, widths)
    num_cols = set(num_cols or [])
    for j, h in enumerate(header):
        c = t.rows[0].cells[j]; shade(c, NAVY_HEX); cell_margins(c)
        write(c, h, bold=True, color=RGBColor(255, 255, 255), size=size,
              align=WD_ALIGN_PARAGRAPH.RIGHT if j in num_cols else None)
        c.vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER
    tr = t.rows[0]._tr; trPr = tr.get_or_add_trPr(); th = OxmlElement("w:tblHeader"); th.set(qn("w:val"), "true"); trPr.append(th)
    for i, row in enumerate(rows, 1):
        for j, v in enumerate(row):
            c = t.rows[i].cells[j]; cell_margins(c)
            if i % 2 == 0: shade(c, TINT_HEX)
            write(c, str(v), bold=(i - 1) in bold_rows, size=size,
                  align=WD_ALIGN_PARAGRAPH.RIGHT if j in num_cols else None)
            c.vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER
        # keep rows together
        trPr = t.rows[i]._tr.get_or_add_trPr(); cs = OxmlElement("w:cantSplit"); trPr.append(cs)
    if note:
        p = doc.add_paragraph(note, style="Body Small"); p.paragraph_format.space_before = Pt(3)
    else:
        doc.add_paragraph().paragraph_format.space_after = Pt(2)
    return t

def bullet(text, lead=None):
    p = doc.add_paragraph(style="List Bullet")
    if lead:
        r = p.add_run(lead + " "); r.bold = True; r.font.color.rgb = NAVY
    p.add_run(text); return p

def para(text, style=None):
    return doc.add_paragraph(text, style=style)

def add_field(run, instr):
    f1 = OxmlElement("w:fldChar"); f1.set(qn("w:fldCharType"), "begin")
    it = OxmlElement("w:instrText"); it.set(qn("xml:space"), "preserve"); it.text = instr
    f2 = OxmlElement("w:fldChar"); f2.set(qn("w:fldCharType"), "separate")
    t = OxmlElement("w:t"); t.text = "1"
    f3 = OxmlElement("w:fldChar"); f3.set(qn("w:fldCharType"), "end")
    for el in (f1, it, f2, t, f3): run._r.append(el)

# ---------------- header / footer ----------------
sec.different_first_page_header_footer = True
hdr = sec.header
ht = hdr.add_table(rows=1, cols=2, width=Inches(TEXT_W)); no_borders(ht); set_widths(ht, [3.0, 3.5])
for c in ht.rows[0].cells: shade(c, NAVY_HEX); cell_margins(c, 90, 90, 140, 140); c.vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER
ht.rows[0].cells[0].paragraphs[0].add_run().add_picture("assets/allen_harrison/footer.png", width=Inches(1.55))
write(ht.rows[0].cells[1], "Investment Committee Memorandum  |  Preserve at Ridgeville", color=RGBColor(255, 255, 255),
      size=8, align=WD_ALIGN_PARAGRAPH.RIGHT)
hdr.paragraphs[0].paragraph_format.space_after = Pt(0)

def footer_into(ftr):
    p = ftr.paragraphs[0]; p.alignment = WD_ALIGN_PARAGRAPH.LEFT
    pPr = p._p.get_or_add_pPr(); bdr = OxmlElement("w:pBdr"); top = OxmlElement("w:top")
    top.set(qn("w:val"), "single"); top.set(qn("w:sz"), "6"); top.set(qn("w:color"), STEEL_HEX); top.set(qn("w:space"), "4")
    bdr.append(top); pPr.append(bdr)
    tabs = OxmlElement("w:tabs"); tb = OxmlElement("w:tab"); tb.set(qn("w:val"), "right"); tb.set(qn("w:pos"), str(int(TEXT_W * 1440)))
    tabs.append(tb); pPr.append(tabs)
    r = p.add_run("Allen Harrison Company  |  Build to serve"); r.font.size = Pt(7.5); r.font.color.rgb = NAVY; r.font.name = BODY_FONT
    r = p.add_run("\tPage "); r.font.size = Pt(7.5); r.font.color.rgb = GREY; r.font.name = BODY_FONT
    r = p.add_run(); r.font.size = Pt(7.5); r.font.color.rgb = GREY; add_field(r, "PAGE")
footer_into(sec.footer)

# ---------------- cover ----------------
cov = doc.add_table(rows=1, cols=1); no_borders(cov); set_widths(cov, [TEXT_W])
c = cov.rows[0].cells[0]; shade(c, NAVY_HEX); cell_margins(c, 500, 420, 200, 200)
p = c.paragraphs[0]; p.alignment = WD_ALIGN_PARAGRAPH.CENTER
p.add_run().add_picture("assets/allen_harrison/full.png", width=Inches(2.6))
tr = cov.rows[0]._tr.get_or_add_trPr(); h = OxmlElement("w:trHeight"); h.set(qn("w:val"), str(int(3.9 * 1440))); h.set(qn("w:hRule"), "exact"); tr.append(h)
c.vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER

sp = doc.add_paragraph(); sp.paragraph_format.space_after = Pt(18)
doc.add_paragraph("Investment Committee Memorandum", style="Eyebrow")
doc.add_paragraph("Preserve at Ridgeville", style="Title")
p = para("240-unit Class-A garden community  |  1050 Old Gilliard Road, Ridgeville, SC 29472  |  Charleston MSA")
p.runs[0].font.color.rgb = GREY
p = para("Stabilized acquisition screen  |  October 1, 2026"); p.runs[0].font.color.rgb = GREY
doc.add_paragraph().paragraph_format.space_after = Pt(10)

# cover key-terms band
kt = doc.add_table(rows=2, cols=4); no_borders(kt); set_widths(kt, [1.625] * 4)
terms = [("Recommendation", "Conditional"), ("Max price for 12% IRR", f"${price/1e6:.2f}M"),
         ("Per unit", money(ppu)), ("Levered IRR / EM", f"{pct(D['A_H9'])} / {D['A_H11']:.2f}x")]
for j, (lab, val) in enumerate(terms):
    a, b = kt.rows[0].cells[j], kt.rows[1].cells[j]
    for cc in (a, b): shade(cc, TINT_HEX); cell_margins(cc, 70, 70, 110, 110)
    write(a, lab.upper(), bold=True, color=STEEL, size=7.5)
    write(b, val, color=NAVY, size=15, font=HEAD_FONT)
tbl = kt._tbl.tblPr; b = OxmlElement("w:tblBorders")
for k in ("top",):
    el = OxmlElement(f"w:{k}"); el.set(qn("w:val"), "single"); el.set(qn("w:sz"), "18"); el.set(qn("w:color"), NAVY_HEX); b.append(el)
el = OxmlElement("w:insideV"); el.set(qn("w:val"), "single"); el.set(qn("w:sz"), "6"); el.set(qn("w:color"), "FFFFFF"); b.append(el)
tbl.append(b)
doc.add_paragraph().paragraph_format.space_after = Pt(14)
p = para("Prepared for the Allen Harrison Investment Committee from the JLL offering memorandum, the RealAI datamart "
         "(rents, occupancy, sales, taxes, financing), and a populated underwriting workbook. Figures in this memo tie to "
         "the companion workbook, Preserve at Ridgeville - Acquisition Underwriting.", style="Body Small")
p.add_run().add_break(WD_BREAK.PAGE)

# ---------------- 1. Recommendation ----------------
doc.add_paragraph("Section 1", style="Eyebrow")
doc.add_heading("Recommendation: conditional approval to pursue", level=1)
para(f"We recommend pursuing Preserve at Ridgeville only at or below {money(price)} ({money(ppu)} per unit). "
     f"At that price the asset produces a {pct(D['A_H9'])} levered IRR and {D['A_H11']:.2f}x equity multiple over a "
     f"five-year hold on 65% LTV, 10-year fixed agency debt at {pct(D['A_C40'],2)}. Debt coverage is comfortable at "
     f"{D['A_C50']:.2f}x DSCR and a {pct(D['A_H34']*0+D['A_C51'])} debt yield. The price is a "
     f"{pct(-disc_to_comps)} discount to the {money(comp_ppu)} per unit average of four recent Class-A garden sales in the submarket.")
para("The discount is warranted. The broker presents the asset as a conventional Class-A community and benchmarks it "
     "against five unrestricted, market-rate comps. It is in fact subject to a USDA Section 538 deed restriction, "
     "running through June 2061, that caps rents at 30% of 115% of area median income. Per the OM, that restriction "
     "survives whether or not the existing loan is assumed. The broker's rent growth, occupancy, and Year-1 tax "
     "assumptions also run ahead of what the data supports.")
doc.add_heading("Conditions to proceed", level=3)
bullet(f"Price at or below {money(price)}. Above that, the 12% levered IRR hurdle fails on base-case assumptions.", "Price discipline.")
bullet("Engage counsel to confirm how the USDA 538 restriction treats rent limits, tenant income certification, transfer approval, and an exit sale before 2061.", "Regulatory diligence.")
bullet("Obtain a Berkeley County tax opinion on point-of-sale reassessment and eligibility for the SC assessable transfer of interest (ATI) exemption at our price.", "Tax diligence.")
bullet("Require the seller's T12 and current rent roll. Most expense lines and all other income currently rest on the broker's Year-1 pro forma.", "Operating diligence.")
bullet("Price the assumable 4.40% USDA loan against new agency debt before final bid. It is not reflected in the base case, which uses new 65% LTV financing as directed.", "Financing alternative.")

# ---------------- 2. Transaction overview ----------------
doc.add_paragraph("Section 2", style="Eyebrow")
doc.add_heading("Transaction overview", level=1)
rows = [("Property", "Preserve at Ridgeville, 240 units, garden, 3 stories, completed May 2023"),
        ("Location", "1050 Old Gilliard Rd, Ridgeville, SC (Berkeley County), Charleston MSA"),
        ("Size and mix", "240,624 SF net rentable; 1,003 SF average; 108 1BR / 114 2BR / 18 3BR"),
        ("Regulatory status", "USDA 538 deed restriction through 6/1/2061: rents capped at 30% of 115% AMI"),
        ("Existing debt", "USDA 538, $30.85M original, 4.40% fixed, 30-yr amortization, matures 6/1/2061, assumable"),
        ("Seller pricing", "Not disclosed in the OM. The OM claims a basis \"well below\" its $250K/unit replacement cost estimate"),
        ("Strategy", "Stabilized acquisition. No value-add or renovation program"),
        ("Underwritten price", f"{money(price)} ({money(ppu)}/unit), plus {pct(D['A_C18'])} closing costs"),
        ("Financing", f"65% LTV, {money(D['SU_C4'])} senior loan, {pct(D['A_C40'],2)} fixed, interest-only, 10-year term"),
        ("Equity required", f"{money(D['A_C64'])} total, against {money(D['A_C19'])} total basis")]
data_table(["Item", "Detail"], rows, [1.55, 4.95], caption="Deal terms and property facts")

# ---------------- 3. Thesis ----------------
doc.add_paragraph("Section 3", style="Eyebrow")
doc.add_heading("Investment thesis", level=1)
bullet("Volvo's Ridgeville plant (about 4,000 jobs anticipated), Redwood Materials' $3.5B battery campus, and Camp Hall are all within an 8-minute drive. Google's $9B Berkeley and Dorchester data center expansion sits nearby. More than 60% of residents work for major corporations.", "Employment at the doorstep.")
bullet("The OM reports zero multifamily units planned within six miles and identifies Preserve as the only Class-A conventional community in Ridgeville. Wetlands, utility costs, and industrial zoning limit new entrants.", "Supply-shielded location.")
bullet(f"In-place rents of $1,362 / $1,654 / $1,858 (1BR/2BR/3BR) sit $794 to $877 below the 2026 AMI limits, and {pct(-D['RC_C48'])} below the market-rate comp average. Rents have real room to move before the regulatory ceiling binds.", "Headroom under the AMI cap.")
bullet(f"At {money(ppu)} per unit, the basis is below every recent submarket Class-A trade except the 2024 Isley sale, and well under the OM's $250K/unit replacement cost estimate.", "Basis.")

# ---------------- 4. OM line items flagged ----------------
doc.add_paragraph("Section 4", style="Eyebrow")
doc.add_heading("Broker assumptions tested against the data", level=1)
para("We accepted broker figures only where comp or datamart evidence corroborates them. The items below are out of line "
     "with comp-supported data, or rest on broker materials alone. No renovation budget is in the OM or the underwriting, "
     "so there is no renovation line to test.")
rows = [("Year-1 rent growth", "4.25%", "Forecast engine base case: flat (cyclical-drawdown flag, low confidence). Subject in-place rent -4.7% trailing 12 months", "2.0% (judgment, see Section 8)"),
        ("Occupancy", "94% (Apr-26)", "89.6% physical (Sep-26); 90% for Jun to Aug. Comp average 96.2%", "92% stabilized"),
        ("Rent positioning", "\"$150 to $200/mo mark-to-market\"", f"Directionally confirmed: subject {pct(-D['RC_C48'])} below comps, but comps are unrestricted and subject is AMI-capped", "Upside bounded by the AMI ceiling"),
        ("Real estate taxes", "$785,683 Year 1 (reassessed)", f"T12 assessor bill $639,392. Template escalates T12 at 3%, giving {money(tax_model_y1)} in Year 1", f"Model understates Year 1 by {money(tax_gap)}"),
        ("Other income", "$777,957", f"No verified T12. Equals {pct(oi_share)} of net rent, above the typical 2% to 12% band", "Adopted, flagged broker-only"),
        ("Operating expenses", "$2.20M (44.8% of EGI)", f"Expense ratio {pct(D['A_H60'])} on our EGI, inside the 35% to 55% band. Line items are broker-sourced except taxes", "Adopted, pending T12"),
        ("Management fee", "2.5% of EGI", "Low for a 240-unit asset; third-party fees typically run higher", "Adopted; sensitivity risk"),
        ("Comp set", "5 market-rate Class-A comps", "All five carry no household restriction. Subject is classified affordable / income-restricted", "Comps used for ceiling only")]
data_table(["Line item", "OM", "Evidence", "Treatment"], rows, [1.15, 1.25, 2.6, 1.5], size=8,
           caption="OM line items versus comp-supported data")

# ---------------- 5. Comps ----------------
doc.add_paragraph("Section 5", style="Eyebrow")
doc.add_heading("What the comps say", level=1)
para("The broker's comp set is the right physical peer group: 2020 to 2024 garden product, 168 to 332 units, 10 to 19 miles "
     "south and east along the I-26 corridor. Datamart rents for four of the five confirm the OM's figures within a few "
     "percent. The Overlook at Cane Bay has no current rent coverage in the datamart and is shown for sales only.")
rc = D["rent_comps"]
rows = [("Preserve at Ridgeville (subject)", "-", "240", "2023", money(D["RC_C47"]), "$1,362", "$1,654", "$1,858", "89.6%")]
for r in rc:
    if r[4] is None: continue
    rows.append((r[0], f"{r[1]:.1f}", str(r[2]), str(r[3]), money(r[4]), money(r[5]), money(r[6]), money(r[7]), pct(r[8])))
rows.append(("Comp average", "", "", "", money(D["RC_C38"]), "", "", "", pct(D["RC_C46"])))
data_table(["Property", "Miles", "Units", "Built", "In-place", "1BR", "2BR", "3BR", "Occ."], rows,
           [1.85, 0.5, 0.5, 0.5, 0.75, 0.6, 0.6, 0.6, 0.6], num_cols=range(1, 9), bold_rows=(len(rows) - 1,), size=8,
           caption="Rent comps (in-place rent per unit per month)",
           note="Subject in-place rent is T12 GPR per unit per month; subject bedroom rents are the OM's 4/27/26 rent roll. Comp rents and occupancy are datamart as of 9/5/2026. All comps are market-rate with no household restriction.")
sc = D["sale_comps"]
rows = []
for r in sc:
    rows.append((r[0], r[4][:7], f"{r[1]:.1f}", str(r[2]), str(r[3]), f"${r[5]/1e6:.1f}M", money(r[6])))
rows.append(("Comp average (unadjusted)", "", "", "", "", "", money(comp_ppu)))
rows.append(("Subject at 12% IRR price", "", "", "240", "2023", f"${price/1e6:.1f}M", money(ppu)))
data_table(["Property", "Sold", "Miles", "Units", "Built", "Price", "$/unit"], rows,
           [2.1, 0.75, 0.55, 0.6, 0.6, 0.85, 1.05], num_cols=range(2, 7), bold_rows=(len(rows) - 2, len(rows) - 1), size=8,
           caption="Sale comps, Class-A garden trades since mid-2024")
para(f"Our price sits {pct(-disc_to_comps)} below the unrestricted comp average. That spread is modest for an asset whose "
     "rents are permanently capped and whose exit buyer must accept a 35-year deed restriction. The Committee should "
     "treat the comp average as a ceiling, not a target.")

# ---------------- 6. Operations ----------------
doc.add_paragraph("Section 6", style="Eyebrow")
doc.add_heading("How it is operating", level=1)
para("Occupancy swings with the leasing season and has not yet settled. It fell to 84% in spring 2025, peaked near "
     "99.6% in March 2026, and has held near 90% since June. The OM's 94% figure predates this summer's softening. "
     f"T12 vacancy loss implies {pct(D['A_H36'])} economic occupancy, below a 90% stabilized threshold, and the "
     f"underwriting's 92% assumption depends on recovering part of that gap in Year 1.")
doc.add_paragraph("Physical occupancy, Sep-2024 to Aug-2026", style="Caption")
doc.add_picture("scratch/charts/occ.png", width=Inches(TEXT_W))
para("Rents are flat while the market rises. Average in-place rent has drifted from about $1,647 in late 2024 to $1,548 "
     "as concessions burned off and leases rolled, while the Charleston MSA average rose about 3% over the same period. "
     "Recent new-lease trade-outs at the property remain slightly negative.")
doc.add_paragraph("Average in-place rent: subject versus Charleston MSA", style="Caption")
doc.add_picture("scratch/charts/rent.png", width=Inches(TEXT_W))
p = para("Source: RealAI datamart, monthly rent and occupancy series.", style="Body Small")

# ---------------- 7. Market ----------------
doc.add_paragraph("Section 7", style="Eyebrow")
doc.add_heading("Market context", level=1)
para("Charleston absorbed a record supply wave in 2024 and 2025, and metro apartment rents were roughly flat to slightly "
     "negative into early 2026. The Summerville and Nexton pipeline the OM tracks (about 3,300 units in lease-up in May "
     "2026) is projected to be largely absorbed by year-end, and planned deliveries fall 35% in 2026 and 50% in 2027. "
     "Class-A cap rates in Charleston run roughly 4.75% to 5.20% and have been compressing. Our 5.55% T12 entry cap sits "
     "35 to 80 bps above that range, a favorable entry relative to unrestricted product. We underwrite a 5.25% exit to "
     "reflect the thinner buyer pool for a restricted asset.")

# ---------------- 8. Underwriting ----------------
doc.add_paragraph("Section 8", style="Eyebrow")
doc.add_heading("How it underwrites", level=1)
pf = D["pf"]
def r_(i, j): return pf[i - 1][j]
rows = [("Gross potential rent", money(r_(6, 1)), money(r_(6, 2)), money(r_(6, 6))),
        ("Vacancy and credit loss", f"({money(-r_(8,1))})", f"({money(-r_(8,2))})", f"({money(-r_(8,6))})"),
        ("Other income", money(r_(10, 1)), money(r_(10, 2)), money(r_(10, 6))),
        ("Effective gross income", money(r_(11, 1)), money(r_(11, 2)), money(r_(11, 6))),
        ("Operating expenses", f"({money(r_(23,1))})", f"({money(r_(23,2))})", f"({money(r_(23,6))})"),
        ("Net operating income", money(r_(26, 1)), money(r_(26, 2)), money(r_(26, 6))),
        ("Debt service", "", f"({money(r_(34,2))})", f"({money(r_(34,6))})"),
        ("Levered cash flow", "", money(r_(38, 2)), money(r_(38, 6))),
        ("DSCR", "", f"{r_(42,2):.2f}x", f"{r_(42,6):.2f}x")]
data_table(["Line", "T12", "Year 1", "Year 5"], rows, [2.5, 1.33, 1.33, 1.34], num_cols=(1, 2, 3), bold_rows=(3, 5, 7),
           caption="Operating pro forma summary")
rows = [("Purchase price / per unit", f"{money(price)} / {money(ppu)}"),
        ("T12 cap rate / Year-1 cap rate", f"{pct(D['A_H5'],2)} / {pct(D['A_H6'],2)}"),
        ("Unlevered IRR / levered IRR", f"{pct(D['A_H8'])} / {pct(D['A_H9'])}"),
        ("Equity multiple / average cash-on-cash", f"{D['A_H11']:.2f}x / {pct(D['A_H10'])}"),
        ("Going-in DSCR / debt yield (T12 NOI)", f"{D['A_C50']:.2f}x / {pct(D['A_C51'])}"),
        ("Breakeven occupancy", pct(D['A_C52'])),
        ("Key assumptions", f"Rent growth {pct(D['A_H16'])}; expense growth {pct(D['A_H17'])}; occupancy {pct(D['A_H19'],0)}; exit cap {pct(D['A_C22'],2)}; 5-year hold; reserves $200/unit")]
data_table(["Metric", "Value"], rows, [2.6, 3.9], caption="Returns and credit metrics at the 12% IRR price")
para(f"The Year-1 NOI step-up of {pct(r_(27,2))} comes almost entirely from occupancy rising from the T12's implied "
     f"{pct(D['A_H36'])} to 92%. It is the single largest reliance in the model. Taxes cut the other way. Substituting the "
     f"OM's own reassessed Year-1 tax of $785,683 lowers Year-1 NOI to about {money(noi_y1_taxadj)}, a "
     f"{pct(cap_y1_taxadj,2)} Year-1 cap rate and {dscr_taxadj:.2f}x DSCR. Credit still clears, but the levered IRR at "
     f"our price would fall below 12% if that tax level persists. Confirming the ATI treatment is therefore a "
     f"price-setting item, not a closing formality.")

# ---------------- 9. Sensitivity ----------------
doc.add_paragraph("Section 9", style="Eyebrow")
doc.add_heading("Sensitivity: rent growth and supply shock", level=1)
hdr_ = ["Exit cap / rent growth"] + [pct(x) for x in D["sens_hdr"]]
rows = [[pct(r[0], 2)] + [pct(v) for v in r[1:]] for r in D["sens"]]
data_table(hdr_, rows, [1.6] + [0.8167] * 6, num_cols=range(1, 7), bold_rows=(2,),
           caption=f"Levered IRR at {money(price)}: exit cap rate versus annual rent growth",
           note="Base case is 5.25% exit and 2.0% growth. A supply-shock case (Summerville lease-up stalls, rent growth 0.5% to 1.0%) maps to the first two columns.")
para("Rent growth drives the verdict. Each 50 bp of annual growth moves the levered IRR about 2 points. At 1.0% growth "
     "the deal returns 8.2%. In a supply-shock case near 0.5% growth it returns 6.1%. The OM's 4.25% sits beyond the top "
     "of this grid, which explains why the broker's numbers look far stronger than ours. Exit cap is the second lever: "
     "50 bps of expansion to 5.75% cuts the base-case IRR to 7.9%. The hurdle clears in the base case, with little "
     "margin on either lever.")

# ---------------- 10. Risks ----------------
doc.add_paragraph("Section 10", style="Eyebrow")
doc.add_heading("Key risks and mitigants", level=1)
rows = [("Regulatory restriction", "AMI rent cap and deed restriction through 2061 limit upside and the exit buyer pool", "Counsel review; 5.25% exit cap; comps treated as a ceiling"),
        ("Rent growth", "Flat trailing rents; engine base case flat; IRR falls about 2 pts per 50 bps", "Underwrite 2.0%, not 4.25%; price to the base case"),
        ("Occupancy", "89.6% today versus 92% underwritten; breakeven 87.4%", "Diligence lease expirations and renewals; monitor fall leasing"),
        ("Tax reassessment", f"Year-1 taxes up to {money(tax_gap)} above model", "County tax opinion and ATI confirmation before final bid"),
        ("Data quality", "Opex and other income rest on broker pro forma", "Seller T12 and rent roll as a bid condition"),
        ("Supply", "Summerville lease-up concessions pull demand south", "Pipeline thinning in 2026-27; zero units planned within 6 miles")]
data_table(["Risk", "Exposure", "Mitigant"], rows, [1.3, 2.75, 2.45], size=8)

# ---------------- 11. Bottom line ----------------
doc.add_paragraph("Section 11", style="Eyebrow")
doc.add_heading("Bottom line", level=1)
para(f"Preserve at Ridgeville is a well-built, well-located asset at the center of the corridor's job growth, but it is "
     f"not the unrestricted Class-A deal the OM presents. At {money(price)} ({money(ppu)} per unit), with rent growth "
     f"reset to 2%, it meets the 12% levered IRR hurdle and is financeable at 65% LTV. We recommend the Committee "
     f"authorize a bid at or below that level, conditioned on regulatory, tax, and T12 diligence. We would walk if the "
     f"seller's expectations rest on the broker's 4.25% rent growth or unrestricted comp pricing.")

doc.add_heading("Sources and methodology", level=3)
para("Property facts, regulatory terms, broker pro forma, and supply pipeline: JLL offering memorandum. Rents, occupancy, "
     "comp attributes, sales, and tax bill: RealAI datamart (as of September 2026). Financing: current agency rate "
     "snapshot (Freddie Mac CME fixed, 65% LTV, 10-year). Cap rate range: published Charleston market survey. Rent "
     "growth: RealAI forecasting engine (low confidence, cyclical-drawdown flag), with the 2.0% base case set by "
     "underwriting judgment. Price solved by goal-seek on the workbook's levered IRR output.", style="Body Small")
para("This memorandum is a screening analysis to support an investment decision. It is not an appraisal, and its "
     "projections are estimates based on the sources above.", style="Disclaimer")

out = "scratch/ic_memo.docx"
if os.path.exists(out): os.remove(out)
doc.save(out); print("saved", out, "taxgap", tax_gap, "noi_adj", noi_y1_taxadj, "dscr_adj", dscr_taxadj, "cap_adj", cap_y1_taxadj)

cd /vercel/sandbox && python3 - <<'EOF'
p='scripts/build_ic_memo.py'; s=open(p).read()
s=s.replace('''the basis is below every recent submarket Class-A trade except the 2024 Isley sale, and well under the OM's $250K/unit replacement cost estimate.''','''the basis sits inside the $196.7K to $237.6K range of recent submarket Class-A trades and well under the OM's $250K/unit replacement cost estimate.''')
s=s.replace('''"Low for a 240-unit asset; third-party fees typically run higher"''','''"At the low end of typical third-party fees for a 240-unit asset"''')
s=s.replace("{pct(D['A_H34']*0+D['A_C51'])}","{pct(D['A_C51'])}")
open(p,'w').write(s)
EOF
python3 scripts/build_ic_memo.py && cd scratch && rm -rf qa/* && libreoffice --headless --convert-to pdf ic_memo.docx --outdir qa >/dev/null 2>&1; pdfinfo qa/ic_memo.pdf | grep Pages; pdftoppm -png -r 80 qa/ic_memo.pdf qa/p

cd /vercel/sandbox/scratch/qa && ls && montage p-*.png -tile 4x2 -geometry +6+6 -background '#888' sheet.png

cd /vercel/sandbox && python3 - <<'EOF'
p='scripts/build_ic_memo.py'; s=open(p).read()
s=s.replace('''    tabs = OxmlElement("w:tabs"); tb = OxmlElement("w:tab");''','''    tabs = OxmlElement("w:tabs")
    for pos in (4680, 9360):
        cl = OxmlElement("w:tab"); cl.set(qn("w:val"), "clear"); cl.set(qn("w:pos"), str(pos)); tabs.append(cl)
    tb = OxmlElement("w:tab");''')
s=s.replace('''        # keep rows together''','''        if i < len(rows):
            for c in t.rows[i].cells:
                for pp in c.paragraphs: pp.paragraph_format.keep_with_next = True
        # keep rows together''')
s=s.replace('''    tr = t.rows[0]._tr; trPr''','''    for c in t.rows[0].cells:
        for pp in c.paragraphs: pp.paragraph_format.keep_with_next = True
    tr = t.rows[0]._tr; trPr''')
open(p,'w').write(s)
EOF
python3 scripts/build_ic_memo.py >/dev/null && cd scratch && rm -rf qa/* && libreoffice --headless --convert-to pdf ic_memo.docx --outdir qa >/dev/null 2>&1; pdfinfo qa/ic_memo.pdf | grep Pages; pdftoppm -png -r 80 qa/ic_memo.pdf qa/p && cd qa && montage p-*.png -tile 4x2 -geometry +6+6 -background '#888' sheet.png

cd /vercel/sandbox && sed -i 's/("Heading 1", 15, NAVY, 16, 6)/("Heading 1", 15, NAVY, 10, 5)/' scripts/build_ic_memo.py && python3 scripts/build_ic_memo.py >/dev/null && cd scratch && rm -rf qa/* && libreoffice --headless --convert-to pdf ic_memo.docx --outdir qa >/dev/null 2>&1; pdfinfo qa/ic_memo.pdf | grep Pages; pdftoppm -png -r 200 -f 7 -l 8 qa/ic_memo.pdf qa/p; ls qa

cd /vercel/sandbox && python3 - <<'EOF'
p='scripts/build_ic_memo.py'; s=open(p).read()
start=s.index('doc.add_heading("Sources and methodology", level=3)')
end=s.index('out = "scratch/ic_memo.docx"')
block=s[start:end]
s=s[:start]+s[end:]
# insert block on cover before page break paragraph
anchor='p.add_run().add_break(WD_BREAK.PAGE)'
i=s.index(anchor)
cover_block=block.replace('doc.add_heading("Sources and methodology", level=3)','doc.add_paragraph("Sources and methodology", style="Eyebrow")')
s=s[:i]+"pass\n"+cover_block+"pb = doc.paragraphs[-1]; pb.add_run().add_break(WD_BREAK.PAGE)\n"+s[i+len(anchor):]
open(p,'w').write(s)
EOF
grep -n "add_break(WD_BREAK.PAGE)\|^pass" scripts/build_ic_memo.py; python3 scripts/build_ic_memo.py >/dev/null && cd scratch && rm -rf qa/* && libreoffice --headless --convert-to pdf ic_memo.docx --outdir qa >/dev/null 2>&1; pdfinfo qa/ic_memo.pdf | grep Pages; pdftoppm -png -r 200 qa/ic_memo.pdf qa/p

cd /vercel/sandbox/scratch/qa && for i in 1 2 3 4 5 6 7 8; do pdftotext -f $i -l $i ic_memo.pdf - | head -3 | tr '\n' ' '; echo " <- p$i"; done

cd /vercel/sandbox && sed -i 's/int(3.9 \* 1440)/int(3.2 * 1440)/; s/cell_margins(c, 500, 420, 200, 200)/cell_margins(c, 300, 300, 200, 200)/; s/sp = doc.add_paragraph(); sp.paragraph_format.space_after = Pt(18)/sp = doc.add_paragraph(); sp.paragraph_format.space_after = Pt(8)/' scripts/build_ic_memo.py && python3 scripts/build_ic_memo.py >/dev/null && cd scratch && rm -rf qa/* && libreoffice --headless --convert-to pdf ic_memo.docx --outdir qa >/dev/null 2>&1; pdfinfo qa/ic_memo.pdf | grep Pages; pdftoppm -png -r 200 qa/ic_memo.pdf qa/p; ls qa

cd /vercel/sandbox && rm -f scratch/ic_memo_final*.docx && python3 skills/docx/scripts/finalize_metadata.py scratch/ic_memo.docx --title "Preserve at Ridgeville - Investment Committee Memorandum" --subject "Stabilized acquisition screen and conditional recommendation for a 240-unit garden community in Ridgeville, SC" --keywords "Preserve at Ridgeville; investment committee; multifamily acquisition; Charleston MSA; USDA 538" --company "Allen Harrison Company" --output scratch/ic_memo_final.docx 2>&1 | tail -5