Tenant Tracking Spreadsheet Guide | SheetCraft

The $3,000 Mistake Every Landlord Makes

You're losing money every month, and you don't even know it. Last week, a landlord in Portland discovered she'd been charging Tenant B the wrong rent for eight months. Not $50 wrong. $375 wrong. Her "system" was a notebook, three different apps, and whatever she remembered. That mistake cost her $3,000.

If you manage more than two properties, you need a tenant tracking spreadsheet that handles leases, deposits, payment history, and violations in one place. Not because spreadsheets are fun, but because scattered information costs you money.

Why Most Landlord Record Systems Fail

Property management software costs $200-400 per month for serious features. Apps like Cozy or Avail work for basic rent collection but fall apart when you need to track lease renewals, deposit calculations, or violation patterns across multiple tenants.

The real problem: you're making decisions with incomplete information. When Tenant A gives notice, do you know their deposit status instantly? When Tenant C is late again, do you have their full payment history visible? Most landlords spend 20 minutes hunting through emails and apps to answer simple questions.

Here's what a proper tenant tracking system prevents:

Deposit miscalculations: Wrong security deposit amounts or forgotten pet deposits
Lease confusion: Not knowing which leases expire when
Payment pattern blindness: Missing chronic late payment trends
Violation amnesia: Forgetting noise complaints or lease violations during eviction proceedings

Core Components of an Effective Tenant Tracker

Master Tenant Registry

Your foundation is a clean tenant list with current status. This isn't about contact information (use your phone for that). This is about business data.

Tenant Name	Unit	Lease Start	Lease End	Monthly Rent	Security Deposit	Status
Smith, John	4A	2024-01-15	2025-01-14	$1,850	$1,850	Active
Johnson, Maria	2B	2024-03-01	2025-02-28	$2,100	$2,100	Active
Williams, Dave	1C	2024-06-01	2025-05-31	$1,675	$1,675	Active

Payment History Tracking

Payment tracking isn't about whether rent arrived. It's about identifying patterns that predict problems. Use a separate tab with this structure:

Tenant Name	Month	Due Date	Paid Date	Amount Due	Amount Paid	Days Late
Smith, John	2026-04	2026-04-01	2026-04-03	$1,850	$1,850	2
Johnson, Maria	2026-04	2026-04-01	2026-04-01	$2,100	$2,100	0
Williams, Dave	2026-04	2026-04-01	2026-04-08	$1,675	$1,675	7

The key calculation is Days Late in column G: =IF(D2="","",D2-C2). This formula shows the gap between due date (C2) and paid date (D2), returning blank if no payment date is entered yet.

Automated Late Payment Flags

Create a rolling late payment average using =AVERAGEIFS() to spot problem tenants before they become disasters.

In your master registry, add a column for "Avg Days Late" with this formula: =AVERAGEIFS(PaymentHistory!$G:$G,PaymentHistory!$A:$A,A2)

This looks up each tenant's name in the payment history tab and averages their late days. If Williams averages 6 days late over six months, that's a pattern. If Smith suddenly jumps from 0 to 5 days late for two months, that's a warning.

Add a status flag: =IF(H2>5,"REVIEW","OK") where H2 is the average late days. Anyone averaging more than 5 days late gets flagged for review.

Lease Management and Renewal Tracking

Upcoming Lease Expirations

Nothing kills cash flow like surprise vacancies. Use conditional formatting and formulas to highlight leases expiring in the next 60-90 days.

Add a "Days Until Expiration" column: =D2-TODAY() where D2 is the lease end date.

Then use conditional formatting to highlight cells where the value is less than 90 days. Red for under 60 days, yellow for 60-90 days.

Renewal Decision Matrix

Not every tenant gets automatic renewal. Create a decision framework that combines payment history, lease violations, and market rent analysis.

Tenant	Avg Late Days	Violations	Current Rent	Market Rate	Rent Gap	Decision
Smith, John	2	0	$1,850	$1,950	$100	Renew +$50
Johnson, Maria	0	0	$2,100	$2,200	$100	Renew +$75
Williams, Dave	6	2	$1,675	$1,800	$125	Non-renewal

