Portfolio Benchmarking: Automated Peer Comparison Across Markets and Vintages

The Problem

Portfolio benchmarking runs manually in Excel. Download each property's P&L export, map inconsistent GL account codes to standard categories, calculate per-unit metrics, copy into a comparison template. By the time you finish, next month's data is ready. Properties don't get compared against appropriate peers because the work takes too long.

The bigger issue is cohort definition. A 300-unit property in Dallas shouldn't benchmark against 80-unit properties in Atlanta, but portfolio-wide averaging does exactly that. Manual work can't segment by market, size band, and vintage simultaneously. You pick one dimension and lose the others.

The Measurement Framework

The script solves three problems: standardizing GL structures, defining peer cohorts, and calculating quartile benchmarks.

Every property uses different GL account numbers. One property codes payroll as 6110, another uses 5025. The GL mapping file translates all accounts into 30 standard categories. Every property's P&L, regardless of GL structure, rolls into the same categories: Payroll, Marketing, Repairs & Maintenance, Utilities, Taxes, Insurance.

The Property Master file defines four cohorts per property: Market (all properties in Dallas, Atlanta, etc.), Size Band (0-100 units, 100-200 units, 200-300 units, 300+), Vintage Band (by decade built), and Portfolio (all properties). A 250-unit 2015-built Dallas property benchmarks against all Dallas properties, all 200-300 unit properties, all 2010+ properties, and the full portfolio.

KPIs standardize by unit count. Revenue items (GPR, Total Revenue) divide by units and months. Vacancy, Bad Debt, Concessions divide by GPR to show loss percentages. Operating expenses annualize and divide by units. The script tracks trailing 3 months (T3) and trailing 12 months (T12) for every property and category.

The Code

Automatic Period Detection

def detect_last_n_months(df_wide, meta_cols, n):
    candidates = [c for c in df_wide.columns if c not in meta_cols]
    usable = []
    for c in candidates:
        vals = df_wide[c].map(coerce_numeric)
        if np.isfinite(vals).any() and vals.abs().sum() != 0:
            usable.append(c)
    return usable[-n:]

t3_cols  = detect_last_n_months(fin, meta_cols, 3)
t12_cols = detect_last_n_months(fin, meta_cols, 12)

The script scans the income statement export and picks the rightmost columns with real data. No hardcoded month names. When you add November data, it automatically uses September-November for T3 and December-November for T12.

GL Mapping and Aggregation

df_t3 = fin_long_t3.merge(gl[["Account","std_category"]], 
                          on="Account", how="left") \
                   .merge(prop[prop_key_cols], 
                          on="_prop_key", how="left")

keys = ["Property","std_category","Units","Market",
        "Size_Band","Vintage_Band"]
agg_t3 = df_t3.groupby(keys).agg(T3_Total=("Amount","sum"))

The merge joins GL categories to transactions, then joins property metadata. The groupby rolls every GL transaction into Property × Category totals. A property with 200 GL accounts produces the same 30 category rows as a property with 50 accounts.

KPI Calculation

GPR = _get(row,"Gross Potential Rent")
VAC = _get(row,"Vacancy")
NRI = GPR + VAC + BD + CON + NRU
TR  = NRI + RUB + OTH
NOI = TR - TEXP

k3["Gross Potential Rent"] = (h3["GPR"] / units / 3)
k3["Vacancy"] = (h3["VAC"] / h3["GPR"])
k3["Payroll & Related"] = (h3["PAY"] * 4 / units)

The script calculates derived line items (Net Rental Income, Total Revenue, NOI) from raw categories. KPIs apply appropriate denominators: monthly revenue divides by units and months, vacancy divides by GPR for percentage, expenses multiply by 4 (T3 to annualize) then divide by units.

Quartile Calculation

kpi_stats = kpi_df.groupby(["Market","Label"]).agg(
    KPI_T3_Q25=("KPI_T3", lambda x: np.nanpercentile(x,25)),
    KPI_T3_Q75=("KPI_T3", lambda x: np.nanpercentile(x,75))
)

For each cohort (Market, Size Band, Vintage, Portfolio) and each KPI, the script calculates 25th and 75th percentile values. Properties below 25th percentile are bottom quartile, between 25th-75th are middle 50%, above 75th are top quartile.

The Excel Output

The workbook has two views. The P&L View sheet shows a property dropdown at the top. Select any property and the sheet recalculates to show T3 and T12 actuals, KPIs, and quartile benchmarks for all four cohorts. Each KPI row shows the property value, then eight benchmark columns (25th/75th percentile for Market, Size, Vintage, Portfolio). A Rank column shows "UGLY" for bottom quartile, "GOOD" for middle 50%, "GREAT" for top quartile.

The T3 Benchmarking sheet shows all properties in columns (sorted by unit count) with KPIs in rows. Every cell displays that property's T3 value for that category. Scanning across a Payroll row shows which properties run high on labor. Scanning down a property column shows the full expense profile.

What This Enables

Manual benchmarking takes 4-6 hours per month. Pull property exports, standardize GL codes, calculate per-unit metrics, update comparison tables. The script runs in 30 seconds.

More importantly, manual work forces you to pick one cohort dimension. You can compare by market or by size or by vintage, not all three simultaneously. The script generates all cohorts every time. A property shows as bottom quartile in its market but middle quartile in its size band, revealing market-specific challenges rather than scale issues.

The quartile system replaces subjective judgment. "Payroll looks high" becomes "Payroll is bottom quartile, 18% above size cohort median." Properties can't hide in portfolio averages. A property at $7,200 NOI per unit looks acceptable until you see the market cohort averages $8,500.

The T3/T12 comparison shows trajectory. Properties with T3 significantly worse than T12 flag deteriorating performance. Properties with T3 better than T12 show recent operational improvements. Monthly snapshots miss these trends.

Apartment Quant