A shared Google Sheet can handle a one-off Myrtle Beach rental with friends. Use one row for each actual charge, then use participant units to split that row fairly.
Who paid the deposit, and who owes a share? The layout below makes both answers visible. It tracks expenses and IOUs; it does not send or hold money.
The template is free to recreate and easy to adapt. It works for lodging, cleaning fees, groceries, gas, meals, utilities, and small incidentals.
Choose the split rule before the first charge
Agree on the split before anyone pays. A formula cannot settle an argument about whether a private room should cost more.
Turns out, the spreadsheet is the easy part. The group rule matters more.
| Situation | Practical rule | What to enter |
|---|---|---|
| Everyone stays the same nights | Equal per-person split | Enter 1 for each participant |
| Arrival or departure dates differ | Split lodging by nights | Enter each person's nights on the lodging row |
| Rooms differ in size, privacy, or amenities | Use agreed room weights | Enter each person's agreed weight |
| Groceries, meals, or gas are used by some people | Usage-based split | Include only the people who used the item |
| An item benefits one person | Keep it personal | Enter one unit for that person, or leave it off the shared costs |
Keep lodging and other charges on separate rows when their rules differ. Rent may follow nights, while a cleaning fee may be divided equally and groceries may include only the people who ate them. Write the decision in the Summary tab so nobody has to reconstruct it later.
Build the Expenses tab
Give every payment its own row. Record the amount actually paid, not an estimate from the listing page.
Use six sample names below, then replace them with your group's names.
| Column | Header | What to enter |
|---|---|---|
| A | Expense ID | A simple reference such as MB-001 |
| B | Date | The date the charge was paid or posted |
| C | Description | Airbnb deposit, final lodging balance, groceries, or gas |
| D | Amount | The actual U.S. dollar amount |
| E | Category | Lodging, Cleaning, Utilities, Groceries, Meals, Gas, Personal, or Other |
| F | Paid By | The exact name of the person who paid |
| G:L | Participant units | Alex through Riley; use 1, 0, nights, or agreed weights |
| M | Total Units | Formula for the row's total units |
| N:S | Participant owed | Formula for each person's share |
| T | Receipt reference | A filename, vendor, or short note |
A unit is flexible. Use 1 for an equal share, 0 for someone excluded from the charge, and a number such as 4 for four nights on a nights-based lodging row.
The first version will not be pretty. That's fine. Get the names, amounts, and units working before spending time on colors.
Add the cost-splitting formulas
Enter the formulas in row 2 of the Expenses tab. Copy them down as you add rows.
In M2, calculate the total units:
=SUM(G2:L2)In N2, calculate Alex's share:
=IFERROR($D2*G2/$M2,0)Drag N2 across to S2. The participant reference will move from G to L, while the total-units column stays fixed. Then copy M2:S2 down the sheet.
If a charge is split equally among six people, enter 1 in all six participant columns. If the same charge follows nights, enter each person's nights instead. The formula handles both cases.
Keep amounts unrounded in the expense rows. Format them as currency for display, then round the final balances.
Build the Summary tab
Set up the Summary tab horizontally so the formulas are easy to copy.
| Cell or row | Label or value |
|---|---|
| A1 | Leave blank |
| B1:G1 | Alex, Jordan, Casey, Taylor, Morgan, Riley |
| A2 | Paid |
| A3 | Owed |
| A4 | Net |
| A5 | Check |
In B2, enter the amount Alex paid:
=SUMIF(Expenses!$F$2:$F$1000,B$1,Expenses!$D$2:$D$1000)Copy B2 across to G2.
In B3, total Alex's owed column:
=SUM(Expenses!N$2:N$1000)Copy B3 across to G3. Because N is the first owed column, the copied formulas will use O, P, Q, R, and S for the other people.
In B4, calculate Alex's net balance:
=ROUND(B2-B3,2)Copy B4 across to G4. A positive net means that person paid more than their share and should receive money. A negative net means that person owes money.
In B5, check the group total:
=ROUND(SUM(B4:G4),2)The result should be $0.00. If it is not, inspect blank participant units, mismatched names, duplicated charges, or rounding.
For a category total, use a SUMIF formula such as:
=SUMIF(Expenses!$E$2:$E$1000,"Groceries",Expenses!$D$2:$D$1000)The paired ranges should start and end on the same rows. If you extend the sheet beyond row 1000, extend every related formula range too.
Set up the Google Sheets template
- Create a blank spreadsheet and name it something clear, such as Myrtle Beach Airbnb Costs.
- Rename the first tab Expenses. Add Summary and Reimbursements tabs.
- Add the headers in the order shown above, then replace the sample names with the real participants.
- Format column B as a date and column D as currency. Freeze row 1 with View, Freeze, 1 row.
- Add the formulas in row 2 and copy them down far enough for the trip.
- Add data validation to Paid By so people choose a name instead of typing different spellings. Set the participant columns to accept nonnegative numbers.
- Protect the formula columns M:S and the Summary tab. Leave the input columns available to the people who are entering expenses.
Use one row per real payment. Do not enter both a booking total and the separate payments that make up that total.
Share the sheet without losing the math
Share the file with named people rather than using a public link. Give editing access to the people who will enter charges, and use viewing access for anyone who only needs to check the totals.
| Access level | Good use |
|---|---|
| Owner | Maintains formulas, tabs, and access |
| Editor | Adds expenses and receipt references |
| Viewer | Reviews costs without changing cells |
Thing is, protected ranges reduce accidental formula edits; they are not a substitute for careful sharing. Anyone who can view the file may still see the expense information.
For the standard sharing menu, see this Google Sheets sharing permissions walkthrough. A separate Google Sheets ranges protection walkthrough covers the protected-range flow.
Keep the Summary formulas locked. Let the group edit only the input area.
Track the trip from booking to settlement
| Stage | Action |
|---|---|
| Before booking | List participants and write the equal, nights-based, or room-weight rule in Summary |
| When someone pays | Add the actual charge, payer, category, and participant units |
| During the trip | Add groceries, meals, gas, and other shared purchases soon after they happen |
| After the final charge | Add missing fees, utilities, credits, and refunds, then inspect the Check cell |
| When someone reimburses | Record the payment on Reimbursements without changing the original expense |
Use these columns on Reimbursements:
| Date | From | To | Amount | Status | Note |
|---|
The spreadsheet records the obligation. Use whatever payment method your group already trusts, then log the result separately. Do not add a reimbursement as a new expense, or the trip will appear to cost more than it did.
Save receipt references using the Expense ID. A note such as MB-014 grocery receipt is enough if the original receipt is stored elsewhere.
Handle uneven nights, rooms, and deposits
Different arrival and departure dates
Put the number of nights in each person's units column on the lodging row. Someone staying four nights gets 4 units; someone staying two nights gets 2. The formula divides the lodging amount by total nights and assigns each person's portion.
Use a separate row for a fixed charge if the group wants that charge split equally. A cleaning fee row might use 1 for each included participant even when the rent row uses nights.
Different rooms
Use agreed weights on the lodging row when rooms have different value. A larger or more private room can receive more units, while people sharing that room can divide its agreed weight between them.
Document the reason in Summary. Room size, bathroom access, privacy, and noise are possible considerations, but the group should choose the rule before the trip.
Deposits and refunds
If Alex pays the full deposit, enter Alex in Paid By and give every included participant the appropriate units. Alex's paid total will rise, while the owed columns show each person's share.
Record a refundable security deposit once. If it is later returned, add a negative row for the refund using the same agreed units. If a deposit was already included in the final booking total, do not count it twice.
Common errors that change the balance
To be honest, most disputes come from small data-entry misses rather than difficult formulas.
| Problem | Fix |
|---|---|
| A name is spelled differently in Paid By and Summary | Use a name dropdown and keep headers identical |
| A row has zero total units | Add the people or weights who should share that charge |
| A deposit and final booking total are both entered in full | Record each actual payment only once |
| Every row is rounded before the summary | Keep row calculations unrounded and round final balances |
| A reimbursement is entered as an expense | Use the Reimbursements tab instead |
| A formula is overwritten | Protect M:S and the Summary formulas |
| A personal purchase is marked for everyone | Give it one participant or remove it from shared costs |
If a balance is off by a cent, check the displayed rounding first. Assign a remaining cent to one person at settlement rather than changing every expense row.
FAQ
Is a spreadsheet enough for a friends' trip?
For a simple one-off rental, often yes. It gives the group a shared record, flexible split rules, and a clear reimbursement total. It will not scan receipts, send payment requests, or enforce repayment.
What if one person pays the entire Airbnb deposit?
Enter that person in Paid By. Mark the actual participants with their agreed units. The payer gets credit for the full amount, and the Summary shows the others' obligations.
Should every Airbnb fee be split equally?
No single rule fits every group. Put fees on separate rows when the group wants different treatment, then use equal units, nights, or another agreed method for each row.
How do we settle the final balances?
Match people with negative Net balances to people with positive Net balances. Record each completed payment on Reimbursements, and leave the original expense records unchanged.
Create the Expenses, Summary, and Reimbursements tabs before the trip. Add one sample shared charge, confirm the Check cell returns $0.00, and have everyone approve the split note before the first deposit is paid.