Group Trip Budget Template: Shared vs Personal Costs

Introduction

A friend pays $300 for accommodation and receives $100 from each of two companions. The group spent $300, not $500. If the spreadsheet counts those repayments as new expenses, its budget is wrong even though everyone has settled up.

A group trip budget template for shared and personal expenses needs to answer two questions separately: how much the trip costs, and who still owes money. The same purchase records support both calculations, but transfers between friends affect only the balances.

The layout below works for three example travelers, Ana, Bo, and Cam. Replace the names with your group and add share columns for extra people. All amounts in the worked examples are hypothetical.

Create a plan, an expense ledger, and a settlement log

Use four tabs: Plan, Expenses, Settlements, and Balances. The summary cells on Plan can serve as your dashboard.

On Plan, list categories with their agreed budget, actual spending, unpaid commitments, and estimated remaining spending. Count each cost once. A $100 deposit on a $300 hotel booking means $100 paid and $200 still committed, not $100 paid plus another $300 due.

Use this forecast:

forecast total = actual expenses + unpaid commitments + remaining estimates

Keep the reserve separate. Money moved between friends is not an unpaid hotel commitment or a new expense. That separation is easier to maintain with a consistent ledger layout.

Copy these columns into the Expenses tab

Put the following headers in row 1:

Column	Header	What to enter
A	Date	Transaction date
B	Description	What was bought or refunded
C	Classification	Shared, optional, or personal
D	Currency	Original transaction currency
E	Original amount	Receipt amount; negative for a supplier refund
F	Conversion rate	Reporting-currency units per original-currency unit
G	Reporting amount	Converted expense amount
H	Paid by	Payer, or recipient of a supplier refund
I	Ana share	Ana's assigned amount in reporting currency
J	Bo share	Bo's assigned amount
K	Cam share	Cam's assigned amount
L	Allocation check	Expense minus assigned shares
M	Notes	Receipt, agreement, split method, or pending status

In G2, use =ROUND(E2*F2,2) and fill down. If you already have the card's final posted amount, enter that reporting amount directly in G and note the method. Preserve the original receipt value in E.

In L2, use =ROUND(G2-SUM(I2:K2),2) and fill down. Every completed row should show zero. Shares must add to the reporting amount; split rules belong in the notes so someone else can check them.

For a personal purchase, assign the full amount to its owner. If they also paid, it won't change anyone's balance, but it will appear in their trip spending. Include personal purchases consistently if you want the dashboard to represent the whole holiday.

With the columns in place, agree what is shared before entering the amounts.

Separate approved costs from personal upgrades

A common accommodation baseline describes what the group approved. It does not automatically entitle someone to a contribution toward any room they choose.

For example, if a group agrees to share one $120 room and keeps that same booking while upgrading it to $180, the $60 difference can be assigned to the person requesting the upgrade if everyone agrees. But if that person books an additional private room, the costs are different. Record the actual reservations and the new allocation rather than treating the original $120 as a transferable allowance.

The same principle applies to premium seats, extra nights, drinks, and optional tours. Use exact share amounts when choices differ. A traveler who opted out before purchase should not silently acquire a share; cancellation after an approved nonrefundable booking needs the agreed cancellation rule.

The allocation check catches arithmetic errors. It cannot tell whether the group consented to the split, which is why the notes matter.

Calculate each person's balance

On Settlements, use columns A to D for Date, From, To, and Amount. Enter transfers between friends as positive amounts in the reporting currency. Keep supplier refunds on Expenses instead.

On Balances, put these headers in columns A to F: Name, Net paid, Assigned cost, Sent, Received, and Balance. For Ana in row 2:

Cell	Formula
B2	=SUMIF(Expenses!H:H,A2,Expenses!G:G)
C2	=SUM(Expenses!I:I)
D2	=SUMIF(Settlements!B:B,A2,Settlements!D:D)
E2	=SUMIF(Settlements!C:C,A2,Settlements!D:D)
F2	=ROUND(B2-C2+D2-E2,2)

For Bo and Cam, copy the row but change the assigned-cost formula to sum J and K respectively. The name references in the other formulas should follow their rows. Google's SUMIF documentation explains the conditional sums used here. Some spreadsheet locales require semicolons instead of commas.

A positive balance means the person should receive money; a negative balance means they should pay. Sending a settlement increases the sender's balance toward zero, while receiving one reduces the recipient's balance toward zero.

Across the group, balances should sum to zero, allowing for rounding. If they don't, check incomplete allocations, misspelled names, or transfers involving someone not listed.

Test the sheet with three purchases

Enter a $30 taxi paid by Ana, shared equally; an $80 dinner paid by Bo, allocated $30 to Ana, $30 to Bo, and $20 to Cam; and $40 of tickets paid by Cam, split between Ana and Cam.

Traveler	Paid	Assigned cost	Balance before settlement
Ana	$30	$60	-$30
Bo	$80	$40	$40
Cam	$40	$50	-$10

The expense total is $150. Enter Ana paying Bo $30 and Cam paying Bo $10 on Settlements. All balances should become zero, and the expense total should remain $150.

This test verifies more than whether columns add up: it checks that your sheet treats purchases and repayments differently. Refunds need the same care.

Record refunds and keep the forecast current

If a supplier returns $60 to Ana for an expense originally split equally, enter a negative $60 expense with Ana in Paid by and negative $20 in each share column. This reduces net spending, reduces each assigned cost, and adjusts Ana's net payment. Don't also record the same supplier refund as a settlement between friends.

For partial refunds, allocate the money according to the refunded portion and the group's agreement. Splitwise's refund guidance explains its corresponding refund workflow if you use an app for settlement instead of the spreadsheet.

Track provisional exchange conversions until the posted card amounts are available. Keep supplier deposits and card holds distinct from final expenses when they are refundable, and note any temporary cash requirement.

Review missing entries regularly, update unpaid commitments after payments, and remove estimates once they become actual expenses. A group trip budget template for shared and personal expenses works when the forecast explains what remains to spend and the balances explain what remains to repay. Neither number should have to do both jobs.