Spreadsheets

Group trip budget template in Google Sheets with who owes whom

A group trip budget template for Google Sheets: pre-trip estimates, an expense log with payer and participants, and a settle-up block for who owes whom.

Editorial illustration for Group trip budget template in Google Sheets with who owes whom

A group trip sheet needs two parts: a pre-trip tab that estimates costs per head, and a log where every expense records who paid and who shared it. A settle-up block then subtracts what each person owes from what they paid. Positive means the group owes them, negative means they owe the group, and the column always adds up to zero.

What goes on the pre-trip tab?

List stay, transport, food and activities with an estimate for each in column B, and the number of travellers in a cell, say B8. Put the per-head figure in column C: =B2/$B$8. Add a total in row 7, =SUM(B2:B6), and a per-head total, =B7/B8. Say the estimates are stay 20,000, transport 8,000, food 8,000 and activities 4,000. That is 40,000, or ₹8,000 a head for five people. Estimates are guesses; their value is that you can compare them with the log at the end and see which category surprised you.

How should the expense log be laid out?

Name the second tab Log. Rows 2 to 100, with five friends: Aman, Bhavna, Chirag, Divya and Eshan.

  • A Date, B Item, C Amount, D Paid by (type the name exactly).
  • E to I: one column per person, headed with their name. Enter 1 if the person shares that expense, otherwise 0.
  • J, number sharing: =SUM(E2:I2)
  • K, per head: =IF(J2=0,0,C2/J2)

The 1 and 0 flags make it easy to handle an activity that only some people did, such as paragliding. A shared cab for everyone gets five ones; a ride three people took gets three.

What does the settle-up block calculate?

Make a small table somewhere to the right, one row per person. For Aman in row 2, with his name in M2:

  • N2, paid: =SUMIFS($C$2:$C$100,$D$2:$D$100,M2)
  • O2, owed: =SUMPRODUCT($K$2:$K$100,E$2:E$100) for Aman. For Bhavna, the column reference moves to F, and so on.
  • P2, net: =N2-O2

Check cell: =ROUND(SUM(P2:P6),2) must be 0. If it is not, a name was misspelt in column D or a row has no flags.

Can you show a worked example?

Say five friends take a four-day hill trip. Hypothetical expenses:

ExpenseAmount (₹)Paid byShared byPer head (₹)
Stay20,000AmanAll five4,000
Fuel, first fill4,000ChiragAll five800
Fuel, second fill3,000EshanAll five600
Food9,000BhavnaAll five1,800
Paragliding6,000DivyaAman, Divya, Eshan2,000

The trip cost 42,000 in total. The settle-up block then reads:

PersonPaid (₹)Owed (₹)Net (₹)
Aman20,0009,200+10,800
Bhavna9,0007,200+1,800
Chirag4,0007,200-3,200
Divya6,0009,200-3,200
Eshan3,0009,200-6,200

Owed for the three who went paragliding is 7,200 plus 2,000, so 9,200; the other two owe 7,200. The nets sum to zero: 10,800 plus 1,800 minus 12,600 is 0. To clear the debts, Chirag pays Aman 3,200, Divya pays Aman 3,200, Eshan pays Aman 4,400 and Eshan pays Bhavna 1,800. That is four transfers instead of a muddle of many.

Compare with the estimate: 42,000 divided by five is ₹8,400 a head, so the trip ran ₹400 a head above the pre-trip figure. A traveller who joins for only part of the trip, or a child who travels free, simply gets a 0 on the rows they did not share.

How do I keep the log honest during the trip?

  1. One person enters rows each evening from everyone's messages.
  2. Photograph bills for anything above an agreed amount.
  3. Log cash separately from UPI, and note the payer for each.
  4. Do not round per-head amounts in the log; round only when paying.

SpendixAI is not a bill-splitting app, so keep the settle-up in this sheet. Your own part of the trip, whichever way you paid, will appear in your statement, where you can categorise it as travel.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports