rental property income and expense spreadsheet template

Residential Rental Financial Tracking System

This system provides a structured framework for tracking monthly cash flow, tax-deductible expenses, and occupancy status for real estate assets. It is designed to be updated weekly or monthly to ensure accurate year-end tax reporting and performance analysis.

Column Definitions

• Date: [Date Format: YYYY-MM-DD]. Records the transaction occurrence.
• Category: [Dropdown: Rent, Maintenance, Utilities, Insurance, Taxes, Mortgage, Other]. Defines the tax classification.
• Description: [Free Text]. Brief memo of the transaction (e.g., [Contractor Name] or [Utility Provider]).
• Property Identifier: [Unique ID]. Links the transaction to a specific unit or building.
• Income: [Currency]. Gross inflow for the period.
• Expense: [Currency]. Outflow for the period.
• Status: [Dropdown: Cleared, Paid, Pending, Overdue]. Tracks reconciliation status.

Calculation Formulas

Calculate Total Monthly Net Cash Flow:

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

Calculate Total Expenses for a Specific Category (e.g., Maintenance):

=SUMIF(B2:B100, "Maintenance", F2:F100)

Calculate Occupancy Percentage (Assumes 1 row per unit per month):

=COUNTIF(E2:E100, ">0") / COUNT(E2:E100)

Formatting and Validation Rules

1. Data Validation (Category Column): Select the range B2:B100. Use "Data Validation" to create a dropdown list containing: Rent, Maintenance, Utilities, Insurance, Taxes, Mortgage, Other.
2. Conditional Formatting (Expense Highlighting): Select the Expense column (F2:F100). Apply a rule: "Cell value is greater than 500" with a light red fill to flag high-cost repairs requiring review.
3. Conditional Formatting (Status Tracking): Select the Status column (G2:G100). Apply a rule: "Text is exactly 'Pending'" with a yellow fill to identify outstanding obligations.