An Airbnb cost split sheet should show both the charge and the person who carries it. Put one expense on each row, record the date in column A, and mark each guest's share weight. Then the sheet can calculate who paid, who benefited, and what remains to reimburse.
A reimbursement is not a new trip expense. Log it on a separate Settlements tab so you don't count the same money twice.
Choose a layout before entering receipts
Use the simple layout when the same people share every line. Use the participant matrix when guests skip nights, share only certain meals, or need weighted shares. The second option handles more trip variations.
| Layout | Use it when | Main fields |
|---|---|---|
| Simple equal split | The same group shares every expense | Date, description, payer, amount, people splitting, per-person share, notes |
| Participant matrix | Different guests benefit from different expenses | Date, description, payer, amount, one share-weight column per person, total weight, per-share amount, notes |
The matrix takes a few more columns. It also prevents the classic "everyone owes one-fourth" mistake.
Set up the Expenses tab
Create a blank Google Sheet and rename the first tab Expenses. Use one row for one charge, refund, or adjustment. Keep the date tied to a consistent rule: use the charge date, or use the first stay date and put the full range in the description.
| Column | Header | What to enter |
|---|---|---|
| A | Date | A real date, not typed text such as "July 15" |
| B | Description | Rental charge, cleaning fee, taxes, service fee, groceries, gas, activity, or refund |
| C | Payer | The person who paid the receipt |
| D | Amount | The amount charged; use a negative amount for a recorded refund |
| E:H | Guest share weights | Put guest names in row 1. Use 1 for a full share and 0 for no share |
| I | People sharing / total weight | Formula that adds the row's weights |
| J | Per-share amount | Formula that divides the amount by the total weight |
| K | Notes | Receipt link, attendance detail, or refund explanation |
Use additional columns for additional guests. A weight can be something other than 1, but agree on that rule first. For example, a half share may help with a partial meal or a deliberately weighted room split.
Set up the sheet in this order:
- Add the headers in row 1 and replace
Alex,Jordan,Casey, andRileywith the actual guest names. - Format column A with
Format > Number > Date. - Format column D and column J as currency.
- Freeze the header with
View > Freeze > 1 row. - Turn on
Data > Create a filterso the group can sort by date or review one person's charges. - Enter the formulas in columns I and J before the group starts adding receipts.
Do not use names with different spellings. Jordan, jordan, and Jordan can behave like different entries in formulas.
Add the cost-splitting formulas
Enter these formulas in row 2, then copy them down far enough for the trip.
I2: =SUM(E2:H2)
J2: =IFERROR(D2/I2,0)If you reserve rows through 500, a small summary box outside the data table can use:
M1: Total expenses
M2: =SUM(D2:D500)For a simple equal-split version, replace the participant columns with People Splitting in column E and use Per-Person Share in column F:
F2: =IFERROR(D2/E2,0)That shorter model works only when every person in the group shares every expense. In its matching Balances tab, use =SUM(Expenses!F:F) for each person's share owed and =SUMIF(Expenses!C:C,A2,Expenses!D:D) for the amount that person paid. If only two guests eat a $48 dinner, mark the two guests in the matrix instead.
Here is a small test set:
| Date | Description | Payer | Amount | Alex | Jordan | Casey | Riley | Notes |
|---|---|---|---|---|---|---|---|---|
| 07/10/2026 | Airbnb deposit | Alex | 800 | 1 | 1 | 1 | 1 | Track the return separately |
| 07/15/2026 | Cleaning fee | Jordan | 160 | 1 | 1 | 1 | 1 | All guests share |
| 07/16/2026 | Dinner | Alex | 48 | 1 | 1 | 0 | 0 | Alex and Jordan only |
The last row has two shares, so its per-share amount is $24.00. That is the useful difference between a true participant matrix and a single group-size cell.
Build the Balances tab
Add a second tab named Balances and a third tab named Settlements. List each guest once in column A. The other columns separate money paid, the guest's calculated share, and any settlements already recorded.
| Column | Header | Meaning |
|---|---|---|
| A | Name | Must match the guest headers in Expenses |
| B | Paid | Expenses where this person is the payer |
| C | Share owed | This person's weighted share of the expenses |
| D | Net before settlement | Paid minus share owed |
| E | Sent | Completed payments sent to others |
| F | Received | Completed payments received |
| G | Open balance | Net after recorded settlements |
Put these formulas in row 2:
B2: =SUMIF(Expenses!$C$2:$C$500,A2,Expenses!$D$2:$D$500)
C2: =SUMPRODUCT(INDEX(Expenses!$E$2:$H$500,0,MATCH(A2,Expenses!$E$1:$H$1,0)),Expenses!$J$2:$J$500)
D2: =B2-C2
E2: =SUMIFS(Settlements!$C$2:$C$500,Settlements!$A$2:$A$500,A2,Settlements!$E$2:$E$500,"Paid")
F2: =SUMIFS(Settlements!$C$2:$C$500,Settlements!$B$2:$B$500,A2,Settlements!$E$2:$E$500,"Paid")
G2: =D2+E2-F2Copy the formulas down for each name. The INDEX and MATCH combination finds that person's share-weight column by matching the name in row 1.
A positive open balance means the person should receive money. A negative balance means the person still owes money. Check the model with =SUM(D2:D20) or a range that covers all guests. It should equal zero before settlements, subject to normal rounding.
In the test set, the $800 deposit and $160 cleaning fee create a $960 shared cost. Four guests owe $240 each. Alex paid $800, so Alex is at +$560 before settlement. Jordan is at -$80, while Casey and Riley are each at -$240.
Keep reimbursements on a Settlements tab
Use the third tab to record money moving between people. It does not change the trip's expense total.
| Column | Header | Example |
|---|---|---|
| A | From | Jordan |
| B | To | Alex |
| C | Amount | 80 |
| D | Date | Date the payment was made |
| E | Status | Pending or Paid |
| F | Notes | Payment confirmation or receipt link |
Add a row when a payment is requested or made. Leave Status as Pending until the payment clears, then change it to Paid. The balance formulas count only paid rows. Split one person's payments into separate rows if several people receive money.
A clear request can say: "The sheet shows $240 due for the shared rental. Please mark it paid after you send it."
With the test set, one workable settlement is Jordan paying Alex $80, Casey paying Alex $240, and Riley paying Alex $240. The sheet records the agreement. Your chosen payment method handles the transfer.
Turns out, keeping the settlement log separate also makes refunds easier to audit. If the host returns a deposit to Alex, enter a negative expense row with Alex as the payer, keep the same participant weights, and explain the refund in Notes; do not enter that return as a reimbursement from one guest to another.
Use the date column for review, not decoration
Sort the Expenses tab from oldest to newest before a trip review. Filter by the stay dates when you want to check only rental charges, or filter by a payer when a receipt is missing.
For a charge covering several nights, use one row with the date convention you chose and write the range in Description. Use separate rows when the group changes from night to night. This small choice keeps the date filter useful later.
This is the bit people skip, then revisit three times. Keep it boring and explicit.
Consider adding a Category column if the group wants rental, food, transportation, and activity totals. Keep categories separate from Description; descriptions explain the receipt, while categories support summaries.
Pick the split rule before the trip
Equal splitting is easy, but it is not automatically fair. The right rule depends on who used the rental or charge.
| Split rule | Works better when | Record it this way |
|---|---|---|
| Equal shares | Everyone uses the same item or stay | Enter 1 for each participant |
| Nights stayed | Guests arrive or leave on different dates | Use separate rows or agreed night weights |
| Actual users | Only some people join a meal, activity, or ride | Mark only those participants |
| Weighted shares | The group has an agreed room, age, or usage adjustment | Write the rule in Notes or a Rules tab |
Thing is, the formula only applies the rule. It cannot settle a disagreement about the rule itself. Agree before booking when the split may feel sensitive.
A refundable deposit deserves its own note. If the group is fronting it while it remains outstanding, include it in the current balance, then add a negative adjustment when it is returned. That keeps the history visible.
Share the file without breaking the math
Give contributors access to the same sheet instead of collecting separate copies. Google explains the available sharing and protected-range controls in its official Sheets instructions.
- Add specific group members when the sheet contains payment details.
- Use Editor access only for people who need to enter or correct rows.
- Protect formula columns I:J and the
Balancestab, while leaving expense input cells editable. - Keep receipt links limited to people who can view the underlying files.
A protected range reduces accidental edits. It does not replace sensible sharing permissions. Use Viewer access for someone who only needs to inspect the record.
Common mistakes and their fixes
| Mistake | Fix |
|---|---|
| Recording a reimbursement as a new expense | Put the transfer on Settlements |
| Using four people for every row | Mark each participant or weight row by row |
| Typing dates as plain text | Format column A as Date and re-enter invalid values |
| Leaving formulas limited to the first few rows | Reserve a range such as row 500 and extend it when needed |
| Keeping a returned deposit in the total | Add a negative adjustment with a clear note |
| Overwriting a formula while entering a receipt | Protect formula cells and leave input columns open |
To be honest, most disputes come from missing context, not difficult formulas. Add the receipt link, the reason for a partial split, and the date of any refund while the details are still fresh.
Questions to settle before sharing
Can I use the seven-column version?
Yes, if the same guests share every expense. Use Date, Description, Payer, Amount, People Splitting, Per-Person Share, and Notes. The formula in the share column is =IFERROR(D2/E2,0). Switch to the matrix as soon as participants vary.
How do I record a refund?
Use a negative amount in Expenses when the refund changes the group cost. Keep the same participant weights and describe what was returned. Use Settlements when one guest sends money to another.
Can the sheet show exactly who should pay whom?
It shows each person's net position. Match negative open balances to positive ones, then record the agreed transfers on Settlements. The sheet is a recordkeeping tool, not a payment service.
What belongs in Notes?
Add a receipt link, the reason only some people participated, the date range covered, or the status of a refundable charge. Do not paste passwords or full card numbers.
Before sharing the file, enter one deposit and one expense with different participants. Confirm the totals, test the date filter, and make sure the balances add to zero before anyone sends money.