House-hack rental income and expense spreadsheet: live in one unit, rent the others
In a house hack you live in one unit and rent the others, so only the rented units get a row on Units & Leases. A bill that also serves your own space goes in as the rental share only, because the sheet divides whatever amount you type; deciding that share is your call, or a tax preparer's.
Free: Rent Due & Late Fee Calculator, one Excel tab. Confirm your email and the file arrives right away, plus an occasional note (at most one a week) on running a small rental. Unsubscribe in one click. Privacy.
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 typed one row per unit, and in a house hack you list only the units you rent out, not the one you live in. That keeps the rent roll, the Rent Ledger and the P&L about rental income alone. The catch is that the unit count behind every split is the number of rows listed for the property, so your own space is never part of the division.
A meter or bill that also serves your own space is the tricky one. The sheet cannot know about your space: it divides a split bill equally among the rented units listed. So you type the rental share of the bill as the Amount, not the whole bill. How to work out that share is your call, or a tax preparer's, and the workbook offers no method for it.
A worked month: 24 Elmwood Court
Invented names and numbers, to show the shape. 2 units on the Units & Leases tab, all with the Property cell set to 24 Elmwood Court.
| Unit | Tenant | Monthly rent |
|---|---|---|
| B | Marcus Chen | $1,500 |
| C | Aisha Okafor | $1,200 |
Occupied units schedule $2,700 a month.
One Rent Ledger row per occupied unit for the month:
| Unit | Tenant | Rent due | Amount paid | Balance |
|---|---|---|---|---|
| B | Marcus Chen | $1,500 | $1,500 | $0 |
| C | Aisha Okafor | $1,200 | $1,200 | $0 |
| Total | $2,700 | $2,700 | $0 |
The month shows $2,700 collected of $2,700 scheduled, with $0 still owed.
Bills for the month, one Expenses row each:
| Bill | Category | Amount | How it is logged |
|---|---|---|---|
| Electricity, rental share of shared meter | Utilities | $92 | Split by units: 2 units, $46 each |
| Water and sewer, rental share of shared meter | Utilities | $78 | Split by units: 2 units, $39 each |
| Unit B bathroom fan replacement | Repairs | $140 | Unit B, not split |
| Total | $310 |
The split bills total $170. The Expenses tab divides each equally by the number of units listed for the property (2), so each unit carries $85 of them.
Where each piece goes in the workbook
- Units & Leases: Type one row per rented unit: unit name, the same Property on both rows, tenant, lease dates, monthly rent, due day and Status Occupied. Leave your own unit off the list, because every row listed counts toward the unit count behind each split.
- Rent Ledger: Type one row per rented unit per month: the unit, the 1st of the month, the due date, the rent and what was paid. Unit B and Unit C have different rents, so type each one's own Monthly rent on its row; Rent due and Balance are worked out from what you type there.
- Expenses: For a meter or bill shared with your own space, type only the rental share as the Amount and set Split by units to Yes, so it divides equally between the rented units. For a repair to one rented unit, fill the Unit cell and set Split by units to No.
- Deposits: One row per rented tenant: unit, tenant, deposit amount and date received. Type your deposit account balance from the bank statement into the reconciliation, and the Difference against the 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,"24 Elmwood Court")returns 2 here. - Rent paid by one unit across the ledger:
=SUMIFS('Rent Ledger'!$G$6:$G$505,'Rent Ledger'!$A$6:$A$505,"B")returns $1,500 on this example.
Four tracking tips
- Spell the Property exactly the same on both rented units and on every Expenses row. Units in property counts rows that match as typed, so a stray typo on Units & Leases changes the count, and one on an Expenses row leaves that row with no Per-unit share.
- A repair inside Unit B alone gets B in the Unit cell and Split by units set to No. Left on Yes, the Expenses tab divides it by the units listed and Unit C carries part of it.
- If a rented unit empties, keep its row with Status typed as Vacant and an asking rent. It stays in the unit count, so split bills keep dividing by the same number. You can then add the gap to the vacancy log on Units & Leases.
- Adding a third rental later, say a converted garage, means one more row with the same Property. Every split bill then divides by three, so the per-unit shares already on the Expenses tab for that property change too.
Questions
Do I list my own unit on the rent roll?
Leave it off in this setup. Units & Leases holds whatever rows you type, and Units in property counts every row listed for the property, your own space included if you add it. With only the rented units listed, a split bill divides among them alone and the rent roll shows rental income only.
How do I enter a utility bill that also serves my own space?
Type the rental share as the Amount, not the whole bill. Working out that share is your call, or a tax preparer's; the sheet has no way to do it for you. Say in the Description that the Amount is the rental share and put the full bill's reference in Receipt ref. With Split by units on Yes, the Expenses tab then divides that amount equally among the rented units.
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
- 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
Already built
The Landlord Rent & Expense Tracker has the rent roll, rent ledger, expenses, deposits and P&L tabs wired together, with example data on every tab. One Excel file, $29.