What happens to cash flow if occupancy drops 5%? How to model it quickly

Integration update, 20 October 2026: Clarity Ledger now connects Xero, QuickBooks Online and FreeAgent. GBP Xero examples below remain valid. Check current provider and currency eligibility; planning, KPI and pack-preparation tasks described as manual remain separate workflows.

This is a spreadsheet modelling guide. Occupancy assumptions, cash-flow forecasts and stress tests are calculated outside Clarity Ledger; its current offer supplies historical GBP Xero reports and reviewed mappings.

An operator-friendly way to stress-test rent, NOI, and cash-without rebuilding your spreadsheet.

You are in a weekly review and someone asks the question that matters more than any IRR slide:

"If occupancy drops 5%, what happens to cash flow?"

In real estate, that one change can ripple through:

• rent collected (obvious)
• recoveries / service charge income (sometimes)
• turnover/leasing costs (often)
• bad debt / arrears (in stress cases)
• and-most importantly-debt service coverage and cash runway

The trick is answering it quickly and consistently, without duplicating files or tearing up your model.

This guide shows a fast, spreadsheet-first approach you can implement in minutes.

---

First: clarify what "occupancy drops 5%" actually means

People use "5%" in two different ways:

1. 5 percentage points (pp): 95% -> 90%
2. 5% relative: 95% -> 90.25% (95% × 0.95)

In property conversations, it is usually percentage points (pp). If you do not clarify internally, you will produce answers that sound precise but are mismatched.

In the steps below, I will assume -5 pp unless stated otherwise.

---

The 10-minute model: a quick cash impact answer

This is the fastest "good enough" approach for portfolio reviews.

Step 1) Start from gross potential rent

Set up (or reference) the base case:

• Units (or NLA / rooms)
• Market / passing rent
• Gross Potential Rent (GPR)

Example:

• GPR = Units * RentPerUnit

If you already have a rent roll, your GPR is basically "rent if fully occupied."

---

Step 2) Apply occupancy as a single multiplier

Define:

• Occ_base (e.g., 0.92)
• Occ_delta (e.g., -0.05 for -5 pp)
• Occ_stress = MAX(0, MIN(1, Occ_base + Occ_delta))

Then:

• Effective Rent (billings)
  Rent_billed = GPR * Occ_stress

This alone answers the headline question: "What happens to top-line rent?"

---

Step 3) Convert billings into cash (collections)

In the real world:

• billed rent is not necessarily cash received
• arrears can rise when occupancy drops (or when tenant quality deteriorates)

Add a collections factor:

• CollectionRate (base might be 0.99; stress might be 0.97-0.98)

Cash in:

• Rent_cash = Rent_billed * CollectionRate

If you skip this step, you will often understate how quickly cash tightens in a stress case.

---

Step 4) Adjust the costs that actually move with occupancy

Not all opex changes when occupancy falls. Most fixed costs stay fixed.

But a few items often move:

• letting / leasing fees
• unit turnaround costs (cleaning, minor repairs)
• utilities in some operating models
• bad debt provision (if you model it separately)

A quick way is to split opex into:

• Fixed opex (unchanged)
• Variable opex (linked to occupancy or to move-outs)

Simple stress approximation:

• Opex_total = Opex_fixed + (Opex_variable * Occ_stress)

Here Opex_variable means variable cost at 100% occupancy, not the variable cost already incurred at base occupancy.

Or even more conservative:

• keep opex flat (worst case for NOI), and only reduce income.

---

Step 5) Flow it through to cash runway (the "so what")

Now connect to the two outflows that decide survivability:

• Debt service (interest + principal)
• Capex (if ongoing)

Cash movement:

• NetCash = Rent_cash - Opex_total - Capex - DebtService

Then track:

• ClosingCash = OpeningCash + NetCash

Your answer becomes:

• "NOI drops by £X per month"
• "Minimum cash is £Y in month Z"
• "We breach DSCR/ICR in months A-B (if applicable)"

That is the minimum set of outputs stakeholders actually act on.

---

A simple numeric example (so you can see it instantly)

Assume monthly:

