rental property income tracker

Residential Lease Revenue and Expense Ledger

This system provides a centralized repository for tracking monthly cash flows across your real estate portfolio. It is designed to capture gross revenue, recurring operational expenses, and net operating income (NOI) per unit. Update this ledger on the 1st of every month to reconcile the previous period's financial performance.

Portfolio Financial Data

Column Definitions

• Date: [YYYY-MM-DD] – Date of transaction occurrence.
• Property ID: [Unit/Building Identifier] – Unique label for the specific asset.
• Category: [Dropdown: Rent, Maintenance, Utilities, Insurance, Taxes, Other] – Classification for tax reporting.
• Description: [Text] – Brief detail of the transaction (e.g., "September Rent" or "Plumbing Repair").
• Revenue: [Currency] – Gross incoming cash flow.
• Expense: [Currency] – Outgoing cash flow.
• Status: [Dropdown: Cleared, Pending, Overdue] – Reconciliation status of the transaction.

Essential Formulas

Calculate Net Monthly Cash Flow:

=SUM(E2:E100) - SUM(F2:F100)

Calculate Total Expenses for a Specific Property:

=SUMIF(B2:B100, "[Unit #101]", F2:F100)

Calculate Average Monthly Rent Received:

=AVERAGEIF(C2:C100, "Rent", E2:E100)

Data Validation and Formatting

1. Status Dropdown: Select the Status column, go to Data > Data Validation, and set Criteria to "List of items" with the values: Cleared, Pending, Overdue.
2. Expense Alert: Select the Expense column, go to Format > Conditional Formatting. Set "Format cells if" to "Greater than" and enter 500. Set the fill color to light red to flag high-cost repairs requiring review.
3. Revenue Highlight: Select the Revenue column, go to Format > Conditional Formatting. Set "Format cells if" to "Greater than 0" and set the text color to green to visualize positive cash flow.