CP Acquisitions Analysis

Command

29 lines

cd /vercel/sandbox && cat > validate.py <<'EOF'
import openpyxl, sys
F=sys.argv[1]; v=openpyxl.load_workbook(F,data_only=True)
A,S,RS,W,SE,RC,SC,M=[v[s] for s in ['Assumptions','Sources & Uses','Returns Summary','Waterfall','Sensitivity','Rent Comps','Sales Comps','IC Memo']]
errs=[]
for ws in v.worksheets:
    for row in ws.iter_rows():
        for c in row:
            if isinstance(c.value,str) and c.value.strip() in ('#REF!','#VALUE!','#DIV/0!','#NAME?','#N/A'): errs.append(f'{ws.title}!{c.coordinate}')
chk={}
chk['V1']=not errs
chk['V2']=abs(S['C11'].value or 0)<1 and S['G11'].value=='BALANCED'
chk['V3']=W['C71'].value=='PASS' and all(W.cell(71,c).value in ('PASS','',None) for c in range(4,14))
t=RS['C21'].value
chk['V4']=abs(SE['F8'].value-t)<1e-6 and abs(SE['F26'].value-t)<1e-6 and abs(SE['F17'].value-RS['C22'].value)<1e-6 and M['C48'].value=='TIES'
chk['V5']=all(abs(a-b)<1e-9 for a,b in [(SE['F5'].value,A['H16'].value),(SE['B8'].value,A['C22'].value),(SE['F14'].value,A['H16'].value),(SE['B17'].value,A['C22'].value),(SE['F23'].value,A['C38'].value),(SE['B26'].value,A['C15'].value)])
chk['V6']=A['H34'].value<=0 and 0<A['H36'].value<=1 and 0<A['H19'].value<=1 and all(0<RC.cell(25,c).value<=1 for c in range(4,12) if RC.cell(25,c).value is not None)
chk['V7']=isinstance(A['H15'].value,int) and 1<=A['H15'].value<=10
chk['V8']=A['C36'].value in ['Interest Only','IO Then Amortizing','Fully Amortizing'] and (A['C37'].value==1 or A['C42'].value>0) and (A['C37'].value!=3 or A['C41'].value>=1)
rents=[RC.cell(14,c).value for c in range(4,12) if isinstance(RC.cell(14,c).value,(int,float))]
prices=[c for c in range(4,12) if isinstance(SC.cell(16,c).value,(int,float))]
wts=[SC.cell(28,c).value or 0 for c in range(4,12)]
chk['V9']=len(rents)>=3 and RC['C29'].value not in ('',None) and len(prices)>=3 and abs(sum(wts)-1)<=0.001 and all((SC.cell(28,c).value or 0)==0 or c in prices for c in range(4,12)) and SC['C32'].value>0
chk['V10']=abs(A['C77'].value+A['C78'].value-A['C64'].value)<=1 and A['H52'].value>0 and A['H50'].value<A['H38'].value
adv={'H5 in [2%,15%]':0.02<=A['H5'].value<=0.15,'C50>0':A['C50'].value>0,'IRR not 0':RS['C21'].value!=0,'C43>=H15':A['C43'].value>=A['H15'].value,
     'C22>=H6-0.005':A['C22'].value>=A['H6'].value-0.005,'opex ratio H60 in [0.30,0.60]':0.30<=A['H60'].value<=0.60}
print(chk); print('advisory',adv); print('errors',errs[:5])
EOF
python validate.py Golden_Nugget_Apartments_CentrePoint_Underwriting_2026-09-28.xlsx

Content

129 lines

import math, os, shutil, sys, tempfile
import openpyxl
NARRATIVE_CELLS = {'executive_summary': 'B11', 'investment_thesis': 'B52', 'property_overview': 'B56', 'market_overview': 'B60',
    'rent_comps': 'B64', 'sales_comps': 'B68', 'business_plan': 'B72', 'financing': 'B76', 'returns_sensitivity': 'B80',
    'risks_mitigants': 'B84', 'conditions_next_steps': 'B88', 'sources_provenance': 'B92'}
def _height(text):  # merged B:G at Calibri 10 ~ 125 chars/line, 13.5 pt/line
    return max(30, round(sum(max(1, math.ceil(len(p) / 125)) for p in text.split('\n')) * 13.5 + 8, 1))
