Tag Archives: multicurrency

Reverse backdated exchange rate change

Software: MoneyWorks #accounting software

You can have foreign currency set in accounts such as the bank, accounts receivable and accounts payable. When there is a rate change, MoneyWorks automatically creates two exchange rate journals. One is to revalue the foreign currency balances due to a change in the exchange rate; another is a reverse of the revaluation journal on the following open period.

Below is an example illustrate how does a change in exchange rate affects the account.

Assuming you start tracking the multiple currencies from January 2016. You added the currency with an exchange of 1SGD:0.7432USD. All the open financial period, let’s say you have already opened from January to March 2016, will have the exchange rate of 1SGD:0.7432USD set. Any transactions in US Dollar added within these three periods will be using this exchange rate.

On 8th January, you record a Sales Invoice of US$10,000 at an exchange rate of 1SGD:0.7432USD. The Singapore Dollars equivalent amount of this US Dollars invoice is S$13,455.33. The double entry behind this Sales Invoice:

Debit USD Accounts Receivable: 10,000
Debit USD Accounts Receivable-~~Delta: 3,455.33

Credit Sales: 10,000
Credit Sales: 3,455.33

Note: Delta account is an account use to capture the exchange different between the base currency and the foreign currency amount.

As at 31 January, you decided to set the month-end exchange rate to 1SGD:0.7400USD. MoneyWorks creates two General Journals for the change of exchange rate automatically when the new exchange rate has entered.

First General Journal dated on 31 January for the change of exchange rate from 1SGD:0.7432USD to 1SGD:0.7400USD. The journal debits S$58.18 to the USD Accounts Receivable-~~Delta account and credit S$58.18 from the Exchange Gain/Loss Unrealised account.

Another General Journal dated 1 February to reverse the backdated rate change of 1SGD:0.7432USD to 1SGD:0.7400USD. So when payment received, the different in an exchange between the invoice and the receipt will post to the exchange gain/loss account. The journal debits S$58.18 to the Exchange Gain/Loss Unrealised account and credit S$58.18 from the USD Accounts Receivable-~~Delta account.

Since you are going to use the new exchange rate of 1SGD:0.7400USD from 1 February (that is, follow the previous month-end exchange rate), you update the exchange rate of 1SGD:0.7400USD as at 1 February. Again, MoneyWorks added two General Journals for the change of rate. One dated 1 February and another dated 1 March to reverse the earlier journal.

The first journal dated on 1 February for the rate change from 1SGD:07432USD to 1SGD:0.7400USD. It debits the USD Accounts Receivable-~~Delta account of S$58.18 and credit S$58.18 from the Exchange Gain/Loss Unrealised account. Since period March has already opened, a reverse journal is automatically added on 1 March to reverse the backdated rate change from 0.7432 to 0.7400. It debits the Exchange Gain/Loss Unrealised account S$58.18 and credit S$58.18 from the USD Accounts Receivable-~~Delta account.

On 20th February, you received payment from the customer for the full settlement of the amount US$10,000. You recorded into the USD Bank at an exchange of 1SGD:0.7400USD and the Singapore Dollars equivalent is S$13,513.31. The receipt:

Debit USD Bank: 10,000
Debit USD Bank-~~Delta: 3,513.51
Debit the Exchange Gain/Loss Unrealised: 58.19

Credit USD Accounts Receivable: 10,000
Credit USD Accounts Receivable-~~Delta: 3,513.51
Credit Exchange Gain/Loss: 58.19

Before the payment received, MoneyWorks has created a reverse journal automatically in the period March due to a change in exchange rate on 1 February. When payment received on 20 February, MoneyWorks automatically add an exchange journal on 20 February (Same date as the payment received) but in the period March to correct the “reverse journal” which posted earlier. The journal:

Debit USD Accounts Receivable-~~Delta: 58.18
(for the unrealised gain of USD58.18)
Debit the Exchange Gain/Loss: 58.18

Credit USD Bank-~~Delta: 58.18
(for the realised loss of USD58.18)
Credit Exchange Gain/Loss Unrealised: 58.18


QuickBooks – Offset overpayment with an invoice in a different exchange rate

Software: Intuit #QuickBooks #accounting software

Overpayment — The amount paid is greater than the amount due.

An accounting software helps the business owner track the receivable and payable efficiently and minimises accounting error such as overpayment.

When an overpayment occurred, you can either give a refund or keep it as a negative receivable and offset it with a future invoice. However, it gets complicated when transactions are in foreign currencies.

Assuming your base currency is Singapore Dollars. In the month of January, you issue an invoice of US$10,000 at an exchange rate of 1USD:1.4SGD to a customer. The invoice debits the accounts receivable S$14,000, Singapore Dollars equivalent, and credit S$14,000 to the Sales.

In February, the customer sends you a payment of US$14,000, which is US$4,000 overpaid, to settle the outstanding. Based on your February exchange rate of 1USD:1.38SGD, QuickBooks recorded S$19,320 into the bank account. A credit balance of S$5,520, which is US$4,000 multiplied by the exchange rate of 1.38, shows in the Accounts Receivable Ageing report and an exchange loss of S$200 shows in the February’s Profit and Loss report.

Exchange Loss as at February:
Invoice total: US$10,000 * 1.4 = S$14,000
Paymentl: US$10,000 * 1.38 = S$13,800
Net exchange loss: S$(14,000 – 13,800) = S$200

