metricvalley Spreadsheet tools for small agencies

Guides

Recording exchange rates monthly for agency reporting in mixed currencies

Updated · 6-minute read

If you bill one client in pounds, pay a freelancer in euros and report in US dollars, you need exchange rates to put everything on one scale. You don't need a rate for every invoice date. For management reporting, one rate per currency per month is enough.

This guide explains why, where to find reliable rates, how to record the source, and how to convert published rates into the "AUD per 1 unit" format the Client Profitability Tracker uses.

Management reporting is not accounting

Your accountant and your monthly management report answer different questions.

Your accountant records what actually happened. A £4,000 invoice paid three weeks later arrives in your bank as a specific number of dollars, after the bank's spread and fees. The difference between the invoice amount and what landed is a realized exchange gain or loss. It belongs in your books and, where relevant, your tax return.

For US tax, the IRS says that in general you should use the exchange rate prevailing when you receive, pay or accrue the item. It also says it has no official exchange rate and generally accepts any posted rate that is used consistently.

Your management report asks something simpler: which clients and months did well, compared with each other? For that, what matters is consistency. If every pound amount in August is converted at the same rate, the clients are compared fairly. A small difference from the rate on the payment date doesn't change which client is strong and which is weak.

So keep the two separate. Use monthly rates for decisions. Leave realized exchange differences to your accountant.

Why one rate per currency per month

A monthly rate matches how most agencies already work. Retainers renew monthly, invoices are grouped by month, and you review results once the month closes.

It also keeps the workload small. With four currencies, you record four numbers a month, not one per transaction.

Decide one of two conventions and keep it:

  • Monthly average. Suits fees and costs spread through the month. The Federal Reserve's G.5 release publishes monthly averages based on daily noon buying rates.
  • Month-end rate. One number taken on the last business day. Easier to find and to check later.

Either is fine. Switching between them from month to month is not.

Where to source rates

Central banks publish free reference rates. These are the main ones:

These rates fit management reporting well because they are public, dated and can be checked later. They are not the rate your bank gave you, and they are not meant to be.

Where you can, take every currency for a month from the same source and the same date or period. Rates mixed from different sources and times won't line up exactly with each other.

How to note the source

Write down where each rate came from, next to the rate. Six months later, you or your accountant will want to know.

A good note has four parts: the source, the series or release, the date or period, and anything you did to the number. For example:

  • RBA monthly, Aug 2026, 0.7000 USD per AUD, inverted
  • ECB reference, Aug 29 2026, AUD per EUR, used as published
  • Fed G.5, Aug 2026 average, JPY per USD, crossed via USD

The "AUD per 1 unit" convention

The tracker stores every rate the same way: how many Australian dollars one unit of the currency buys. AUD itself is always 1.0. This holds even when you report in US dollars.

One common base means any pair of currencies converts using two numbers. To convert from a source currency into your report currency, the workbook divides:

source currency's AUD quote ÷ report currency's AUD quote

Published rates come in different directions, so you may need to convert them first. Here is how, using illustrative rates, not quotes for any date:

Currency Published rate (illustrative) Source style AUD per 1 unit How
USD 0.7000 USD per AUD RBA, or Fed (USD per AUD) 1.4286 1 ÷ 0.7000
GBP 0.5300 GBP per AUD RBA 1.8868 1 ÷ 0.5300
EUR 1.6260 AUD per EUR ECB 1.6260 Use as published
JPY 147.00 JPY per USD Fed 0.009718 1.4286 ÷ 147.00

The rule of thumb: if the published rate is "foreign currency per AUD", divide 1 by it. If it is already "AUD per foreign unit", use it as it is. If it is quoted against a third currency, cross through that currency.

A worked example (fictional)

Fictional example. Linden Row Agency is not a real business. The figures are illustrative.

Linden Row reports in US dollars. In August it billed a London client £4,000 and paid a Berlin freelancer €1,200. Using the rates in the table above:

  • Fee: £4,000 × 1.8868 ÷ 1.4286 = US$5,282.93
  • Cost: €1,200 × 1.6260 ÷ 1.4286 = US$1,365.81

If it switched the report currency to euros, the same rates would convert the pound fee into euros: £4,000 × 1.8868 ÷ 1.6260 = €4,641.57. No new rates are needed.

Common mistakes

  • Entering the rate upside down. 0.7000 and 1.4286 are the same rate in opposite directions. A USD figure that looks about half or double what you expect is the usual sign.
  • Using the wrong month. A September rate on August rows quietly shifts every figure.
  • Mixing sources within a month. Pick one source per month where possible.
  • Updating past months. Once a month is reviewed, leave its rates alone so reports stay reproducible.
  • Treating the result as your bank balance. Converted fees are a management view, not the cash you received.

How the tracker handles rates

The Client Profitability Tracker has an FX rates sheet where you enter one AUD per 1 unit rate per currency per month, with a short note of where each rate came from. You choose the report currency from AUD, USD, EUR, GBP, CAD, NZD, JPY, SGD, HKD and CHF.

A rate is only needed when an amount is converted into a different currency. Same-currency amounts and AUD convert at 1.0. Rates are entered by hand; there is no automatic feed. If a needed rate is missing, duplicated, not positive or entered for the wrong month, the affected row is marked Review and left out of the totals until you fix it.

If you only work in one currency, a free lite edition with the same contribution calculations and no FX rates sheet is planned, with no release date yet. For how the converted figures feed into client results, see contribution before overhead. Setup steps, including entering rates, are in the tracker's setup guide.

Sources

Common questions

Is one exchange rate per month accurate enough for agency reporting?

For management reporting, yes. You are comparing clients and months, not settling invoices. One rate per currency per month, from a named source and used consistently, keeps the comparison fair. Your accountant still records the actual amounts received and paid.

Where can I find free, reliable exchange rates for monthly reporting?

Central banks publish reference rates for free. The Reserve Bank of Australia, the European Central Bank, the Federal Reserve (H.10 and G.5 releases) and the Bank of England all publish them. Pick one source and note it next to each rate.

How do I convert a rate quoted as US dollars per Australian dollar into AUD per 1 USD?

Divide 1 by the published rate. If the rate is 0.7000 US dollars per Australian dollar, then 1 US dollar is 1 ÷ 0.7000 = 1.4286 Australian dollars.

All guides