Rent Ledger Template with Rent Roll

About this template

A rent ledger is the payment history of one tenancy: every rent charge, late fee or credit on one side, every payment received on the other, and the balance after each. Landlords need it to know who owes what, and it is the document a court, a lender or a future landlord asks for when there is a dispute about rent. Tenants keep their own for the same reason.

The Rent Ledger sheet starts with the lease terms - tenant, property, unit, lease dates, monthly rent, the day rent is due, the late fee, the grace period and the security deposit - then the ledger itself: Date, Description, Charges, Payments, Balance, Payment method, Reference and Notes from row 10. Each balance is the opening balance plus all charges so far minus all payments so far, so a blank line never breaks the column.

The Rent Roll sheet is the landlord's overview of a whole building or portfolio: one row per unit with the tenant, lease start and end, monthly rent, deposit held, balance due and status (Occupied, Vacant or Notice given). Under the table it works out the number of units, occupancy rate, scheduled rent, rent from occupied units, vacancy loss, deposits held and total balance due. Leases ending within 60 days are highlighted amber and unpaid balances red, so renewals and collections are hard to miss.

Late fees and security deposit limits are set by state and local law in the US - some states cap late fees or require a grace period - so the late fee and grace period are inputs, not rules the sheet applies.

How to use it

1. Fill in the lease terms - Tenant, property, unit and lease dates in column B, and rent, due day, late fee, grace period and deposit in column D. They are reference figures for the ledger below.
2. Charge rent each month - On the due date, add a line with Rent - month in Description and the rent in Charges. Late fees and other charges go in Charges on their own lines.
3. Record every payment - Date received, the amount in Payments, how it was paid and a reference such as the check or money order number. The balance updates on that line.
4. Keep the rent roll current - On the Rent Roll sheet, list each unit once. Update status when a tenant gives notice or moves out, and the balance due from each tenant's ledger.
5. Print a statement - Print the Rent Ledger sheet (US Letter, header repeated on every page) as a tenant statement, or download the blank PDF to keep a paper ledger.

Formulas in this workbook

Every formula below is written into the workbook itself, exactly as shown. The cell references are the ones the default options produce; change the row or column counts and they shift to match.

• =IF(COUNT(C12:D12)=0,"",$E$11+SUM($C$12:C12)-SUM($D$12:D12)) - Running balance on the first ledger line: the opening balance in E11 plus every charge so far, minus every payment so far. Lines with no amounts stay blank.
• =SUM($C$12:$C$51) - Total charges in the summary block; total payments is the same SUM over the Payments column.
• =E11+G3-G4 - Balance due: opening balance plus charges minus payments.
• =IF(G5<=0,"Paid up","Balance due") - Account status at a glance.
• =IF(D6="","",D6-TODAY()) - On the Rent Roll: days until each lease ends, from today's date. Within 60 days the cell turns amber.
• =COUNTA(A6:A25) - Number of units on the rent roll.
• =COUNTIF(H6:H25,"Occupied") - Occupied units; the occupancy rate divides this by the number of units.
• =IFERROR(B29/B28,"") - Occupancy rate: occupied units divided by all units.
• =SUMIF(H6:H25,"Vacant",E6:E25) - Vacancy loss: the monthly rent of every vacant unit - income the building is not collecting.

Customising it

• Keep one Rent Ledger sheet per tenant: right-click the tab, Move or Copy, Create a copy, and rename it after the unit.
• Link the rent roll's Balance due to each ledger's balance (for example ='Unit 1A'!G5) so the roll updates itself.
• Add a Security deposit ledger below the rent ledger to track deductions at move-out, which many states require you to itemize.
• Add a Due date column and a formula comparing it with the payment date plus the grace period to flag late payments automatically.