Why pay for a subscription app when a single spreadsheet can settle your household rent every month?

A shared spreadsheet keeps everyone honest. You do not need third-party payment integrations or confusing account sign-ups to divide housing costs fairly. With five columns and a few basic formulas, Google Sheets calculates exact contributions, tracks who paid, and shows remaining balances in real time.

It just works.

Pick Your Splitting Method

Equal splits work best when bedroom sizes and amenities match closely. If four roommates share a $3,200 apartment and everyone has roughly identical bedrooms, each person simply owes $800.

Rooms are rarely identical, though.

When one roommate has a private bathroom and a walk-in closet while another takes a tiny converted den, an even split creates resentment. In those households, square footage gives you an objective baseline. You divide each private room's square footage by the total private bedroom space to get individual percentages.

Income-based splitting works differently.

Couples and close friends often choose this route when earnings differ significantly. Turns out, setting rent proportionally by income ensures no single person spends half their paycheck while another spends ten percent. According to a comparison of rent splitting methods by FairSplit, proportional splits prevent lower earners from slipping past the standard 30 percent housing cost burden.

Set Up the Spreadsheet Grid

Open a clean sheet. Put your core table in rows 1 through 10. You can always expand the range later if you bring in more housemates.

Here is a clean column structure for the sheet:

Column Header Purpose
A Tenant Name Roommate or tenant identifier
B Split Basis Monthly income or bedroom square feet
C Total Rent Base monthly lease amount (same cell reference)
D Share % Calculated percentage of rent
E Amount Owed Individual dollar share for the month
F Paid? Yes/No status dropdown
G Notes Adjustments, utility add-ons, or parking fees

Add your fixed numbers first. Put the total monthly rent charged by the landlord into cell C2.

If you split evenly, you can leave column B blank. If you split by earnings or room dimensions, enter each person's monthly take-home income or square footage in cells B2 through B5.

Essential Formulas for Your Calculations

Spreadsheet formulas eliminate manual math and rounding arguments. Once you set them up, the sheet updates instantly whenever numbers change.

For an even split, place this formula in cell E2:

=C$2 / COUNTA(A2:A10)

The COUNTA function counts how many names appear in column A. As people move in or out, the dollar share recalculates automatically. The dollar sign before row 2 freezes the total rent cell so you can drag the formula down without breaking the reference.

For a proportional split based on income or room size, calculate the percentage in cell D2 first:

=IFERROR(B2 / SUM($B$2:$B$10), 0)

People often forget to update formulas when somebody moves out, leaving you staring at a divide-by-zero error on the first of the month because nobody adjusted the sum range. The IFERROR wrapper prevents that headache.

Then calculate the individual dollar amount in cell E2:

=D2 * C$2

Copy both formulas down across all active tenant rows. As shown in the Spark rent split calculator guide, checking that column D sums to 100% guarantees no portion of the lease is left uncovered.

Now build a summary block in row 12 to track payment collection:

  • Total Collected: =SUMIFS(E2:E10, F2:F10, "Yes")
  • Remaining Balance: =SUMIFS(E2:E10, F2:F10, "No")
  • Lease Total Check: =IF(SUM(E2:E10)=C2, "Matches Lease", "Discrepancy")

A guide to SUMIF and COUNTIF by Unlimited Sheets demonstrates how conditional summing lets you isolate unpaid balances without writing custom scripts. If someone pays half, log partial amounts in your notes column.

Lock Down Permissions so Formulas Do Not Break

Shared spreadsheets break when someone accidentally deletes a formula cell instead of updating their payment status. You can avoid this by managing sharing roles and locking specific ranges.

First, decide who needs editing rights.

If one lead tenant handles payments to the landlord, make that person the primary editor. Share the file with the other roommates as Viewers or Commenters. If everyone needs to mark their own payments as complete, grant Edit access to the file, but restrict edits on sensitive cells.

To protect your formula cells, highlight columns C, D, and E.

Click Data in the top menu, then select Protect sheets and ranges. Set permissions to restrict editing to yourself. Detailed steps on protecting ranges from SheetsBootcamp recommend leaving only the status column editable so roommates can toggle their payment dropdown without touching formulas.

Thing is, Google Sheets sharing links can be forwarded easily if you choose "Anyone with the link can edit."

Always share directly to your roommates' email addresses instead. The GeeksforGeeks sharing permissions walkthrough outlines how restricting general link access prevents unwanted guests from viewing sensitive household finances.

Setting Ground Rules for Monthly Upkeep

A template only works if the household follows consistent habits. Without shared agreements, numbers get stale quickly.

Agree on these house standards before signing the lease:

  • The monthly deadline for logging income or travel adjustments.
  • Which expenses belong in rent versus a separate utility tracker.
  • How roommates handle mid-lease changes when somebody takes an extended vacation or sublets a room.
  • Who actually transfers the lump sum to the landlord account.

Revisit the sheet whenever someone's circumstances change. If you use income-based splits, schedule a quick check-in every six months or whenever someone gets a promotion. For utility bills that fluctuate, keep a separate tab rather than altering the base rent formula.

Open a blank Google Sheet right now, label your columns from A to G, and type your lease total into cell C2. Run the numbers with your roommates before rent day arrives so everyone knows exactly what they owe.

No surprises, no friction.