A practical playbook for real estate scenario planning

How to model interest rate moves, occupancy swings, refurb programmes, and cash impacts-without rebuilding your spreadsheet every time.

Real estate underwriting is rarely wrong because someone can't build a spreadsheet. It's wrong because the spreadsheet can't adapt.

You start with a base model. Then your lender asks, "What happens if rates are 150 bps higher?" Your asset manager asks, "What if occupancy dips for six months?" Your contractor says, "We can phase the refurb programme-two units a month." And suddenly you're duplicating tabs, copy/pasting formulas, and introducing errors you won't find until you're mid-board pack.

The fix isn't a more complex model. It's a scenario-ready model architecture: one calculation engine, multiple assumption sets, and a clean way to switch between them.

Below is a practical, spreadsheet-first playbook you can implement in Excel or Google Sheets.

The core idea: stop "editing the model"-start "feeding the model"

A scenario-ready real estate model separates three things:

The engine (your formulas and schedules: rent roll logic, debt schedule, capex timing, cash waterfall)
Base assumptions (your best estimate: base rates, base occupancy, base rent growth, base opex)
Scenario overlays (the "what-if" deltas that modify the base case)

If you build those layers cleanly, adding a new scenario becomes: Add a row to a table -> select scenario -> outputs update. No rebuilding, no duplicating tabs.

A simple workbook structure that scales

You don't need 30 tabs, but you do need clear separation. A practical structure:

00_Settings: scenario dropdown, global toggles, calendar (monthly timeline)
01_Base_Assumptions: rent, occupancy, opex, capex baseline inputs
02_Scenarios: a scenario table (one row per scenario)
10_Revenue: rent & occupancy calculations
20_Opex: operating cost logic
30_Capex: refurb programme + other capex
40_Debt: debt schedule(s), interest, covenants
50_Cashflow: cash waterfall, reserves, distributions
90_Dashboard: KPI outputs + scenario comparisons

Rule of thumb: Inputs live in Assumptions/Scenarios tabs. Calculations live in the modules. Outputs live in the dashboard. Avoid "random inputs" buried in the middle of schedules.

Step 1: Create a scenario table (your control panel)

Make a single table where each row is a scenario and each column is a driver.

Example:

Scenario	Scenario Start	Rate Shock (bps)	Occ Delta (pp)	Occ Ramp (months)	Refurb Start	Refurb Units / Month	Refurb Capex / Unit	Downtime (months)	Rent Premium (%)
Base	2026-01-01	0	0	0	2026-03-01	2	15000	1	8%
Downside	2026-01-01	150	-5	6	2026-05-01	1	17000	2	6%
Upside	2026-01-01	-50	+2	3	2026-02-01	3	15000	1	10%

Then add a Scenario Selector cell (e.g., B2) with a dropdown of scenario names.

Step 2: Pull scenario parameters with one lookup pattern

In a "Scenario Inputs" block, retrieve the selected scenario's parameters from the table.

Excel pattern (XLOOKUP)

SelectedScenario = the dropdown cell (e.g., B2)
Scenarios[Rate Shock (bps)] = the column in your scenario table

Example:

RateShock_bps =XLOOKUP(SelectedScenario, Scenarios[Scenario], Scenarios[Rate Shock (bps)])

Do the same for Occupancy delta, ramp months, refurb capex per unit, etc.

Why this matters

Every module in the model references one set of scenario parameters-not scattered ad-hoc edits.

Step 3: Apply scenarios over time (step, ramp, or phased)

Real-world scenarios usually don't flip instantly. Build a simple timeline logic:

t = current month in your model timeline
ScenarioStart = date from scenario table
MonthsFromStart = number of months since ScenarioStart

Then define an "activation factor":

Step change (instant): Active = IF(t >= ScenarioStart, 1, 0)
Linear ramp (e.g., over 6 months): Active = MIN(1, MAX(0, MonthsFromStart / OccRampMonths))

