Rental property vacancy rate spreadsheet: physical vs economic vacancy

There are two honest vacancy numbers, and they answer different questions. This guide gives the formula for each and works one unit through a year.

General information only, not tax, legal or accounting advice. Late-fee, deposit and notice rules vary by state, city and lease; check your own. Tax references describe US federal pages on irs.gov as of the date above.

Two numbers, two questions

Physical vacancy asks how much of the time your units sat empty. Economic vacancy asks how much of the rent you could have collected you did not. They differ because rent is also lost while a unit is occupied (a free month, a discount, an unpaid balance).

The formulas

Available days    = Units x (DATE(Year,12,31) - DATE(Year,1,1) + 1)
Vacant days       = next lease start - first vacant day   (add up each vacancy)
Physical vacancy  = Vacant days / Available days

Potential rent    = sum of the monthly rent of every unit, all 12 months
Collected rent    = rent actually received for the year
Economic vacancy  = IF(Potential=0, 0, 1 - Collected / Potential)

The DATE expression returns 365 for a normal year and 366 for a leap year, so you never type the day count. For vacant days, record the first day the unit was empty and the first day of the next lease; subtracting one from the other counts exactly the days in between.

Worked example: one unit, 2025

One unit rents for $1,500 a month. The previous tenant left and the first empty day was March 1, 2025. The next lease started April 30. Over the year you collected $14,400.

Potential rent   = 12 x 1,500                = 18,000
Vacant days      = Apr 30 - Mar 1            = 60
Available days   = 1 x 365                   = 365
Physical vacancy = 60 / 365                  = 0.1644 = 16.4%
Economic vacancy = 1 - 14,400 / 18,000       = 0.2000 = 20.0%

The economic figure is higher because the loss is bigger than the vacant days alone explain. The rent gap is $18,000 - $14,400 = $3,600. Spread over the year, $18,000 is $49.32 a day, and the 60 vacant days account for $18,000 x 60 / 365 = $2,958.90. The remaining $641.10 was rent due while the unit was occupied but not collected, such as a concession or an unpaid balance.

Same 60 days, bigger building

Physical vacancy depends on the denominator. If the same 60 vacant days happen in a duplex where both units were available all year, available days are 2 x 365 = 730, and physical vacancy is 60 / 730 = 0.0822, or 8.2%. When you compare years or properties, compare like with like: the same units, the same year, the same rule for what counts as available.

Decide the rules before you log

• Do days a unit is off the market for renovation count as available? Pick a rule and write it down.
• Do you measure potential rent at the lease rent or at the rent you are advertising for vacant units?
• Do you count rent you collect late (after year end) in collected rent? Be consistent.

What the IRS says about vacant property

Publication 527 (2025) says that if you hold property for rental purposes, you may be able to deduct ordinary and necessary expenses for managing, conserving or maintaining it while it is vacant. It also says you "can't deduct any loss of rental income for the period the property is vacant." So lost rent never shows up as an expense line anywhere; it only appears in the vacancy figures above. The publication also says that if you list rental property for sale and it is not held out and available for rent, the expenses are not deductible rental expenses.

What this does not cover

This is a measuring tool, not a forecast, and it gives no benchmark for what a good vacancy rate is. It does not prorate rent for part months, treat uncollectable rent for tax purposes, or give tax advice; a qualified preparer should confirm how vacant-property expenses are handled for you. Landlord-tenant rules vary by state.

Sources

• IRS Publication 527 (2025), Residential Rental Property

More landlord guides

• Security deposit ledger template for Excel: columns and a reconciliation check
• Schedule E expense categories spreadsheet: sorting receipts into lines 5-19
• Rental property income and expense spreadsheet: a 12-month duplex log
• Rent roll template for Excel: what a small landlord's rent roll needs
• Rent payment tracker spreadsheet: a late-status formula
• Prorated rent formula in Excel for a mid-month move-in
• Lease expiration tracker spreadsheet with a rent increase preview
• Late fee calculator in Excel: a formula with a grace period
• Duplex profit and loss per unit: splitting shared costs and finding NOI