Assuming you and the customer has come to an agreement of offsetting the overpayment with the future invoices instead of giving a refund.

In March, the customer purchased US$20,000 worth of goods from you at an exchange of 1USD:1.36SGD. In the receivable report, it shows the Singapore Dollar equivalent of S$27,200. Together with the previous overpayment of S$5,520, the net receivable is S$21,680 (S$27,200 – S$5,520).

In Apri, the customer paid US$16,000 (US$20,000 – US$4,000) at an exchange rate of 1USD:1.37SGD to settle the outstanding.

Due to multiple exchange rates involved, you should not offset the invoice of US$20,000 (Exchange rate 1USD:1.36SGD) with the payment of US$16,000 (Exchange rate 1USD:1.37SGD) and the credit (overpayment) of US$4,000 (Exchange rate 1USD:1.38SGD) in one transaction. You should consider breaking up the payment into two transactions instead.

First, you have to offset the February’s overpayment with the invoice, which you have issued in March. Set the “Amount Pay” of the Receive Payment as 0.00, highlight the Sales Invoice and click the “Discounts and Credits” button (on top of the Receive Payment transaction) and select the overpayment transaction of US$4,000 from the Discounts and Credits window.

Discounts and Credits

The offsetting of the Overpayment of US$4,000 (Exchange rate 1USD:1.38) against the March’s outstanding amount of US$4,000 (Exchange rate 1USD:1.36) gives an exchange gain of $80. Since the overpayment is in February, the exchange gain will fall in February’s Profit & Loss report. Together with the previous exchange loss of S$200, the net exchange loss is S$120.

Updated Exchange Loss as at February:
January Invoice: US$10,000 * 1.4 = S$14,000
March Invoice: US$4,000 (partial) * 1.36 = S$5,440
Total Invoice in SGD: S$19,440

Less Payment: US$14,000 * 1.38 = S$19,320
Net exchange loss: S$(19,440 – 19320) = S$120

Finally, receive the payment of US$16,000 at an exchange of 1USD:1.37SGD and date it as April. The exchange gain for April will be S$160.

Exchange Gain as at April:
March Invoice: US$16,000 (balances) * 1.36 = S$21,760
April payment: US$16,000 (payment received in April) * 1.37 = S$21,920
Net Exchange Gain: S$(21,920 – 21,760) = S$160

If you prefer the exchange gain of $80 (from offsetting of March invoice with the February overpayment) to fall in the month of April instead of February, then you may consider to record it via general journal manually instead of the Receive Payment method which we have illustrated above.

Stays with us. We shall discuss how to offset the overpayment manually in our later post.

Advance payment to a supplier in foreign currency

Software: MoneyWorks #accounting software

A supplier may request a deposit or an advance payment when you place an order if you do not have a credit payment term arranged. The accounting entries get complicated when the transaction is in a foreign currency. The accounts such as the Bank, Accounts Receivable and Accounts Payable can be in a foreign currency, but the other account type such as Current Liabilities, Current Assets, etc. will only be in a base currency.

For example, assuming your base currency is in a Singapore Dollars (SGD). You place an order of USD22,500.00 at an exchange of 1SGD:0.75USD and an advance payment of USD10,000 have wired from the USD Bank account. A payment transaction will automatically record in MoneyWorks when you process the Purchase Order with “Pay Deposit for Order” process. This payment:

Debit 170.200 Advance to a supplier account (a Current Assets account): 13,333.33

Credit 130.300 USD Bank account: 10,000
Credit 130.000-~~DEL USD Bank account: 3,333.33

(The advance payment to the supplier in Singapore Dollars: 10,000/0.75 = 13,333.00)

On the following month, the exchange rate has changed to 1SGD:0.74USD and you process the Purchase Order with “Receiving Goods with Invoice” process, a Purchase Invoice with the new exchange rate will record in MoneyWorks. The Purchase Invoice:

Debit 160.100 Inventory Asset account: 30,405.41

Credit 170.200 Advance to a supplier account: 13,513.51
Credit 230.200 Accounts Payable (USD) account: 12,500.00
Credit 230.200-~~DEL Accounts Payable (USD) account: 4,391.90

(The advance payment to a supplier in Singapore Dollars which has reversed: 10,000/0.74 = 13,513.51)

The deposited amount in US Dollars has reversed out from the Advance to a Supplier account when the Purchase Invoice has recorded. Due to a different in the exchange rate between the two transactions, there is a Singapore Dollars value left in the account. Working:

  • The advance payment in Singapore Dollars: 10,000/0.75 = 13,333.33
  • The advance payment in Singapore Dollars which has reversed: 10,000/0.74 = 13,513.51
  • There is an exchange gain of 180.18 ( 13,513.51 – 13,333.33) between the two transactions.

You have to pass a journal (or a receipt to simulate a journal) to “remove” the Singapore Dollars balance in the Advance to Supplier account. The journal debits 180.18 to the Advance to a Supplier account and credits 180.18 from the Exchange Gain/Loss account.

Managing foreign currency transactions can be complex, the accountant has to ensure each account has recorded correctly after the transaction has posted.

Advance payment to a supplier

Note: The account code and account name used in this post are for illustration, your actual account code and name used may be different.