Now you can apply deltas smoothly instead of hard jumps.

How to model the four big scenario drivers

1) Interest rate scenarios (without rebuilding the debt schedule)

Build the debt schedule once

At minimum, your debt module should include:

Opening balance
Interest rate (index + margin, with hedge logic if needed)
Interest expense
Principal repayment
Closing balance

A practical monthly interest calculation:

Interest = AvgBalance * AnnualRate / 12 Where AvgBalance = (Opening + Closing)/2 (good enough for monthly models).

Make "Rate" a driver, not a hardcoded input

Set up:

BaseIndexRate_t (could be a constant, a curve, or forward assumptions)
Margin (loan margin)
ScenarioRateShock (from scenario table, in bps)

Then:

IndexRate_t = BaseIndexRate_t + (ScenarioRateShock / 10,000) * Active

And:

AllInRate_t = IndexRate_t + Margin

That's it: one debt schedule, unlimited interest rate scenarios.

Optional: model a rate cap (simple but useful)

If there's an interest rate cap, approximate:

EffectiveIndex_t = MIN(IndexRate_t, CapStrike)

Then use EffectiveIndex_t in your all-in rate.

Common mistakes to avoid

Applying a rate shock to fixed-rate debt (separate fixed vs floating tranches)
Changing the rate but forgetting fees, amortisation, or cash reserves
Not surfacing interest cover / DSCR flags on the dashboard

2) Occupancy scenarios (link revenue and costs)

Occupancy is not just revenue. It also affects:

Leasing commissions / letting fees
Turnover costs
Bad debt / arrears
Utilities or variable service costs (sometimes)

Model occupancy as a time series

Even if you only input a single occupancy number, create BaseOcc_t on the timeline (e.g., 92% flat, or seasonal, or lease-up curve).

Then scenario-adjust it:

Occ_t = BaseOcc_t + OccDelta * Active

Where:

OccDelta is in percentage points (pp), not percent change (e.g., -5 pp means 92% -> 87%)

Clamp it safely:

Occ_t = MIN(1, MAX(0, Occ_t))

Revenue flow

A clean approach:

GrossPotentialRent_t = Units * MarketRent_t
EffectiveRent_t = GrossPotentialRent_t * Occ_t
OtherIncome_t = Units * OtherIncomePerUnit_t * Occ_t (optional)
EGI_t = EffectiveRent_t + OtherIncome_t

Now occupancy impacts everything downstream automatically: NOI, cash flow, covenants, distributions.

3) Refurb programmes (phase it, include downtime, and capture uplift)

Refurbs are where many models break-because people treat them like a single capex number.

A scenario-ready way is to model a unit pipeline.

Inputs (from scenario table)

RefurbStart
UnitsPerMonth
CapexPerUnit
DowntimeMonths (units out of service during works)
RentPremium (uplift on refurbished units)
Optional: RefurbDurationMonths or TotalUnitsToRefurb

Pipeline logic (conceptually)

Each month:

Units start refurb
Some units are under refurb (offline)
After downtime, units complete refurb and become "refurbished stock"

Key drivers you want in the engine:

UnitsStarted_t
UnitsUnderRefurb_t (offline units)
UnitsCompleted_t
UnitsRefurbished_t (cumulative completed)

A simple monthly recurrence (easy to implement):

UnitsStarted_t = IF(t >= RefurbStart, UnitsPerMonth, 0) (or stop once you hit a target number)
UnitsCompleted_t = UnitsStarted_{t - DowntimeMonths} (shift the starts by downtime)
UnitsRefurbished_t = UnitsRefurbished_{t-1} + UnitsCompleted_t
UnitsUnderRefurb_t = UnitsUnderRefurb_{t-1} + UnitsStarted_t - UnitsCompleted_t

Tie refurb into occupancy and rent

Downtime reduces rentable units (or effective occupancy).

Two common approaches:

