Travel and vehicles

A trip across two countries: two currencies in one sheet

Build a multi currency trip expense sheet for a two-country trip: currency, local amount, rate, rupees, country subtotals and leftover cash.

Editorial illustration for A trip across two countries: two currencies in one sheet

A trip across two countries fits in one sheet if every row carries four things: the currency charged, the amount in that currency, the rate you actually got, and the rupee amount worked out from them. Add a country column and a subtotal for each country is one SUMIFS formula away. The rates below are made up so the arithmetic is easy to follow; use the ones on your own card statements and receipts.

Which columns does the sheet need?

  • A Date and B Country: the country where the money was spent, not where you slept that night.
  • C Currency: EUR, CHF or whatever was charged.
  • D Item and E Local amount: exactly as printed on the receipt or card alert.
  • F Rate: rupees per one unit of that currency.
  • G Rupees: in row 2 write =E2*F2 and fill down.
  • H Paid by: card, cash or ATM cash, so you can check each against its own source later.

Which rate goes in the rate column?

For card payments, the cleanest number is the rupee amount on the card statement or the app alert. Until it arrives, type a planning rate and mark the row as an estimate. When the statement shows up, overwrite the rupee cell with the real figure and change the rate cell to =G2/E2, so the rate is worked backwards from what actually left your account. For cash, use the rate you paid when you got it: the rupee debit divided by the amount received at the ATM, or what you handed the exchange counter divided by what you got back.

Here is a made-up trip from Italy into Switzerland, with euros at roughly ₹90 and Swiss francs at roughly ₹100, nudged a little by each day's card rate.

DateCountryCurrencyItemLocal amountRateRupees
12 MarItalyEURDinner42903,780
12 MarItalyEURMuseum tickets30902,700
13 MarItalyEURHotel, two nights1809016,200
14 MarItalyEURTrain ticket to Zurich55904,950
14 MarSwitzerlandCHFLunch281002,800
14 MarSwitzerlandCHFTram pass161001,600

How do I get a subtotal for each country?

Put the country names in J2 and J3, then use SUMIFS, which takes the range to add first and then each range and condition pair:

  • =SUMIFS($G$2:$G$500,$B$2:$B$500,J2) gives ₹27,630 for Italy (3,780 + 2,700 + 16,200 + 4,950) and ₹4,400 for Switzerland (2,800 + 1,600).
  • =SUM($G$2:$G$500) gives the trip total of ₹32,030.
  • =SUMIFS($E$2:$E$500,$C$2:$C$500,"EUR") adds up the euros themselves, which is handy for checking against the notes left in your wallet.

What do I do with the border day?

Pick one rule and keep to it. The simplest is that the country column follows where the money was spent. The train ticket above was bought at a station in Italy in euros, so it sits in Italy even though the journey ends in Switzerland. Lunch in Zurich in francs sits in Switzerland. If you would rather see transport between the countries on its own, add a third label such as Between and use it for any ticket that crosses the border. Either way, the currency column keeps the charged currency, so nothing gets muddled when you check against statements.

What about cash left over from the first country?

Leftover euros are not spent money. They are cash you already paid for, so handle them in steps:

  1. Count the notes and coins the night before you cross, and write the total in the sheet.
  2. Value them at the rate you paid for them. Say 40 euros bought at ₹90 each are worth ₹3,600.
  3. If you exchange them at a counter, record one row with both sides. Say you get CHF 35, valued at ₹100 each, which is ₹3,500. The ₹100 gap is the cost of the swap, so log it as a currency exchange cost in Switzerland.
  4. If you carry them home, leave them as cash on hand and log the spend only when you use them or swap them later.

When does a sheet stop being enough?

A sheet is fine for one trip. If you travel regularly and want foreign spends kept next to everything else, SpendixAI lets each expense be in its own currency and shows totals in a base currency using the rate saved with each transaction. Choose that base currency before you import any statements, because it cannot be changed once transactions exist.

SPENDIXAI JOURNAL

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

Explore your reports