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.