A) Reduce rentable units (more explicit):

RentableUnits_t = TotalUnits - UnitsUnderRefurb_t
Then compute rent off RentableUnits_t instead of TotalUnits.

B) Convert to an "effective occupancy" impact (quick):

OccAdjusted_t = Occ_t * (RentableUnits_t / TotalUnits)

For rent uplift after refurb:

AvgRent_t = BaseRent_t * (1 + RentPremium * (UnitsRefurbished_t / TotalUnits))

Capex is straightforward:

RefurbCapex_t = UnitsStarted_t * CapexPerUnit

Now your refurb scenario automatically affects:

capex timing
temporary income loss
post-works income uplift
cash balance and covenant headroom

All without touching the model structure.

4) Cash impacts (make liquidity the headline output)

Scenario planning fails when it only reports IRR and ignores cash timing.

Your cash module should clearly show:

NOI
Less: Capex
Less: Debt service (interest + principal)
Less/Plus: Working capital movements (optional)
= Net cash movement
Opening cash
Closing cash
Minimum cash covenant / reserve target
Distributions (if applicable)

Make "cash risk" obvious on the dashboard

Add 3-5 outputs that decision-makers actually act on:

Minimum cash balance over the hold period
Months of cash runway at trough
Peak funding requirement (if cash goes negative)
DSCR / ICR minimum and breach months
Cumulative distributions and timing

When you can answer "When do we run out of cash?" in one line, your model becomes operational-not just analytical.

Scenario comparison without chaos: run multiple scenarios side-by-side

Once you have a scenario selector, you can extend to comparisons in two ways:

Option A: Simple "Scenario Switch" dashboard

Select scenario -> dashboard updates. Best for live meetings.

Option B: Scenario grid (Base / Downside / Upside on one page)

Create a small block where each column sets a different scenario name and pulls outputs via lookups.

Pattern:

For each KPI, store results in a table keyed by ScenarioName
Use XLOOKUP to fetch KPI values for Base/Downside/Upside side-by-side

This avoids maintaining three separate models.

Practical tips that prevent "spreadsheet drift"

Never hardcode scenario edits inside calculation tabs. Keep deltas in the scenario table.
Use consistent naming:
- Base_* for baseline drivers
- Scn_* for scenario parameters
- *_t for time series
Put a big "Selected Scenario" label on every output page (so screenshots don't get misused).
Build two or three sanity checks:
- Cash roll-forward ties
- Debt schedule ties (opening + draws - repayments = closing)
- Occupancy clamped 0-100%

When spreadsheets start to break: multi-entity portfolios and investor reporting

If you're managing multiple SPVs, the real pain isn't one model-it's consolidation + consistency:

Different charts of accounts
Slightly different modelling assumptions per SPV
Manual rollups
Reporting packs that take days

That's usually the moment you either (a) invest heavily in spreadsheet governance, or (b) move toward a system that standardises mappings and supports portfolio-level scenario planning.

For example, a Xero- or QuickBooks-based multi-entity approach built around SPV consolidation, standardised chart of accounts mappings, FP&A reporting, and "what-if" scenario planning (rates, occupancy, refurb programmes) can give you a one-stop portfolio view and investor-ready reporting with consistent logic.

Copy/paste starter template: your minimum viable scenario layer

If you only implement one thing this week, implement this:

A Scenario Selector cell with dropdown
A Scenario Table with rows = scenarios
A Scenario Inputs block that pulls values via XLOOKUP
Replace hardcoded assumptions in the engine with:
- BaseValue + ScenarioDelta * Active

That's enough to stop rebuilding spreadsheets every time someone asks, "What if?"

If your biggest pain is running scenarios across multiple SPVs and producing consistent investor packs, it may be time to move beyond spreadsheets into a workflow that consolidates entities, standardises accounts, and runs scenarios at portfolio level.

A practical playbook for real estate scenario planning

Ready for portfolio-grade reporting?