Use a Google Sheets template with columns for deposit details, payer/contributor names, amounts paid, refund received, and balances. Add formulas like SUMIFS for totals and QUERY for summaries, as shown in Relayfi's expense tracker guide. This helps U.S. roommates or shared housing groups track security deposits for apartments, utilities, or move-out refunds without needing apps.

For a basic setup with two roommates, start with a total deposit split evenly. Columns track who paid what upfront, the group refund amount, and individual balances. Customize for more roommates by adding columns. Update the sheet when payments or refunds occur.

Recommended Columns for Security Deposit Tracking

Core fields draw from rental tracking examples like those on Platuni and roommate templates on Corrie Haffly's blog. Adapt them for deposits.

  • Date Paid: When the deposit was paid to the landlord.
  • Payer Name: Roommate who paid (e.g., "Roommate1", "Roommate2").
  • Amount Paid: Individual contribution.
  • Total Deposit: Full group deposit amount (repeat in each row or reference once).
  • Refund Date: When landlord returns the deposit.
  • Refund Amount: Total refund received.
  • Balance Owed/Received: Formula-calculated amount each roommate gets or owes (positive for owed to them).

For four roommates, add columns like "Roommate3 Amount Paid" and "Roommate4 Amount Paid". Include a "Split Type" column for equal or uneven splits.

Setup Steps in Google Sheets

  1. Go to sheets.google.com and create a new blank spreadsheet.
  2. Name it "Security Deposit Tracker - [Apartment Address]".
  3. In row 1, add the recommended columns from A1 to G1.
  4. Enter sample data in rows 2+: For a deposit split evenly, row 2: Date Paid, Payer "Roommate1", Amount Paid, Total Deposit. Row 3: Same date, Payer "Roommate2", Amount Paid.
  5. In a "Summary" sheet tab, reference totals for quick views.
  6. Adjust for group size by inserting columns and copying formulas, as in Corrie Haffly's roommate tracker.

Key Formulas for Balances and Summaries

Use these formulas from Relayfi for deposit tracking.

  • Total contributions by payer: =SUMIFS(C:C, B:B, "Roommate1") where column C is Amount Paid, B is Payer Name.
  • Per-person refund share: If equal split, = (F2 / number of roommates) - SUMIFS(C:C, B:B, "Roommate1") (F is Refund Amount).
  • Query for summaries: =QUERY(A:G, "SELECT B, SUM(C) GROUP BY B LABEL SUM(C) 'Total Paid'") groups payments by payer.
  • Filter discrepancies: =FILTER(A:G, C:C > E:E * 0.5) shows rows where Amount Paid exceeds half the total deposit.

Copy formulas down or across when adding rows/columns. Test with sample data to verify.

Handling Reimbursements and Uneven Splits

For deposits where one roommate pays 100% upfront and others reimburse, add a "Split Type" column (e.g., "Reimbursement"). Mark the payer at 100%, others at 0%, per Expense Sorted's approach.

Tradeoffs:

  • Equal split: Simple for identical rooms; use = Total Deposit / count of payers.
  • Income-based: List incomes in a separate column, split inversely (higher earner pays more).
  • Usage-based: For utilities deposit, prorate by past usage.

Update cadence: Log payments immediately. Review monthly or at move-out. For partial refunds, enter the total received and recalculate shares.

Sharing, Updates, and Common Mistakes

Share via the green Share button: Add roommates' emails for edit access, as in examples like Medium's household tracker.

Review process: Set a shared calendar reminder for monthly checks. Export to PDF for records.

Common mistakes:

  • Forgetting to copy formulas when inserting columns for new roommates.
  • Not documenting receipts (attach links or note file names in a "Receipt" column).
  • Ignoring uneven splits, leading to disputes.
  • Overlooking partial refunds; always enter actual refund amount.

A spreadsheet works for simple groups with 2-6 people and infrequent updates. For larger groups or automated reminders, consider split-bill apps, but keep records here for disputes.

FAQ

How do I adjust the template for 4 roommates instead of 2?
Insert two columns after Amount Paid (e.g., Roommate3 Paid, Roommate4 Paid). Copy SUM formulas to include them, per Corrie Haffly's customization.

What if the security deposit refund is partial or disputed?
Enter the actual refund in Refund Amount. Use FILTER formula to spot discrepancies. Keep landlord emails and photos as records.

Can I use conditional formatting to flag unpaid balances?
Yes, like =AND(G2>0, G2<100) for balances under $100 (yellow), adapted from Relayfi's budget thresholds.

When should I switch from a spreadsheet to a split-bill app?
If your group needs payment reminders or integrations; spreadsheets suffice for tracking alone in small, trusted groups.

How do I document receipts for deposit disputes?
Add a "Receipt Link/Note" column with Google Drive links or descriptions. Export sheet before move-out.

Is this template suitable for non-roommate groups like travel deposits?
Yes, relabel columns for vacation rental deposits; track contributions and refunds similarly.

Next, create your sheet and test with past deposit data. Check local landlord-tenant laws for refund timelines, as they vary by state.