A Google Sheets tracker can settle a roommate water bill without turning every payment into a debate. Record the full bill, the amount the household agreed to split, each person's share, and the reimbursement status.
Keep it simple. A personal water filter does not automatically erase someone's share of a municipal water bill.
Water bills may combine fixed service charges, usage charges, sewer fees, or environmental charges. Phoenix, for example, describes its rates using monthly service, volume, and environmental components on its Water and Sewer Rates page. Other utilities use different categories, so the actual bill and your written roommate rule should control.
Decide what the roommate is opting out of
Write down the charge before you enter a percentage. A filter purchase, an account service charge, and a shared water bill are not automatically the same expense.
| Situation | Sheet treatment | Example |
|---|---|---|
| A roommate buys a personal filter or bottled water | Do not change the municipal bill automatically. Track the personal purchase separately unless the group agrees to reimburse it. | Jordan buys a filter for Jordan's own drinking water but still shares the agreed household utility charge. |
| The group identifies a specific charge that one roommate will not share | Keep the original bill visible, enter the approved excluded amount, and set that person's share to 0% for that line. | The group agrees that Jordan will not share a clearly identified optional item on the bill. |
| One roommate pays the entire utility bill upfront | Keep that person in the Payer column, then calculate each person's responsibility normally. | Alex pays $80. In a two-person 50/50 split, Jordan reimburses Alex $40. |
Use 0% only for a defined charge. A preference for a cheaper option is not, by itself, a complete split rule.
Thing is, the sheet records an agreement. It cannot decide whether the agreement is fair.
Build the Google Sheets template around two tabs
Two tabs are enough for most small households. The Bills tab explains the charge, while the Settlements tab shows whether money actually moved.
Bills tab
Use real roommate names in place of Alex, Jordan, and Sam. Keep Total bill and Amount to split separate so an agreed exclusion does not disappear from the record.
| Column | What to enter |
|---|---|
| Bill date | Date shown on the utility bill |
| Due date | Payment deadline, if shown |
| Description | For example, Water bill - January |
| Total bill | Full amount charged by the utility |
| Excluded amount | A charge the group explicitly agreed not to split; enter 0 when none applies |
| Amount to split | Total bill minus excluded amount |
| Split rule | Equal, usage-based, room-based, nights stayed, or another written rule |
| Payer | Roommate who paid the utility |
| Alex %, Jordan %, Sam % | Each person's agreed responsibility percentage |
| Alex share, Jordan share, Sam share | Dollar amounts calculated from the percentages |
| Split check | Confirms the percentages total 100% |
| Reimbursement status | Open, requested, paid, or disputed |
| Paid date | Date the reimbursement reached the payer |
| Notes | Receipt name, bill link, exception, or explanation |
Do not put the price of a separate filter into Excluded amount unless it appears on the same bill or the household has approved it as a credit. Otherwise, it mixes two different expenses.
Settlements tab
Add one row for each reimbursement request.
| Column | What to enter |
|---|---|
| Request date | Date the payer requested the money |
| Bill date | Date of the related water bill |
| From | Person who owes the reimbursement |
| To | Person who paid upfront |
| Amount | That person's calculated share |
| Status | Requested, paid, or disputed |
| Paid date | Date the payer confirms receipt |
| Note | Reference to the bill row or payment record |
A separate row for each person is clearer than one vague running balance. You can combine several requests later if everyone agrees, but keep the underlying bill dates in the note.
Optional Rules tab
A small Rules tab prevents the same argument from returning next month. Write down:
- Which charges count as shared household costs.
- Which split method applies to ordinary water bills.
- How the group handles filters, bottled water, guests, and move-in or move-out dates.
- Who enters bills and when a reimbursement changes to paid.
Add formulas that catch bad splits
Assume row 2 uses this order:
A Bill date, B Due date, C Description, D Total bill, E Excluded amount, F Amount to split, G Split rule, H Payer, I Alex %, J Jordan %, K Sam %, L Alex share, M Jordan share, N Sam share, O Split check
Enter percentages as percentages. Use 50%, not 50.
| Cell | Formula |
|---|---|
| F2 | =IF($D2="","",ROUND($D2-$E2,2)) |
| L2 | =IF($F2="","",ROUND($F2*$I2,2)) |
| M2 | =IF($F2="","",ROUND($F2*$J2,2)) |
| N2 | =IF($F2="","",ROUND($F2-$L2-$M2,2)) |
| O2 | =IF($F2="","",IF(ROUND(SUM($I2:$K2),4)=1,"OK","Check split")) |
The final share uses the remaining cents. That keeps the calculated shares equal to Amount to split, even when a bill cannot divide evenly.
If there are only two roommates, use the second person's share as the residual and remove the Sam columns. If the check says Check split, stop before requesting money.
Format the bill and share columns as currency. Format the percentage columns as percentages. Copy formulas down only after testing the first row.
Use the payer column correctly
The payer is the person who advanced the money. The payer is not automatically responsible for 100% of the expense.
Suppose Alex pays an $80 bill and Alex and Jordan agreed to split it equally. The Bills tab shows Alex as the payer, Alex's share as $40, and Jordan's share as $40. The Settlements tab then shows Jordan sending $40 to Alex.
That distinction fixes a common mistake. Setting Alex to 100% in the Bills tab would describe Alex as responsible for the whole charge, not merely the person who paid first.
A 100% reimbursement makes sense when one person truly owes the entire approved expense to another person. Put that transfer in Settlements, with From, To, and Amount. Do not use it to describe an ordinary shared utility bill.
Choose a split rule the household can explain
Equal splitting is often the easiest starting point, but it is not the only option. Use one rule consistently unless the group agrees to an exception.
| Method | Works better when | Tradeoff |
|---|---|---|
| Equal per person | Everyone shares the home and the utility connection | Simple, but it may not reflect unusual usage |
| Usage-based | The group has reliable readings or another agreed measure | More tailored, but someone must collect and verify the data |
| Room size or occupancy | Rooms, occupants, or living areas differ materially | Requires a written percentage and can invite repeated debate |
| Nights stayed | A roommate joins or leaves during a billing period | Needs move-in, move-out, and guest dates |
| Income-based | The household voluntarily chooses ability to pay as a contribution rule | Measures contribution capacity, not water usage |
| Reimbursement after proof | One person paid an approved expense for the group | Requires a receipt and agreement that the expense was shared |
Fixed charges deserve a specific conversation. A roommate's lower drinking-water use may not change the cost of keeping the household connected to the utility. If the bill separates fixed and variable portions, the group can decide whether to split them differently, then record that decision in Split rule and Notes.
Run the monthly reimbursement workflow
- Save a photo or PDF of the bill somewhere the roommates can access. Give it a clear name, such as
2026-01-water-bill, and reference that name inNotes. - Enter the bill date, due date, total bill, and any approved excluded amount.
- Confirm the split rule before anyone asks for payment. A short group message is enough.
- Enter the percentages and wait for
Split checkto showOK. - Add one Settlements row for each roommate who owes the payer. Use that person's calculated share, not the full bill.
- Change the settlement to
Paidonly after the payer confirms receipt. Add the paid date, and do not delete the original request.
A payment request can be plain:
January water bill: total $80, shared amount $80. Alex paid it. Jordan's agreed share is $40. Please mark the settlement paid after it arrives.
Enter the bill first. Then enter the settlement. That small order keeps the request tied to a visible charge.
Share the file without losing control
For a household money record, sharing with specific people is usually narrower than using an open link. Give roommates the least access they need.
| Permission | Useful for |
|---|---|
| Viewer | Someone who only needs to read the record |
| Commenter | Someone reviewing a proposed split without changing cells |
| Editor | A roommate who enters bills, updates statuses, or adds settlement rows |
Google Sheets sharing roles and access settings are described in this Google Sheets permissions guide. Start with Viewer or Commenter while the rule is being discussed. Use Editor for the people who actually maintain the tracker.
Protect the header and formula ranges, then leave input cells editable. The guide to protecting sheets and ranges shows how to protect a range while allowing edits in selected cells.
Review link access whenever a roommate moves out. The spreadsheet is a record of roommate payments, not a public noticeboard.
Fix the mistakes that create disputes
| Common mistake | Better practice |
|---|---|
| Giving someone 0% because they bought a filter | Decide whether the filter is personal or whether a specific utility line is excluded |
| Giving the payer 100% on every bill | Separate payment advance from each person's responsibility |
| Requesting money while the split check is wrong | Correct the percentages first |
| Losing a penny during rounding | Let the final share absorb the remaining cents, or agree on a rotation |
| Editing an old row without an explanation | Add a note with the correction date and reason |
| Marking a request paid when it was only sent | Update the status after the payer confirms receipt |
| Putting a separate personal purchase into the water bill | Track it on its own row or tab |
Turns out, the cents are often where a tidy split gets untidy. A visible residual formula is better than silently changing someone's amount.
The spreadsheet also does not change the utility provider's account terms. If the account is in one roommate's name, check the provider's rules and any lease or roommate agreement before relying on the tracker as proof of responsibility. Local landlord and utility requirements can differ.
Test one bill before sharing the template
Enter the most recent bill, apply the agreed rule, and create a sample settlement row. Check that the total shares equal the amount to split, the payer is identified correctly, and the status remains open until payment arrives.
To be honest, a short written rule beats a clever formula. Once everyone approves the treatment of the filter and any fixed charges, copy the tested row for the next bill and add settlement rows as reimbursements happen.