Small apartment building rent roll spreadsheet: six units, one vacant

In a six-unit building one empty apartment changes the picture: it has no Rent Ledger row, but its asking rent still counts toward potential monthly rent, and it still takes one share of every split bill.

General information only, not tax, legal or accounting advice. Late-fee, deposit and notice rules vary by state, city and lease; check your own. Names and numbers in the example are invented.

What changes for this kind of rental

Units & Leases is the rent roll: one row per unit, the vacant one included, with totals for units listed, occupied, vacant, scheduled rent, vacant asking rent and potential rent. The Rent Ledger takes one row per occupied unit per month, so a building with one empty unit has five ledger rows a month, not six.

Split bills divide by the six units listed, the vacant one included, so the empty apartment still takes its share of the shared bills. The vacancy log at the bottom of Units & Leases records the empty stretch: first vacant day and next lease start, both typed by you. The P&L takes its physical vacancy from that log.

A worked month: Hawthorne Court Apartments

Invented names and numbers, to show the shape. 6 units on the Units & Leases tab, all with the Property cell set to Hawthorne Court Apartments.

Occupied units schedule $9,850 a month. The vacant unit adds $1,850 of asking rent, so potential monthly rent is $11,700.

One Rent Ledger row per occupied unit for the month:

The month shows $9,850 collected of $9,850 scheduled, with $0 still owed.

Bills for the month, one Expenses row each:

The split bills total $1,465. The Expenses tab divides each equally by the number of units listed for the property (6), so each unit carries $244.17 of them.

The vacancy log row

Vacant days is next lease start minus first vacant day, within the measured period.

Where each piece goes in the workbook

1. Units & Leases: Add six rows on one Property. Five get a tenant and Status Occupied. Unit 1C gets a blank Tenant, Status Vacant and its asking rent in Monthly rent. Then add a vacancy log row below the unit rows: 1C, first vacant day, next lease start.
2. Rent Ledger: Add one row for each of the five occupied units for the month. All five paid in full, so each Balance is zero. Unit 1C gets no ledger row: with no tenant, its asking rent shows only in the Units & Leases totals.
3. Expenses: Log water, electricity, trash and landscaping with Split by units set to Yes, so each divides by six. The parking lot seal coat serves the whole site: leave Unit empty and Split by units on No.
4. P&L: Compare Scheduled rent with Rent collected for the month, then read the physical and economic vacancy lines. Physical vacancy comes from the vacancy log row; the small vacancy calculator sits on the same tab.

Two formulas worth a cell of their own

• Units in a property, the number behind every split: =COUNTIF('Units & Leases'!$B$6:$B$15,"Hawthorne Court Apartments") returns 6 here.
• Rent paid by one unit across the ledger: =SUMIFS('Rent Ledger'!$G$6:$G$505,'Rent Ledger'!$A$6:$A$505,"1A") returns $1,850 on this example.

Four tracking tips

1. Status is a typed cell. When a tenant leaves, clear the Tenant name, set Status to Vacant yourself and keep the asking rent in Monthly rent so the vacant asking rent and potential rent totals pick the unit up.
2. Leave Next lease start empty in the vacancy log while the unit is still unrented, and fill it once a lease is signed. Vacant days in period is a formula, so besides the unit, the only things you type are the two dates.
3. Units & Leases holds ten unit rows in all, so a six-unit building leaves room to add units. A second building counts against the same ten rows and goes in the property list on Start Here as another property.
4. Net operating income by unit on the P&L lists every unit on Units & Leases, vacant ones included. Unit 1C has no rent collected, so it shows only its share of the split bills, which makes an empty unit's carrying cost visible.

Questions

How do I see the rent I am missing while a unit is empty?

Units & Leases shows vacant asking rent beside scheduled rent, which is the monthly gap against full occupancy. The vacancy log gives the vacant days, and the P&L reports physical vacancy from them. Economic vacancy compares scheduled and collected on ledger rows only, so an empty unit with no row does not register there.

What happens to the split bills when a unit is vacant?

Nothing changes. Per-unit share is the amount divided by the units listed for the property, vacant ones included, so each share stays the same. When the next lease starts, you set Status to Occupied by hand; that changes the occupied count, not the divisor.

Related landlord guides

• Rental property income and expense spreadsheet: a 12-month duplex log
• Rent roll template for Excel: what a small landlord's rent roll needs
• Rental property vacancy rate spreadsheet: physical vs economic vacancy

More landlord guides

• Landlord spreadsheets by property type and lease situation
• Basement apartment and ADU rental income and expense spreadsheet: two very different units
• Condo rental income and expense spreadsheet: one unit, HOA dues and assessment
• Fourplex rental income and expense spreadsheet: four units, partial payment
• House-hack rental income and expense spreadsheet: live in one unit, rent the others
• Month-to-month tenants rent tracker spreadsheet: leave Lease end empty, watch the ledger
• Partial rent payments tracker: monitor balances when tenants pay part of monthly rent
• Rent increase at lease renewal: plan and preview increases as leases end
• Room rental income and expense spreadsheet: four rooms, three tenants, one empty room
• Single-family rental income and expense spreadsheet: one house, one tenant
• Tenant turnover and vacancy tracker: monitor move-outs, move-ins, and prorated rent
• Triplex rental income and expense spreadsheet: three units, shared water