cd /vercel/sandbox && python skills/xlsx/scripts/workbook_profile.py template.xlsx 2>/tmp/err.log | head -200; echo "---ERR---"; cat /tmp/err.log
cd /vercel/sandbox && python skills/xlsx/scripts/workbook_extract.py template.xlsx --sheet _PreparationAudit --columns A,B,C 2>/tmp/e.log echo --- cat /tmp/e.log
cd /vercel/sandbox && python skills/xlsx/scripts/workbook_extract.py template.xlsx --sheet _PreparationAudit --columns A,B,C --output /tmp/pa.csv --mode both 2>&1 echo --- cat /tmp/pa.csv
cat /tmp/pa.values.csv
cd /vercel/sandbox && python skills/xlsx/scripts/workbook_extract.py template.xlsx --sheet Version --columns A,B,C,D,E,F,G --output /tmp/ver --mode values 2>&1 | tail -5 cat /tmp/ver.values.csv
cd /vercel/sandbox && find /tmp -name "ver*"; cat /tmp/ver.csv 2>/dev/null; ls /tmp
cd /vercel/sandbox && python skills/xlsx/scripts/workbook_extract.py template.xlsx --sheet Version --columns A,B,C,D,E,F,G --output /tmp/ver.csv --mode values 2>&1 cat /tmp/ver.csv 2>/dev/null || cat /tmp/ver.csv.csv 2>/dev/null || ls /tmp | grep ver
cd /vercel/sandbox && python skills/xlsx/scripts/workbook_extract.py template.xlsx --sheet Assumptions --columns A,B,C,D,E,F,G,H,I,J --output /tmp/assump.csv --mode both 2>&1 | tail -3 wc -l /tmp/assump.values.csv /tmp/assump.formulas.csv
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=1, max_row=80):
for cell in row:
if cell.value is not None and cell.column_letter in ('B','C','D','E','F','G','H','I'):
font = cell.font
color = font.color.rgb if font.color and font.color.rgb else None
fill = cell.fill.fgColor.rgb if cell.fill and cell.fill.fgColor else None
if isinstance(cell.value,str) and cell.value.startswith('='):
continue
print(cell.coordinate, repr(cell.value), 'font=',color,'fill=',fill)
" 2>&1 | head -100
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template.xlsx', data_only=False)
for name in wb.sheetnames:
ws = wb[name]
cnt=0
for row in ws.iter_rows():
for cell in row:
if cell.value is None: continue
f = cell.font
fill = cell.fill
try:
fc = f.color.rgb if f.color else None
except: fc=None
try:
bgc = fill.fgColor.rgb if fill.fgColor else None
except: bgc=None
if fc in ('FF0000FF','0000FF'):
cnt+=1
print(name, cnt)
"
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template.xlsx', data_only=False)
ws = wb['Returns Summary']
for row in ws.iter_rows():
for cell in row:
if cell.value is None: continue
f = cell.font
try:
fc = f.color.rgb if f.color else None
except: fc=None
if fc in ('FF0000FF','0000FF'):
print(cell.coordinate, repr(cell.value))
"
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template.xlsx', data_only=False)
ws = wb['Returns Summary']
for r in range(60,68):
print(r, [ws.cell(row=r,column=c).value for c in range(1,6)])
"
cd /vercel/sandbox && cat > clean_template.py << 'EOF'
import openpyxl, json
wb = openpyxl.load_workbook('template.xlsx', data_only=False)
sheets_to_clean = ['Assumptions', 'Dev Budget', 'Rent Comps', 'Sales Comps']
cleared = {}
for name in sheets_to_clean:
ws = wb[name]
n = 0
for row in ws.iter_rows():
for cell in row:
if cell.value is None:
continue
f = cell.font
try:
fc = f.color.rgb if f.color else None
except Exception:
fc = None
if fc in ('FF0000FF', '0000FF'):
cell.value = None
n += 1
cleared[name] = n
wb.save('template_clean.xlsx')
print(json.dumps(cleared, indent=2))
EOF
python3 clean_template.pyNow let's confirm the file opens cleanly and recalc without errors (blank inputs will produce errors/blanks, which is expected in an unpopulated template — but let's verify the file isn't corrupted).
cd /vercel/sandbox && python skills/xlsx/scripts/recalc.py template_clean.xlsx 2>&1 | tail -30
cd /vercel/sandbox && python skills/xlsx/scripts/workbook_extract.py template_clean.xlsx --sheet "Dev Budget" --columns A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P --output /tmp/db.csv --mode values 2>&1 | tail -3 cat /tmp/db.csv
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template_clean.xlsx', data_only=False)
ws = wb['Dev Budget']
for r in range(1,70):
b = ws.cell(row=r,column=2).value
c_ = ws.cell(row=r,column=3).value
d = ws.cell(row=r,column=4).value
print(r, repr(b), repr(c_), repr(d))
"
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template_clean.xlsx', data_only=False)
for name in ['Rent Comps','Sales Comps']:
ws = wb[name]
print('===',name)
for r in range(1, min(ws.max_row,40)+1):
vals = [ws.cell(row=r,column=c).value for c in range(1,13)]
if any(v is not None for v in vals):
print(r, vals)
"
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template_clean.xlsx', data_only=False)
ws = wb['Sources & Uses']
for r in range(1, ws.max_row+1):
vals = [ws.cell(row=r,column=c).value for c in range(1,7)]
if any(v is not None for v in vals):
print(r, vals)
"
All fully formula-driven (no inputs) — good, output-only sheet. Now let's check Returns Summary, Waterfall, and Summary sheets for the key output cells.
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template_clean.xlsx', data_only=False)
ws = wb['Returns Summary']
for r in range(1, 70):
vals = [ws.cell(row=r,column=c).value for c in range(1,5)]
if any(v is not None for v in vals):
print(r, vals)
"
Good — key output cells identified. Now let's quickly check the Waterfall and Break-Even output cells, and Summary sheet, plus Assumptions rows 45-384 (unit mix/opex table row ranges) precisely and the exact assumption cell coordinates I need for the field guide.
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template_clean.xlsx', data_only=False)
ws = wb['Waterfall']
for r in range(1, ws.max_row+1):
vals = [ws.cell(row=r,column=c).value for c in range(1,6)]
if any(v is not None for v in vals):
print(r, vals)
"
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template_clean.xlsx', data_only=False)
ws = wb['Assumptions']
for r in range(1,184):
b = ws.cell(row=r,column=2).value
c = ws.cell(row=r,column=3).value
g = ws.cell(row=r,column=7).value
h = ws.cell(row=r,column=8).value
if any(v is not None for v in [b,c,g,h]):
print(r,'|B:',b,'|C:',c,'|G:',g,'|H:',h)
" > /tmp/assump_full.txt
wc -l /tmp/assump_full.txtcd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template_clean.xlsx', data_only=False)
ws = wb['Assumptions']
for r in range(162,194):
vals = [ws.cell(row=r,column=c).value for c in range(2,11)]
print(r, vals)
"
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template_clean.xlsx', data_only=False)
for name in ['Break-Even','Summary']:
ws = wb[name]
print('===',name)
for r in range(1, ws.max_row+1):
vals = [ws.cell(row=r,column=c).value for c in range(1,7)]
if any(v is not None for v in vals):
print(r, vals)
"
I have enough. Now writing the two deliverables.
mkdir -p /vercel/sandbox/output
# Ground-Up Development Model — Population Agent
## 1. Role and Model Summary
This agent populates the **RealAI Ground-Up Development Model** (v3.0), a monthly-engine, annual-presentation development pro forma covering acquisition through stabilized exit for a single ground-up (or major-conversion) project across five supported asset classes (Multifamily, Office, Retail, Industrial, Mixed-Use). The model runs a hidden monthly calculation engine (`_MonthlyCalc`, months 0-132) beneath annual, investor-facing sheets (Assumptions, Dev Budget, Draw Schedule, Sources & Uses, Pro Forma, Debt Schedule, Returns Summary, Waterfall, Sensitivity, Break-Even, Rent Comps, Sales Comps, Summary). All logic is closed-form — no VBA, no Excel Data Tables, no goal-seek — so every output recalculates instantly from the Assumptions and Dev Budget input surfaces.
The dependency narrative: **Assumptions** sets the property, timing, growth, financing, and unit-economics inputs. **Dev Budget** itemizes the hard/soft cost stack that, escalated and spread monthly by the closed-form S-curve logic, drives **Draw Schedule** (which funds the 8-pass construction loan sizing) and rolls up into **Sources & Uses**. The unit mix and OpEx tables on Assumptions drive the revenue and expense engine that produces **Pro Forma** cash flow, which the **Debt Schedule** services (construction, permanent, and optional mezzanine tranches). **Returns Summary** derives unlevered and levered IRR/equity multiple and the development-yield-vs.-market-cap spread; **Waterfall** splits levered cash flow through a 4-tier GP/LP promote; **Sensitivity** and **Break-Even** stress the same engine outputs against exit cap, cost, rent, and delay scenarios. **Summary** is a read-only dashboard of the whole chain. **Rent Comps** and **Sales Comps** benchmark the underwritten rent and exit value against real market comparables and raise a variance flag if the underwriting drifts from them.
## 2. Field Guide
**Sign & scale conventions:** All dollar inputs are entered as positive numbers (no negative-vacancy or negative-expense convention). Percentages are entered as decimals (0.05, not 5). Currency scale is whole dollars. Blue font on light-yellow fill (`FFFFF2CC`) marks every true input cell; black is formula; green is a cross-sheet link — never write to a black or green cell.
### 2.1 Scalar inputs — Assumptions sheet
```yaml
property_overview:
- field: Property Name
cell: Assumptions!C5
source_class: user_required
intent: "Deal identity — free text, drives every comp-sheet and Summary header link."
- field: Address
cell: Assumptions!C6
source_class: user_required
- field: City, State, Zip
cell: Assumptions!C7
source_class: user_required
- field: Property Type
cell: Assumptions!C8
source_class: user_required
options: [Multifamily, Office, Retail, Industrial, Mixed-Use]
intent: "Indexes _Labels for every asset-class-specific noun (Unit/Suite, Rent basis, recovery method) and is the single switch for the revenue-engine path."
- field: Revenue Basis
cell: Assumptions!C9
source_class: user_required
options: [Per Unit, Per SF]
intent: "Per Unit runs the absorption/turnover engine (multifamily-style); Per SF runs the per-suite lease-with-rollover engine (office/retail/industrial-style)."
- field: Units/Suites Count
cell: Assumptions!C10
source_class: datamart_preferred_user_fallback
intent: "Physical unit count; datamart-sourced for an existing/comparable asset, user-required for a de novo design."
- field: Gross Building Area (SF)
cell: Assumptions!C11
source_class: user_required
- field: Site Area (Acres)
cell: Assumptions!C12
source_class: user_required
- field: Net Rentable Area (SF)
cell: Assumptions!C13
source_class: formula
note: "=SUM(J164:J173) — computed from the Unit Mix roster. NEVER write here directly."
- field: Parking Spaces
cell: Assumptions!C14
source_class: user_required
land_and_acquisition:
- field: Acquisition Type
cell: Assumptions!C24
source_class: user_required
options: [Land, Existing Building]
- field: Closing Costs & Title
cell: Assumptions!C26
source_class: user_optional_datamart_backfill
note: "Acquisition Cost itself is a formula pulling the Land line from Dev Budget (C25 = 'Dev Budget'!E42); do not write C25 directly — write the Dev Budget Land/Acquisition budget line instead."
timing_and_exit:
- field: Analysis Start Date
cell: Assumptions!C35
source_class: user_required
- field: Pre-Development (Months)
cell: Assumptions!C36
source_class: user_required
- field: Construction Length (Months)
cell: Assumptions!C37
source_class: user_required
- field: Lease-Up Driven By
cell: Assumptions!C38
source_class: user_required
options: [Pace, Length]
- field: Lease-Up Pace (Units/Mo)
cell: Assumptions!C39
source_class: ai_estimate
intent: "Used when Lease-Up Driven By = Pace. Forecasting-skill-informed if the market has absorption comps; otherwise ai_estimate flagged for confirmation."
- field: Lease-Up Length (Input, Mo)
cell: Assumptions!C40
source_class: ai_estimate
note: "Used only when Lease-Up Driven By = Length."
- field: Absorption Curve
cell: Assumptions!C41
source_class: template_default
options: [S-Curve]
note: "Per-Unit revenue path only."
- field: Absorption Steepness (1-10)
cell: Assumptions!C42
source_class: template_default
default: 4
- field: Target Stabilized Occupancy
cell: Assumptions!C43
source_class: datamart_preferred_user_fallback
intent: "The single occupancy lever in v3.0 (structural vacancy was removed). Pull comparable stabilized occupancy from property_mfr market comps; user override otherwise."
- field: Exit Strategy
cell: Assumptions!C44
source_class: user_required
options: [Hold to Year N]
- field: Hold Period (Years)
cell: Assumptions!C45
source_class: user_required
- field: Months After Stabilization
cell: Assumptions!C46
source_class: user_optional_datamart_backfill
development_cost_drivers:
- field: Hard Cost Escalation (%/Yr)
cell: Assumptions!C59
source_class: datamart_preferred_user_fallback
intent: "Route through the forecasting skill (construction-cost trend) as a pre-fill the user confirms."
- field: Contingency (% of Hard Cost)
cell: Assumptions!C62
source_class: user_optional_datamart_backfill
default_note: "5% is a reasonable house default absent user direction."
- field: Developer Fee (% of Total Cost)
cell: Assumptions!C63
source_class: user_optional_datamart_backfill
exit_disposition:
- field: Exit Cap Rate
cell: Assumptions!C66
source_class: datamart_preferred_user_fallback
intent: "Forecasting-skill cap-rate band position for the asset class/market at the modeled exit year; user may override."
- field: Selling Costs (% of Value)
cell: Assumptions!C67
source_class: template_default
- field: Market Cap Rate (Stabilized)
cell: Assumptions!C68
source_class: datamart_preferred_user_fallback
intent: "Feeds the going-in development yield spread — pull current stabilized cap rate for the asset class/submarket."
construction_financing_senior:
- field: Senior Loan-to-Cost (Target)
cell: Assumptions!C75
source_class: user_required
- field: Rate Structure
cell: Assumptions!C76
source_class: user_required
options: [Fixed, SOFR + Spread]
- field: Fixed Rate
cell: Assumptions!C78
source_class: user_required
note: "Used when Rate Structure = Fixed."
- field: SOFR (Assumed)
cell: Assumptions!C79
source_class: datamart_preferred_user_fallback
intent: "Pull from national_metrics_monthly / mortgage_rates; user may override."
- field: Spread (bps)
cell: Assumptions!C80
source_class: user_required
- field: Rate Floor
cell: Assumptions!C81
source_class: user_optional_datamart_backfill
- field: Origination Fee (% of Loan)
cell: Assumptions!C83
source_class: user_optional_datamart_backfill
- field: Other Financing Costs ($)
cell: Assumptions!C84
source_class: user_optional_datamart_backfill
- field: "% of Lease-Up Income to Offset Int."
cell: Assumptions!C85
source_class: template_default
- field: Draw Order
cell: Assumptions!C86
source_class: user_required
options: [Equity First, Pari Passu, Loan First]
construction_financing_mezz:
- field: Mezzanine Enabled
cell: Assumptions!C98
source_class: user_required
options: [Yes, No]
- field: Total LTC (Senior + Mezz)
cell: Assumptions!C100
source_class: user_required
note: "Only meaningful when Mezzanine Enabled = Yes."
- field: Mezzanine Rate (Accrued)
cell: Assumptions!C101
source_class: user_required
- field: Mezzanine Fee (% of Loan)
cell: Assumptions!C102
source_class: user_required
permanent_financing:
- field: Sizing Method
cell: Assumptions!C108
source_class: user_required
options: [LTV Only, DSCR Only, Lesser of LTV & DSCR]
- field: Loan-to-Value (%)
cell: Assumptions!C110
source_class: user_required
- field: Minimum DSCR
cell: Assumptions!C111
source_class: user_required
- field: Interest Rate (Annual)
cell: Assumptions!C112
source_class: datamart_preferred_user_fallback
intent: "Pull from mortgage_rates for the asset class/loan program; user may override."
- field: Interest-Only Period (Mo)
cell: Assumptions!C113
source_class: user_required
- field: Amortization (Years)
cell: Assumptions!C114
source_class: user_required
- field: Origination Fee (% of Loan)
cell: Assumptions!C115
source_class: user_optional_datamart_backfill
permanent_mezzanine:
- field: Perm Mezzanine Enabled
cell: Assumptions!C126
source_class: user_required
- field: Total LTV (Perm + Mezz)
cell: Assumptions!C128
source_class: user_required
- field: Minimum Combined DSCR
cell: Assumptions!C129
source_class: user_required
- field: Perm Mezzanine Rate
cell: Assumptions!C130
source_class: user_required
- field: Perm Mezzanine Fee (%)
cell: Assumptions!C131
source_class: user_required
waterfall_structure:
- field: Co-Invest / Promote Structure
cell: Assumptions!C140
source_class: user_required
options: [Yes, No]
intent: "If No, waterfall is bypassed — 100% to equity holder."
- field: GP Co-Invest %
cell: Assumptions!C142
source_class: user_required
- field: Tier 1 Preferred Return
cell: Assumptions!C146
source_class: user_required
- field: Tier 2 Hurdle IRR
cell: Assumptions!C147
source_class: user_required
- field: Tier 2 GP Promote %
cell: Assumptions!C148
source_class: user_required
- field: Tier 3 Hurdle IRR
cell: Assumptions!C149
source_class: user_required
- field: Tier 3 GP Promote %
cell: Assumptions!C150
source_class: user_required
- field: Tier 4 GP Promote % (Residual)
cell: Assumptions!C151
source_class: user_required
growth:
- field: Use Staged Inputs
cell: Assumptions!H28
source_class: user_required
options: [No, Yes]
intent: "No = single flat rate per series (H31:H33 below); Yes = the year-by-year override table at Assumptions!C156:J158 is used instead."
- field: Market Rent Growth (%/Yr)
cell: Assumptions!H31
source_class: datamart_preferred_user_fallback
intent: "Route through the forecasting skill for the market/asset class."
- field: Other Income Growth (%/Yr)
cell: Assumptions!H32
source_class: template_default
- field: Expense Growth (%/Yr)
cell: Assumptions!H33
source_class: datamart_preferred_user_fallback
- field: Growth Begins (Month)
cell: Assumptions!H34
source_class: template_default
- field: Staged growth override table
range: "Assumptions!C156:J158 (rows: Market Rent / Other Income / Expense growth; cols C-J = Year 1-8)"
source_class: user_optional_datamart_backfill
note: "Ignored unless Use Staged Inputs = Yes."
stabilized_operations:
- field: Credit Loss / Bad Debt (%)
cell: Assumptions!H37
source_class: datamart_preferred_user_fallback
- field: Concessions — Stabilized (%)
cell: Assumptions!H38
source_class: datamart_preferred_user_fallback
- field: Concessions — Lease-Up (%)
cell: Assumptions!H39
source_class: user_optional_datamart_backfill
- field: Loss to Lease (% of Occ. Rent)
cell: Assumptions!H40
source_class: datamart_preferred_user_fallback
- field: Other Income ($/Unit or SF/Mo)
cell: Assumptions!H41
source_class: datamart_preferred_user_fallback
- field: Replacement Reserves ($/Unit/Yr)
cell: Assumptions!H42
source_class: template_default
- field: Recurring CapEx ($/Unit/Yr)
cell: Assumptions!H43
source_class: template_default
leasing_turnover_recoveries:
- field: Recovery Method
cell: Assumptions!H46
source_class: user_required
options: ["NNN (Full Recovery)", "Base Year Stop", "Modified Gross", "Gross (No Recovery)"]
intent: "Recovery Code (H47, formula) drives the recoveries calc; H48 shows the asset-class-suggested default from _Labels for the user's reference."
- field: Recovery Ratio (Modified Gross)
cell: Assumptions!H49
source_class: user_optional_datamart_backfill
- field: Annual Turnover Rate (%) [Per-Unit path]
cell: Assumptions!H52
source_class: datamart_preferred_user_fallback
- field: Make-Ready Cost per Turn ($) [Per-Unit path]
cell: Assumptions!H53
source_class: user_optional_datamart_backfill
- field: Renewal Probability [Per-SF path]
cell: Assumptions!H55
source_class: user_optional_datamart_backfill
- field: Downtime on Rollover (Mo) [Per-SF path]
cell: Assumptions!H56
source_class: user_optional_datamart_backfill
- field: TI — New Lease ($/SF) [Per-SF path]
cell: Assumptions!H57
source_class: user_optional_datamart_backfill
- field: TI — Renewal ($/SF) [Per-SF path]
cell: Assumptions!H58
source_class: user_optional_datamart_backfill
- field: LC — New (% of Lease Value) [Per-SF path]
cell: Assumptions!H59
source_class: template_default
- field: LC — Renewal (%) [Per-SF path]
cell: Assumptions!H60
source_class: template_default
- field: Rollover Rent (% of Market) [Per-SF path]
cell: Assumptions!H61
source_class: template_default
```
### 2.2 Table inputs
**Unit Mix / Suite Roster** — `Assumptions!B164:J173` (10 rows, one per unit type/suite type; row 174 = Total/Weighted Average, all formulas)
| Column | Field | Type |
|---|---|---|
| B164:B173 | Unit Type label | text, user_required |
| C164:C173 | Count | number, user_required |
| D164:D173 | Size (SF) | number, user_required |
| E164:E173 | Rent ($/Unit/Mo or $/SF/Yr per Revenue Basis) | number, datamart_preferred_user_fallback (rental-comps skill) |
| F164:F173 | Lease Start (Mo) | number, user_optional (Per-SF path only) |
| G164:G173 | Term (Mo) | number, user_optional (Per-SF path only) |
| H164:H173 | Free Rent (Mo) | number, user_optional |
| I164:I173 | Escalation (%/Yr) | number, template_default |
| J164:J173 | Total SF | formula — do not write |
Sum of C164:C173 must equal Assumptions!C10 (Units Count) — write the roster first, then the count field derives structural checks against it (`Roster Total = NRA` check at Assumptions!G72/H72).
**Operating Expenses — Stabilized, Untrended** — `Assumptions!B180:I189` (10 line rows; row 190 = Total, formula)
| Column | Field | Type |
|---|---|---|
| B180:B189 | Expense Line label | text, user_required |
| C180:C189 | Basis | enum: `$ / Unit / Yr`, `$ / SF / Yr`, `% of EGI`, user_required |
| D180:D189 | Input value | number, datamart_preferred_user_fallback (property_mfr T12 comps) |
| I180:I189 | Recoverable? | enum: Yes/No, user_required (drives Recovery Method calc) |
| E,F,G,H (180:189) | $/Year, per-metric, per-SF, %EGI | formulas — do not write |
**Staged Growth Overrides** — `Assumptions!C156:J158`, columns C-J = Year 1-8, rows = Market Rent Growth / Other Income Growth / Expense Growth. Only populate when `Use Staged Inputs` (H28) = Yes.
### 2.3 Development Budget — Dev Budget sheet
Seven category blocks, each a header row (category label, pre-filled, do not overwrite) followed by 6 line-item rows:
| Category | Header row | Item rows |
|---|---|---|
| 1. Construction Costs | 6 | 7-12 |
| 2. Architecture & Engineering | 13 | 14-19 |
| 3. Site Improvements | 20 | 21-26 |
| 4. Soft Costs | 27 | 28-33 |
| 5. FF&E | 34 | 35-40 |
| 6. Land / Acquisition (label toggles by Acquisition Type) | 41 | 42-47 |
| 7. Other & Contingency | 48 | 49-54 |
Per item row, columns B-P: **B** Budget Item (text, user_required), **C** Basis (enum: `$ Total`, `% of Hard Cost`, `% of Total Cost`, `$/Unit`; user_required), **D** Input (number, user_required — this is the amount or rate depending on Basis), **H** Escalate? (Yes/No, user_optional), **I** Phase (enum: Pre-Development/Construction, user_required), **J** Start (Mo) (offset within Phase, user_required), **K** Length (Mo) (user_required), **L** Method (S-Curve / Straight-Line, user_optional), **M** Steepness (1-10, template_default). Columns E, F, G, N, O, P are formulas (Amount, $/Unit, % of Total, Start/End Absolute month, Tie-Out) — never write to them. The Land/Acquisition category's total (row 41, column E) is what Assumptions!C25 (Acquisition Cost) reads — do not write Assumptions!C25 directly.
At least one line item per category is required for the category subtotal to be meaningful; leave unused item rows within a category blank (formulas resolve to $0/"—" via IFERROR).
### 2.4 Comp tables (populated via skills, not the user)
**Rent Comps** — `Rent Comps!D6:I13` (facts: 6 comps) + `Rent Comps!D14:I14` (avg market rent) + `Rent Comps!C19` (single "Adjustment for New Construction %" input). Subject column C is fully formula-linked to Assumptions.
**Sales Comps** — `Sales Comps!D6:I13` (facts), `D17:I17` (comp sale price — input), `D21:D25`→`I21:I25` (adjustment %s: Time/Size/Year Built/Location/Market Conditions — input), `D27:I27` (weight % — input, must sum to 100% across populated comps). All price-per-unit, adjusted-price, and concluded-value cells (rows 18, 26, 28, 31-34) are formulas.
## 3. Intake Design
One combined opening form:
1. **Property/site** — `property_place_search` (address or parcel) or free-text description if unbuilt/off-market land.
2. **Documents** (optional, multi-file) — site plan, cost budget, appraisal, market study if available (routes through the document-reconciliation protocol where a document conflicts with datamart values).
3. **Property Type** — select (Multifamily / Office / Retail / Industrial / Mixed-Use).
4. **Revenue Basis** — select (Per Unit / Per SF) — default by Property Type (Multifamily → Per Unit; others → Per SF), user may override.
5. **Deal thesis** — free text (units/suites count target if known, target hold period, target return).
6. **Deal terms** (batched, only if the user wants to specify now — otherwise deferred to gap-fill): total dev cost target or $/unit budget, financing structure (senior LTC, rates), promote structure.
After identity resolution and any document parsing, run retrieval (Section 4), then a single targeted gap-fill form covering only unresolved `user_required` fields (financing terms, waterfall tiers, hold period, exit strategy, and any unit-mix/budget line items the datamart/documents could not supply).
## 4. Retrieval Plan
Phase order: (1) resolve property identity and parse any uploaded documents; (2) confirm deal basics with the user; (3) run all analytical retrieval below; (4) present the assumption-confirmation summary; (5) write and validate.
| Field(s) | Source | Grain | Fallback order |
|---|---|---|---|
| Units count, GBA, NRA, year built, unit mix (existing/comparable asset) | `property_mfr` topics | property / submarket | property → submarket comp median → user |
| Unit-type rents (Assumptions unit mix col E) | `rental-comps` skill | submarket | comps → market → user |
| OpEx line items (Assumptions D180:D189) | `property_mfr` T12 topics, submarket comp benchmarks | submarket/market | comps → market avg → user |
| Target Stabilized Occupancy | `property_mfr` occupancy topics | submarket | submarket → market → user |
| Market Rent Growth, Other Income Growth, Expense Growth, Hard Cost Escalation | `forecasting` skill (`scripts/forecast.py`) | market | engine output is the pre-fill; user confirms/overrides |
| Exit Cap Rate, Market Cap Rate (Stabilized) | `forecasting` skill cap-rate band position | asset class / market | engine → user override |
| SOFR (Assumed), Permanent Interest Rate | `mortgage_rates`, `national_metrics_monthly` | national/loan program | live rate → user override |
| Sales Comps table | `sales-comps` skill | writes to `Sales Comps!D6:I34` write rectangle | — |
| Rent Comps table | `rental-comps` skill | writes to `Rent Comps!D6:I19` write rectangle | — |
| Everything else in Section 2 marked `user_required` | user intake / gap-fill form | — | no fallback — ask |
## 5. Assumption Confirmation
Present a message-text defaults table (Assumption | Value | Source) for everything resolved silently — source labels: `user-provided` / `property data` / `forecast engine` / `web research` / `default`. Follow with a short confirmation form for only the high-materiality fields: Exit Cap Rate, Market Cap Rate, Hard Cost Escalation, Market Rent Growth, Target Stabilized Occupancy, and the full financing/waterfall structure. Do not re-ask about anything the user already specified in intake.
## 6. Write Rules
1. Read the field guide (Section 2) before any write.
2. Clear the declared table regions (Unit Mix rows 164-173, OpEx rows 180-189, Dev Budget item rows per category, comp write rectangles) before writing — even if no prior data is present, this guarantees no stale row bleeds across categories.
3. Write scalar inputs first (Assumptions, financing, waterfall), then tables (Unit Mix, OpEx, Dev Budget, comps) — table writes are obligatory regardless of whether clearing found anything.
4. Recalculate the workbook (LibreOffice headless / `recalc.py`).
5. Validate (Section 7).
6. Deliver.
Never write to a black-font formula cell, a green-font cross-sheet link, a protected template default (Recovery Ratio suggestion cells, `_Labels`, `_MonthlyCalc`, `_SensCalc`, `_PreparationAudit` — all hidden and off-limits), or any cell outside this field guide's declared write coordinates.
## 7. Validation
Recalculate and read the model's own built-in checks (all formulas, all must read PASS/BALANCED/CONVERGED/ON TARGET/OK):
| Check | Cell |
|---|---|
| Sources & Uses Balance | `Assumptions!H64` |
| Budget Spread Tie-Out | `Assumptions!H65` |
| Loan Sizing Convergence | `Assumptions!H66` |
| LTC on Target | `Assumptions!H67` |
| Unlevered CF: Monthly = Annual | `Assumptions!H68` |
| Levered CF: Monthly = Annual | `Assumptions!H69` |
| Per-Year Monthly = Annual | `Assumptions!H70` |
| Waterfall LP + GP Tie-Out | `Assumptions!H71` |
| Roster Total = NRA | `Assumptions!H72` |
| OpEx Table Ties to Model | `Assumptions!H73` |
| Lease-Up Pace Plausible | `Assumptions!H74` |
| Total LTC >= Senior LTC | `Assumptions!H75` |
| Dev Budget per-line/category tie-out | `Dev Budget!P` column (OK/INCOMPLETE per row) |
**Blockers (stop, do not deliver):** any check above reading FAIL/REVIEW/IMBALANCED/INCOMPLETE; any new `#REF!`, `#DIV/0!`, `#VALUE!`, `#N/A`, `#NAME?`; NOI (`_MonthlyCalc!C317` stabilized NOI) ≤ 0; Total Operating Expenses (`Assumptions!E190`) ≥ Effective Gross Income; Unit Mix roster count sum ≠ Units Count input; any Dev Budget category with all-zero item rows under a non-zero category total.
**Advisory flags (surface, do not block):** Development Spread (Trended/Untrended) inside ±0 bps of zero; Stabilized DSCR — Senior below 1.20x; LTC — Total Debt above 80%; Break-Even Rent (% of Underwritten) above 100% (means the deal doesn't clear its own debt covenant at underwritten rent); Break-Even Cost Overrun negative (already over budget at zero tolerance); Levered IRR at 6-month delay (`Break-Even!C37`) reading "BELOW HURDLE."
## 8. Output Reading and Goal-Seek
Read results only from: `Returns Summary` (rows 37-69: unlevered/levered IRR, equity multiple, development yield and spread, residual land value), `Waterfall!C68:E72` (LP/GP equity, profit, IRR, equity multiple) and `Waterfall!C78:C81` (promote breakdown), `Sources & Uses!C18/G18/C21-C27`, `Summary` (dashboard mirror of the above — cite the underlying sheet, not Summary, since Summary is itself a formula link), `Break-Even` (rows 11, 12, 20, 21, 29, 35-39).
**Goal-seek protocol** (e.g., "what land price clears a 150bps development spread," "what rent clears a 1.25x DSCR"): vary only the single named input through its approved write cell in Section 2 (e.g., Dev Budget Land item `D`-column input, or Assumptions unit-mix rent column E), leave every other cell untouched, recalculate, and check the target output cell. ≤10 iterations, binary search, tolerance = the smaller of $1,000 or 0.1% of the target value. Note the model already computes several of these in closed form without iteration — check `Returns Summary!C69` (Supportable Land/Acquisition Basis) and `Break-Even` rows 11/12/20/21/29 before running a manual goal-seek; if a closed-form answer already exists, read it instead of iterating. Report the closest-tested value and its resulting output if the target tolerance is not converged within 10 iterations.
## 9. Guardrails
- No structural modification of the workbook (no added/removed rows, columns, or sheets).
- Never recreate the model from scratch — populate the existing template only.
- Never compute IRR, equity multiple, NOI, development yield, DSCR, or any other model output outside the workbook — the workbook's own formulas are the sole calculation authority.
- Never write a datamart-sourced value without following the declared fallback order in Section 4.
- Never silently override a value the user already confirmed in an earlier turn.
- Never write to `_MonthlyCalc`, `_SensCalc`, `_Labels`, `_PreparationAudit`, or any formula/cross-sheet-link cell.
Now the briefing:
# Your Development Model, Explained ## What your template does This is a ground-up development pro forma that takes a project from acquisition/pre-development through construction, lease-up, stabilization, and a modeled exit — supporting Multifamily, Office, Retail, Industrial, or Mixed-Use. It sizes your construction loan (senior and, if you want it, a mezzanine tranche) and a permanent take-out loan, builds a monthly cash flow engine, and rolls that up into annual investor-facing sheets. It reports: unlevered and levered IRR and equity multiple, the development yield vs. market cap rate spread (the core "did we build it cheaper than we could buy it" test), a full GP/LP promote waterfall across four tiers, and a break-even analysis (what rent, occupancy, cost overrun, or exit cap would erase your return). It also carries rent and sales comp tables that flag when your underwriting drifts too far from the market. Everything runs on formulas — no macros, no manual "run the loan sizing" button, no Excel what-if tables to remember to refresh. Change an assumption and every downstream number — the loan size, the draw schedule, the IRR, the promote — updates immediately. ## What we'll pull automatically - **Property facts** (unit count, building area, year built) — from the datamart if this is an existing or comparable asset; from you if it's a de novo design. - **Market rents by unit type** — from rent comps in your submarket, written straight into the Unit Mix table. - **Operating expenses** — benchmarked against comparable properties' actual T12 operating history. - **Stabilized occupancy** — from submarket lease-up/occupancy data. - **Rent growth, expense growth, and hard-cost escalation** — from our forecasting engine, calibrated to your market and asset class (you confirm or override). - **Exit cap rate and stabilized market cap rate** — from current cap-rate positioning for your asset class and market. - **Financing rates** (SOFR, permanent loan rate) — from live mortgage-rate data (you confirm or override). - **Sale comps** — for your exit valuation cross-check. Every one of these has a fallback to your own number if you'd rather set it yourself, or if the market doesn't have good data at your location. ## What we'll ask you for These can't come from any dataset — they're your deal, not the market's: - Deal terms: senior/mezzanine loan-to-cost, interest rate structure, draw order, permanent loan sizing method, interest-only period, amortization. - GP/LP structure: co-invest split and all four promote tiers (preferred return, hurdle IRRs, promote percentages). - Timing: pre-development length, construction length, hold period, exit strategy. - The development budget itself — your cost line items by category (construction, A&E, site work, soft costs, FF&E, land, contingency) and how each is basis'd, escalated, and phased. Optional and datamart-backfillable if you skip them: lease-up pace, turnover/rollover assumptions, concessions, loss-to-lease, replacement reserves. ## House assumptions we'll protect A handful of values are template defaults, not deal terms, and we'll leave them alone unless you tell us otherwise: absorption curve shape (S-curve) and steepness, the 5% hard-cost contingency default, selling costs at 2% of value, the 150bps required development spread used in the residual land value calc, and the recovery-ratio default for modified-gross leases. ## Anything we flagged - The `.xlsx` has no macros, no VBA, no Excel Data Tables — everything (construction loan sizing, S-curve draw spread, residual land value, break-even analysis, sensitivity grids) is closed-form. Nothing was stripped; there was nothing to strip. - **Convention to know:** dollar inputs are entered as positive numbers throughout — there's no "enter vacancy as a negative" trick here. Percentages are entered as decimals (0.05, not 5%). - **Convention to know:** the "Revenue Basis" toggle (Per Unit vs. Per SF) changes which leasing engine runs underneath — Per Unit models absorption and turnover (multifamily-style); Per SF models suite-by-suite leases with rollover, TI, and LC (office/retail/industrial-style). Set your Property Type first; we default the basis sensibly from it. - **Data note:** the file we introspected arrived populated with a specific sample deal (a Charlotte, NC multifamily project, its unit mix, its budget, and its comps). We cleaned all of that out before building your agent — the template your agent will populate is fully blank. If you want that sample deal restored as a reference case, let us know and we'll pull it from the original upload. - Known simplifications versus a fully custom build: one hold-period exit only (no mid-hold refinance), annual (not monthly) waterfall distribution, no junior/mezzanine-on-mezzanine stacking, no after-tax module. Sensitivity grids hold the permanent loan at its base size (cost-overrun columns will slightly understate carry as a result). Ready to run this on your first deal — just give us an address or a site description and we'll take it from there.
cd /vercel/sandbox && cat > preflight.py << 'EOF'
import openpyxl, datetime
wb = openpyxl.load_workbook('template_clean.xlsx', data_only=False)
a = wb['Assumptions']
db = wb['Dev Budget']
# Scalars
a['C5'] = 'Test Property'
a['C6'] = '123 Test St'
a['C7'] = 'Testville, TX 75001'
a['C8'] = 'Multifamily'
a['C9'] = 'Per Unit'
a['C10'] = 200
a['C11'] = 210000
a['C12'] = 9.0
a['C14'] = 300
a['C24'] = 'Land'
a['C35'] = datetime.datetime(2026,10,1)
a['C36'] = 6
a['C37'] = 24
a['C38'] = 'Pace'
a['C39'] = 15
a['C40'] = 13
a['C41'] = 'S-Curve'
a['C42'] = 4
a['C43'] = 0.94
a['C44'] = 'Hold to Year N'
a['C45'] = 10
a['C46'] = 6
a['C59'] = 0.035
a['C62'] = 0.05
a['C63'] = 0.03
a['C66'] = 0.0525
a['C67'] = 0.02
a['C68'] = 0.05
a['C75'] = 0.60
a['C76'] = 'SOFR + Spread'
a['C78'] = 0.075
a['C79'] = 0.0325
a['C80'] = 275
a['C81'] = 0.065
a['C83'] = 0.01
a['C84'] = 175000
a['C85'] = 1.0
a['C86'] = 'Equity First'
a['C98'] = 'No'
a['C100'] = 0.75
a['C101'] = 0.12
a['C102'] = 0.02
a['C108'] = 'Lesser of LTV & DSCR'
a['C110'] = 0.65
a['C111'] = 1.25
a['C112'] = 0.0625
a['C113'] = 60
a['C114'] = 30
a['C115'] = 0.01
a['C126'] = 'No'
a['C128'] = 0.80
a['C129'] = 1.10
a['C130'] = 0.10
a['C131'] = 0.015
a['C140'] = 'Yes'
a['C142'] = 0.10
a['C146'] = 0.08
a['C147'] = 0.12
a['C148'] = 0.20
a['C149'] = 0.18
a['C150'] = 0.30
a['C151'] = 0.40
a['H28'] = 'No'
a['H31'] = 0.03
a['H32'] = 0.025
a['H33'] = 0.0275
a['H34'] = 13
a['H37'] = 0.005
a['H38'] = 0.01
a['H39'] = 0.04
a['H40'] = 0.01
a['H41'] = 70
a['H42'] = 300
a['H43'] = 150
a['H46'] = 'Gross (No Recovery)'
a['H49'] = 0.5
a['H52'] = 0.45
a['H53'] = 1400
a['H55'] = 0.7
a['H56'] = 4
a['H57'] = 45
a['H58'] = 15
a['H59'] = 0.06
a['H60'] = 0.03
a['H61'] = 1.0
# Unit mix rows 164-168 (5 types)
unit_rows = [
(164,60,720,1975),
(165,40,800,2125),
(166,70,1075,2545),
(167,20,1180,2720),
(168,10,1375,3150),
]
for r,c,sz,rent in unit_rows:
a.cell(row=r,column=2).value = f"Type {r}"
a.cell(row=r,column=3).value = c
a.cell(row=r,column=4).value = sz
a.cell(row=r,column=5).value = rent
a.cell(row=r,column=9).value = 0.02
# OpEx rows 180-189 (10 lines) - use first 9
opex = [
("Real Estate Taxes","$ / Unit / Yr",2850,"Yes"),
("Insurance","$ / Unit / Yr",1050,"Yes"),
("Utilities","$ / Unit / Yr",800,"Yes"),
("Repairs & Maintenance","$ / Unit / Yr",850,"Yes"),
("Management Fee","% of EGI",0.03,"No"),
("Payroll & Benefits","$ / Unit / Yr",1750,"Yes"),
("General & Administrative","$ / Unit / Yr",425,"No"),
("Advertising & Marketing","$ / Unit / Yr",325,"No"),
("Contract Services","$ / Unit / Yr",450,"Yes"),
]
for i,(label,basis,val,rec) in enumerate(opex):
r = 180+i
a.cell(row=r,column=2).value = label
a.cell(row=r,column=3).value = basis
a.cell(row=r,column=4).value = val
a.cell(row=r,column=9).value = rec
# Dev Budget: one line item per category
budget_items = [
(7,"Hard Construction","$ Total",30000000,"Yes","Construction",1,24,"S-Curve",5),
(14,"Architecture Fee","$ Total",1200000,"No","Pre-Development",1,6,"Straight-Line",5),
(21,"Site Work","$ Total",2500000,"Yes","Construction",1,12,"S-Curve",5),
(28,"Legal & Soft Costs","$ Total",900000,"No","Pre-Development",1,6,"Straight-Line",5),
(35,"FF&E","$ Total",800000,"No","Construction",18,6,"Straight-Line",5),
(42,"Land Purchase","$ Total",5200000,"No","Pre-Development",1,1,"Straight-Line",5),
(49,"Contingency","% of Hard Cost",0.05,"No","Construction",1,24,"S-Curve",5),
]
for r,label,basis,val,esc,phase,start,length,method,steep in budget_items:
db.cell(row=r,column=2).value = label
db.cell(row=r,column=3).value = basis
db.cell(row=r,column=4).value = val
db.cell(row=r,column=8).value = esc
db.cell(row=r,column=9).value = phase
db.cell(row=r,column=10).value = start
db.cell(row=r,column=11).value = length
db.cell(row=r,column=12).value = method
db.cell(row=r,column=13).value = steep
wb.save('template_preflight.xlsx')
print("saved")
EOF
python3 preflight.pycd /vercel/sandbox && python skills/xlsx/scripts/recalc.py template_preflight.xlsx 2>&1 | tail -20
Zero errors with placeholder values written. Now check the checks read PASS, and the economic sniff test (higher land cost → lower development spread).
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template_preflight.xlsx', data_only=True)
a = wb['Assumptions']
checks = ['H64','H65','H66','H67','H68','H69','H70','H71','H72','H73','H74','H75']
for c in checks:
print(c, a[c].value)
rs = wb['Returns Summary']
print('Unlevered IRR', rs['C37'].value)
print('Levered IRR', rs['C44'].value)
print('Dev Spread Trended', rs['C58'].value)
print('Dev Yield Trended', rs['C56'].value)
"
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template_preflight.xlsx', data_only=False)
db = wb['Dev Budget']
db.cell(row=42,column=4).value = 15200000 # raise land cost
wb.save('template_preflight_highland.xlsx')
"
python skills/xlsx/scripts/recalc.py template_preflight_highland.xlsx 2>&1 | tail -10
python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template_preflight_highland.xlsx', data_only=True)
rs = wb['Returns Summary']
print('Dev Spread Trended (high land)', rs['C58'].value)
print('Levered IRR (high land)', rs['C44'].value)
"Confirmed: raising land cost by $10M drops the development spread from 307bps to 151bps and levered IRR from 21.3% to 12.9% — the model moves in the expected direction. All checks pass, zero errors, writes hit no formula cells. Verification complete. Now saving and presenting the two deliverables.
Agent generated from your Ground-Up Development Model template (v3.0)
Your template is a monthly-engine, annual-presentation development pro forma spanning acquisition through stabilized exit, with a closed-form construction loan sizing, S-curve draws, a 4-tier GP/LP promote, sensitivity grids, and a break-even page — no macros, no VBA, no Excel Data Tables anywhere.
One thing before the deliverables: the uploaded file arrived populated with a specific sample deal (a Charlotte, NC multifamily project — unit mix, budget, comps, all filled). Per your instruction, I cleaned it — 628 input cells across Assumptions, Dev Budget, Rent Comps, and Sales Comps blanked, leaving formulas, labels, and the two genuine house defaults (Recovery Ratio suggestion, Required Development Spread) intact. That clean file is included below alongside the two deliverables.
I verified the generated agent's mapping against the model before shipping: wrote placeholder values to every mapped input on a scratch copy, recalculated with zero formula errors, confirmed every model check (Sources & Uses balance, loan convergence, LTC, monthly-vs-annual ties, waterfall tie-out, roster-to-NRA, OpEx tie-out) read clean, and ran an economic sniff test — raising the land line by $10M dropped the trended development spread from 307bps to 151bps and levered IRR from 21.3% to 12.9%, exactly the direction it should move.
Files:
1. RealAI_Development_Model_agent.md (the population agent)
2. RealAI_Development_Model_briefing.md (the user-facing summary)
3. template_clean.xlsx (the cleaned, blank template the agent targets)
Status: ready
This is a read-only snapshot of a RealAI analysis.