The decision formula: =IF(AND(B2<4,C2<2),"Renew +$"&ROUND((E2-D2)/2,0),"Non-renewal")

This suggests renewal with a moderate rent increase for good tenants (under 4 days average late, fewer than 2 violations) and flags problematic tenants for non-renewal.

Security Deposit Management

Deposit Accounting

Security deposits aren't your money until a tenant moves out and you document damages. Track them properly to avoid legal problems and ensure accurate refunds.

Tenant	Move-in Date	Security Deposit	Pet Deposit	Interest Rate	Interest Earned	Total Due
Smith, John	2024-01-15	$1,850	$300	2.5%	$67	$2,217
Johnson, Maria	2024-03-01	$2,100	$0	2.5%	$54	$2,154

Interest calculation: =C2(1+E2/100)((TODAY()-B2)/365)

This compounds the security deposit at the specified interest rate for the number of days the tenant has occupied the unit. Some states require interest on deposits, others don't. Know your local laws.

Move-out Damage Assessment

When tenants leave, document everything. Create a damage assessment section that calculates deposit refunds automatically:

Item	Cost	Tenant Responsibility	Landlord Responsibility
Carpet cleaning	$150	$150	$0
Wall paint (normal wear)	$300	$0	$300
Broken window	$125	$125	$0
Total Deductions		$275	$300

Refund calculation: =Security_Deposit_Total-Tenant_Responsibility_Total

Violation and Communication Log

Documentation wins eviction cases. Track every lease violation, warning, and communication in a timestamped log.

Date	Tenant	Type	Description	Action Taken	Follow-up Date
2026-04-15	Williams, Dave	Noise	Neighbor complaint about loud music after 11pm	Verbal warning	2026-04-22
2026-04-22	Williams, Dave	Noise	Second complaint from same neighbor	Written warning	2026-05-01

Use data validation to create dropdown menus for "Type" (Noise, Pet, Maintenance, Payment, Other) and "Action Taken" (Verbal Warning, Written Warning, Notice to Quit, etc.).

Financial Performance by Tenant

Track which tenants generate the most profit after maintenance, vacancy costs, and management time.

Tenant	Annual Rent	Maintenance Costs	Late Fees Collected	Management Time (Hours)	Net Profit
Smith, John	$22,200	$450	$0	3	$21,600
Johnson, Maria	$25,200	$200	$0	2	$24,800
Williams, Dave	$20,100	$800	$150	12	$18,850

Net profit formula: =B2-C2+D2-(E2*50)

This assumes you value your management time at $50/hour. Williams generated $20,100 in rent but cost you $800 in maintenance plus 12 hours of your time dealing with violations and late payments. His true value: $18,850 versus $24,800 for hassle-free Maria.

Automated Reporting and Alerts

Monthly Dashboard

Create a summary dashboard that shows key metrics at a glance:

Collection rate: =SUM(Amount_Paid)/SUM(Amount_Due)
Average late days: =AVERAGE(Days_Late_Column)
Leases expiring next 90 days: =COUNTIFS(Lease_End_Date,"<"&TODAY()+90,Status,"Active")
Total security deposits held: =SUMIF(Status,"Active",Security_Deposit)

Email Alerts

Set up conditional formatting to highlight urgent items:

Red: Rent over 10 days late
Yellow: Leases expiring within 60 days
Orange: Tenants with 2+ violations in past 6 months

Stop Managing Tenants with Sticky Notes

A proper tenant tracking system saves you 5-10 hours per month and prevents expensive mistakes. You need one place to see lease statuses, payment patterns, and violation history without hunting through apps, emails, and notebooks.

If you're managing more than five units, build this system now. If you're managing 20+ units, this spreadsheet approach will work until you hit 50+ units and justify property management software costs.

The SheetCraft Rental Property Analyzer includes a complete tenant tracking module plus cash flow analysis, cap rate calculations, and expense tracking. It handles the complex formulas and formatting automatically, so you can focus on managing properties instead of building spreadsheets.