Real properties don’t rent 100% of the time. Vacancy loss discounts potential rent by an expected vacancy rate to get realistic effective income — potential rent times the vacancy rate.
The example
$60k potential rent at 5% vacancy.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | Potential rent | 60000 |
| 3 | Less 5% vacancy | → $57,000 |
The formula
The formula:
How it works
How it works:
- Potential rent assumes every unit is leased all year — the optimistic ceiling.
- The vacancy rate (and credit loss for non-payment) estimates how much you won’t collect.
potential_rent * (1 - vacancy_rate)gives effective gross income.- The loss itself is
potential_rent * vacancy_rate— useful as a separate budget line.
Vacancy feeds NOI. Effective gross income (after vacancy) is the top line of the NOI calculation — understate vacancy and every downstream metric (NOI, cap rate, DSCR) is too rosy. A 5–10% allowance is typical; use local market data where you can.
Try it: interactive demo
Potential rent and vacancy rate.
Variations
Vacancy loss amount
The dollar loss:
From vacant days
Rate from downtime:
Add credit loss
Vacancy + bad debt:
Pitfalls & errors
Don’t skip it. Zero vacancy makes every downstream metric too optimistic.
Rate as decimal. 5% is 0.05 — check your cell isn’t storing 5.
Credit loss too. Add an allowance for non-paying tenants where relevant.
Practice workbook
Frequently asked questions
How do I account for vacancy in Excel?
How do I calculate the vacancy loss amount?
What vacancy rate should I use?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class