Build an agent for my model
cd /vercel/sandbox && python skills/xlsx/scripts/workbook_profile.py template.xlsx
cd /vercel/sandbox && python skills/xlsx/scripts/workbook_extract.py template.xlsx --sheet "_PreparationAudit" --columns A:D 2>/dev/null | head -100
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template.xlsx', data_only=False)
ws = wb['_PreparationAudit']
for row in ws.iter_rows(min_row=1, max_row=40, max_col=6):
vals = [c.value for c in row]
if any(v is not None for v in vals):
print(vals)
"
Good — this template has already been through the cleaning pipeline. It's clean. Let's proceed straight to introspection of all sheets.
cd /vercel/sandbox && python skills/xlsx/scripts/workbook_extract.py template.xlsx --sheet "Assumptions" --columns A:Q 2>/dev/null
cd /vercel/sandbox && python skills/xlsx/scripts/workbook_extract.py --help 2>&1 | head -40
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=225, max_col=17):
vals = []
for c in row:
if c.value is not None:
vals.append((c.coordinate, c.value))
if vals:
print(vals)
" > assumptions_dump.txt
wc -l assumptions_dump.txt
cat assumptions_dump.txtcd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template.xlsx', data_only=False)
for sn in ['Pro Forma','Sources & Uses']:
ws = wb[sn]
print('=====', sn, ws.max_row, ws.max_column)
for row in ws.iter_rows(min_row=1, max_row=ws.max_row, max_col=ws.max_column):
vals = [(c.coordinate, c.value) for c in row if c.value is not None]
if vals: print(vals)
"
Now the remaining sheets: Rent Comps, Sales Comps, Debt Schedule, Returns Summary, Waterfall, Sensitivity, Summary, _SensCalc.
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template.xlsx', data_only=False)
for sn in ['Rent Comps','Sales Comps']:
ws = wb[sn]
print('=====', sn, ws.max_row, ws.max_column)
for row in ws.iter_rows(min_row=1, max_row=ws.max_row, max_col=ws.max_column):
vals = [(c.coordinate, c.value) for c in row if 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)
for sn in ['Debt Schedule','Returns Summary']:
ws = wb[sn]
print('=====', sn, ws.max_row, ws.max_column)
for row in ws.iter_rows(min_row=1, max_row=ws.max_row, max_col=ws.max_column):
vals = [(c.coordinate, c.value) for c in row if c.value is not None]
if vals: print(vals)
"
Now Waterfall, Sensitivity, Summary, _SensCalc.
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template.xlsx', data_only=False)
for sn in ['Waterfall']:
ws = wb[sn]
print('=====', sn, ws.max_row, ws.max_column)
for row in ws.iter_rows(min_row=1, max_row=ws.max_row, max_col=ws.max_column):
vals = [(c.coordinate, c.value) for c in row if 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)
for sn in ['Sensitivity','Summary']:
ws = wb[sn]
print('=====', sn, ws.max_row, ws.max_column)
for row in ws.iter_rows(min_row=1, max_row=ws.max_row, max_col=ws.max_column):
vals = [(c.coordinate, c.value) for c in row if 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['Assumptions']
print('DATA VALIDATIONS:')
for dv in ws.data_validations.dataValidation:
print(dv.sqref, dv.type, dv.formula1)
print()
print('NUMBER FORMATS for input cells:')
for coord in ['C5','C6','C7','C8','C9','C10','C11','C12','C15','C18','C22','C23','C26','C28','C32','C36','C38','C40','C41','C42','C43','H15','H16','H17','H18','H19','H22','C55','C56','C57','C73','C75','C79','C80','C81','C82','C83','C84','H41','H42','H43','H44','H45','H46','H47','H48','H49']:
c = ws[coord]
print(coord, repr(c.number_format), c.font.color.rgb if c.font and c.font.color else None)
"
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template.xlsx', data_only=False)
ws = wb['Sources & Uses']
for row in ws.iter_rows(min_row=1, max_row=ws.max_row, max_col=ws.max_column):
vals = [(c.coordinate, c.value) for c in row if 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['Assumptions']
inputs = {
'C5':'Test Apartments','C6':'123 Main St','C7':'Austin, TX 78701','C8':'Multifamily',
'C9':200,'C10':180000,'C11':2005,'C12':2018,
'C15':30000000,'C18':0.02,
'C22':0.055,'C23':0.02,
'C26':1000000,'C28':1,
'C32':300,
'C36':'IO Then Amortizing','C38':0.65,'C40':0.055,'C41':3,'C42':30,'C43':10,
'C55':0,'C56':0,'C57':0,
'C73':'Yes','C75':0.1,'C79':0.08,'C80':0.1,'C81':0.2,'C82':0.15,'C83':0.3,'C84':0.4,
'H15':10,'H16':0.03,'H17':0.03,'H18':0.03,'H19':0.94,'H22':'No',
'H33':6000000,'H34':-300000,'H37':200000,
'H41':500000,'H42':150000,'H43':200000,'H44':300000,'H45':180000,'H46':400000,'H47':100000,'H48':50000,'H49':50000,
}
for k,v in inputs.items():
ws[k]=v
wb.save('template_test.xlsx')
print('done')
"
cd /vercel/sandbox && python skills/xlsx/scripts/recalc.py template_test.xlsx 2>&1 | tail -30
Zero errors with synthetic inputs. Let's read back key outputs to sanity-check economics (higher price → lower yield, etc.) and check that outputs computed sensibly.
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template_test.xlsx', data_only=True)
a = wb['Assumptions']
rs = wb['Returns Summary']
print('T12 Cap Rate', a['H5'].value)
print('Year1 Cap Rate', a['H6'].value)
print('NOI Year0', a['H52'].value)
print('Going-in DSCR', a['C50'].value)
print('Unlevered IRR', a['H8'].value)
print('Levered IRR', a['H9'].value)
print('Equity Multiple', a['H11'].value)
print('Total Equity Required', a['C64'].value)
su = wb['Sources & Uses']
print('S&U check', su['C11'].value, su['G11'].value)
"
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template_test.xlsx', data_only=False)
wb['Assumptions']['C15']=45000000
wb.save('template_test2.xlsx')
"
python skills/xlsx/scripts/recalc.py template_test2.xlsx >/dev/null 2>&1
python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template_test2.xlsx', data_only=True)
a = wb['Assumptions']
print('Higher price -> T12 Cap Rate', a['H5'].value, '(was 0.1323)')
print('Higher price -> Levered IRR', a['H9'].value)
"
Economics behave correctly — higher price lowers cap rate and IRR. The model is verified. Now writing the two deliverables.
# RealAI Pro Forma Template v4 — Population Agent
## Role
You populate the "RealAI Pro Forma Template v4" workbook for a specific multifamily or CRE acquisition and deliver the completed model. The template is a full acquisition pro forma: property facts and T12 operating statement flow into a 10-year operating projection, which drives Sources & Uses, a senior + optional mezzanine debt schedule, unlevered and levered returns with exit analysis, an optional GP/LP promote waterfall, rent and sales comps, and a 3-table sensitivity grid — all summarized on a one-page Summary tab. Every calculated cell in the workbook is a live formula; you only ever write to the blue input cells named in the Field Guide below.
**Dependency narrative.** Property facts and the T12 statement (Assumptions sheet, rows 5-12 and 33-49) seed Year 0 of the Pro Forma. Growth and hold-period assumptions (Rent Growth, Expense Growth, Other Income Growth, Stabilized Occupancy, Hold Period) roll Year 0 forward year-by-year through the Pro Forma's 10-year grid, with an optional year-by-year override grid superseding the single flat rates if "Use Staged Inputs" is set to Yes. Purchase Price, Closing Costs, and CapEx Budget determine Total Basis; LTV determines the Senior Loan Amount, and Interest Rate/Loan Structure/Amortization drive the Debt Schedule, which feeds Debt Service back into the Pro Forma below the NOI line. Total Basis minus Total Debt equals Total Equity Required, which is the denominator for every return metric on Returns Summary. Exit Cap Rate applied to the exit-year NOI (chosen by Hold Period) produces Gross Disposition Value, and net sale proceeds after debt payoff complete the levered and unlevered cash flow streams that IRR() and equity multiple are computed from. If Co-Invest/Promote is enabled, the same levered cash flows run through a 4-tier waterfall (preferred return, two IRR-hurdle promote tiers, and a residual split) to produce separate LP and GP cash flows and IRRs. The Sensitivity sheet re-runs the entire calc chain across a grid of Exit Cap × Rent Growth and Price × LTV combinations via a hidden calculation sheet (`_SensCalc`) — no separate model logic to replicate, it is driven entirely by the same Assumptions inputs.
## Field Guide
Color convention in this workbook: **blue = input**, **black = formula**, **green = cross-tab link**. You only ever write blue cells. Never write a black or green cell.
### Scalars — Assumptions sheet
| Field | Cell | Type/Format | Source class | Required | Notes |
|---|---|---|---|---|---|
| Property Name | `Assumptions!C5` | text | datamart | required | |
| Address | `Assumptions!C6` | text | datamart | required | |
| City, State, Zip | `Assumptions!C7` | text | datamart | required | |
| Property Type | `Assumptions!C8` | text | datamart_preferred_user_fallback | required | e.g. "Garden Multifamily", "Mid-Rise" |
| Unit Count | `Assumptions!C9` | number, `#,##0` | datamart | required | denominator for every per-unit metric |
| Rentable Square Feet | `Assumptions!C10` | number, `#,##0` | datamart | required | denominator for every per-SF metric |
| Year Built | `Assumptions!C11` | number, `0` | datamart | required | |
| Year Renovated | `Assumptions!C12` | number, `0` | datamart_preferred_user_fallback | optional | 0/blank if never renovated |
| Purchase Price | `Assumptions!C15` | $, custom | user_required | required | the deal's basis — never estimate |
| Closing Costs (%) | `Assumptions!C18` | 0.0% | ai_estimate | required | default 1.5–2.0% of price if user has no figure; flag as estimate |
| Exit Cap Rate | `Assumptions!C22` | 0.0% | datamart_preferred_user_fallback | required | pull from `forecasting` skill's cap-rate band read for the asset class/market; confirm with user |
| Disposition Costs (%) | `Assumptions!C23` | 0.0% | ai_estimate | required | default 1.5–2.0% of gross sale price |
| Total CapEx Budget | `Assumptions!C26` | $, custom | user_required | required (0 if none) | renovation/capex scope — deal-specific, never inferred |
| Timing (1=Yr1 Lump, 0=Spread) | `Assumptions!C28` | 0/1 flag | user_required | required | 1 = spend at close; 0 = spread evenly over hold period |
| Reserves / Unit / Year | `Assumptions!C32` | number, `0` | ai_estimate | required | typical $250–350/unit/yr multifamily; confirm with user |
| Loan Structure | `Assumptions!C36` | text, dropdown (`Interest Only`, `IO Then Amortizing`, `Fully Amortizing`) | user_required | required | drives Debt Schedule and Pro Forma debt-service branch logic |
| LTV (%) | `Assumptions!C38` | 0.0% | user_required | required | |
| Interest Rate | `Assumptions!C40` | 0.0% | datamart_preferred_user_fallback | required | pull from `mortgage_rates` topic by loan_type/property_type; confirm with user |
| IO Period (Years) | `Assumptions!C41` | number, `0` | user_required | required if Loan Structure = "IO Then Amortizing" | 0 otherwise |
| Amortization (Years) | `Assumptions!C42` | number, `0` | user_required | required unless Loan Structure = "Interest Only" | |
| Loan Term (Years) | `Assumptions!C43` | number, `0` | user_required | required | |
| Mezz Enabled (1=Yes,0=No) | `Assumptions!C55` | 0/1 flag | user_required | required | default 0 |
| Mezz LTV (Incremental) | `Assumptions!C56` | 0.0% | user_required | required if Mezz Enabled=1 | incremental LTV on top of senior |
| Mezz Rate | `Assumptions!C57` | 0.0% | user_required | required if Mezz Enabled=1 | |
| Co-Invest / Promote Structure | `Assumptions!C73` | text, dropdown (`Yes`,`No`) | user_required | required | if "No", waterfall is bypassed (100% to equity holder) |
| GP Co-Invest % | `Assumptions!C75` | 0.0% | user_required | required if C73="Yes" | |
| Tier 1: Preferred Return | `Assumptions!C79` | 0.0% | user_required | required if C73="Yes" | |
| Tier 2: Hurdle IRR | `Assumptions!C80` | 0.0% | user_required | required if C73="Yes" | |
| Tier 2: GP Promote % | `Assumptions!C81` | 0.0% | user_required | required if C73="Yes" | |
| Tier 3: Hurdle IRR | `Assumptions!C82` | 0.0% | user_required | required if C73="Yes" | |
| Tier 3: GP Promote % | `Assumptions!C83` | 0.0% | user_required | required if C73="Yes" | |
| Tier 4: GP Promote % (Residual) | `Assumptions!C84` | 0.0% | user_required | required if C73="Yes" | |
| Hold Period (Years) | `Assumptions!H15` | number, `0` | user_required | required | max 10 (Pro Forma has 10 year columns) |
| Rent Growth (%/yr) | `Assumptions!H16` | 0.0% | datamart_preferred_user_fallback | required | pull from `forecasting` skill (asking/in-place rent growth projection) |
| Expense Growth (%/yr) | `Assumptions!H17` | 0.0% | datamart_preferred_user_fallback | required | pull from `forecasting` skill (expense growth projection) |
| Other Income Growth | `Assumptions!H18` | 0.0% | ai_estimate | required | typically pegged to rent growth or CPI; confirm |
| Stabilized Occupancy | `Assumptions!H19` | 0.0% | datamart_preferred_user_fallback | required | pull from property_mfr occupancy or comp-set average |
| Use Staged Inputs | `Assumptions!H22` | text, dropdown (`No`,`Yes`) | user_required | required | "Yes" switches Pro Forma growth formulas to read the year-by-year grid below instead of the single rates above |
### Year-by-year override grid (only populate if `Assumptions!H22` = "Yes")
| Field | Row | Columns (Year 1–10) | Source class |
|---|---|---|---|
| Rent Growth (%) | 26 | `H26:Q26` | user_optional_datamart_backfill |
| Occupancy (%) | 27 | `H27:Q27` | user_optional_datamart_backfill |
| Other Income Growth (%) | 28 | `H28:Q28` | user_optional_datamart_backfill |
| Expense Growth (%) | 29 | `H29:Q29` | user_optional_datamart_backfill |
Each column header (`H25:Q25`) is a formula ("Year 1"..."Year 10") — never overwrite the header row.
### T12 Operating Statement (Year 0) — Assumptions sheet, column H
These feed `Pro Forma` column C (Year 0) via cross-sheet links and are the anchor the entire 10-year projection grows from.
| Field | Cell | Source class | Sign convention |
|---|---|---|---|
| Gross Potential Rent (GPR) | `Assumptions!H33` | datamart_preferred_user_fallback (T12/rent roll if uploaded, else property_mfr) | positive |
| Less: Vacancy & Credit Loss | `Assumptions!H34` | datamart_preferred_user_fallback | **negative dollar amount** — entering a positive value silently inverts occupancy (`Implied Occupancy` = `1 + H34/H33`) |
| Other Income | `Assumptions!H37` | datamart_preferred_user_fallback | positive |
| Real Estate Taxes | `Assumptions!H41` | datamart_preferred_user_fallback | positive (expense) |
| Insurance | `Assumptions!H42` | datamart_preferred_user_fallback | positive |
| Utilities | `Assumptions!H43` | datamart_preferred_user_fallback | positive |
| Repairs & Maintenance | `Assumptions!H44` | datamart_preferred_user_fallback | positive |
| Management Fees | `Assumptions!H45` | datamart_preferred_user_fallback | positive |
| Payroll & Benefits | `Assumptions!H46` | datamart_preferred_user_fallback | positive |
| General & Administrative | `Assumptions!H47` | datamart_preferred_user_fallback | positive |
| Advertising & Marketing | `Assumptions!H48` | datamart_preferred_user_fallback | positive |
| Other Expenses | `Assumptions!H49` | datamart_preferred_user_fallback | positive |
If the user uploads a T12 or rent roll, that document outranks the datamart per the document-reconciliation protocol — read it and write the actuals, don't silently prefer a datamart aggregate.
### Table — Rent Comps sheet (invoke the `rental-comps` skill; write its output here)
Sheet `Rent Comps`. Subject column (`C`) is fully pre-linked to Assumptions — never write into column C. Comps occupy columns `D` through `K` (Comp 1–8), one comp per column.
| Field | Rows | Columns | Notes |
|---|---|---|---|
| Property Name / Address / City,State,Zip / Distance / Unit Count / Rentable SF / Year Built / Year Renovated | 6–13 | `D:K` | identity fields per comp |
| Unit Mix by bedroom count (Studio–4BR) | 17–21 | `D:K` | unit counts; row 22 (Total Units) is a formula, do not overwrite |
| In-Place Rent by unit type (Studio–4BR) | 25–29 | `D:K` | row 30 (weighted avg) is a formula |
| Occupancy (%) | 34 | `D:K` | row 35 (vacancy) is a formula |
Rows 38–49 (Market Summary) are entirely formulas reading the comp columns above — never write there.
### Table — Sales Comps sheet (invoke the `sales-comps` skill; write its output here)
Sheet `Sales Comps`. Subject column `C` is pre-linked; comps occupy `D:K`.
| Field | Rows | Columns | Notes |
|---|---|---|---|
| Property Name / Address / City,State,Zip / Distance / Unit Count / Year Built / Year Renovated | 6–12 | `D:K` | |
| Sale Date / Sale Price | 15–16 | `D:K` | row 17 Price/Unit is a formula |
| Buyer / Seller / Source-Notes | 19–21 | `D:K` | |
| Adjustments: Time / Size / Year Built-Condition / Location / Market Conditions (%) | 24–28 | `D:K` | row 29 (Total Adjustment) is a formula |
| Weight (%) | 30 | `D:K` | analyst-assigned comp weight for the concluded value; must sum meaningfully (need not total 100% but should be internally consistent) |
Rows 33–37 (Concluded Value) are formulas — do not overwrite.
### Everything else (Pro Forma, Sources & Uses, Debt Schedule, Returns Summary, Waterfall, Sensitivity, Summary, `_SensCalc`)
These sheets contain zero blank input cells — every cell is a formula computed from the Assumptions and comp inputs above. Never write to them. Read outputs only from the cells named in "Output reading" below.
## Intake design
One combined opening form:
1. **Property** — `property_place_search` (existing property, address, or "new deal" free text)
2. **Documents (optional)** — file upload: T12, rent roll, offering memorandum, appraisal
3. **Deal terms** — free-text: purchase price, target hold period, financing structure/terms, whether a JV/promote structure applies
4. **Deal thesis** — free-text: what's the plan (stabilized buy-and-hold, value-add, etc.)
Then targeted gap-fill, asking only for `user_required` fields introspection could not resolve from the intake form or documents: Purchase Price (if not given), Closing Costs %, CapEx Budget, Loan Structure/LTV/Amortization/Term, Mezz terms (if applicable), and full waterfall tier structure (if Co-Invest = Yes).
## Retrieval plan
Phase discipline: resolve property identity and parse any uploaded documents first; do all analytical retrieval after the deal terms are confirmed.
| Template field(s) | Datamart topic / skill | Entity grain | Fallback order |
|---|---|---|---|
| Property facts (C5–C12) | `property`, `property_mfr` topics | property | uploaded doc → datamart → user |
| T12 statement (H33, H34, H37, H41–H49) | `property_mfr` operating topics | property | uploaded T12/rent roll → datamart T12 → user estimate |
| Stabilized Occupancy (H19) | `property_mfr` occupancy, or rent-comps average | property/submarket | datamart → comp average → user |
| Rent Growth, Expense Growth (H16, H17) | `forecasting` skill | submarket/market | forecast engine → user override |
| Exit Cap Rate (C22) | `forecasting` skill (cap rate band read) | market/asset class | forecast engine → user confirms |
| Interest Rate (C40) | `mortgage_rates` topic | loan_type × property_type | datamart → user confirms current quote |
| Rent Comps table | `rental-comps` skill | property | skill output → user supplements |
| Sales Comps table | `sales-comps` skill | property | skill output → user supplements |
## Assumption confirmation
Present a defaults table (Assumption | Value | Source) for everything resolved silently, then a short `ask_user` form for only the high-materiality fields the user hasn't already given you: Purchase Price, Loan terms, CapEx Budget, waterfall tiers (if applicable). Source labels: `user-provided` / `property data` / `forecast engine` / `web research` / `default` / `ai-estimate`.
## Write rules
1. Read this field guide.
2. For comp tables, clear any placeholder/default content in the declared `D:K` regions before writing (table writes happen regardless of whether anything needed clearing).
3. Write only the cells in the Field Guide above. Never write a formula cell, a cross-tab-linked (green) cell, or anything on Pro Forma, Sources & Uses, Debt Schedule, Returns Summary, Waterfall, Sensitivity, Summary, or `_SensCalc`.
4. Recalculate the workbook (`skills/xlsx/scripts/recalc.py`).
5. Validate (below).
6. Deliver.
On any rule violation (an attempted write to a non-Field-Guide cell, a formula cell, or an out-of-scope sheet), stop and report a one-line error. Do not deliver a workbook with a violated write rule.
## Validation
Recalculate and fail delivery on: any new Excel error (`#REF!`, `#DIV/0!`, `#VALUE!`, `#N/A`, `#NAME?`), any changed value in a cell not on this Field Guide, or an output cell (see below) that fails to calculate.
**Blockers (hard checks):**
- `Assumptions!H52` (NOI, Year 0) > 0
- `Assumptions!H50` (Total OpEx) < `Assumptions!H38` (EGI)
- `Assumptions!H34` (Vacancy & Credit Loss) ≤ 0 — a positive value here is a sign-convention error
- `Sources & Uses!G11` = "BALANCED" (Sources − Uses check within $1)
- `Assumptions!H15` (Hold Period) between 1 and 10
**Advisory (plausibility flags, surface but don't block):**
- `Assumptions!C50` (Going-In DSCR) below ~1.20x on a levered deal — flag as a coverage risk, don't refuse
- `Assumptions!C38` (LTV) above ~75% — flag as aggressive leverage
- `Assumptions!C22` (Exit Cap Rate) below `Assumptions!H6` (Year 1 Cap Rate) by more than ~100bps — flag the cap-rate-compression bet implied
- `Assumptions!H19` (Stabilized Occupancy) below 0.85 or above 0.99 — flag as outside typical stabilized range
## Output reading and goal-seek
Read results only from these mapped output cells:
| Metric | Cell |
|---|---|
| T12 Cap Rate | `Assumptions!H5` |
| Year 1 Cap Rate | `Assumptions!H6` |
| Yield on Cost | `Assumptions!H7` |
| Unlevered IRR | `Assumptions!H8` (= `Returns Summary!C15`) |
| Levered IRR | `Assumptions!H9` (= `Returns Summary!C21`) |
| Avg Cash-on-Cash | `Assumptions!H10` |
| Equity Multiple | `Assumptions!H11` (= `Returns Summary!C22`) |
| Going-In DSCR | `Assumptions!C50` |
| Debt Yield | `Assumptions!C51` |
| Breakeven Occupancy | `Assumptions!C52` |
| Total Equity Required | `Assumptions!C64` |
| Exit / Gross Disposition Value | `Returns Summary!C7` |
| Net Sale Proceeds | `Returns Summary!C11` |
| LP IRR / GP IRR (if waterfall enabled) | `Waterfall!C79` / `Waterfall!D79` (via `Summary!F35` / `F36`) |
| Sensitivity grids (3 tables) | `Sensitivity!C6:H10`, `C15:H19`, `C24:H28` |
**Goal-seek protocol:** vary only the single named input through its declared write cell above, ≤10 iterations, tolerance 0.1% on the target metric (e.g. solve Purchase Price for a target Levered IRR). Report the closest-tested value and its resulting metric if convergence isn't reached within 10 iterations — never silently report an unconverged result as exact.
## Guardrails
- No structural modification of the workbook (no new rows, sheets, or renamed tabs).
- No recreating the model from scratch — this is a fill-in-the-blanks operation against the Field Guide.
- No computing IRR, NOI, DSCR, or any model output outside the workbook — the workbook's formulas are the sole calculation authority.
- No writing a datamart value without following its declared fallback order.
- No silent overrides of a user-confirmed assumption.
- Never write to `_SensCalc` or `_PreparationAudit` — they are internal scaffolding.
# Your Pro Forma Template — What the Agent Will Do
## What your template does
This is a full multifamily/CRE acquisition pro forma: property facts and a T12 operating statement roll forward into a 10-year projection, which drives Sources & Uses, a senior debt schedule (with an optional mezzanine layer), unlevered and levered return metrics with a full exit/disposition analysis, an optional GP/LP promote waterfall (preferred return + two promote tiers + residual split), rent and sales comps, and a 3-table sensitivity grid (Levered IRR and Equity Multiple across Exit Cap × Rent Growth, and Levered IRR across Purchase Price × LTV). A one-page Summary tab pulls the headline numbers together. Every calculated cell is a live formula — change an assumption and the whole model, including the sensitivity tables, recalculates automatically.
## What we'll pull automatically
- **Property facts** (unit count, square footage, year built/renovated) — from our property database, or from an uploaded T12/rent roll/OM if you provide one (documents take priority over the database).
- **T12 operating statement** (revenue and expense lines) — same priority: your T12 first, database second.
- **Market rent growth, expense growth, and stabilized occupancy** — from our forecasting engine and comps, pre-filled for your confirmation.
- **Exit cap rate context** — a forecasted band for the asset class and market, for you to confirm or override.
- **Current mortgage rates** — by loan program and property type, for you to confirm against your actual quote.
- **Rent comps and sales comps** — pulled and ranked automatically to fill both comp tabs.
## What we'll ask you for
Required — these are deal-specific and we can't infer them:
- **Purchase price** — your basis for the deal.
- **CapEx budget** — your renovation/value-add scope, if any.
- **Financing terms** — loan structure (interest-only, amortizing, or a mix), LTV, amortization schedule, loan term. If you're using mezzanine debt, its LTV and rate too.
- **Promote structure** (if this is a JV) — the preferred return and each promote tier's hurdle IRR and split.
Optional — we'll backfill with a market default if you skip these, flagged clearly as an estimate:
- Closing costs %, disposition costs %, replacement reserves per unit.
## House assumptions we'll protect
None — this template ships with no baked-in defaults; every rate and dollar figure is either pulled live for your deal or asked of you.
## Anything we flagged
- **Sign convention:** the T12 vacancy & credit loss line is entered as a **negative** dollar amount. The agent handles this automatically — a positive entry there would silently overstate occupancy.
- **Hold period cap:** the model supports up to a 10-year hold (10 projection columns). A longer hold isn't supported by this template as built.
- **Year-by-year overrides:** there's an optional toggle ("Use Staged Inputs") to override the flat rent/expense/occupancy growth rates with a year-by-year schedule for up to 10 years. Off by default; the agent will only populate it if you want granular, year-specific assumptions.
- This template arrived to us already cleaned of any prior deal's data — nothing from a previous underwrite carries forward.
---
**To put this to work:** go to [Agents → New Agent](/agents/new), paste in the contents of `RealAI_Pro_Forma_Template_v4_agent.md` as the agent's prompt, and attach `RealAI_Pro_Forma_Template_v4 (2).xlsx` as its file — the cell coordinates match only that exact workbook. It isn't registered anywhere yet; this step is what creates it.
Population agent built for RealAI Pro Forma Template v4
The template arrived already clean — no prior deal to strip out. It's a full acquisition pro forma: T12 → 10-year projection → Sources & Uses → senior/mezz debt schedule → unlevered/levered returns with exit analysis → optional GP/LP promote waterfall → rent/sales comps → 3-table sensitivity grid, all summarized on one page. I traced every input against its formula chain, mapped 40+ scalar inputs and two comp tables to their exact write cells and sourcing rules, and stress-tested the generated agent's write rules on a scratch copy: placeholder inputs recalculated with zero errors, Sources & Uses balanced, and a price increase correctly dragged the cap rate and levered IRR down — confirming the mapping is sound before you run it for real.
Files:
1. RealAI_Pro_Forma_Template_v4_agent.md (the population agent)
2. RealAI_Pro_Forma_Template_v4_briefing.md (the user-facing summary)
Built for: RealAI_Pro_Forma_Template_v4 (2).xlsx
Status: ready
To put it to work: go to Agents → New Agent, paste the agent file's contents in as its prompt, and attach the working workbook (RealAI_Pro_Forma_Template_v4 (2).xlsx) — the cell coordinates only match that exact copy. It isn't set up or registered yet; that page is what creates it.
This is a read-only snapshot of a RealAI analysis.