Prerequisites
- ✓Working coliving underwriting model (5-year P&L + IRR)
- ✓Excel Data Table function familiarity (or Python with pandas)
TL;DR
Test 3 high-impact variables: exit cap rate (±100bps), stabilized occupancy (±5pp), ADR (±10%). Build a 9-cell IRR matrix per variable pair. Communicate in a tornado chart showing IRR impact range per variable. Stop adding variables beyond the top 5; analysis paralysis hurts decisions.
Step-by-step
- 1
1. Identify the top 5 sensitivity variables
Always: exit cap rate, stabilized occupancy, ADR, debt cost, lease-up timeline. Sometimes: capex, exit timing, FX (for cross-border deals).
- 2
2. Set realistic ranges
Exit cap: ±100bps from base. Occupancy: ±5pp. ADR: ±10% (matched to historical volatility). Debt cost: ±200bps. Don't use ±50% on everything — defeats the purpose.
- 3
3. Build 2-variable Data Tables in Excel
Data → What-If Analysis → Data Table. Two-variable: row input = ADR, column input = occupancy. Output cell = IRR. Repeat for ADR × exit cap rate, etc.
- 4
4. Build a tornado chart
For each variable: IRR at +1 SD vs IRR at -1 SD vs base. Sort variables by total IRR swing. The variable at the top is the deal's binding sensitivity.
- 5
5. Stress-test downside scenarios
Combined adverse scenarios. E.g., exit cap +100bps AND occupancy -5pp AND ADR -10%. Often 80% of practical downside risk. If equity IRR stays positive, deal is robust.
- 6
6. Communicate to investors
Headline: base IRR + downside-case IRR. Tornado chart showing variable importance. 1-page summary; institutional investors specifically want the 9-cell IRR matrix on top 2 variables.
Common issues + fixes
×Including too many variables
→Beyond the top 5 sensitivities, marginal information drops fast. Most institutional pitches stop at 3 variables.
×Unrealistic positive scenarios
→If your 'upside' case has occupancy 95%+, exit cap 200bps lower, ADR +20%, you're not modelling — you're hoping. Anchor upside to defensible market data.
×Sensitivity tables but no narrative
→Always include a 1–2 sentence summary per chart explaining what the sensitivity tells the reader. Numbers without interpretation are noise.
Frequently Asked Questions
What's the most important sensitivity to test for coliving?
Almost always exit cap rate. ±50bps swings IRR 150–250bps. Stabilized occupancy second. ADR third. Other variables typically smaller.
Should I show base, upside, and downside scenarios?
Yes. Base case + downside case is non-negotiable. Upside case useful for investor conversations but downside is the credibility test.
How do I communicate sensitivity to non-technical investors?
Tornado chart. Verbal frame: 'IRR is most sensitive to exit cap (±300bps swing) and least sensitive to ADR (±100bps swing). The deal's primary risk is therefore market-cap-rate environment, not operational.'
Cash Flow Projector
Open the tool →