basic rental property income expense tracker

Rental Property Financial Ledger

This system provides a standardized framework for tracking monthly cash flow across your investment portfolio. It is designed to capture all operational inflows and outflows to assist with tax preparation and performance analysis. Update this ledger weekly or immediately upon processing any transaction to maintain accurate financial health metrics.

Column Definitions

• Date: Date format (YYYY-MM-DD). Use the date the transaction cleared your bank account.
• Property ID: Text or Dropdown. Use a unique identifier for each unit (e.g., [Unit Number/Address Alias]).
• Category: Dropdown. Select from: Rent, Maintenance, Utilities, Management, Insurance, Taxes, or Mortgage.
• Description: Text. Brief memo regarding the transaction (e.g., [Vendor Name] or [Tenant Last Name]).
• Amount: Currency. Enter positive numbers for income and negative numbers for expenses.
• Type: Formula-driven. Identifies if the transaction is an inflow or outflow.

Essential Formulas

Calculate Total Net Cash Flow:

=SUM(E2:E1000)

Calculate Total Income (Sum only positive values):

=SUMIF(E2:E1000, ">0")

Calculate Total Expenses (Sum only negative values):

=SUMIF(E2:E1000, "<0")

Automatic Type Labeling (Place in Column F):

=IF(E2>0, "Income", "Expense")

Data Validation & Formatting

1. Category Dropdown: Select the "Category" column, go to Data > Data Validation, and set criteria to "List of items." Input: Rent, Maintenance, Utilities, Management, Insurance, Taxes, Mortgage, Other.
2. Negative Value Alert: Select the "Amount" column. Go to Conditional Formatting > Format cells if... > "Less than" > 0. Set text color to red to visually distinguish expenses from income.
3. Date Validation: Select the "Date" column. Go to Data > Data Validation > Criteria "Date" > "is valid date" to ensure all entries are chronological and formatted correctly for sorting.