Basement apartment and ADU rental income and expense spreadsheet: two very different units

A basement apartment or ADU under the same address is two units on Units & Leases, even though they differ in size. The sheet splits a shared bill equally by unit count and nothing else, so a bill for one side goes in with that unit named.

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

The main house and the basement apartment are two rows on Units & Leases with the same Property text, so Units in property is two and a split bill is divided by two. The rows can differ in rent, lease dates and due day, but the split does not know the main house is bigger. It is equal by unit count, never by square feet or usage.

A bill both sides use, like the roof or a shared meter, goes in once at the full amount with Split by units on Yes and no Unit. A bill for one side, like the main house water heater, gets that unit in the Unit cell and Split by units on No, so the other unit carries none of it. Yard work is typed property level, also with Split by units on No.

A worked month: 31 Oak Street

Invented names and numbers, to show the shape. 2 units on the Units & Leases tab, all with the Property cell set to 31 Oak Street.

Occupied units schedule $3,600 a month.

One Rent Ledger row per occupied unit for the month:

The month shows $3,600 collected of $3,600 scheduled, with $0 still owed.

Bills for the month, one Expenses row each:

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

Where each piece goes in the workbook

1. Units & Leases: Type two rows with the same Property: Main House and Basement Apt, each with its own tenant, rent, lease dates and due day. Both count toward Units in property, which stays at two even if one of them goes vacant.
2. Rent Ledger: Type one row per unit per month, each with its own rent and amount paid. Balance works out per row, and Status is measured against the as-of date on Start Here, so one unit running late does not blur into the other unit's line.
3. Expenses: Roof and shared electric: full amount, Split by units Yes, Unit empty. Water heater: Main House in the Unit cell and Split by units No. Each row also takes a category from the dropdown.
4. Deposits: One row per tenant, so Main House and Basement Apt each get their own, with deposit amount and date received. Deposits stay out of rent and out of the P&L, so they never mix with the monthly rent on the ledger.

Two formulas worth a cell of their own

• Units in a property, the number behind every split: =COUNTIF('Units & Leases'!$B$6:$B$15,"31 Oak Street") returns 2 here.
• Rent paid by one unit across the ledger: =SUMIFS('Rent Ledger'!$G$6:$G$505,'Rent Ledger'!$A$6:$A$505,"Main House") returns $2,200 on this example.

Four tracking tips

1. Pick one spelling for each unit name, Main House and Basement Apt, and use it on Units & Leases, the Rent Ledger, Expenses and Deposits. Totals per unit match the Unit text as typed, so a variant spelling is not counted for that unit, and on the Rent Ledger the Property column reads Unit not found for it.
2. Before choosing Yes on Split by units, ask whether the other unit would use the bill at all. The roof covers both, so it is split. A water heater that serves only the main house is not. Sort each bill one way or the other when you log it.
3. If the basement apartment should carry a smaller share of a bill, the sheet will not do it: there is no percentage or square-foot option. Log it as two Expenses rows, each with one unit named and Split by units on No, using the amounts you decide.
4. Give each unit its own Lease end. Days to lease end and the Lease flag, which uses 60 and 90 day windows, work out per row, so a basement lease ending in a different month than the main house shows on its own row. Both stay blank when Lease end is empty or Status is not Occupied.

Questions

Should utilities split equally if the main house is bigger?

Split by units has one setting: it divides equally by unit count, so with two units each carries half. It has no square-foot, bedroom or usage setting. Your lease and local rules decide what a tenant owes; the sheet only records the share it computes.

How do I handle a repair to the basement unit only?

Fill the Unit cell with Basement Apt and set Split by units to No. The full amount stays with that unit and is not divided with the main house. Left on Yes, the Expenses tab divides it equally between both units instead, so the No is what keeps it on one unit.

Related landlord guides

• Rental property income and expense spreadsheet: a 12-month duplex log
• Duplex profit and loss per unit: splitting shared costs and finding NOI
• Rental property vacancy rate spreadsheet: physical vs economic vacancy

More landlord guides

• Landlord spreadsheets by property type and lease situation
• 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
• 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