Spreadsheets

Convert foreign trip expenses to rupees in Google Sheets

Convert foreign trip expenses to rupees in Google Sheets with GOOGLEFINANCE, a manual card-rate override and a way to log the markup on your statement.

Editorial illustration for Convert foreign trip expenses to rupees in Google Sheets

In Google Sheets, =GOOGLEFINANCE("CURRENCY:USDINR") returns the current dollar rate in rupees, and multiplying an amount by it gives a rupee figure. For a trip log you also want a column for the rate your card actually used, because that is what you pay. The sheet below uses the typed rate when it exists and falls back to the market rate when it does not.

How do you set up the trip sheet?

ColumnHeadingWhat goes in it
ADateDate of the spend
BWhatHotel, taxi, meal
CCurrencyA three-letter code such as USD, EUR or AED
DAmountIn that currency
EMarket rate=GOOGLEFINANCE("CURRENCY:"&C2&"INR")
FCard rateTyped from the statement, left blank until then
GRate used=IF(F2<>"",F2,E2)
HRupees=D2*G2

Copy row 2 down. A total in rupees is =SUM(H2:H200). To see where the money went, add a category column and use SUMIFS, the same way you would in any expense sheet.

Why does the live rate keep changing old rows?

GOOGLEFINANCE is live, so every time the sheet recalculates, column E shows today's rate, and a taxi from last week silently changes value. Freeze the number for any row you have finished. Select the cells in E, copy, and use Paste special, Values only. Or ask for the rate on the day of spend with =INDEX(GOOGLEFINANCE("CURRENCY:USDINR","price",A2),2,2), which returns the rate for that date. Rates can be delayed or unavailable, so treat the result as an estimate and check it against your statement.

Why is the amount on the card statement different?

The number on your statement is rarely the same as what you computed from a market rate. Common reasons, all of which you should confirm against your own card terms:

  • The card network and your bank convert on their own timing, which may be a day or two after the purchase.
  • The bank may add a markup on top of the converted amount.
  • A foreign-transaction fee or taxes may appear as a separate line.
  • If a shop offers to charge you in rupees at the counter, that shop's rate is used instead.

How do you log the markup?

When the statement arrives, enter the rupee amount charged in a new column and work backwards. Say you spent 100 units of a foreign currency, the market rate was ₹90.00, and the statement shows ₹9,270. Then:

  1. Card rate, typed in F: =9270/100, which is ₹92.70.
  2. Markup as a share: =F2/E2-1, which is 92.70 divided by 90.00 minus 1, exactly 3%.
  3. Markup in rupees: =H2-D2*E2, which is 9,270 minus 9,000, or ₹270.

These figures are invented. Yours will differ, and a fee line, if any, should go in as its own row so the rupee total matches the statement.

What else belongs in a trip total?

The rows you type in are only part of the cost. Add separate lines for forex purchased before you travel, cash withdrawn at an ATM abroad, any fee the ATM or your bank charged, and tickets or hotels you paid in rupees. Keep each as its own row, with currency INR where it was already in rupees, so column H reads right for all of them. For a daily average across the trip, use =SUM(H2:H200)/(MAX(A2:A200)-MIN(A2:A200)+1). The +1 counts both the first and last day. Compare that with the budget you set, and you will know whether the last week needs trimming.

When you want this done for you

Once a trip is over, the sheet is mostly a record of rates. SpendixAI handles each expense in its own currency, totals in a base currency using the rate saved with each transaction, and has a Foreign-currency spend report. The base currency must be chosen before you import, and it cannot be changed once transactions exist.

SPENDIXAI JOURNAL

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

Explore your reports