How to track expenses in two currencies

Step 1: pick one base currency

Your base currency is the one every total, chart and comparison is shown in. Pick the currency most of your spending happens in.

For most people that is also the currency they are paid in, and a foreign currency only turns up now and then. Someone paid in Sydney and living there picks the Australian dollar, and the occasional pound or US dollar gets translated.

The other case is earning in one currency and spending in another. Say you work for a US company, are paid in US dollars, and live in Australia. Pick the Australian dollar anyway, the one you spend in, and translate your income each time it lands. Spending is what you look at most often, so it is the part that should need no translating.

Numbers in different currencies cannot be added or compared directly. If your spending shows £5,000 and your savings show $1,000, those two figures tell you nothing about each other until both are in one currency.

Step 2: translate income on the day it lands

If your income arrives in another currency, record it in plain words: this much landed in that currency today, which is this much in my base currency. A freelance payment of US$2,000 that arrives on 6 October is recorded as US$2,000, and as its value in Australian dollars at that day's rate. Each row carries its own day's rate.

Step 3: translate spending at the rate on the day you paid

A London dinner on 14 October is converted at the rate for 14 October, not the rate on the day you tidy your records. Two things make the figure honest.

Keep the original amount on the row. This is the paper trail. Two months later you will look at a list of Australian-dollar figures and not remember that one of them was paid in pounds. With the original currency and amount kept beside the converted figure, you can check it against a receipt and correct it.

Use what your bank charged, where you know it. The rate you look up is the rate available that day. Your bank applies its own conversion rate and may add an international transaction fee, and both differ between banks and between cards at the same bank. When the statement shows the Australian-dollar amount that left your account, record that amount: it is what you actually paid.

A worked example

Base currency: Australian dollars. The rates below are illustrative, not real market rates, and the bank figures are made up to show the gap a fee creates.

Where the bank's figure exists, it wins over the looked-up rate, because it includes the bank's rate and any fee. Where there is none, as with cash, the rate on the day is a fair estimate.

An expense tracker that keeps the exchange rate from the day you paid

This is the property to look for, whatever you use: the rate is frozen on the row when it is recorded. If an app or a spreadsheet converts foreign rows at today's rate every time you open it, last month's London dinner quietly changes value each morning, and your past months stop adding up to what you actually spent. A frozen rate means a month you have closed stays closed.

Freezing also matters if you change base currency later, say after a move: past rows keep what they were recorded in, new rows take the new base, and totals across the change are shown per currency rather than added together.

Doing it in Google Sheets

If you track in a spreadsheet, Google Sheets can look up a past day's rate with the GOOGLEFINANCE function. With the date in A2, this returns the closing USD to AUD rate for that day:

=INDEX(GOOGLEFINANCE("CURRENCY:USDAUD","close",A2),2,2)

For a historical date, GOOGLEFINANCE returns a small table with a header row (date, then close), so INDEX picks the second row, second column. Swap the currency codes for other pairs, for example CURRENCY:GBPAUD.

Its limits, plainly:

• It gives a daily close from Google's data, not your bank's rate, and it does not include fees.
• A weekend or public holiday may have no row, so the formula can return an error; use the previous weekday's date.
• Once a rate is right, copy the cell and paste it as a value, so it stays frozen and does not recalculate later.

Whatever you track in, Google Sheets, Excel or an app, it should make this part easy: a place for the original currency and amount, the rate, and a converted figure that stays put.

When another tool fits

If you live in two currencies fully, with accounts, income and bills in both, keeping them as two separate budgets can be simpler than translating. That is what YNAB's own guide recommends: when using multiple currencies, "we recommend creating a separate plan for each", and "make sure all accounts in a plan use the same currency." You record what you send from one plan and what you receive in the other. It suits people whose second currency is a whole financial life rather than the occasional trip or subscription.

Questions