Travel and vehicles

Forex card reload log: rate, date and amount per currency

A forex card reload log with date, currency, rupees debited, rate and amount loaded gives your weighted average rate across reloads. Template and example.

Editorial illustration for Forex card reload log: rate, date and amount per currency

A forex card reload log is a sheet where each top-up gets one row: the date, the currency, the rupees you paid, the rate applied and the foreign amount that landed on the card. Once you have a few rows, one formula gives you the weighted average rate you really paid across all reloads, which is more honest than averaging the rates. Here is the layout and a worked example, all with invented numbers.

The columns to set up

  1. A: Load date.
  2. B: Currency, such as USD or EUR.
  3. C: Rupees debited, from your bank or card statement.
  4. D: Rate applied, as shown on the transaction or receipt.
  5. E: Amount loaded, in the foreign currency.
  6. F: Spent, in the foreign currency, added as you use the card.
  7. G: Balance, a formula.

Use a separate sheet or a filtered block for each currency, because their rates and balances must not be mixed. The rate you pay may include a margin or fee, so use the figure from your own receipt or statement and do not assume it equals the rate on any website.

A worked example

Say you reload a card with US dollars three times before a trip. The rates are invented.

DateCurrencyRupees debitedRate appliedAmount loaded
5 NovUSD42,00084.00500
20 NovUSD21,30085.20250
1 DecUSD17,16085.80200
TotalUSD80,460weighted rate below950

Each row checks out: 500 times 84 is 42,000; 250 times 85.20 is 21,300; 200 times 85.80 is 17,160. The rupees add up to 80,460 and the dollars to 950.

How do I calculate the weighted average rate?

Divide the total rupees by the total foreign amount: =SUM(C2:C4)/SUM(E2:E4). Here, 80,460 divided by 950 is about 84.69. A simple average of the three rates would be (84.00 + 85.20 + 85.80) divided by 3, which is 85.00. The two differ because you bought more dollars at the lower rate. The weighted figure is the one that matches the rupees you actually spent.

If the rows are in different ranges, SUMPRODUCT is another route, but the division above is simpler and gives the same answer here.

How do I track the balance?

In G2, enter =SUM($E$2:E2)-SUM($F$2:F2) and copy down. Each row shows the total loaded so far minus the total spent so far. If you spend 120 dollars at a hotel and 85 at a restaurant, put 205 in F for the dates, and the balance falls to 950 minus 205, which is 745.

What should I do when I spend abroad?

The statement for the card usually shows each spend in the foreign currency. Add it to column F as you go, or in a batch after the trip, copying from the statement. If you convert a spend to rupees for your records, use your weighted average rate, and say that you did.

Where does SpendixAI come in?

SpendixAI lets each expense be in its own currency, with totals in a base currency using the rate saved with each transaction, and has a foreign-currency spend report. Choose your base currency before importing, because it cannot be changed once transactions exist. The reload log is still your record of how the card balance and the average rate were built.

Is the reload rate the same as the spend rate?

Not necessarily. The rate you paid to load the card is one thing, and a charge that falls outside the card currency can be converted again by the card network or the issuer, with its own margin. If a statement line shows a different currency from the card balance, note it and check the issuer's terms. Keep the reload log for what you paid in, and use the statement for what was converted on the way out.

A quick pre-reload check

  • Note the rate and any fee on the screen before you confirm.
  • Save the receipt after the reload.
  • Match the rupee debit to your bank statement.
  • Update the balance after every spend.
SPENDIXAI JOURNAL

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

Explore your reports