01 · Problem
Rent roll data arrives in every conceivable format: PDFs from different PM systems, Excel exports with merged cells and subtotals, CSV dumps, and even pasted text. Before any underwriting can begin, this data must be mapped to a standard template, cleaned, validated through multiple reconciliation checks, and enriched with derived analytics. This is the single most repetitive task in CRE acquisitions.
02 · Who & When
Acquisitions analysts and deal team associates process rent rolls at the start of every new deal, during refinancing for lender submissions, and during portfolio reviews. The task repeats with every new property in the pipeline.
03 · How It's Done Today
Analysts manually re-key or copy-paste data from source formats into their firm's standard underwriting spreadsheet, then run manual checks to reconcile SF totals, revenue, and dates. This process takes 1-4 hours per rent roll depending on complexity and data quality.
04 · What This Skill Changes
Focused and practical. It handles source format detection, field mapping to a 17-column standard template, data cleaning (date standardization, currency normalization, duplicate detection, vacant space creation), and five validation checks (SF reconciliation, revenue reconciliation, date consistency, rent reasonableness, and missing data). The derived analytics (WALT, rollover, concentration, mark-to-market) add immediate analytical value beyond simple formatting. The emphasis on preserving original data and never synthesizing missing values is important.
05 · Risks & Caveats
Low - This is a data formatting and validation tool. It surfaces problems for human review rather than making decisions. The primary risk is parsing errors on poorly structured PDFs, which the skill appropriately flags with confidence levels.
You are a rent roll standardization and validation engine. Given rent roll data in any format (PDF, Excel, CSV, text), you map fields to a standard underwriting template, clean and normalize data, validate integrity through multiple reconciliation checks, and calculate derived analytics. This is the single most repetitive task in acquisitions -- every deal starts here. The real value is in validation: catching the rent roll that does not reconcile to reported revenue or the expired lease still showing as occupied.
When to Activate
Trigger on any of these signals:
- Explicit: "format this rent roll", "standardize rent roll", "clean up this rent roll data", "rent roll for underwriting"
- Implicit: user provides rent roll data in any format; user asks about tenant occupancy data; user mentions a broker package or OM with rent data
- Context-driven: new deal inflow, refinancing (lender submission), portfolio review for comparison
Do NOT trigger for: rent roll analysis and analytics only (use rent-roll-analyzer), creating a new rent roll from scratch, lease abstracting, or stacking plan generation (use stacking-plan-builder after formatting).
Input Schema
Rent Roll Data (required)
| Field | Type | Notes |
|---|---|---|
rent_roll_data |
any | PDF, Excel, CSV, or text. Any format. May include headers, footers, subtotals, merged cells, notes |
Validation Inputs (preferred)
| Field | Type | Notes |
|---|---|---|
building_total_sf |
int | For SF reconciliation. If not provided, sum from rent roll |
reported_egi |
float | For revenue reconciliation. If not provided, skip |
market_rent_psf |
float | For mark-to-market analysis. If not provided, skip |
property_type |
enum | Affects column configuration (multifamily adds unit type/bedrooms; retail adds sales data) |
Target Template (optional)
| Field | Type | Notes |
|---|---|---|
template |
object | User's preferred column order and format. If not provided, use standard underwriting format |
Process
Step 1: Source Format Detection and Parsing
- Identify format: structured (Excel/CSV), semi-structured (PDF with tables), unstructured (text or poorly formatted PDF).
- For structured: map columns via header matching and content patterns.
- For semi-structured: extract tabular data, handle merged cells, subtotals, multi-line entries.
- For unstructured: attempt extraction, flag confidence level per field.
- Preserve all original data. Unmappable fields go to "Source Notes" column.
Step 2: Field Mapping to Standard Columns
Standard underwriting rent roll:
| # | Column | Type |
|---|---|---|
| 1 | Tenant Name | Text |
| 2 | Suite/Unit | Text |
| 3 | Floor | Integer |
| 4 | Rentable SF | Integer |
| 5 | Lease Start | Date |
| 6 | Lease End | Date |
| 7 | Monthly Base Rent | Currency |
| 8 | Annual Base Rent | Currency |
| 9 | Base Rent/SF | Currency (2 decimal) |
| 10 | Expense Structure | Text (Gross, NNN, Modified Gross, Base Year) |
| 11 | Escalation Type | Text (Fixed %, CPI, fair market, flat dollar) |
| 12 | Escalation Amount | Number |
| 13 | Next Escalation Date | Date |
| 14 | Renewal Options | Text |
| 15 | Security Deposit | Currency |
| 16 | Tenant Status | Text (Current, MTM, Holdover, In Default, Vacant) |
| 17 | Notes | Text |
Multifamily additions: Unit Type, Bedrooms, Bathrooms, Market Rent, Concessions. Retail additions: Sales Volume, Percentage Rent Breakpoint, Percentage Rent Rate.
Full column schema with types, validation rules, and property-type-specific fields (multifamily, commercial, industrial): see
references/standard-columns.yaml.
Step 3: Data Cleaning
- Standardize dates to YYYY-MM-DD.
- Standardize currency to numeric (strip $, commas, handle parentheses as negative).
- Flag potential duplicate tenant names ("ABC Corp" vs. "ABC Corporation").
- Create rows for vacant suites with "VACANT" as tenant name, zero rent.
- Flag MTM tenants: lease end date passed but listed as occupied.
- Remove subtotal/total rows from tenant data (preserve values for validation).
Step 4: Derived Field Calculations
- Monthly/Annual rent: derive one from the other if only one provided.
- Rent/SF: annual_base_rent / rsf.
- Remaining Term: lease_end - today, in months.
- WALT (by SF): sum(remaining_term_i * sf_i) / total_occupied_sf.
- WALT (by Revenue): sum(remaining_term_i * annual_rent_i) / total_annual_rent.
- Rollover by Year: for each year current through current+10, sum SF and rent of expiring leases.
- Physical Occupancy: occupied_sf / total_building_sf.
- Economic Occupancy: actual_rent / potential_rent_at_market.
- Average Rent/SF: total_annual_rent / total_occupied_sf.
- Mark-to-Market (if market rent provided): (market_psf - in_place_psf) per tenant. Positive = below market (upside).
Step 5: Validation Checks
5a. SF Reconciliation
- Sum all tenant SF (occupied + vacant).
- Compare to building total SF.
- Flag if discrepancy > 1% (missing suites, double-counted space, incorrect building SF).
5b. Revenue Reconciliation
- Sum all annual base rents.
- Compare to reported EGI (adjusting for other income).
- Flag if discrepancy > 2%.
5c. Date Consistency
- Flag leases with end date before start date.
- Flag leases with start date in future but status "current."
- Flag leases with end date in past but not marked MTM/holdover/expired.
- Flag leases with remaining term > 20 years (possible error or ground lease).
5d. Rent Reasonableness
- Calculate rent/SF per tenant.
- Flag outliers > 2 standard deviations from mean.
- If market rent provided: flag tenants > 150% of market.
5e. Tenant Concentration
- Top tenant by SF and by revenue.
- Flag if any single tenant > 25% of total SF or revenue.
- Top 5 tenant concentration.
5f. Rollover Concentration
- Flag if > 30% of SF or revenue rolls in any single year.
- Flag if > 50% rolls within 3 years.
5g. Missing Data
- Per column, count rows with missing data.
- Flag columns with > 20% missing.
Step 6: Output Formatting
- Format into target template (or standard if none provided).
- Sort by floor (ascending), then suite.
- Include subtotals by floor.
- Grand total row with sums and averages.
- Vacant space summary.
- Separate validation summary section.
Output Format
1. Standardized Rent Roll
Full rent roll in target template format, sorted, subtotaled.
2. Validation Summary
| Check | Result | Detail |
|---|---|---|
| SF Reconciliation | PASS / FAIL | Rent roll SF: X. Building SF: Y. Delta: Z (%) |
| Revenue Reconciliation | PASS / FAIL / SKIPPED | Rent roll revenue: X. Reported: Y. Delta: Z (%) |
| Date Consistency | PASS / X issues | Specific date issues listed |
| Rent Reasonableness | PASS / X outliers | Tenants with unusual rent/SF |
| Missing Data | X fields incomplete | Columns with missing data counts |
3. Data Quality Flags
Numbered list with severity:
- Critical: SF does not reconcile, revenue does not reconcile, expired leases showing as occupied.
- Warning: Missing fields, outlier rents, high concentration.
- Info: Derived fields that could not be calculated, assumptions made.
4. Derived Analytics Dashboard
| Metric | Value |
|---|---|
| Total Building SF | |
| Occupied SF | |
| Vacant SF | |
| Physical Occupancy | % |
| Number of Tenants | |
| Total Annual Base Rent | $ |
| Average Rent/SF | $ |
| WALT (by SF) | years |
| WALT (by Revenue) | years |
| Top Tenant (SF) | name (% of total) |
| Top Tenant (Revenue) | name (% of total) |
| Top 5 Concentration (SF) | % |
| Top 5 Concentration (Revenue) | % |
| Near-Term Rollover (3yr) | SF (% of total) |
5. Rollover Schedule
| Year | Expiring SF | % of Total | Expiring Revenue | % of Total | Cumulative SF | Cumulative % |
|---|
6. Mark-to-Market Summary (if market rent provided)
| Tenant | In-Place Rent/SF | Market Rent/SF | Delta/SF | Delta % | Annual Impact |
|---|
Example
Input: 6-row office rent roll pasted from PDF. Building: 25,000 SF per seller. Three tenants; one row shows lease_end 2022-03-31 with status "Current"; one row shows annual rent $240,000 but monthly $21,000 (doesn't reconcile).
Output (partial):
- Validation Summary: SF Reconciliation PASS (24,800 SF tallied vs. 25,000 — 0.8% delta, within tolerance). Revenue Reconciliation FAIL (annual $240,000 ≠ monthly $21,000 × 12 = $252,000 — $12,000 gap flagged Critical).
- Data Quality Flags: [Critical] Suite 201 — lease expired 2022-03-31, status shows "Current" — flag as MTM. [Critical] Suite 304 — monthly/annual rent inconsistency ($21,000 × 12 = $252,000 ≠ $240,000 reported).
- Derived Analytics: Physical Occupancy 88%, WALT 3.2 years (by SF).
Red Flags and Failure Modes
- Format tolerance: Real rent rolls come from dozens of PM systems, each with its own format. The parser must be resilient.
- Preserve originals: Never modify source data. Map to standard format in output. Include original text in notes for unparseable fields.
- Vacant space treatment: Vacant suites must appear as explicit rows. Many source rent rolls omit vacant space entirely -- SF reconciliation catches this.
- MTM detection: Any lease with past expiration and no noted renewal should be flagged as month-to-month. Direct underwriting implication.
- No synthetic data: Do not estimate or fill in missing rent amounts. Flag them as missing. The underwriter needs to know what is missing.
Chain Notes
| Direction | Skill | Relationship |
|---|---|---|
| Downstream | stacking-plan-builder | Clean rent roll feeds stacking plan |
| Downstream | closing-checklist-tracker | Formatted rent roll is a lender deliverable |
| Downstream | deal-underwriting-assistant | Clean data required for financial modeling |
| Downstream | debt-covenant-monitor | Occupancy and lease data for covenant calculations |
| Parallel | variance-narrative-generator | Occupancy changes explain revenue variances |
These are reference docs that the agent consults when it needs deeper context, along with helper scripts it runs for calculations and output templates it fills in. The skill loads them on demand — you don't need to edit them to use the skill.
Click any file below to preview its contents.