rent ledger template excel free

Residential Property Rental Payment Tracker

This system provides a structured framework for tracking monthly rental income, monitoring outstanding balances, and maintaining a clear audit trail for property management. It is designed for individual landlords or property managers to update on a monthly basis following the receipt of tenant payments.

Column Definitions

• Date Received: Date format (YYYY-MM-DD). Must be a valid calendar date.
• Tenant Name: Text. Full legal name of the primary leaseholder.
• Property Unit: Alphanumeric. Identifier for the specific dwelling.
• Amount Due: Currency. The contractual monthly rent amount.
• Amount Paid: Currency. The actual amount received from the tenant.
• Payment Method: Dropdown (Bank Transfer, Check, Wire Transfer, Cash, Other).
• Status: Calculated field based on the balance remaining.

Operational Formulas

Balance Calculation (Column H): Place this in the first row of your balance column to calculate the difference between due and paid amounts:

=D2 - E2

Status Logic (Column G): Use this nested IF statement to automatically categorize the payment status:

=IF(E2=0, "Overdue", IF(E2<D2, "Partial", "Paid"))

Total Monthly Revenue: Place this at the bottom of the "Amount Paid" column to calculate total income:

=SUM(E2:E50)

Data Integrity Rules

1. Data Validation (Payment Method): Select the "Payment Method" column, go to Data > Data Validation, and select "List." Enter: Bank Transfer, Check, Wire Transfer, Cash, Other.
2. Conditional Formatting (Status):
   • Set rule: If cell contains "Overdue," apply Red Fill with Dark Red Text.
   • Set rule: If cell contains "Paid," apply Green Fill with Dark Green Text.
3. Date Validation: Select the "Date Received" column, go to Data > Data Validation, and select "Date" to ensure only valid calendar dates are entered.