Month-to-month tenants rent tracker spreadsheet: leave Lease end empty, watch the ledger
With no fixed end date, Lease end stays empty on Units & Leases and Days to lease end and the Lease flag stay blank with it. What you watch instead is the Rent Ledger: due date, amount paid and Status each month.
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
With no fixed term, nothing on Units & Leases counts down. Days to lease end and the Lease flag are formulas that need a Lease end date, so with the cell empty they stay blank. The Increase % and Rent after increase columns do not use a lease end, so they keep working without a renewal date.
Type an Increase % on a unit's row and Rent after increase previews the new rent. It is only a preview: nothing changes until you retype Monthly rent yourself once the new rent is agreed and in writing. What notice a month-to-month tenant is owed is a matter for your lease and local rules, not for the sheet.
A worked month: 42 Market Street
Invented names and numbers, to show the shape. 4 units on the Units & Leases tab, all with the Property cell set to 42 Market Street.
| Unit | Tenant | Monthly rent |
|---|---|---|
| Unit 1 | Sofia Bergstrom | $1,300 |
| Unit 2 | David Osei | $1,350 |
| Unit 3 | Michelle Torres | $1,300 |
| Unit 4 | Thomas Wright | $1,275 |
Occupied units schedule $5,225 a month.
One Rent Ledger row per occupied unit for the month:
| Unit | Tenant | Rent due | Amount paid | Balance |
|---|---|---|---|---|
| Unit 1 | Sofia Bergstrom | $1,300 | $1,300 | $0 |
| Unit 2 | David Osei | $1,350 | $1,350 | $0 |
| Unit 3 | Michelle Torres | $1,300 | $1,300 | $0 |
| Unit 4 | Thomas Wright | $1,275 | $1,275 | $0 |
| Total | $5,225 | $5,225 | $0 |
The month shows $5,225 collected of $5,225 scheduled, with $0 still owed.
Bills for the month, one Expenses row each:
| Bill | Category | Amount | How it is logged |
|---|---|---|---|
| Property management monthly fee | Management fees | $280 | Split by units: 4 units, $70 each |
| Water and sewer, shared meter | Utilities | $156 | Split by units: 4 units, $39 each |
| Common area lighting repair | Repairs | $85 | Property level, not split |
| Total | $521 |
The split bills total $436. The Expenses tab divides each equally by the number of units listed for the property (4), so each unit carries $109 of them.
Rent after a 4% increase
Type the Increase % on Units & Leases and the Rent after increase column previews the new rent. It is only a preview: change Monthly rent yourself once the new rent is agreed and in writing.
| Unit | Tenant | Current rent | Increase % | Rent after increase |
|---|---|---|---|---|
| Unit 1 | Sofia Bergstrom | $1,300 | 4% | $1,352 |
| Unit 2 | David Osei | $1,350 | 4% | $1,404 |
| Unit 3 | Michelle Torres | $1,300 | 4% | $1,352 |
| Unit 4 | Thomas Wright | $1,275 | 4% | $1,326 |
Where each piece goes in the workbook
- Units & Leases: Type four rows with the same Property: unit, tenant, monthly rent, due day and Status Occupied. Lease start can hold the date the tenancy began. Leave Lease end empty and the Lease flag and Days to lease end stay blank.
- Rent Ledger: Type one row per unit per month: the unit, the 1st of the month, the due date, the rent and what was paid. Status reads Paid, Paid late, Partial, Late or Due against the as-of date on Start Here, and Days late starts after the grace days.
- Expenses: The management fee and the shared water bill go in once each at the full amount with Split by units on Yes. The lighting repair is a property-level bill: Split by units No and no Unit. Each row takes a category from the dropdown.
- Deposits: One row per tenant: unit, tenant, deposit amount and date received. Move-out date and Settled on are typed when a tenant actually leaves, not from a lease end, so leave them empty until then. Type the bank balance into the reconciliation; Difference 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,"42 Market Street")returns 4 here. - Rent paid by one unit across the ledger:
=SUMIFS('Rent Ledger'!$G$6:$G$505,'Rent Ledger'!$A$6:$A$505,"Unit 1")returns $1,300 on this example. - The rent preview, in the Rent after increase column:
=ROUND(F6*(1+L6),2)with Increase % typed as a percentage.
Four tracking tips
- Due day on Units & Leases is a typed reference that no formula reads, so type each ledger row's Due date yourself. Days late counts after the grace days on Start Here, and a Late fee shows as charged, not as collected, once days late reaches 1.
- Rent Ledger rows carry their own typed Monthly rent. When an increase takes effect, type the new rent on Units & Leases and on the ledger rows from that month on; earlier rows keep the old rent, so past balances do not move.
- Do not fill Lease end with a placeholder date to tidy the row. With a date there, Days to lease end counts down to it and the Lease flag moves into its 60 and 90 day windows for a date that means nothing.
- When a tenant moves out, type the Move-out date on that month's ledger row to prorate the last month by the rule on Start Here, and log the gap in the vacancy log on Units & Leases. Change Status to Vacant yourself; it does not follow the tenant cell.
Questions
What goes in Lease end for a month-to-month tenant?
Leave it empty. The sheet has no month-to-month setting: Days to lease end and the Lease flag are formulas that need a Lease end date, so they stay blank without one. Rent after increase does not use it, and the Rent Ledger works from the rent and due date you type.
How do I track a month-to-month tenant who is late?
Look at the Rent Ledger row for that month. Status shows Paid, Paid late, Partial, Late or Due against the as-of date, Days late counts after the grace days, and Running balance per unit carries what is still owed. The sheet sends no reminders: you contact the tenant yourself.
Related landlord guides
- Rent payment tracker spreadsheet: a late-status formula
- Rental property income and expense spreadsheet: a 12-month duplex log
- Lease expiration tracker spreadsheet with a rent increase preview
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
- 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.