how to create a rental ledger in excel

Residential Rental Income and Payment Tracker

This system serves as the central source of truth for tracking monthly rent obligations, actual payments received, and outstanding balances for [Property Name/Address]. It is designed to be updated immediately upon receipt of funds and reconciled on the [Day of Month, e.g., 1st] of each month.

Transaction Ledger

Column Definitions

• Date: Date the transaction was recorded (Format: YYYY-MM-DD).
• Tenant Name: Full legal name as it appears on the lease agreement.
• Category: Type of charge (e.g., Rent, Late Fee, Security Deposit, Utilities).
• Amount Due: The contractual amount owed by the tenant.
• Amount Paid: The actual amount received from the tenant.
• Status: Calculated field indicating the payment state (Paid, Partial, Overdue, or Pending).

Essential Formulas

Calculate Remaining Balance (per row): Place this in the [Balance] column to see what is still owed for a specific line item:

=D2-E2

Calculate Total Outstanding Arrears: Use this to see the total amount owed across the entire portfolio:

=SUMIF(F:F, "Overdue", D:D) - SUMIF(F:F, "Overdue", E:E)

Automated Status Logic: Place this in the Status column to trigger labels based on payment progress:

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

Data Integrity Rules

• Data Validation (Category): Select the [Category] column, go to Data > Data Validation > List. Enter: Rent, Late Fee, Security Deposit, Utilities, Maintenance.
• Conditional Formatting (Overdue): Highlight the [Status] column. Create a rule where cell value equals "Overdue" to trigger a [Light Red Fill] with [Dark Red Text].
• Conditional Formatting (Paid): Highlight the [Status] column. Create a rule where cell value equals "Paid" to trigger a [Light Green Fill] with [Dark Green Text].