Room rental income and expense spreadsheet: four rooms, three tenants, one empty room

Rent a house by the room and each room becomes a unit row on Units & Leases, vacant ones included. Here four rooms mean four rows and three tenants, and a shared utility divides by four, not three, because the count is units listed.

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

With rooms, the unit is the room, not the house. Room 1 to Room 4 carry the same Property text, and each has its own rent and due day, plus its own Rent Ledger row for each month it has a tenant. A tenant who pays late or short shows up on that room's own Status and Balance, so no house-wide total hides who is behind.

Units in property counts every room listed for the address, vacant ones included. With Room 4 empty, each split bill still divides by four, so Room 4 is assigned a share like the occupied rooms. Equal by room count is the only split the sheet has: it does not weight a larger room, a private bath or heavier use.

A worked month: 15 Chestnut Lane

Invented names and numbers, to show the shape. 4 units on the Units & Leases tab, all with the Property cell set to 15 Chestnut Lane.

Occupied units schedule $2,450 a month. The vacant unit adds $850 of asking rent, so potential monthly rent is $3,300.

One Rent Ledger row per occupied unit for the month:

The month shows $2,450 collected of $2,450 scheduled, with $0 still owed.

Bills for the month, one Expenses row each:

The split bills total $332. The Expenses tab divides each equally by the number of units listed for the property (4), so each unit carries $83 of them.

Where each piece goes in the workbook

1. Units & Leases: Type four rows with the same Property: Room 1 to Room 4, each with its monthly rent and due day, plus tenant and lease dates for an occupied room. For the empty room, type the asking rent, leave Tenant blank and type Vacant in Status yourself; nothing fills it in.
2. Rent Ledger: Type one row per occupied room per month: the room, the 1st of the month, the due date, the rent and what was paid. Room 4 needs a row only once someone moves in, and a move-in date on that row prorates the first month by the rule on Start Here.
3. Expenses: Type each shared utility once at the full amount with Split by units on Yes, and the Expenses tab divides it by the four rooms listed. A cost for one room only gets that room in the Unit cell and Split by units set to No.
4. Deposits: One row per tenant: room, tenant, deposit amount and date received. Room 4 gets no row until its tenant pays a deposit. Type your deposit account balance into the reconciliation, and the Difference against deposits held now should read 0.

Two formulas worth a cell of their own

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

Four tracking tips

1. Type the Property text identically on all four room rows and on every Expenses row. Units in property counts the rows that match as typed, so one room entered with a shortened street name would change the count and every utility share with it.
2. A late fee is worked out per ledger row, so a late Room 3 gets a fee on its own row and a room that paid on time gets none. It is charged once, when Days late reaches 1, by the Flat or Percent setting on Start Here, and it shows as charged, not as collected.
3. A lock rekey for Room 2 gets Room 2 in the Unit cell and Split by units set to No. Left on Yes, the Expenses tab would spread the same bill equally across all four rooms, including the empty one.
4. When a room turns over, use the vacancy log on Units & Leases: the room, First vacant day and Next lease start. Vacant days is a formula and feeds physical vacancy on the P&L. A room like Room 4 today is a row with no Next lease start.

Questions

Should utilities be split equally if one room uses more?

The sheet only divides equally by the number of rooms listed. It cannot split by usage, square feet or bedrooms, and the Expenses tab will show the same per-unit share for every room. If one room should pay more, your lease and that room's rent are where that gets decided.

How do I show a room's rent during vacancy?

Keep the room's row on Units & Leases, type the asking rent in Monthly rent, leave Tenant blank and type Vacant in Status. The totals then list it under vacant asking rent and add it to potential monthly rent, apart from the scheduled rent of the occupied rooms.

Related landlord guides

• Rental property income and expense spreadsheet: a 12-month duplex log
• Rent payment tracker spreadsheet: a late-status formula
• 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
• Single-family rental income and expense spreadsheet: one house, one tenant
• Small apartment building rent roll spreadsheet: six units, one vacant
• Tenant turnover and vacancy tracker: monitor move-outs, move-ins, and prorated rent
• Triplex rental income and expense spreadsheet: three units, shared water