A travel budget spreadsheet that survives the trip
A travel budget spreadsheet needs seven categories, a planned budget built from per-day and fixed costs, and an actuals log you fill in every night. Add a buffer line and a separate line for card fees. Compare planned with actual by day two, while you can still change the plan.
Most travel budgets fail on the second day, when the sheet stops being updated. The design below is built to be filled in quickly, on a phone, by someone who is tired. The amounts in the worked example are made up and round. Use your own quotes, receipts and booking confirmations, because anyone else's daily figure belongs to a different trip.
Seven categories, no more
Use lodging, transport, food, activities, shopping, insurance and fees, and a buffer. Fewer than that and a catch-all line swallows half the budget; more than that and nobody fills the sheet in. Seven is enough to show where the money went and few enough to log in a minute.
Inside a category, split the lines that people argue about. Track groceries apart from restaurants and split each its own way, because one meal out for the whole group and a shop for the kitchen are different kinds of shared cost. Put taxes and deposits on their own lines under lodging, so the number you booked and the number you paid are both visible.
- Lodging: rooms per night, deposits, local taxes
- Transport: flights, local transit, transfers, parking, fuel and tolls
- Food: groceries, restaurants, coffee and snacks
- Activities: tours, entry tickets, tips
- Shopping: souvenirs, pharmacy, forgotten gear
- Insurance and fees: the policy, card fees, visas
- Buffer: money you have not assigned
Estimate before you book anything
You need three things to build a first budget: the costs that are fixed, a daily figure for the costs that scale with the length of the trip, and the number of people. Fixed costs are the ones you pay once, such as flights, an intercity train, a tour and insurance. Daily costs are meals, local transit, coffees and entry fees.
Take the daily figure from your own receipts or from recent quotes for the place, and write down where it came from. Do not borrow a number from an article about somewhere else, because it hides the assumptions that make it right or wrong for you. The point of the first budget is to learn which lines are big, and you will refine the rest as bookings arrive.
A worked example with made-up round numbers: four people, five days, and a food figure of 30 per person per day gives 600 for food. Add a fixed cost of 400 per person for transport and 900 for lodging in total, and each line can be set against what you actually pay.
Build it in three tabs
The first tab holds the setup: the dates, the number of people, your home currency and a small table of the currencies you will spend, each with a rate. The second is the planned budget, one row per category with columns for per person per day, days and people, and a total that multiplies across. The third is the actuals log.
The log has one row for each expense: date, category, currency, amount, who paid, and the amount converted to your home currency. At the top of the planned tab, add the actual total for each category with a SUMIF. Google's help describes SUMIF as a conditional sum across a range, with the syntax SUMIF(range, criterion, [sum_range]). A formula such as =SUMIF(Log!B:B,"Food",Log!F:F) adds every converted amount whose category is Food.
Put planned, actual and remaining side by side for each category. Remaining is the number that changes what you do tomorrow.
Currency: convert once, at the rate you really paid
Google Sheets can pull an exchange rate in with GOOGLEFINANCE, and that is fine for planning. Google's help warns that quotes are not sourced from all markets, may be delayed by up to 20 minutes, and are provided for information only. That is a planning rate, not a receipt.
For the actuals log, use what your bank or card statement says you were charged, not the rate you saw in the sheet. The figure you paid is the figure the budget should reflect, so enter the statement amount when it posts, and let small differences sit rather than correcting them from memory.
Choose one rule for the whole trip and say it in the setup tab: either the rate on the day of each charge, or one fixed rate. Mixing the two makes the total impossible to reconcile.
Give card fees their own line
The Consumer Financial Protection Bureau's regulation treats a charge for a card purchase or cash advance made outside the United States as a foreign transaction fee, including a purchase in dollars abroad and a purchase from a foreign merchant. The fee can come from the card issuer, and a fee from a third party that does the conversion counts when it is passed on to you.
The regulation does not say how large the fee is. That is in your cardholder agreement, so read it before you go and write the rate in the setup tab. Add a line for card fees and cash machine charges under insurance and fees, so they do not quietly eat the food budget.
Log it every night, then compare
Each evening, spend a minute on three things: what was spent that day, in which category, and in which currency. Take the amount from the banking app or the receipt rather than from memory, because memory rounds down.
Then look at the remaining column. A workable rule of thumb is that if a category is running about a fifth over plan two days in a row, decide something that day, such as cooking one more meal or swapping a paid activity for a free one. It is a rule of thumb, not a law; the point is a decision on day two rather than a post-mortem at home.
Sharing the sheet with a group
For two people, a paid-by column is enough. For a bigger group, add a share per person and a running balance, and keep shared costs on one tab and personal splurges on another so that the trip's variance is not one person's shopping. Agree the split rule before the first receipt, as the guide to splitting costs describes.
Spreadsheets break at group size in three places. Nobody updates them on a phone between activities. Uneven splits, such as three people skipping the museum, need hand arithmetic. And the person who paid for dinner also has to remember to log it. If you settle in cash every night, a sheet is fine. If you settle at the end, expect an evening of reconciling.
Adapting one sheet to the kind of trip
The skeleton stays the same; what changes is which rows carry the weight. For a road trip, add fuel and tolls, work out the fuel from the distance and your car's real fuel use, and expect lodging to be several one-night stays rather than one booking.
For a family trip, add a column per child where tickets and meals are priced separately, and a child-rate flag so you know which lines are discounted. For a cruise, replace lodging with the pre-cruise hotel, port transfers and parking, gratuities, drink packages and shore excursions, and read the cruise line's own page for what is included.
The costs most budgets leave out
Money leaks out through fees and small logistics. Card and cash machine fees, tips that differ from country to country, airport transfers, checked bags, visa or entry fees, travel insurance and the extra night when a flight leaves early or late are the usual ones.
Add a line for each before you leave, even if the amount is a guess. A guess you can correct is better than a surprise on a statement.
Hold back a buffer
Finish with a buffer line of about a tenth of the total, as in the budget-trip guide. It is not spending money and nobody is allowed to plan against it. If it survives the trip it becomes the start of the next budget.
Keep the shared costs next to the plan
In Ollin you log an expense, say who paid, split it evenly or by share, and see who owes whom across the whole trip, with an optional budget to keep the total in view, all beside the day-by-day plan the group shares. It is free for up to 15 travelers on a trip; expense insights, multi-currency and CSV export are on Pro.
Use the spreadsheet for the planning and the category view you want to shape yourself, and use the shared plan for the part that needs everyone: who paid and who owes.
The short version
- Use seven categories: lodging, transport, food, activities, shopping, fees and insurance, buffer
- Estimate from fixed costs plus a daily figure from your own receipts
- Build three tabs: setup, planned budget, actuals log
- Total each category with SUMIF and show remaining next to planned
- Log the amount your card or bank actually charged
- Give card fees and cash machine charges their own line
- Log every night and compare by day two
- Keep a buffer line of about a tenth of the total
Do the arithmetic
Add who paid and who shared each bill and get the few payments that settle everyone up. It runs in your browser, with no sign-up.
Free trip cost splitterSources
- Google Docs Editors Help: SUMIF: the function that totals one category from a log, with its syntax and an example.
- Google Docs Editors Help: GOOGLEFINANCE: pulling exchange rates into a sheet, and the caveat that quotes may be delayed.
- Consumer Financial Protection Bureau: Regulation Z, section 1026.4: what counts as a foreign transaction fee on a credit card and who can impose it.
Questions
What categories should a travel budget include?
Seven: lodging, transport, food, activities, shopping, insurance and fees, and a buffer. Split lines inside a category when a group would argue about them, such as groceries and restaurants.
How do you total spending by category in Google Sheets?
Use SUMIF, which Google describes as a conditional sum across a range. Keep one row for each expense with a category column and an amount column, and total a category with a formula like =SUMIF(Log!B:B,"Food",Log!F:F).
Should you use GOOGLEFINANCE for exchange rates in a travel budget?
Use it for planning only. Google says its quotes may be delayed by up to 20 minutes and are for information, so log the amount your bank or card actually charged for the actuals.
Are foreign transaction fees charged on purchases in dollars?
They can be. The Consumer Financial Protection Bureau's regulation treats a charge on a purchase made outside the United States as a foreign transaction fee, even when the purchase is in dollars. Your cardholder agreement gives the actual rate.
How much buffer should a travel budget hold?
About a tenth of the total is a common choice. Treat it as unassigned money for fees, transfers and the unplanned extra night, not as spending money.
How do you split a travel budget with a group?
Agree the split rule before the first receipt, record who paid for every expense, and keep personal spending separate from shared costs. Settle the net balances once, at the end or each night.
Plan it together in one place
Describe the trip and watch the plan come together — free, no account needed.
Plan your trip freeGet the app for iPhone and Android
Keep reading: How to plan a vacation on a budget: the decision order · How to split trip costs with friends, fairly · A day-by-day itinerary template for Google Sheets, built step by step · How to plan a road trip, step by step · Group trips · Family trips