I'll need your Excel financial modeling template to get started — this is what I'll introspect to build your personalized population agent.
cd /vercel/sandbox && python skills/xlsx/scripts/workbook_profile.py template.xlsx
cd /vercel/sandbox && python skills/xlsx/scripts/workbook_sample.py template.xlsx 2>&1 | head -100
Given the scope of a 14-sheet full acquisition model, let me pull complete extracts sheet-by-sheet rather than relying on samples, since I need every input cell's exact address.
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template.xlsx', data_only=False)
print(wb.sheetnames)
"
cd /vercel/sandbox && python3 -c "
import openpyxl
wbf = openpyxl.load_workbook('template.xlsx', data_only=False)
wbv = openpyxl.load_workbook('template.xlsx', data_only=True)
for sn in wbf.sheetnames:
wsf = wbf[sn]
wsv = wbv[sn]
print('='*20, sn, 'dims', wsf.dimensions)
"
cd /vercel/sandbox && python3 << 'EOF'
import openpyxl, csv
wbf = openpyxl.load_workbook('template.xlsx', data_only=False)
wbv = openpyxl.load_workbook('template.xlsx', data_only=True)
import os
os.makedirs('dump', exist_ok=True)
for sn in wbf.sheetnames:
wsf = wbf[sn]
wsv = wbv[sn]
with open(f'dump/{sn.replace(" ","_").replace("&","and")}.csv','w',newline='') as f:
w = csv.writer(f)
w.writerow(['cell','value','formula','number_format','font_color','fill_color'])
for row in wsf.iter_rows():
for cell in row:
if cell.value is None:
continue
v = wsv[sn][cell.coordinate].value if cell.coordinate in [c.coordinate for c in wsv[sn][cell.row]] else None
vv = wsv.cell(row=cell.row, column=cell.column).value
fc = cell.font.color.rgb if cell.font and cell.font.color else None
fillc = cell.fill.fgColor.rgb if cell.fill and cell.fill.fgColor else None
w.writerow([cell.coordinate, vv, cell.value if isinstance(cell.value,str) and str(cell.value).startswith('=') else '', cell.number_format, fc, fillc])
print("done")
EOF
ls dumpcd /vercel/sandbox && python3 << 'EOF'
import openpyxl, csv, os
wbf = openpyxl.load_workbook('template.xlsx', data_only=False)
wbv = openpyxl.load_workbook('template.xlsx', data_only=True)
os.makedirs('dump', exist_ok=True)
for sn in wbf.sheetnames:
wsf = wbf[sn]
wsv = wbv[sn]
with open(f'dump/{sn.replace(" ","_").replace("&","and")}.csv','w',newline='') as f:
w = csv.writer(f)
w.writerow(['cell','value','formula','number_format','font_color'])
for row in wsf.iter_rows():
for cell in row:
if cell.value is None:
continue
vv = wsv.cell(row=cell.row, column=cell.column).value
formula = cell.value if isinstance(cell.value,str) and cell.value.startswith('=') else ''
fc = None
try:
fc = cell.font.color.rgb if cell.font and cell.font.color else None
except Exception:
fc = None
w.writerow([cell.coordinate, vv, formula, cell.number_format, fc])
print("done")
EOF
ls dumpcd /vercel/sandbox && sed -n '95,300p' dump/Assumptions.csv | tr -d '\r'
cd /vercel/sandbox && python3 -c "
import csv
with open('dump/Assumptions.csv') as f:
r = list(csv.reader(f))
for row in r:
if row and row[0] and row[0][0] in 'ABCDEFGHIJKLMNOPQRST':
import re
m = re.match(r'^[A-Z]+(\d+)', row[0])
if m and int(m.group(1)) >= 94:
print(row)
" 2>&1 | head -200That's the end of the input section (row 94). Rows 95-235 are likely blank/unused. Let's confirm and move to other sheets.
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template.xlsx', data_only=False)
ws = wb['Assumptions']
for row in ws.iter_rows(min_row=95, max_row=235):
for c in row:
if c.value is not None:
print(c.coordinate, repr(c.value))
"Confirmed — Assumptions sheet ends at row 94. Now let's look at the remaining sheets.
Good, this is fully derived. Now let's check the remaining sheets: Rent Comps, Sales Comps, Debt Schedule, Returns Summary, Waterfall, Sensitivity, Summary, Market & Demographics, Tenant Intelligence, _SensCalc, _Data.
Debt Schedule is fully derived (no inputs). Rent Comps & Sales Comps are input tables matched to rental-comps/sales-comps skills. Now let's look at Returns Summary, Waterfall, Sensitivity, Summary, Market & Demographics, Tenant Intelligence, _SensCalc, _Data.
Both fully derived. Now Sensitivity, Summary, Market & Demographics, Tenant Intelligence, _SensCalc, _Data.
Sensitivity, Summary fully derived (all read cells). Now Market & Demographics, Tenant Intelligence, _SensCalc, _Data — these are the big sheets.
cd /vercel/sandbox && wc -l dump/Market_and_Demographics.csv dump/Tenant_Intelligence.csv dump/_SensCalc.csv dump/_Data.csv
cd /vercel/sandbox && awk -F',' '{print $1,$4}' dump/_Data.csv | grep -v ',,' | sed -n '1,40p'
echo ---
grep -c '^=' dump/_Data.csv
grep 'FF0000FF' dump/_Data.csv | wc -l
head -5 dump/_Data.csvcd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template.xlsx', data_only=False)
ws = wb['_Data']
for row in ws.iter_rows(min_row=1, max_row=130):
vals = [c.value for c in row if c.value is not None]
if vals:
print(row[0].row, vals[:8])
" | head -140cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template.xlsx', data_only=False)
ws = wb['Market & Demographics']
for row in ws.iter_rows(min_row=1, max_row=60):
vals = [(c.coordinate,c.value) for c in row if c.value is not None]
if vals:
print(vals[:6])
"cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template.xlsx', data_only=False)
ws = wb['Market & Demographics']
for row in ws.iter_rows(min_row=1, max_row=294):
b = row[0].value if len(row)>0 else None
if b:
print(row[0].row, repr(b))
"cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template.xlsx', data_only=False)
ws = wb['Market & Demographics']
for r in range(1,295):
v = ws.cell(row=r, column=2).value
if v:
print(r, repr(v))
"cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template.xlsx', data_only=False)
ws = wb['Market & Demographics']
for r in range(110,295):
vals = [ws.cell(row=r,column=c).value for c in range(1,20)]
if any(v is not None for v in vals):
print(r, vals)
"Good, sheet ends at row 109. Now let's confirm which cells in Market & Demographics are true blue inputs (datamart-sourced values pasted in) vs formulas, and do the same scan for Tenant Intelligence.
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template.xlsx', data_only=False)
ws = wb['Market & Demographics']
for r in range(1,110):
for c in range(2,20):
cell = ws.cell(row=r,column=c)
if cell.value is not None:
isf = isinstance(cell.value,str) and cell.value.startswith('=')
color = None
try:
color = cell.font.color.rgb if cell.font and cell.font.color else None
except: pass
if not isf and color=='FF0000FF':
print(cell.coordinate, repr(cell.value))
" | head -80cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template.xlsx', data_only=False)
ws = wb['Tenant Intelligence']
for r in range(1,266):
v = ws.cell(row=r, column=2).value
if v:
print(r, repr(v))
"cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template.xlsx', data_only=False)
ws = wb['Tenant Intelligence']
for r in range(60,266):
vals = [ws.cell(row=r,column=c).value for c in range(1,25)]
if any(v is not None for v in vals):
print(r, vals)
"Good, sheet ends at row 60. Now let's check full row/column content of Tenant Intelligence rows 5-46 to understand data columns.
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template.xlsx', data_only=False)
ws = wb['Tenant Intelligence']
for r in range(5,47):
vals = [(openpyxl.utils.get_column_letter(c)+str(r), ws.cell(row=r,column=c).value) for c in range(2,10) if ws.cell(row=r,column=c).value is not None]
if vals:
print(vals)
"cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template.xlsx', data_only=False)
ws = wb['_SensCalc']
for r in range(1,20):
vals = [(openpyxl.utils.get_column_letter(c)+str(r), ws.cell(row=r,column=c).value) for c in range(1,15) if ws.cell(row=r,column=c).value is not None]
if vals:
print(vals)
"I found the model's full structure. Before generating the agent, I need to flag something.
Finding: This workbook is fully populated with a specific deal — "The Ridley," a 301-unit property at 12517 Beach Blvd, Jacksonville, FL, with a $65M purchase price, named rent/sale comps, live financing terms, and a filled waterfall structure — not a blank template.
mkdir -p /vercel/sandbox/clean && cp /vercel/sandbox/template.xlsx /vercel/sandbox/clean/RealAI_Pro_Forma_Template_The_Ridley_clean.xlsx
ls /vercel/sandbox
import openpyxl
SRC = "template.xlsx"
OUT = "clean/RealAI_Pro_Forma_Template_clean.xlsx"
wb = openpyxl.load_workbook(SRC, data_only=False)
def clear(ws_name, cells):
ws = wb[ws_name]
for c in cells:
ws[c] = None
def clear_range(ws_name, col_range, row_range):
ws = wb[ws_name]
for col in col_range:
for row in row_range:
coord = f"{col}{row}"
cell = ws[coord]
if cell.value is not None and not (isinstance(cell.value, str) and cell.value.startswith("=")):
ws[coord] = None
# ---- Assumptions sheet ----
# Property identity & address
clear("Assumptions", ["C5","C6","C7","C8","C9","C10","C11","C12","C13","C14"])
# Demographics / tenant snapshot (J5:K15, L5:M10, O5:P10)
clear_range("Assumptions", ["K"], range(5,16))
clear_range("Assumptions", ["M"], range(5,11))
clear_range("Assumptions", ["P"], range(5,11))
# Operational metrics
clear_range("Assumptions", ["C"], range(17,24))
# Acquisition / disposition / hold / growth (deal + market specific)
clear("Assumptions", ["C26","C29","H26","H27","H28","H29","H30","C33","C34"])
# Year-by-year override table
clear_range("Assumptions", ["H","I","J","K","L","M","N","O","P","Q"], range(37,41))
clear("Assumptions", ["H33"])
# CapEx
clear("Assumptions", ["C37","C39"])
# Financing - senior debt
clear("Assumptions", ["C47","C49","C50" if False else None]) # C50 is formula, skip
clear("Assumptions", ["C51","C52","C53","C54"])
# T12 operating statement (Year 0)
clear_range("Assumptions", ["H"], range(44,61))
# Waterfall toggle values that are deal-specific (keep tier % structure as house default)
clear("Assumptions", ["C83","C85"])
# ---- Rent Comps sheet: clear comp columns D:K (keep Subject col C which is formula-linked) ----
clear_range("Rent Comps", ["D","E","F","G","H","I","J","K"], list(range(6,62)))
# ---- Sales Comps sheet: clear comp columns D:K ----
clear_range("Sales Comps", ["D","E","F","G","H","I","J","K"], list(range(6,77)))
# ---- Market & Demographics: clear geography columns E,F,G (Zip/County/MSA hardcoded) ----
clear_range("Market & Demographics", ["E","F","G"], list(range(7,110)))
# ---- Tenant Intelligence: clear geography columns E,F,G ----
clear_range("Tenant Intelligence", ["E","F","G"], list(range(7,61)))
clear_range("Tenant Intelligence", ["C"], [37,38,39,40,41,42,43,44,45]) # subject hardcoded (non-formula) cells
# ---- _Data: clear entire historical/demographic blue input block (deal & geo specific) ----
clear_range("_Data", ["C","D","E","F","G"], list(range(5,131)))
wb.save(OUT)
print("saved", OUT)
cd /vercel/sandbox && python3 clean_template.py
clear("Assumptions", ["C47","C49","C50" if False else None]) # C50 is formula, skip
clear("Assumptions", ["C51","C52","C53","C54"])clear("Assumptions", ["C47","C49"])
clear("Assumptions", ["C51","C52","C53","C54"])cd /vercel/sandbox && python3 clean_template.py
Now let's verify it opens cleanly and check the recalc/scan (informational — this is a cleaned template, not a delivered model, so I won't force zero-error gating, but I do want to confirm no corruption).
cd /vercel/sandbox && python skills/xlsx/scripts/recalc.py clean/RealAI_Pro_Forma_Template_clean.xlsx 2>&1 | tail -30
Now writing the two deliverables.
# RealAI Pro Forma — Population Agent
## 1. Role and Model Summary
This agent populates the **RealAI Pro Forma Template** — a multifamily acquisition underwriting model — for a specific deal, using the datamart, the rental-comps and sales-comps skills, the forecasting skill, and targeted user input. It does not build new logic; it fills labeled input cells in the existing workbook, recalculates, and reports results.
The model is annual (Year 0 T12 + Years 1–10, though only years within the hold period are populated), denominated in whole dollars, and covers a single stabilized-or-value-add multifamily asset. Its dependency chain:
- **Assumptions** is the control sheet. Property facts and T12 operating-statement inputs seed Year 0 of the **Pro Forma**, which grows GPR, vacancy, other income, and each expense line forward at the rates set on Assumptions (single-rate or year-by-year override, toggled by "Use Staged Inputs"). Pro Forma NOI flows to **Debt Schedule** (loan amortization/interest) and to **Returns Summary** (unlevered and levered cash flow, exit value via the exit-cap/hold-period NOI, IRR/equity multiple). **Waterfall** takes Returns Summary's levered cash flow and Assumptions' promote-tier terms and splits it between LP and GP if the co-invest/promote toggle is "Yes"; otherwise 100% flows to the single equity holder. **Sensitivity** and its hidden engine **_SensCalc** independently recompute Levered IRR and Equity Multiple across exit-cap/rent-growth and price/LTV grids — fully formulaic, no manual data-table step. **Sources & Uses** and **Summary** are read-only roll-ups of the same inputs.
- **Rent Comps**, **Sales Comps**, **Market & Demographics**, and **Tenant Intelligence** are supporting exhibits: comp tables benchmark the subject's rents and basis; Market & Demographics and Tenant Intelligence benchmark the subject against its zip/county/MSA on supply-demand and tenant-financial-health metrics. **_Data** is a hidden bulk-retrieval sheet holding the monthly time series and demographic distributions that feed those two exhibit sheets' trailing/YoY calculations — it is entirely datamart-sourced and has no user-facing role.
The workbook was cleaned of its original sample deal ("The Ridley," Jacksonville FL) before this agent was generated; every coordinate below was verified against the blank structure.
## 2. Field Guide
Color convention in this workbook: **blue = input**, **black = formula**, **green = cross-tab link**. Never write to a black or green cell.
### 2.1 Property Identity & Physical Facts — `Assumptions` sheet
| Field | Cell | Intent | Source class | Req'd | Convention |
|---|---|---|---|---|---|
| Property Name | C5 | Deal identifier, propagates to Rent/Sales Comps and Summary via green links | datamart_preferred_user_fallback | Yes | text |
| Address | C6 | Street address | datamart_preferred_user_fallback | Yes | text |
| City, State, Zip | C7 | Geography for all comp/market pulls | datamart_preferred_user_fallback | Yes | text |
| Property Type | C8 | Building type (e.g. LOW_RISE, GARDEN, HIGH_RISE) | datamart | Yes | text enum from datamart |
| Unit Count | C9 | Denominator for all per-unit metrics | datamart_preferred_user_fallback | Yes | integer |
| Rentable Square Feet | C10 | Denominator for all per-SF metrics | datamart_preferred_user_fallback | Yes | integer |
| Year Built | C11 | Vintage; drives comp filtering | datamart | Yes | integer |
| Year Renovated | C12 | 0/blank if never renovated | datamart_preferred_user_fallback | No | integer |
| Location Rating | C13 | A–D letter rating, appears on comp sheets | ai_estimate | No | text |
| Improvement Rating | C14 | A–D letter rating | ai_estimate | No | text |
### 2.2 Tenant/Area Snapshot — `Assumptions` sheet (feeds Rent/Sales Comps "Subject" column via green links)
| Field | Cell | Source class |
|---|---|---|
| Average Household Income | K5 | datamart (tenant demographics, zip grain) |
| Median Household Income | K6 | datamart |
| FICO Credit Score | K7 | datamart |
| Rent-to-Income Ratio | K8 | datamart |
| Rent-to-FICO Ratio | K9 | datamart (may be "—" if not computed) |
| Debt-to-Income Ratio | K10 | datamart |
| Credit Utilization Ratio | K11 | datamart |
| Net Worth Tier (Median Range) | K12 | datamart |
| Liquid Resources Tier | K13 | datamart |
| Investment Resources Tier | K14 | datamart |
| Short-Term Liability Tier | K15 | datamart |
| Average Age | M5 | datamart |
| Mobility Score | M6 | datamart |
| Walk / Transit / Bike Score | M7 / M8 / M9 | api (location_walkability) |
| Crime Grade | M10 | api (zipcode_crime_stats) |
| Average Education Score, Bachelor's %, HS Diploma % | P5 / P6 / P7 | datamart |
| Elementary / Middle / High School (name + rating) | P8 / P9 / P10 | api (nearby_school_ratings) |
All of these are area-level facts, never user-required — silently retrieved and surfaced for confirmation only if materially unusual.
### 2.3 Property Operational Metrics — `Assumptions!C17:C23`
| Field | Cell | Source class |
|---|---|---|
| Asking rent / unit | C17 | datamart (property_mfr) |
| Asking rent / sq ft | C18 | datamart |
| In-place rent / unit | C19 | datamart_preferred_user_fallback (T12/rent roll if uploaded) |
| In-place rent / sq ft | C20 | datamart_preferred_user_fallback |
| Occupancy % | C21 | datamart_preferred_user_fallback |
| Days on market | C22 | datamart |
| Trade out % | C23 | datamart |
Document-reconciliation rule: if the user uploads a rent roll or T12, those values outrank the datamart for C19–C21 per the document-reconciliation protocol.
### 2.4 Acquisition — `Assumptions!B25:C30`
| Field | Cell | Intent | Source class | Req'd |
|---|---|---|---|---|
| Purchase Price | C26 | The user's basis; drives everything downstream | user_required | Yes |
| Closing Costs (%) | C29 | Template default 2% | template_default | No |
| C27 (Price/Unit), C28 (Price/SF), C30 (Total Basis) | — | formulas, never write | — | — |
### 2.5 Disposition & Growth/Hold — `Assumptions!B32:H30`
| Field | Cell | Intent | Source class | Req'd |
|---|---|---|---|---|
| Exit Cap Rate | C33 | Applied to forward NOI in the exit year; spread vs. going-in cap (H5/H6) is the market-timing bet | datamart_preferred_user_fallback (forecasting skill cap-rate band) | No |
| Disposition Costs (%) | C34 | Template default 2% | template_default | No |
| Hold Period (Years) | H26 | Bounds every projected year via `IF(year>H26,"",…)` — Pro Forma, Debt Schedule, Returns Summary all key off this | user_required | Yes |
| Rent Growth (%/yr) | H27 | Single-rate GPR growth assumption used when Use Staged Inputs = No | user_optional_datamart_backfill (forecasting skill) | No |
| Expense Growth (%/yr) | H28 | Single-rate OpEx growth | user_optional_datamart_backfill (forecasting skill) | No |
| Other Income Growth | H29 | Single-rate other-income growth | user_optional_datamart_backfill (forecasting skill) | No |
| Stabilized Occupancy | H30 | Applied as `1 − vacancy%` from Year 1 forward | user_optional_datamart_backfill (forecasting skill / market occupancy) | No |
| Use Staged Inputs | H33 | "No" = single-rate growth; "Yes" = year-by-year overrides below drive the model instead | user_optional | No (default No) |
| Year-by-year Rent/Occupancy/Other Income/Expense Growth (Years 1–10) | H37:Q40 | Only read when H33="Yes" | user_optional_datamart_backfill (forecasting skill, per-year) | No |
### 2.6 CapEx & Reserves — `Assumptions!B36:C44`
| Field | Cell | Source class | Req'd |
|---|---|---|---|
| Total CapEx Budget | C37 | user_required (renovation scope is deal-specific thesis) | No unless value-add |
| Timing (1=Yr1 Lump, 0=Spread) | C39 | user_optional (default 0) | No |
| Reserves / Unit / Year | C43 | template_default ($250) | No |
### 2.7 Financing — Senior Debt — `Assumptions!B46:C58`
| Field | Cell | Intent | Source class | Req'd |
|---|---|---|---|---|
| Loan Structure | C47 | "Interest Only" / "Fully Amortizing" / other → drives Loan Code (C48, formula) | user_required | Yes |
| LTV (%) | C49 | Sets Loan Amount (C50, formula) | user_required | Yes |
| Interest Rate | C51 | user_optional_datamart_backfill (mortgage_rates topic, multifamily) | No |
| IO Period (Years) | C52 | Only relevant if Loan Structure = "Fully Amortizing" partial-IO | user_required if applicable | Conditional |
| Amortization (Years) | C53 | user_required | Yes |
| Loan Term (Years) | C54 | user_required | Yes |
### 2.8 T12 Operating Statement (Year 0) — `Assumptions!G43:H60`
All eight lines are dollar amounts for the trailing twelve months; C26 Purchase Price is not read here, this is pure operations.
| Field | Cell | Source class |
|---|---|---|
| Gross Potential Rent (GPR) | H44 | datamart_preferred_user_fallback (T12 document if uploaded, else property_mfr) |
| Less: Vacancy & Credit Loss | H45 | datamart_preferred_user_fallback — **sign convention: enter as a negative number** |
| Other Income | H48 | datamart_preferred_user_fallback |
| Real Estate Taxes | H52 | datamart_preferred_user_fallback |
| Insurance | H53 | datamart_preferred_user_fallback |
| Utilities | H54 | datamart_preferred_user_fallback |
| Repairs & Maintenance | H55 | datamart_preferred_user_fallback |
| Management Fees | H56 | datamart_preferred_user_fallback |
| Payroll & Benefits | H57 | datamart_preferred_user_fallback |
| General & Administrative | H58 | datamart_preferred_user_fallback |
| Advertising & Marketing | H59 | datamart_preferred_user_fallback |
| Other Expenses | H60 | datamart_preferred_user_fallback |
Document-reconciliation rule: an uploaded T12 always outranks the datamart property_mfr financial topic for this block.
### 2.9 Mezzanine Financing (optional) — `Assumptions!B65:C69`
| Field | Cell | Source class | Req'd |
|---|---|---|---|
| Mezz Enabled (1=Yes, 0=No) | C66 | user_optional (default 0) | No |
| Mezz LTV (Incremental) | C67 | user_required if enabled | Conditional |
| Mezz Rate | C68 | user_required if enabled | Conditional |
### 2.10 GP/LP Waterfall Structure — `Assumptions!B82:C94`
| Field | Cell | Intent | Source class | Req'd |
|---|---|---|---|---|
| Co-Invest / Promote Structure | C83 | "No" bypasses the whole Waterfall sheet — 100% to the single equity holder | user_optional (default No) | No |
| GP Co-Invest % | C85 | user_required if C83=Yes | Conditional |
| Tier 1: Preferred Return | C89 | template_default (8%) unless the user overrides | template_default | No |
| Tier 2: Hurdle IRR / GP Promote % | C90 / C91 | template_default (12% / 20%) | template_default | No |
| Tier 3: Hurdle IRR / GP Promote % | C92 / C93 | template_default (18% / 30%) | template_default | No |
| Tier 4: GP Promote % (Residual) | C94 | template_default (40%) | template_default | No |
Waterfall tier percentages are house-standard promote structure — confirm once with the user, then treat as locked unless they redirect (per the "house assumptions" checkpoint).
### 2.11 Comp Tables — `Rent Comps` and `Sales Comps` sheets
Both sheets have a fixed "Subject" column (C) that is entirely green-linked to Assumptions/other sheets — never write to column C. Comp columns D–K are the write surface, one column per comp.
**Rent Comps** — write rectangle `D6:K61`, sourced via the **rental-comps skill**:
- Rows 6–15: identity (Property Name, Address, City/State/Zip, Distance, Unit Count, Rentable SF, Year Built, Year Renovated, Location/Improvement Rating)
- Rows 18–24: operational metrics (asking/in-place rent per unit and per SF, occupancy, DOM, trade-out %)
- Rows 28–31: in-place rent by unit type (Studio/1BR/2BR/3BR/4BR) — only rows with comp data populated
- Rows 35–45: tenant financial profile (income, FICO, DTI, credit utilization, net worth/liquid/investment/liability tiers)
- Rows 49–60: location & education profile (age, mobility/walk/transit/bike score, crime grade, education, schools)
**Sales Comps** — write rectangle `D6:I77` (up to 6 comps per the sales-comps skill's 4–6 comp selection), sourced via the **sales-comps skill**:
- Rows 6–14: identity
- Rows 17–23: operational metrics
- Rows 26–41: transaction detail and adjustment grid (Sale Date, Sale Price, Time/Size/Year Built/Location/Market Conditions adjustment %, Weight %) — adjustment and weight inputs are ai_estimate, surfaced for confirmation; the sales-comps skill's own comp-ranking output should seed the Weight (%) row
- Rows 52–76: tenant financial and location/education profile (same shape as Rent Comps)
Row 40 (Total Adjustment), row 42 (Adjusted Price/Unit), and rows 45–49 (Concluded Value block) are formulas — never write.
### 2.12 Market & Demographics sheet — fully datamart-sourced, no user input
Write rectangle `E7:G109`, three columns per metric: **E = Zip Code, F = County, G = MSA** (columns C/D are green/formula-linked to Assumptions and Rent Comps and must not be written). Sourced from `census_place`/`zipcode`/`county`/`market` topics (population, jobs, households, housing supply, permits) and `property_mfr`/`rental-comps` aggregates (rent/occupancy trend rows 7–13). Retrieve once per geography grain and confirm coverage before writing — if zip-level coverage is thin, widen to county/MSA per the standard coverage-check rule and leave the thin cell blank rather than fabricating a value.
### 2.13 Tenant Intelligence sheet — fully datamart-sourced, no user input
Write rectangle `E7:G17` and `E37:G45` (Zip/County/MSA columns; C is Assumptions-linked, D is Rent-Comps-average-linked — never write). Sourced from the same tenant-demographics and area-facts topics as §2.2, at zip/county/MSA grain instead of point location.
### 2.14 `_Data` sheet — bulk historical time series, fully datamart-sourced, no user input
Write rectangle `C5:G130`, five columns: **C = Property, D = Comp Average, E = Zip Code, F = County, G = MSA**. Rows are monthly periods (most-recent ~12 months) for: Asking Rent (5–16), In-Place Rent (20–31), Occupancy (35–46), Days on Market (50–61), Trade-Out % (65–76); followed by point-in-time demographic distribution tables: Income (80–91), Net Worth (95–106), FICO (110–114), Employment by Industry (118–130). This sheet feeds Market & Demographics' YoY/trailing-average rows (§2.12) via formula — populate it first, in the same retrieval pass as §2.12, using the trend/history variant of the same topics.
## 3. Intake Design
**One combined opening form:**
1. Property/address (`property_place_search`, single) — resolves entity ID and geography for every downstream pull
2. Document upload (optional, multiple) — T12, rent roll, appraisal, OM
3. Deal terms (all `user_required`, text/number fields): Purchase Price, Loan Structure, LTV, Amortization, Loan Term, Hold Period
4. Free-text thesis (textarea, optional): "Anything about your renovation plan, targeted hold, or capital structure?"
5. Waterfall mode toggle (select): "Single equity holder" vs. "GP/LP with promote" — if the latter, GP Co-Invest %
**Targeted gap-fill (second form, only if still missing after retrieval):** Interest Rate (if mortgage_rates lookup fails for the property type/loan program), CapEx Budget (if renovation scope was implied but not sized), Mezz terms (if mezz enabled).
Phase discipline: resolve the property entity and parse any uploaded documents *before* the deal-terms confirmation step; all comp, market, and tenant-intelligence retrieval happens after Purchase Price and financing terms are confirmed (so Sensitivity/Returns/Waterfall math has a stable base case to compute against).
## 4. Retrieval Plan
| Data need | Datamart topic / skill | Grain | Fallback order |
|---|---|---|---|
| Property facts (§2.1) | `property_mfr` identity/physical topics | property | user_fallback if unmatched |
| Tenant/area snapshot (§2.2) | `property_mfr` tenant-demographics topic; `location_walkability`; `zipcode_crime_stats`; `nearby_school_ratings` | property → zip | leave blank with note if unavailable |
| Operational metrics (§2.3) | `property_mfr` rent/occupancy topics | property | uploaded rent roll/T12 outranks datamart |
| T12 statement (§2.8) | `property_mfr` financial topic | property | uploaded T12 outranks datamart |
| Interest rate | `mortgage_rates` | loan_type=matching program, property_type=multifamily | user_required if no match |
| Cap rate / growth pre-fills (§2.5) | forecasting skill (`forecast.py`) | market/submarket | user confirms or overrides |
| Rent Comps (§2.11) | rental-comps skill | zip → submarket → market | — |
| Sales Comps (§2.11) | sales-comps skill | zip → submarket → market | — |
| Market & Demographics (§2.12) | `zipcode`, `county`, `market` topics: population, jobs, households, housing supply/permits | zip/county/market | widen geography if zip coverage thin |
| Tenant Intelligence (§2.13) | `property_mfr`/geography tenant-demographics topics | zip/county/market | same widening rule |
| _Data history (§2.14) | trend/history variants of the rent, occupancy, income/net-worth/FICO/employment topics | property/comp/zip/county/market | — |
Skills invoked: **rental-comps** (writes Rent Comps D:K), **sales-comps** (writes Sales Comps D:I), **forecasting** (pre-fills H27/H28/H29/H30/C33 and the year-by-year override table if staged inputs are used). Retrieval order: identity resolution → document parsing → deal-terms confirmation → comps/market/tenant-intelligence retrieval (batched, one topic call per grain, not one call per cell).
## 5. Assumption Confirmation
Message-text defaults table for everything resolved silently (Assumption | Value | Source), followed by a short confirmation form for only the high-materiality fields: Purchase Price basis check, Exit Cap Rate, Rent/Expense Growth, Interest Rate, and (if enabled) Waterfall terms. Source labels: `user-provided` / `property data` / `forecast engine` / `web research` / `default` / `ai-estimate`.
## 6. Write Rules
1. Read this field guide before writing anything.
2. Clear the declared table regions (§2.11–2.14 write rectangles) before writing new comp/geography rows — even if no prior data was detected, clearing is obligatory for these tables, not conditional.
3. Write only to cells listed as inputs above. Never write to a black (formula) or green (cross-tab link) cell — this includes Assumptions!C27/C28/C30/C48/C50/C57/C58/C61-C80/C87/C88, every Pro Forma/Debt Schedule/Returns Summary/Waterfall/Sensitivity/Summary cell, and the Subject columns on Rent Comps/Sales Comps (column C).
4. Recalculate the workbook after writing.
5. Validate (§7).
6. Deliver.
Any violation of rule 3 stops the run with a one-line error; do not deliver a workbook with a formula cell overwritten.
## 7. Validation
Recalc via the workbook's engine. **Blockers** (fail the run):
- Any new `#REF!`, `#DIV/0!`, `#VALUE!`, `#N/A`, `#NAME?` beyond the expected `#N/A` on unbuilt years past the hold period.
- Sources & Uses check cell (`Sources & Uses!G10`) ≠ "BALANCED".
- Waterfall check row (`Waterfall!C71:M71`) contains "FAIL".
- NOI (`Pro Forma!C26` and forward) ≤ 0 for Year 0.
- Total Operating Expenses ≥ Effective Gross Income for Year 0.
- Vacancy & Credit Loss entered as a positive number (sign-convention violation — must be negative).
- Any protected template default (§2.10 waterfall tiers, §2.4/2.6 template defaults) changed without explicit user confirmation.
**Advisory flags** (surface, don't block):
- Going-in cap rate (`Assumptions!H5`) more than 150bps off the exit cap rate (`Assumptions!C33`) without a stated market-timing thesis.
- Breakeven Occupancy (`Assumptions!C63`) above 92%.
- Going-In DSCR (`Assumptions!C61`) below 1.20x.
- LTV above 75%.
- Levered IRR (`Returns Summary!C21`) materially below unlevered IRR (`Returns Summary!C15`) with positive leverage — flag negative leverage.
## 8. Output Reading and Goal-Seek
Read results only from: `Summary` sheet (one-page roll-up), `Returns Summary!C15/C16/C21/C22/C25/C26`, `Waterfall!C79:D88` (LP/GP IRR, equity multiple, promote split — only if promote enabled), `Sensitivity!C6:H10` and `C15:H19` and `C24:H28` (all pre-built, no manual step).
**Goal-seek protocol** (e.g., "what purchase price gets me to a 15% levered IRR?"): vary only `Assumptions!C26` (Purchase Price) — or whichever single named input the user specifies — through its declared write location, ≤10 iterations, bisection on the target output cell, tolerance ±0.1% on IRR or ±$1 on dollar targets. If unconverged after 10 iterations, report the closest-tested value and its resulting output, not a guessed exact answer.
## 9. Guardrails
- No structural modification of the workbook (no new sheets, no new rows/columns, no renamed tabs).
- No recreating the model from scratch — always fill the existing template.
- No computing IRR, NOI, equity multiple, or any other model output outside the workbook; the workbook's own formulas are the sole calculation authority.
- No writing a datamart value without following the declared fallback order in §4.
- No silent overrides of user-confirmed assumptions on a re-run — ask before changing a value the user already confirmed.
- No writing to Rent Comps/Sales Comps Subject column (C) or to any Pro Forma/Debt Schedule/Returns Summary/Waterfall/Sensitivity/Summary cell — those are100% formula-driven.
# Your Pro Forma Template — What It Does and How We'll Fill It ## What your template does This is a full multifamily acquisition underwriting model: a Year-0 T12 operating statement projected forward through your hold period, a senior (and optional mezzanine) debt schedule, unlevered and levered returns with an exit-cap-rate disposition, an optional GP/LP promote waterfall, a fully automatic sensitivity grid (exit cap × rent growth, and price × LTV), plus supporting exhibits — rent comps, sales comps, and market/tenant benchmarking against your property's zip code, county, and metro. It supports two modes you can toggle: **single-rate growth vs. year-by-year staged growth** (for a phased renovation or lease-up), and **single equity holder vs. GP/LP promote structure** (4-tier waterfall with preferred return and two promote hurdles). ## What we'll pull automatically - **Property facts** — address, unit count, square footage, year built, property type - **Operating history** — T12 revenue and expense lines, occupancy, asking/in-place rents (an uploaded T12 or rent roll always outranks the datamart here) - **Financing benchmarks** — current multifamily mortgage rates by loan program, as a starting point you confirm or override - **Market trends** — rent growth, expense growth, and cap-rate context from our forecasting engine, as pre-fills you confirm - **Comps** — 8 rent comps and up to 6 sale comps, selected and scored automatically - **Area context** — population, jobs, housing supply and permits, and tenant financial-health metrics (income, credit, education) at your zip code, county, and metro, benchmarked against the comps Where the datamart doesn't reach (walkability, crime grade, school ratings), we pull those from dedicated lookups automatically as well. ## What we'll ask you for **Required (only you know these):** - Purchase price - Loan structure, LTV, interest rate confirmation, amortization, and loan term - Hold period - Renovation/CapEx budget, if this is a value-add play - Whether a GP/LP promote structure applies, and if so the GP's co-invest % **Optional — we'll backfill if you skip them:** - Rent growth, expense growth, other-income growth, exit cap rate (forecast-engine pre-fills) - Interest rate (current market rate by loan program) - Mezzanine debt terms, if you enable mezz financing ## House assumptions we'll protect The four-tier waterfall structure — 8% preferred return, 12%/20% and 18%/30% promote hurdles, 40% residual promote — is treated as your house standard and left untouched unless you tell us to change it. Same for closing costs (2%), disposition costs (2%), and reserves ($250/unit/year) — reasonable defaults you can override at any time. ## Anything we flagged - **Sign convention:** Vacancy & Credit Loss must be entered as a *negative* dollar amount on the T12 — the model handles the math, but a positive entry will silently invert your occupancy read. We enforce this automatically. - **Original file:** the copy you uploaded had a fully populated example deal in it (not a blank template). We cleaned it before building this agent — comp tables, market/tenant data, financing and waterfall terms, and the T12 statement were cleared back to blank inputs; the house-standard waterfall percentages and generic fee defaults were left in place. Please spot-check the cleaned file before your first live deal — clearing occasionally catches a value that was actually meant as a reusable default, or misses one that should have been cleared. - **Formulas will show blanks or "—" until first use** — that's expected on a blank template, not a defect. - No macros were present (this was a `.xlsx`, not `.xlsm`), so there was no VBA to triage.
Now let's save and present all three deliverables (cleaned workbook, agent spec, briefing).
RealAI Pro Forma template mapped and ready — population agent generated
Your uploaded workbook is a full multifamily acquisition model (Assumptions → Pro Forma → Debt Schedule → Returns Summary → Waterfall, with Rent/Sales Comps, Market & Demographics, and Tenant Intelligence exhibits, and a fully self-computing Sensitivity grid). It came in populated with a real example deal, not a blank template, so per your choice I cleaned it — comp tables, T12 financials, financing/waterfall terms, and market/tenant data cleared to blank inputs; house-standard defaults (waterfall promote tiers, closing/disposition costs, reserves) left in place.
Files delivered:
Status: ready_with_limitations Limitation: the automated cleaning pass is judgment-based — please spot-check the cleaned workbook (especially the waterfall toggle and financing block) before running your first live deal through it.
A few notes worth flagging directly: the T12's Vacancy & Credit Loss line must be entered as a negative number (the model's sign convention — the agent enforces this automatically going forward), and the Sensitivity sheet needs no manual data-table step — it's fully formula-driven already.
This is a read-only snapshot of a RealAI analysis.