rental property income and expense tracker

Residential Investment Portfolio Financial Ledger

This ledger is designed to track monthly cash flows, monitor tax-deductible expenditures, and calculate net operating income for [Portfolio Name]. It should be updated on a weekly basis to ensure accurate reporting for fiscal year-end tax preparation.

Column Definitions

• Date: Date of transaction. Type: Date. Validation: YYYY-MM-DD.
• Property ID: Unique identifier for the asset. Type: Text/Dropdown. Validation: Must match [List of Managed Units].
• Category: Financial classification. Type: Dropdown. Validation: [Rental Income, Maintenance, Utilities, Insurance, Taxes, Mortgage, Management Fee, Other].
• Description: Brief memo of the transaction. Type: Text.
• Amount: Currency value. Type: Number. Validation: Use negative values for expenses and positive for income.
• Status: Payment state. Type: Dropdown. Validation: [Cleared, Pending, Void].

Formulas

• Calculate Net Monthly Profit:

  =SUM(E2:E100)
  

• Calculate Total Expenses per Category:

  =SUMIF(C2:C100, "Maintenance", E2:E100)
  

• Calculate Property Occupancy Rate (if tracking units):

  =COUNTIF(F2:F100, "Cleared") / COUNTA(A2:A100)
  

Data Validation & Formatting Rules

1. Expense Alert: Apply conditional formatting to the "Amount" column where cell value is less than 0, set cell background to light red.
2. Dropdown Validation: Highlight the "Category" column and use Data Validation to create a list based on the categories defined in the Column Definitions section.
3. Date Sorting: Apply a filter to the header row to allow sorting by date or property ID for rapid reconciliation against [Bank Account Name] statements.