• GPR = £100,000
• Base occupancy = 95%
• Stress occupancy = 90% (-5 pp)
• Collections = 99% (base), 98% (stress)
• Opex fixed = £25,000
• Opex variable at full occupancy = £5,000
• Debt service = £40,000
• Capex = £5,000

Base case

• Rent billed = £100,000 × 0.95 = £95,000
• Rent cash = £95,000 × 0.99 = £94,050
• Opex = £25,000 + (£5,000 × 0.95) = £29,750
• Net cash = £94,050 - £29,750 - £40,000 - £5,000 = £19,300

Stress case

• Rent billed = £100,000 × 0.90 = £90,000
• Rent cash = £90,000 × 0.98 = £88,200
• Opex = £25,000 + (£5,000 × 0.90) = £29,500
• Net cash = £88,200 - £29,500 - £40,000 - £5,000 = £13,700

Result: a -5 pp occupancy move reduces net cash by £5,600/month (from £19,300 to £13,700) here-before any knock-on effects like higher leasing costs, longer voids, or covenant traps.

---

The 30-minute model: make it realistic without making it heavy

If you want something still fast, but closer to reality, add two upgrades:

Upgrade 1: ramp the occupancy drop over time

Occupancy typically does not drop overnight across the whole asset.

Create an activation factor:

• Step change: Active = IF(Month >= StartMonth, 1, 0)
• Ramp: Active = IF(RampMonths <= 0, IF(Month >= StartMonth, 1, 0), MIN(1, MAX(0, MonthsFromStart / RampMonths)))

Then:

• Occ_stress_t = MAX(0, MIN(1, Occ_base_t + Occ_delta * Active))

This stops your forecast from looking like a cliff.

Upgrade 2: model the cause of occupancy drop (voids vs price vs churn)

A 5 pp occupancy drop can come from:

• a few leases expiring with slower re-letting
• downtime from refurb works
• deliberate vacancy while repositioning
• weaker demand / higher incentives

Your cash answer changes depending on the cause.

A lightweight way to capture this:

• add a "void duration" assumption (e.g., 1 month -> 2 months)
• add a "leasing cost per move-in" assumption
• optionally add a "concession" or "incentive" line

This produces a much more believable cash story: not just "rent down," but "rent down and leasing costs up."

---

The spreadsheet pattern that makes this fast every time

If you only implement one modelling improvement, implement this:

Use a scenario table + a single scenario selector

Instead of rewriting assumptions in-line, keep a small table like:

• Scenario name
• Occupancy delta (pp)
• Collections rate
• Start month
• Ramp months

Then all your modules reference the selected scenario.

This gives you:

• Base / Downside / Severe downside instantly
• No duplicate files
• Fewer formula errors
• Repeatable answers in meetings

---

Common mistakes that make occupancy stress tests misleading

1. Mixing up % vs percentage points (95% -> 90% is -5 pp, not -5%)
2. Reducing opex too aggressively (many costs are fixed)
3. Ignoring collections/arrears (cash tightens before NOI tells you)
4. Forgetting downtime/capex interaction (refurbs often cause occupancy dips)
5. Not checking covenants / cash traps (sometimes cash is restricted even if "portfolio cash" looks fine)

---

What to report after you run the stress (the 5 outputs people care about)

When someone asks "what happens if occupancy drops 5%," a useful answer is:

1. NOI impact (£/month and £/year)
2. Minimum cash balance and when it happens
3. Peak funding requirement (if cash goes negative)
4. Debt service coverage impact (DSCR/ICR proxy)
5. Which SPVs/assets drive the downside (portfolio concentration risk)

That last one is where multi-entity portfolios struggle: the portfolio number is easy; the explainable breakdown is hard.

---

Connect the model to reviewed historical reports

Use consistent reporting dates and account definitions when assembling actuals for the spreadsheet. Add rent-roll, collection, debt and capex assumptions in your own model; historical accounting reports alone do not provide those drivers.

Clarity Ledger supports selected GBP Xero companies and saved profit and loss, balance sheet and trial balance reports, with mappings you review and apply. It does not run occupancy scenarios or cash-flow forecasts. Review the current product scope before choosing a workflow.