Security deposits get messy fast. Who actually owes what when someone fronts the whole payment?
Put the whole record into a single Google Sheets file across three tabs: Ledger, Reimbursements, and Summary. That keeps landlord transactions separate from roommate transfers.
The Ledger tab logs the official deposit in a Total amount column. Each roommate's share gets calculated directly from agreed percentages.
Build the tracker around the money flow
Give every external transaction its own row. If cash leaves for the landlord or returns from the landlord, log it here.
Charges stay positive. Refunds stay negative. Peer-to-peer Venmo transfers do not belong on this tab.
Always use actual first names. An audit column called Alex share amount saves headaches later on.
| Column | What to enter | Example |
|---|---|---|
| Date | Date the charge was paid or the refund was received | 2026-01-01 |
| Entry type | Charge or Refund |
Charge |
| Description | Specific detail about the transaction | Apt 123 security deposit |
| Paid by | Roommate who paid the landlord for a charge | Alex |
| Refund received by | Roommate who received a refund; leave blank for a charge | Alex |
| Total amount | Positive for a charge, negative for a refund | $2,000 |
| Split type | Equal, Rent-proportioned, or Custom |
Rent-proportioned |
| Alex share % | Alex's agreed percentage | 50% |
| Alex share amount | Formula result based on the total amount | $1,000 |
| Sam share % | Sam's agreed percentage | 50% |
| Sam share amount | Formula result based on the total amount | $1,000 |
| Status | Open, Received, Settled, or Disputed |
Open |
| Receipt or note | Receipt reference, landlord message, or deduction detail | Lease receipt |
| Share check | Total of all share percentages | 100% |
Suppose Alex writes a single $2,000 check for the place. You split everything down the middle. Alex gets marked under Paid by, the total shows $2,000, and both share amounts hit $1,000.
Fast forward a year: the landlord sends back $1,700 to Alex. Record a fresh row labeled Refund with Alex as recipient and -$1,700 in total. Both shares automatically recalculate to -$850. That minus sign makes the math work.
Set up the Google Sheets file
Head over to Google Sheets and spin up a blank workbook. Three tabs keep things organized without building a tangled maze.
- Rename your three tabs
Ledger,Reimbursements, andSummary. - Recreate the columns above inside
Ledger. When housing three or more people, add an extra percentage and share amount column pair for each roommate right beforeStatus. - Stick to exact name spellings. If your ledger reads
Alex, the other two tabs cannot readAlexander. Plain text formulas break on nicknames. - Set
Total amountand share amounts to currency format. Format the share percentage columns as percentages so typing 50 registers as 50% rather than 5,000%. - Create dropdown validation lists for entry type, split type, and status. It stops typos before they happen.
- Limit editing permissions to people who need them. Lock down formula cells using sheet protection so accidental edits do not wipe calculations.
Freeze the top row too. Less scrolling.
Add the amount and share formulas
In this standard layout, Total amount sits in column F, Alex's share percentage in H, Alex's dollar share in I, Sam's percentage in J, and Sam's dollar share in K.
Paste this formula into I2:
=IF(OR($F2="",H2=""),"",ROUND($F2*H2,2))Drop this one into K2:
=IF(OR($F2="",J2=""),"",ROUND($F2*J2,2))Drag both down your sheet. Extra roommates follow the exact same logic in their respective columns.
For the Share check column, drop in:
=IF(COUNTA(H2,J2)=0,"",SUM(H2,J2))Every row with numbers should read 100%. If you have three or four people living together, make sure every percentage column gets included inside that SUM range, because if a row fails that quick sanity check, you will spend half an hour hunting down why the balances are off. Catch it early.
Choose the split before anyone pays
A share percentage reflects agreed responsibility, not bank balances on move-in day. Who fronted cash is an entirely different issue.
Equal splits make sense for matching bedrooms. Rent-proportioned splits fit when one roommate already pays more for square footage. Custom splits handle perks like garage parking or en-suite baths.
Thing is, no formula can decide what feels fair. Talk through the rule first, then plug in the percentages.
A roommate paying the entire initial deposit might still hold only a 50% stake. Payer status and share percentage answer two different questions.
Keep reimbursements on their own tab
The Reimbursements tab captures roommate-to-roommate transfers. It never creates a new shared expense.
| Column | Purpose |
|---|---|
| Date | Date the payment was sent |
| From | Roommate who sent the money |
| To | Roommate who received it |
| Amount | Positive payment amount |
| Status | Pending, Paid, or Disputed |
| Note | Purpose, payment reference, or confirmation |
Say Alex covers the full $2,000 deposit on day one. A week later, Sam sends Alex $1,000. Do not touch the original $2,000 ledger charge. Instead, log one line on this tab: Sam sent $1,000 to Alex.
Logging that transfer on the ledger would double-count the initial cost. Keep them separate, and nobody gets confused.
Let the Summary tab calculate the balance
Set these headers across row 1 of Summary:
Roommate | Charges paid | Refunds received | Assigned share | Reimbursements sent | Reimbursements received | Net balance
Put Alex in cell A2. Then enter these formulas across the row:
B2: =SUMIFS(Ledger!$F$2:$F$200,Ledger!$D$2:$D$200,A2,Ledger!$B$2:$B$200,"Charge")
C2: =-SUMIFS(Ledger!$F$2:$F$200,Ledger!$E$2:$E$200,A2,Ledger!$B$2:$B$200,"Refund")
D2: =SUM(Ledger!$I$2:$I$200)
E2: =SUMIF(Reimbursements!$B$2:$B$200,A2,Reimbursements!$D$2:$D$200)
F2: =SUMIF(Reimbursements!$C$2:$C$200,A2,Reimbursements!$D$2:$D$200)
G2: =B2-C2-D2+E2-F2Sam's entry in row 3 takes the exact same formulas, except column D points to his share amount:
=SUM(Ledger!$K$2:$K$200)Copy everything down for more roommates, updating column D to match each person's share column.
Here is how to read Net balance:
- Positive numbers mean the group owes that roommate cash back.
- Negative numbers mean that roommate still owes the household.
- Zero means everyone is square.
Turns out, this setup relies completely on negative refund entries. If the landlord cuts two separate refund checks, enter each one as its own row under that specific roommate's name.
Handle move-out refunds and deductions carefully
Compare the landlord's itemized statement against the original deposit check. Wait for final numbers before typing.
If your $2,000 deposit returns $1,700 because of a $300 cleaning charge, write down one refund row for -$1,700. Do not create another row for the $300 deduction. The missing cash already accounts for that loss. Entering it twice ruins your balances.
Store your signed lease, deposit receipts, move-in photos, roommate agreement, and the landlord's breakdown in a shared folder. A spreadsheet helps you calculate debts, but it does not replace legal contracts.
State laws govern deposit timelines. Under California Courts guidance, for instance, landlords face a strict 21-day deadline to return funds or provide an itemized deduction list. Do not assume other states follow California rules. Check your local statutes.
Before move-out
Decide in writing how you will split deductions before inspection day arrives. Clarify how single-room damage gets handled and who collects any group refund checks.
After the landlord responds
Type the exact amount returned, list the recipient, and link the breakdown. Change the row status to Received or Disputed.
During the lease
Review the Summary tab whenever someone sends a reimbursement. Checking in monthly prevents awkward pileups.
Mistakes that throw off the balance
Typing 50 into a cell instead of 50% turns a $2,000 deposit into a phantom $100,000 expense. Always format percentage cells properly and verify the math by hand.
Do not assign a 100% share to whoever fronted the deposit. That person just paid the bill; their liability is still whatever you agreed on.
Never type a refund as a positive number. Positive values behave like extra charges, throwing off the entire summary balance.
Keep unconfirmed deductions off the ledger. Leave guesses in notes until the landlord's final statement arrives.
To be honest, formulas rarely cause the biggest headaches. It is almost always a misspelled nickname or an unrecorded cash transfer that throws off the numbers.
FAQ
Should the roommate who paid the full deposit have a 100% share?
No. List them as the payer in column D, but give them their agreed percentage share in column H. They only get 100% if they agreed to cover the entire cost alone.
Can we split the deposit based on rent percentages?
Yes, as long as everyone agrees. If one roommate pays 60% of rent for the master bedroom, you can use a 60/40 deposit split too. Document it first.
How do we track a partial refund?
Log the partial return as a single negative row in the ledger using your agreed split percentages. Then record any balancing payments between roommates on the Reimbursements tab.
How do we track a landlord deduction for damage?
Record only the net refund received from the landlord. Add a note describing the deduction and save their itemized invoice. If one roommate caused specific damage, adjust their reimbursement obligations accordingly.
Can this tracker handle more than two roommates?
Easily. Add an extra percentage and share amount column on the Ledger tab for each new person, update your share check formula, and point each Summary row to that roommate's assigned share column.
Build the three tabs now, confirm your split percentages before anyone sends money to the landlord, and only log reimbursements when cash actually hits an account.