Column layout
This assumes the layout of the free rent ledger CSV: Charge in column G, Payment in H, Late Fee in I, Other Charge or Credit in J, and Balance in K, with headings in row 1. If your columns differ, change the letters to match.
The formulas
In the first data row (row 2), the balance is just that row's activity:
=G2+I2+J2-H2
In every row after that, add the previous balance:
=K2+G3+I3+J3-H3
Copy the row 3 formula down the column. The same formulas work in Excel, Google Sheets, Numbers, and LibreOffice. Enter credits in column J as negative numbers.
Keep one tenant per ledger
A running balance only makes sense for one tenant or lease at a time. Use one sheet per tenant, or one file per property with a separate tab for each unit. Mixing tenants in the same column makes the balance meaningless.
Checks before you trust it
- Blank cells count as zero, so a missed entry will not cause an error. Skim for rows with a charge but no payment.
- If you insert or delete a row, re-copy the formula so it still points at the row above.
- Do not type over a formula with a number. Add a correction row instead, so the history stays visible.
- Compare the final balance with your bank deposits each month. See the monthly rent collection checklist.
CSV files do not store formulas. Save the working copy as .xlsx or in Google Sheets after adding them.