def write_narratives(path, narratives):
    wb = openpyxl.load_workbook(path); m = wb['IC Memo']
    for key, text in narratives.items():
        ref = NARRATIVE_CELLS[key]; m[ref].value = text; m.row_dimensions[int(ref[1:])].height = _height(text)
    wb.save(path)
def export_memo_pdf(recalculated_xlsx, pdf_out):   # run only on a workbook that has just been recalculated
    vals = openpyxl.load_workbook(recalculated_xlsx, data_only=True)['IC Memo']
    wb = openpyxl.load_workbook(recalculated_xlsx); m = wb['IC Memo']
    for row in m.iter_rows():
        for c in row:
            if isinstance(c.value, str) and c.value.startswith('='): c.value = vals[c.coordinate].value
    for name in [n for n in wb.sheetnames if n != 'IC Memo']: del wb[name]
    wb.active = 0; tmp = tempfile.mkdtemp(); stage = os.path.join(tmp, 'IC_Memo.xlsx'); wb.save(stage)
    sys.path.insert(0, 'skills/xlsx/scripts'); from office.soffice import run_soffice
    run_soffice(['--headless', '--convert-to', 'pdf', '--outdir', tmp, stage], check=True, capture_output=True, timeout=240)
    shutil.move(os.path.join(tmp, 'IC_Memo.pdf'), pdf_out); return pdf_out

import glob, subprocess
from openpyxl.drawing.image import Image as XLImage
def logo_png(out='CentrePoint_logo.png'):
    svg = next(iter(glob.glob('**/CentrePoint-logo.svg', recursive=True)), None)
    if svg is None: raise FileNotFoundError('CentrePoint-logo.svg not attached')
    try:
        import cairosvg   # pip install cairosvg if absent
        cairosvg.svg2png(url=svg, write_to=out, output_width=2400, background_color='white')
    except ImportError:
        subprocess.run(['convert', '-background', 'white', '-density', '600', svg, '-resize', '2400x', '-flatten', out], check=True)
    return out
def ensure_memo_logo(path):
    wb = openpyxl.load_workbook(path); m = wb['IC Memo']
    if m._images: return 'present'
    img = XLImage(logo_png()); r = img.width / img.height; img.height = 40; img.width = int(40 * r)
    m.add_image(img, 'B1'); m.row_dimensions[1].height = 46; wb.save(path); return 'restored'

