How to set up a rent ledger in a spreadsheet
A rent ledger is the running record of what each tenant was charged and what they paid. Set up well, it answers "who owes what, and since when?" at a glance, and it keeps the full history if you ever need to explain a balance. This page shows a simple layout for 1 to 10 units that you can build in your own spreadsheet. Near the end, we describe Rentlark, our own spreadsheets, so you can judge whether it fits.
For the wider routine (expenses, repairs, year end), see our guide: How to track rent and expenses in a spreadsheet.
The columns
One row per event, oldest at the top:
| Column | What goes in it |
|---|---|
| A Date | The day rent came due, or the day a payment arrived |
| B Unit | A short, fixed unit ID (for example OAK-1) |
| C Tenant | Spelled the same way every time |
| D Type | Rent charge, Payment, Late fee, Credit, Adjustment |
| E Charge | Money owed. Blank on payment rows |
| F Payment | Money received. Blank on charge rows |
| G Balance | That tenant's running balance (formula) |
| H Method | Check number, transfer reference |
| I Note | For example "October rent" or "second half of October" |
Charges and payments go on separate rows
- Post the rent charge on the due date, even before the tenant pays. The balance then always shows what is owed.
- Post each payment on the day it arrives, with the method. A tenant who pays in two parts gets two payment rows.
- Never type over an old row. The history is the point of a ledger.
A running balance per tenant
With several tenants on one sheet, the balance has to add up only that tenant's rows. In G2, then copied down:
=SUMIFS($E$2:E2, $C$2:C2, C2) - SUMIFS($F$2:F2, $C$2:C2, C2)
It adds every charge and subtracts every payment for the tenant in that row, from the top of the sheet down to the current row. A positive number is owed; a negative number is a credit. SUMIFS works in Google Sheets and in Microsoft Excel 2016 or later. If tenants share a name, use the unit ID as well, or give each lease its own ID.
Late fees and other charges
Add a late fee as its own charge row, and only if your lease provides for it. Rules on late fees, and on accepting partial rent, vary by state and city, so check yours. Keep a short note of the lease section that allows the fee.
Corrections
If an amount was entered wrong, reverse it with a negative row of the same type and a note saying why, then enter the right amount: for a wrong payment, a negative Payment row, not an Adjustment row. Deleting rows makes the running balance jump with no explanation.
Deposits stay out of the ledger balance
Track security deposits in their own section or tab, so deposit money is never mistaken for rent. Some states and cities have rules on where deposits are held and on interest, so check yours.
The monthly check
Once a month, filter to the last day of the month and read each tenant's balance. Match payments to your bank statement. Anything still positive is open, and the rows above it show exactly how it built up. Our free one-page checklist covers the full monthly routine: Download the Small landlord monthly record-keeping checklist (PDF).
If you'd rather not build it yourself: Rentlark
Rentlark is a set of rent and expense tracking spreadsheets for owners of 1 to 10 units and house hackers, made by AI agents. It doesn't use the ledger layout above: rent is tracked in a Rent Status tab, a lease-by-month grid showing due, paid and balance. You log each payment by lease, and Rentlark applies each payment to the oldest unpaid month first; its "For month" column is for your reference only. What it includes:
- Tracks rent per unit and per lease. Designed for Google Sheets and Microsoft Excel 2016 or later.
- A Rent Status tab shows each lease month by month with statuses such as Paid, Paid late, Partial, Unpaid, Late, Credit and Vacant.
- Rent Status carries partial payments and credit forward to the next month for each lease.
- An Expenses tab where each cost is logged once.
- A Repairs tab with opened date, unit, issue, priority, vendor, status, closed date and cost.
- An Annual Summary that totals rent and expenses by property, a records summary for your tax preparer.
- Expense categories follow the IRS Schedule E expense lines, to help you organize records for your tax preparer. Not tax advice. Based on the IRS draft 2026 Schedule E; categories may be updated if the final form changes.
What it isn't: it tracks the rent you record; payments don't run through it, and it has no bank connections. It has no leases, notices or screening forms.
Price: the price on our Gumroad page. One-time purchase, no subscription. Free updates within version 1.x. 30-day refund through Gumroad. You must be 18 or older to buy. Gumroad may add sales tax or VAT at checkout.
FAQ
What is the difference between a rent ledger and a rent roll? A rent roll is a snapshot: each unit, its lease and its rent on one date. A ledger is the history: every charge and payment over time, with a running balance.
Should I record rent as a charge before it's paid? Yes. Posting the charge on the due date means the balance always shows what's owed, whether or not the payment has arrived.
How do I fix a wrong entry? Add a correcting row with a note rather than deleting or overwriting the original.
Does Rentlark take rent payments from tenants? No. Rentlark tracks the rent you record. Payments don't run through it.
Which spreadsheet apps is Rentlark designed for? For Google Sheets and Microsoft Excel 2016 or later.
Who makes Rentlark? Rentlark is made by AI agents for seller Tomer Avni. Questions: rentlark@mail.grokbot.com.
Made by AI agents. Seller: Tomer Avni. Support: rentlark@mail.grokbot.com. Not tax or legal advice.
How this was made: this page was researched and written by AI agents for seller Tomer Avni.
Rentlark home · Rent and expense tracking guide · Monthly checklist (PDF) · Terms · Privacy · Refund policy