A rent ledger that handles partial payments
The hard part of a rent ledger is not the full payments. It is the tenant who pays $600 of $1,100 on the 3rd and the rest "next week". Here is how to set it up so the unpaid part never gets lost.
The rule: log payments, not months
Most homemade ledgers have one cell per month that says "paid". That breaks the first time a payment is split. Keep two things apart instead:
- A payment log. One row per payment: date, door, amount, method. Partial payments are just smaller rows.
- A month view that adds up the log. Due = this month's rent + anything still owed from before. Paid = the sum of this month's log rows. Balance = due minus paid.
In Excel or Google Sheets the "paid" cell is one formula: =SUMIFS(Log!C:C, Log!B:B, A5, Log!A:A, ">="&$B$3, Log!A:A, "<"&EDATE($B$3,1)), where column A of the log is the date, B the door, C the amount, and B3 the first day of the month you are looking at.
What it looks like (November, sample data)
| Door | Carried in | Due | Paid | Balance | Status |
|---|---|---|---|---|---|
| 412 Alder St · A | $0 | $1,150 | $1,150 | $0 | Paid |
| 412 Alder St · B | $0 | $1,100 | $600 | $500 | Partial |
| 88 Fenwick Ave | $0 | $1,650 | $1,650 | $0 | Paid |
| 1907 Larkin Rd · 1 | $0 | $925 | $0 | $925 | Late |
| 1907 Larkin Rd · 2 | $0 | $950 | $950 | $0 | Paid |
412 Alder St · B paid $600 of $1,100. In December that door starts with $500 carried in, so December shows $1,600 due without you doing anything:
| Door | Carried in | Due | Paid | Balance |
|---|---|---|---|---|
| 412 Alder St · A | $0 | $1,150 | $0 | $1,150 |
| 412 Alder St · B | $500 | $1,600 | $0 | $1,600 |
| 88 Fenwick Ave | $0 | $1,650 | $0 | $1,650 |
| 1907 Larkin Rd · 1 | $925 | $1,850 | $0 | $1,850 |
| 1907 Larkin Rd · 2 | $0 | $950 | $0 | $950 |
Late flags
Pick a grace period (the sample uses 5 days after the due day). Status is Paid when balance is zero or less, Partial when something came in, Late after the grace period, otherwise Due. Late-fee rules differ by state, so the sheet flags, it does not charge.
Skip building it. The free Doorledger rent ledger already does this for up to 10 doors, free. The full Doorledger workbook ($29 once, no monthly fee) adds a Monday view, 24 recurring repair jobs, leases, deposits, expenses and a Year summary by Schedule E line.