N = {}
N['executive_summary'] = (
"CentrePoint is screening Golden Nugget Apartments, a 206-unit, 1976 low-rise garden community at 291 W Belleview Ave in Englewood, CO, with no asking price; this memo sets our bid. "
"At $22,750,000 ($110,437/unit, a 7.7% T12 cap rate) the deal clears all five IC hurdles with a levered IRR of 11.0% and a 1.87x equity multiple on $9,555,000 of equity at close, 60% LTV senior debt, and a 7-year hold. "
"The all-hurdles price is $22,750,000 and the binding hurdle is the 11.0% levered IRR; equity multiple, cash-on-cash, debt yield and DSCR all clear at higher prices. "
"The deciding metric is levered IRR, and it is fragile: the combined stress case (exit cap +50 bp, rent growth -150 bp) falls to 2.0%, and a 10% price increase drops the IRR to 7.1%. "
"Our bid is 43.1% below the $40,012,063 comp-implied value, so the seller is unlikely to accept it unless diligence shows more NOI than the datamart T12 supports. "
"Proposed recommendation: Approve with Conditions at no more than $22,750,000; the price that moves this to Approve is $18,720,000.")
N['investment_thesis'] = (
"- No business plan was provided (the sponsor thesis field was 'none'), so this is underwritten as a stabilized, as-is hold with no capital program.\n"
"- The case rests on buying current cash flow at a high going-in yield (7.7% T12 cap, 7.5% Year-1 cap) against a Denver multifamily cap rate of 5.48%, then exiting at 6.25%. The exit cap sits below the going-in cap on purpose: we are pricing below market, not betting on cap compression.\n"
"- Rent upside is modest and depends on execution. In-place rent of $1,117 trails the $1,173 asking rent by 5.0%, and the adjacent Brix on Belleview gets 14.0% more. Submarket rents are falling, though, so we model 1.0% growth, not a mark-to-market.\n"
"- Expenses grow at 3.35% versus 1.0% for rent, so NOI erodes from $1,749,115 (T12) to $1,701,305 in Year 1 and keeps sliding. Returns come from the entry yield, not from growth.")
N['property_overview'] = (
"Golden Nugget Apartments is a 206-unit, 3-story frame/brick low-rise garden property built in 1976 on 4.36 acres, with 123,806 rentable SF (601 SF average unit). The mix is mostly 1BR ($1,082 in-place) with some 2BR ($1,507). "
"The property is market-rate, FEMA Zone X, and has no recorded renovation year. "
"Physical occupancy is 95.2% (30-day average 96.7%), with a trailing-12-month retention estimate of 70.9% and a 27-day median days on market for recent leases. "
"The T12 is a datamart reconstruction, not a seller statement: GPR $2,765,885, vacancy and credit loss $95,085 (96.6% implied economic occupancy), and 2025 taxes of $110,830 ($538/unit) on a $1.43M assessed value. "
"All other expense lines are Arapahoe County benchmarks. Total opex is $921,685 (34.5% of EGI), which is light for a 50-year-old asset because utilities and other income are both blank.")
N['market_overview'] = (
"The Englewood submarket (Denver CBSA) is soft. Submarket occupancy is 93.4%, in-place rents fell 2.07% over the trailing 12 months, and new-lease tradeouts average -2.65%. Submarket asking rent of $1,730 sits well above the subject. "
"Arapahoe County is similar: 93.2% occupancy, T12 in-place rent -1.88%, tradeouts -3.59%, and a 66-day median days on market. "
"Supply is easing. County multifamily permits fell from 2,702 units (T25-T36) to 1,734 (T13-T24) to 1,534 in the trailing 12 months through July 2026. "
"The forecast engine's base case for submarket in-place rent is -0.65%/yr over 7 years. Confidence is LOW, with the flag 'boom extrapolation suspected' because history starts in July 2024, so we override it with judgment at 1.0%. "
"The Denver multifamily cap rate of 5.48% sits mid-band on 21-year history but near the top of its trailing 10-year band, with the 10-year Treasury elevated.")
N['rent_comps'] = (
"Eight market-rate comps within 2.6 miles average $1,454 in-place (median $1,333). The subject's $1,119 T12 GPR-per-unit sits 23.0% below the average. "
"That gap overstates the upside. Four comps are newer or taller product (Oxford Station 2016, Sequel 2023, Noble Old Hampden 2024, Parkview Towers high-rise). The closest like-for-like vintage comps, Off Broadway Flats ($1,036) and Creekside ($1,059), rent at or below the subject. "
"The real benchmark is Brix on Belleview (0.06 mi, 1962, renovated 2012) at $1,274, which suggests a renovation-driven premium of roughly 14% if CentrePoint funds a program. "
"Comp average occupancy is 89.4%, pulled down by Creekside (70.6%, n=6) and the two lease-ups. Creekside and Debbie J II have thin rent samples.")
N['sales_comps'] = (
"Six market-rate sales from July 2022 to May 2025 within 3.0 miles give a weighted adjusted value of $194,233/unit, or $40,012,063 for the subject (unadjusted average $212,210/unit). "
"Adjustments are negative for time (Denver values have fallen since 2022) and for the larger, newer comps (Fielder's Creek 1983, Summit Riverside 1986/2013). "
"Our $22,750,000 bid is 43.1% below comp-implied value. The comps traded on higher rents ($1,025-$1,679 today) and likely lower cap rates. At the comp-implied price our NOI is only a 4.4% cap, and the model loses equity. "
"Excluded: pre-development land trades, restricted and affordable assets, 2014-vintage product, and Lorinda Apartments, where the datamart price conflicts with an OM source by about 2x. "
"Kingsbrook Arms ($150,000/unit, 2025) and SouthGlenn Place ($164,338/unit, 2024) are the most recent and most relevant prints, and even they sit well above our basis.")
N['business_plan'] = (
"- No capital program is underwritten (CapEx budget blank). Replacement reserves are $350/unit/year, the pre-1990 house default, or $72,100 per year.\n"
"- Operations are underwritten as-is: 95.0% stabilized economic occupancy (capped from 95.2% physical), rent growth 1.0%, other income growth 1.0%, expense growth 3.35%.\n"
"- Upside we have not underwritten: a RUBS/utility bill-back and fee program (other income is blank in the T12), and a light interior program aimed at the Brix on Belleview premium. Both need a seller T12 and a unit walk before they can go into the price.\n"
"- A renovation program would also have to clear the tenant affordability ceiling described in Risks.")
N['financing'] = (
"Senior loan of $13,650,000 (60% LTV) at 6.11%, the Freddie Mac CME 7-year fixed quote for 65% LTV. It is interest-only for 2 years, then amortizes over 30 years, with a 7-year term matched to the 7-year hold. "
"Going-in DSCR is 2.04x on IO debt service and debt yield is 12.8%, both well above the 1.25x and 9.0% hurdles; breakeven occupancy is 63.5%. "
"Levered cash flow steps down in Year 3 when amortization starts. "
"No mezzanine and no promote structure are modeled. Adding 10 points of LTV lifts the IRR to 12.3%, but we do not recommend it given the NOI trend.")
N['returns_sensitivity'] = (
"Base case at $22,750,000: levered IRR 11.0%, equity multiple 1.87x, total profit $8,294,267, and average cash-on-cash 6.9%, all on equity at close of $9,555,000. Unlevered IRR is 8.3%, average DSCR 1.79x, exit value $26,501,392, and net sale proceeds $13,223,376. Year-1 cash-on-cash on total equity required ($9,627,100) is 8.3%.\n"
"Stress cases (levered IRR): exit cap +50 bp 9.0%; rent growth -150 bp 4.4%; both combined 2.0%; price +10% 7.1%; LTV +10 pts 12.3%. Only the LTV case clears 11.0%.\n"
"Solved prices (goal-seek on purchase price only):\n"
"- IRR price: $22,750,000 ($110,437/unit, 43.1% below comp-implied value).\n"
"- All-hurdles price: $22,750,000, bound by levered IRR. Equity multiple would allow about $25.1M, Year-1 cash-on-cash $28.2M, and debt yield $32.4M.\n"
"- Approve price (combined stress clears 11.0%): $18,720,000 ($90,874/unit, 53.2% below comp-implied), with a base levered IRR of 18.6% and a 2.71x multiple.\n"
"Returns fall steeply with price. At the 5.48% market cap-rate price (about $31.9M) the base-case levered IRR is negative (-4.5%, 0.75x).")
N['risks_mitigants'] = (
"- Price vs. market: our bid is 43.1% below comp-implied value, and the market is pricing this asset on rent growth we cannot support. Mitigant: hold the price discipline and walk away if the gap cannot be closed.\n"
"- Stress cases below hurdle: exit cap +50 bp (9.0%), rent growth -150 bp (4.4%), combined (2.0%), price +10% (7.1%). A -0.5% rent path is close to the forecast engine's base case (-0.65%/yr, LOW confidence), so the downside is realistic.\n"
"- Tenancy credit: the average resident FICO is 614, median household income is $50,582 (74.5% of households earn $35-50K), 59.9% have net worth under $25K, and the past-due rate is 14.05%, up 5.66 pts in 12 months. Expect bad debt above the T12's 3.4% vacancy and credit loss. Mitigant: require the seller's aged receivables and a bad-debt history.\n"
"- Rent headroom: rent-to-income is 25.1% today. A 30% ceiling on median income implies about $1,265/month, roughly 13% above in-place rent. That caps mark-to-market and renovation premiums.\n"
"- T12 built from fallbacks: insurance, R&M, management, payroll, G&A, marketing and other expenses come from Arapahoe County benchmarks (n=188). Utilities and other income are blank; the county other-income benchmark (85.8% of net rent) was rejected as implausible. Insurance at $486/unit is likely light for Denver hail exposure. Mitigant: seller T12 and a quote.\n"
"- Property tax: the $110,830 bill reflects a $1.43M assessed value. Colorado's biennial reappraisal could lift it after a sale is recorded.\n"
"- Exit cap (6.25%) is below the going-in cap (7.7%). This is intentional (a below-market entry) and not a compression bet.\n"
"- Vintage: a 1976 frame building with no recorded renovation and only $350/unit of reserves. Mitigant: a PCA before LOI.")
N['conditions_next_steps'] = (
"- Pricing condition: bid no more than $22,750,000 (the all-hurdles price, bound by levered IRR). This is 43.1% below the $40,012,063 comp-implied value, so it is probably not achievable against market pricing; treat it as a disciplined bid, not an expected clearing price.\n"
"- To Approve outright: $18,720,000, or diligence evidence that lifts NOI.\n"
"- Obtain the seller T12, rent roll, aged receivables and utility bills. Re-underwrite other income, utilities, insurance and bad debt from actuals.\n"
"- Order a PCA, insurance quote, and a property-tax reassessment estimate.\n"
"- Confirm a Freddie/Fannie term sheet at 60% LTV with 2 years IO.\n"
"- Confirm Prepared By and the recommendation before IC.")
N['sources_provenance'] = (
"- Purchase price $22,750,000: ai-estimate (goal-seek to IC hurdles; the user asked us to suggest a price). LTV 60%, 7-yr hold, 7-yr term, IO then amortizing, 2-yr IO, 30-yr amortization: user-provided.\n"
"- Property facts, GPR, vacancy loss, taxes, occupancy, rents, tenant profile: property data (RealAI datamart, as of 2026-09-26).\n"
"- Insurance, R&M, management, payroll, G&A, marketing, other expense: Arapahoe County P&L benchmarks x EGI (county benchmark, n=188). Utilities and other income: blank (no qualifying source).\n"
"- Stabilized occupancy 95.0%: property data (95.15% physical), capped at 0.95 per house rule.\n"
"- Rent growth 1.0%: ai-estimate. Forecast engine base -0.65%/yr, LOW confidence, flags 'boom extrapolation suspected' and 'wide scenario divergence'; overridden by judgment. Other income growth = rent growth.\n"
"- Expense growth 3.35%: forecast engine (CPI proxy, base-case CAGR, LOW confidence, flag 'thin or absent peer cohort; mean reversion not applied').\n"
"- Exit cap 6.25%: ai-estimate (Denver MF cap 5.48% + about 75 bp for vintage and the rate environment). Interest rate 6.11%: Freddie Mac CME 7-yr, 65% LTV. Closing and disposition 2.0%, reserves $350/unit: default.\n"
"- Rent and sales comps: RealAI datamart; buyer names from web research where found; sales-comp adjustments and weights: ai-estimate. Protected defaults (no mezz, no promote, no staged inputs, house hurdles) unchanged.")

