Skip to content
KenyaTax

Accounting

Foreign currency sales and exchange difference tracker

Record sales in dollars, euros or pounds, convert them to shillings, and see the exchange gain or loss when the customer pays.

Foreign currency sales and exchange difference tracker template

When you invoice in a foreign currency, the shillings you receive depend on the rate on the day you are paid. This tracker records each invoice at the rate on the invoice date and the rate on the payment date, and shows the exchange gain or loss, the unpaid balance and the total received.

Exporters, freelancers and service businesses with foreign customers, and accountants who must record exchange differences in the accounts.

What is included

  • Shilling value at invoice date and at payment date
  • Exchange gain or loss for each invoice
  • Settled or unpaid status
  • Totals invoiced, received, net gain or loss and unpaid
  • Summary by currency with a chart

Screenshots

  • Foreign currency invoices of Foreign currency sales and exchange difference tracker
  • Invoiced by currency of Foreign currency sales and exchange difference tracker

How to use it

  1. Record each invoice with the customer, the currency and the amount in that currency.
  2. Type the exchange rate on the invoice date; the shilling amount is calculated.
  3. When the customer pays, enter the rate on that day.
  4. Read the exchange gain or loss and the unpaid invoices at the top.

Templates are tools to help with your own records. They are not tax or legal advice. Check the figures against official sources before filing or paying.

More in this category

What the foreign currency sales and exchange difference tracker does

When you invoice in a foreign currency, the shillings you receive depend on the rate on the day you are paid. This tracker records each invoice at the rate on the invoice date and the rate on the payment date, and shows the exchange gain or loss, the unpaid balance and the total received.

Who it is for

Exporters, freelancers and service businesses with foreign customers, and accountants who must record exchange differences in the accounts.

How to use the workbook

  1. Record each invoice with the customer, the currency and the amount in that currency.
  2. Type the exchange rate on the invoice date; the shilling amount is calculated.
  3. When the customer pays, enter the rate on that day.
  4. Read the exchange gain or loss and the unpaid invoices at the top.
  5. Save a copy under a new name for each month or year, so earlier records stay as they were.

What is inside

  • Shilling value at invoice date and at payment date
  • Exchange gain or loss for each invoice
  • Settled or unpaid status
  • Totals invoiced, received, net gain or loss and unpaid
  • Summary by currency with a chart

The workbook has these sheets: Instructions, Invoices, By currency. The main table has the columns Invoice no.; Customer; Currency; Amount in currency; Rate on invoice date; Amount in KES at invoice; Rate on payment date; Amount received in KES. Yellow cells are for you to fill in and grey cells calculate by themselves, so leave the grey cells alone. The example rows show how it works; type over them with your own entries.

Tips for getting it right

  • Use the Central Bank of Kenya rate or your bank's rate consistently.
  • Keep the bank advice showing the shillings you actually received.
  • A fall in the shilling's value creates a gain on dollar sales; a rise creates a loss.
  • Check how exchange differences are treated for tax before you file.

Why keep this record

Accounting records let you answer three questions at any time: what came in, what went out and who owes whom. Kept consistently they also make tax returns, loan applications and the sale of a business much easier, because the evidence is already organised. Entering transactions the day they happen is far cheaper than reconstructing them at year end.

Questions people ask

Do I need to know Excel formulas? No. Type into the yellow cells and the rest is worked out for you. The formulas are visible if you want to see how a figure is reached.

Can I add rows or change the layout? You can add your own columns to the right and your own notes. If you insert rows inside a table, copy the grey formula cells down so the new rows calculate too.

Will it work on my phone or in Google Sheets? Yes for viewing and typing, but dropdown lists and the charts look best in desktop Excel or LibreOffice.

Keeping it safe and up to date

Good accounting records are a legal duty as well as a management tool: keep the supporting receipts and invoices with the workbook, and keep each year's copy for at least five years. Work on a copy, never on your only file, and back it up.

Read how to keep business records for KRA for the wider picture. Free calculators that go with it: Vat calculator. See more accounting templates or ask us for a custom one.

The workbook works in Microsoft Excel, LibreOffice and Google Sheets. It is a tool to help you work out and record figures, not tax, legal or accounting advice, and it is not affiliated with the Kenya Revenue Authority. If something does not work as described, contact us and we will put it right.

Need something different? Request a custom template, browse all guides or see every Excel template.

Scroll inside the box to read more.