Fourplex rental income and expense spreadsheet: four units, partial payment

A fourplex means four Rent Ledger rows a month and common-area bills that can divide four ways. This month Unit 2 paid half its rent, so Balance and Running balance show what that one unit still owes.

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

Four units mean four payment histories that can be on time, late, in full or in part, and the Rent Ledger keeps each on its own row per month. Status reads Paid, Paid late, Partial, Late or Due, measured against the as-of date on Start Here, and Days late counts only after the grace days you set there. Balance is Rent due minus Amount paid.

Common-area costs come next. The electric and trash bills are each logged once with Split by units on Yes and divide equally across the four units listed. The hallway repair and the trim work belong to no single apartment, so they are property-level rows with no unit.

A worked month: Riverside Quads

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

Occupied units schedule $5,600 a month.

One Rent Ledger row per occupied unit for the month:

The month shows $4,900 collected of $5,600 scheduled, with $700 still owed.

Bills for the month, one Expenses row each:

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

Where each piece goes in the workbook

1. Units & Leases: Add four rows for units 1, 2, 3 and 4 with the same Property name. Give each a lease start and end, a monthly rent and a due day, and set Status to Occupied. The four rents are equal here, but each row takes its own amount.
2. Rent Ledger: Add one row per unit for the month. Units 1, 3 and 4 paid in full. Unit 2 paid half, so type that in Amount paid and leave the rent alone: Status reads Partial and Balance shows the half still owed.
3. Expenses: Log the electricity and trash bills with Split by units set to Yes, so each divides by four and Per-unit share fills in. The hallway repair and the caulking job serve the whole building: leave Unit empty and Split by units on No.
4. P&L: Compare Scheduled rent with Rent collected; the Not yet collected row holds Unit 2's missing half. Rent collected, not Scheduled rent, is what has actually been paid on that month's ledger rows.

Two formulas worth a cell of their own

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

Four tracking tips

1. Add a ledger row for every unit every month, even when a tenant pays nothing. A row with an empty Amount paid still carries the rent due, so Balance and the P&L Not yet collected line stay honest instead of the unit dropping out.
2. Running balance per unit reads right only when ledger rows are in date order. Add each new month for Unit 2 below the last one, not above it, so a short payment carries forward in the right sequence.
3. If one of the four units goes vacant, keep its row on Units & Leases with Status set to Vacant. Units in property still counts it, so the electric and trash bills keep dividing by four instead of shifting to three.
4. If a contractor also works inside one apartment, log that part as its own Expenses row with that Unit filled in, so the building-wide cost and the apartment cost stay on separate rows.

Questions

When one tenant pays only part of the rent, where does it go?

Type what arrived in Amount paid on that unit's Rent Ledger row. Rent due stays at the lease amount, so Balance shows the gap and Status reads Partial. The P&L counts only the received amount in Rent collected and puts the rest under Not yet collected.

Does a short rent payment change that unit's share of the electric bill?

No. Per-unit share on the Expenses tab is the bill amount divided by the units listed for the property, so it is the same whether a tenant pays in full, in part or not at all. The shortfall shows in Balance and Running balance on the Rent Ledger and in Not yet collected on the P&L.

Related landlord guides

• Rent payment tracker spreadsheet: a late-status formula
• Rental property income and expense spreadsheet: a 12-month duplex log
• Rent roll template for Excel: what a small landlord's rent roll needs

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
• 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
• 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