if __name__ == '__main__':
    F = sys.argv[1]
    wb = openpyxl.load_workbook(F); m = wb['IC Memo']
    assert not (isinstance(m['C7'].value, str) and m['C7'].value.startswith('='))
    m['C7'].value = 'Approve with Conditions'; wb.save(F)
    write_narratives(F, N)
    for k, t in N.items(): print(k, len(t))

Target	Price	$/unit	vs. comp-implied value	Levered IRR / EM / DSCR / debt yield
IRR price	$22,750,000	$110,437	−43.1%	11.0% / 1.87x / 2.04x / 12.8%
All-hurdles price (set by IRR)	$22,750,000	$110,437	−43.1%	same
Approve price (combined stress still ≥11%)	$18,720,000	$90,874	−53.2%	18.6% / 2.71x / 2.48x / 15.6%

Returns at $22.75M vs. hurdles

Pricing

The risks that matter most

Assumptions I set (please confirm or change)

Metric	Actual	Hurdle
Levered IRR	11.0%	11.0%
Equity multiple	1.87x	1.50x
Going-in DSCR	2.04x	1.25x
Debt yield	12.8%	9.0%
Year-1 cash-on-cash	8.3%	5.0%

Assumption	Value	Source
Purchase price	$22,750,000	ai-estimate (solved to hurdles)
LTV / hold / loan term / structure	60% / 7 yrs / 7 yrs / 2 yrs interest-only, then 30-yr amortization	user-provided
Interest rate	6.11%	Freddie Mac 7-yr fixed at 65% LTV
Rent growth / other income growth	1.0% / 1.0%	ai-estimate (forecast engine said −0.65%, LOW confidence)
Expense growth	3.35%	forecast engine (inflation proxy)
Stabilized occupancy	95.0%	property data (95.15% physical, capped at 95%)
Exit cap	6.25%	ai-estimate (Denver 5.48% plus about 75 bp)
Reserves / closing costs / disposition costs	$350/unit / 2% / 2%	default