Most small landlords start tracking rent the same way: a notebook, a notes app, or a spreadsheet with one row per tenant and a check mark for each month. It works until the first partial payment. Then the check mark stops meaning anything, and you are scrolling through your bank app trying to remember whether March was paid in full or only most of the way.
Here is a simple way to track rent payments in Excel that survives partial payments, late payments and the occasional tenant who pays two months at once.
Rule one: one row per payment, never one row per month
The mistake almost everyone makes is building the sheet around months. Build it around payments instead. Every time money arrives, add a row. Never overwrite an old one.
- Date received: the day the money reached you, not the date written on the check.
- Tenant (or unit): spelled the same way every time, so formulas can find it.
- Amount: exactly what arrived.
- Method: check, transfer, cash, app. Useful when a payment bounces.
- Note: anything you will want to remember in April, like "paid late fee separately".
That is your payment log. It is boring, and boring is the point: it is a record of what happened, and nothing in it ever needs editing.
Rule two: work out what is owed, do not type it
On a second tab, list each tenant with their monthly rent and the date their lease started. Format both tabs as tables (Ctrl+T) and name the log Payments. Now the sheet can do the arithmetic. For rent due on the 1st, the number of months due so far is:
=DATEDIF([@LeaseStart], TODAY(), "m") + 1
Multiply that by the monthly rent and you have the total rent owed since the lease began. Then subtract everything that tenant has paid, which SUMIFS pulls straight from the payment log:
=[@Rent] * [@MonthsDue] - SUMIFS(Payments[Amount], Payments[Tenant], [@Tenant])
The result is the tenant's balance. Zero means paid up. A positive number is what they still owe. A negative number means they paid ahead.
This quietly solves the partial payment problem. If rent is $1,200 and February arrives as $800 on the 4th and $400 on the 20th, you do not have to decide which month each payment belongs to. Money simply covers the oldest month first, which is the usual way to apply rent payments.
If you would rather not build the formulas yourself, Landlord Books does this from each lease, with the due day, the grace days and oldest-month-first payments already worked out, plus a ledger per tenant.
Whichever sheet you use, the logic is the same, and it is worth understanding so you can trust the number it gives you. A balance you can explain to a tenant line by line is a balance you can stand behind.
Rule three: decide what "late" means before anyone is late
"Late" is not the same as "unpaid on the 1st". Most leases give a grace period, and some states require one. If rent is due on the 1st with five grace days, rent becomes late on the 6th. So the honest test is simple: on the day after the grace period ends, anyone with a balance above zero is late.
A few things to settle in writing, in the lease, not in the moment:
- The due day and the number of grace days.
- The late fee, if any. Many states cap late fees or say when they can be charged, so check your state's rules before you set one.
- Whether a payment goes to the late fee or to rent first. Log the fee as its own line so it never hides inside the rent balance.
Keep security deposits out of the rent column
A security deposit is not rent. If you plan to give it back at the end of the lease, it is money you are holding, not money you earned, and the IRS does not treat it as income when you receive it. If you later keep part of it for damage, that part counts as income in the year you keep it. One catch: a "deposit" that will be used as the last month's rent is advance rent, which counts as income when you receive it.
In the sheet, give deposits their own tab or their own column. Mixing them into the payment log makes every tenant look paid ahead and every year look more profitable than it was. Rules differ by situation, so if a deposit question affects your taxes, check with a tax professional.
Five minutes a month
Once the log and the balance formula are in place, rent tracking is a short monthly habit: add each payment as it arrives, look at the balances the day after the grace period, and deal with anything above zero while it is still small. The sheet does the remembering, which is the whole job of a sheet.
If you want one that already does the landlord math, the sheets are on this site.