Prerequisites
- ✓Excel or Google Sheets
- ✓Operator data: bed count, occupied bed-nights, total revenue, days in period
- ✓Optional: per-property breakdown for multi-property modelling
TL;DR
RevPAB = Total Revenue ÷ (Total Beds × Days in Period). Build inputs sheet (5 cells: beds, days, occupied nights, revenue, currency); calculations sheet (3 derived metrics: occupancy %, ADR, RevPAB); sensitivity table varying ADR × occupancy. For multi-property, replicate per row. Total time: 30 minutes.
Why this matters
RevPAB is the only number that compresses pricing and occupancy into one comparable metric across coliving properties. Tracking occupancy alone hides under-pricing; tracking ADR alone hides empty beds. Operators who report only occupancy to investors typically discover the gap 12-18 months too late, when a portfolio looks 'full' but is leaking 15-25% of available revenue. RevPAB is what institutional capital benchmarks against, every coliving M&A diligence pack we've seen treats it as the headline KPI.
The modelling discipline matters more than the formula. Two common errors corrupt almost every operator's first attempt: (a) revenue-mixing, bundling rent, deposits, and ancillary income inconsistently across properties, which makes RevPAB un-comparable; (b) bed-count drift, counting offline rooms (renovation, fire damage, regulatory hold) as 'available'. A clean model labels both definitions explicitly and lets you toggle. Get this right at the spreadsheet stage and your investor reporting becomes defensible.
What 'good' RevPAB looks like is sharply market-dependent, Lisbon €550-720/bed/month, Berlin €650-820, London £950-1,300, NYC $1,100-1,500, Bangalore ₹15,000-28,000. Properties below the bottom of the band need diagnostic (pricing, lease-up, regulatory friction); properties above the top are usually either premium positioning or unsustainable pricing that will revert. The sensitivity table, varying ADR ±20% × occupancy ±20%, is what shows you which lever to pull when the number drifts.
Step-by-step
- 1
1. Create the inputs block
Set up rows 2-6 of column B with: total beds (B2), days in period (B3, typically 30 for monthly or 365 for annual), occupied bed-nights in that period (B4), total revenue (B5, in your reporting currency), currency code (B6, e.g. "EUR"). Worked example: 50-bed Lisbon property, 30-day month, 1,395 occupied bed-nights, €33,480 total revenue. Use named ranges (beds, days, occupiedNights, revenue) if you'll reference these elsewhere in the workbook.
- 2
2. Calculate available bed-nights
B8 = B2 * B3 → 50 × 30 = 1,500 available bed-nights. This is the denominator for both occupancy and RevPAB. For accurate analysis exclude beds that were intentionally offline (renovation, fire damage), most operators report two variants: 'physical bed-nights' (total inventory regardless of status) and 'available bed-nights' (excluding offline units). Pick one definition and label it.
- 3
3. Calculate occupancy rate
B9 = B4 / B8 → 1,395 ÷ 1,500 = 93%. Format as percentage with one decimal place. If the figure is below 85% for more than 3 consecutive months in a stabilized property, flag it for diagnostic review (pricing, lease-up curve, regulatory). For new builds, expect 60-75% in months 1-6 and 85%+ by month 9-12.
- 4
4. Calculate ADR (average daily rate)
B10 = B5 / B4 → €33,480 ÷ 1,395 = €24/night. This is the average revenue earned per occupied bed-night. Track ADR alongside occupancy, a property running 95% occupancy at €18 ADR is leaving pricing on the table, while 80% at €30 is overpriced for the market.
- 5
5. Calculate RevPAB (per bed-night and per month)
B11 = B5 / B8 → €33,480 ÷ 1,500 = €22.32/night (per-bed RevPAB). B12 = B11 * 30 = €670/month per bed. RevPAB integrates both pricing and occupancy. The same property with 95% occupancy and €25 ADR would yield €712/month, better. With 88% and €25 it'd yield €660/month, worse. RevPAB is the single number to track over time.
- 6
6. Build a 5×5 sensitivity table
Vary ADR (rows: -20%, -10%, base, +10%, +20%) × occupancy (columns: same range). Each cell = ADR × occupancy × 30 days. Color-code: green for ≥ base RevPAB, red for <80% of base. This shows you which lever (price or volume) drives your number most. In most coliving markets, occupancy elasticity is steeper than price elasticity, losing 5 occupancy points usually hurts more than dropping ADR 10%.
- 7
7. Replicate for multi-property comparison
Stack each property's inputs as columns C, D, E... or rows 20, 21, 22... (whichever fits your audience). Add a benchmark column showing the market RevPAB range from the EC city benchmark pages, Lisbon €550-720, Berlin €650-820, London £950-1,300, NYC $1,100-1,500. Properties below the bottom of the range need diagnosis; above the top usually means premium positioning or unsustainable pricing.
Common issues + fixes
×Inconsistent revenue treatment across properties (some include ancillary, some don't)
→Add a 'revenue type' column. Best practice: track gross-rent RevPAB and total-revenue RevPAB separately. Comparing apples to apples requires consistent treatment.
×Currency mixing across multi-currency portfolios
→Add a USD/EUR-equivalent column with exchange rate as a separate input. RevPAB comparisons across currencies without FX-adjustment are misleading.
×Revenue figures include security deposits
→Filter out security-deposit collections, they're balance-sheet items, not revenue. Only recognized rent + ancillary income belongs in RevPAB.
Frequently Asked Questions
What's a good RevPAB target for my property?
Heavily market-dependent. See the city benchmark pages for stabilized RevPAB ranges, Lisbon €550-720, Berlin €650-820, London £950-1,300, NYC $1,100-1,500, Bangalore ₹15,000-28,000.
Should I model RevPAB per-month or per-night?
Track both. Per-night for daily ops (matches PMS reporting), per-month for investor reporting (matches cash flow). The order-of-magnitude difference is misleading without clear units.
How do I project RevPAB into the future?
Project ADR (price growth assumption) and occupancy separately, then multiply. ADR grows ~CPI in regulated markets, faster in unregulated. Occupancy converges to stabilized over the lease-up period.
RevPAB Calculator
Open the tool →