Whether you own one rental property or twenty, keeping your numbers straight is one of the most important and most overlooked parts of being a landlord. Rent payments come in at different times. Maintenance costs hit unexpectedly. Property taxes, insurance, and mortgage payments go out on different schedules. Without a clear tracking system it is genuinely hard to know whether a property is actually making you money.
Excel handles rental property management well for small to mid-sized landlords. Here is how to build a system that tracks rent collection, property expenses, and profitability per unit, so you always know exactly where each property stands.
Start with a master list of all your properties and tenants. One row per unit, with the key details you reference constantly. This sheet is the anchor for everything else.
| Column | What to Enter |
|---|---|
| Property ID | Short code for each unit, like MAIN-1A or PROP-001 |
| Address | Full property address including unit number |
| Tenant Name | Current tenant's full name |
| Tenant Email | Primary contact email |
| Lease Start | When the current lease began |
| Lease End | When the lease expires, flag with conditional formatting |
| Monthly Rent | Current rent amount |
| Security Deposit | Amount held on deposit |
| Status | Occupied, Vacant, or Notice Given |
Your rent tracker logs every payment received and automatically flags anything that is late or missing. This is the sheet you check on the first of every month to know exactly where you stand with every tenant.
Here is what a monthly rent tracker looks like with automatic status:
| Column | What to Enter or Formula |
|---|---|
| Property ID | Links to your master list |
| Month | The month this row covers |
| Rent Due | Monthly rent amount from master list via VLOOKUP |
| Date Paid | Enter when payment received, leave blank if unpaid |
| Amount Paid | Enter actual amount received |
| Balance | =Rent Due - Amount Paid |
| Days Late | =IF(Date Paid is blank, TODAY()-Due Date, Date Paid-Due Date) |
| Status | =IF(Balance=0,"Paid",IF(TODAY() is past due date,"Late","Pending")) |
| Late Fee | Enter if applicable per your lease terms |
Every dollar you spend on a rental property needs to be tracked by property. This matters for two reasons. First, tax time, where you need to report expenses per property. Second, profitability, where you need to know which properties are actually making money after all costs are accounted for.
| Column | What to Enter |
|---|---|
| Date | Date the expense was paid |
| Property ID | Which property this expense belongs to |
| Category | Maintenance, Repairs, Insurance, Mortgage, Taxes, Management, Utilities, Other |
| Vendor | Who was paid |
| Description | Brief note on what the expense was for |
| Amount | Amount paid |
| Tax Deductible | Yes or No, most rental expenses are deductible |
| Receipt Filed | Yes or No, confirms documentation exists |
With this data tracked you can pull expenses for any property automatically:
This is the sheet that tells you whether each property is actually worth owning. Total rent collected minus total expenses gives you your net operating income per property per year. Compare that to what you paid for the property and you have your actual return on investment.
| Metric | Formula |
|---|---|
| Annual Rent Collected | =SUMIFS pulling from rent tracker by property and year |
| Vacancy Loss | =Expected annual rent minus actual collected |
| Total Expenses | =SUMIF from expense tracker by property |
| Net Operating Income | =Rent Collected minus Total Expenses |
| Cap Rate | =Net Operating Income / Property Value |
| Cash on Cash Return | =Annual Cash Flow / Total Cash Invested |
Seeing these numbers side by side across all your properties often reveals surprises. A property that feels like it performs well might have a lower cap rate than a smaller property that requires less maintenance. The data makes these comparisons objective rather than based on gut feel.
The system works best with a simple monthly routine. On the first of each month, add a new row to your rent tracker for every unit, enter payments as they come in, and log any expenses from the previous month. Takes about 20 minutes for a small portfolio and gives you a complete, current financial picture of every property at any time.
At year end your tax accountant gets clean, organized data by property. Your expenses are already categorized. Your income is already totaled. What typically takes most landlords a stressful weekend takes you about an hour.
If you would like a custom rental property tracking workbook built for your specific portfolio, with your properties, your expense categories, and your reporting needs, that is exactly what we do at HelpMyData.
Tell us about your portfolio and we'll build a complete Excel system covering rent collection, expenses by property, and profitability per unit. Starting at just $75.
📧 Get Started Today