A couple of points were clarified by our accountant.

RE over/under payments.

If there is an underpayment then it should be treated as a bad debt and accounted for as such.  It is actually tax deductable.  Of course, you can pay the outstanding amount with petty cash if you want to tidy up the accounts and clear out the aged debtors etc.

Overpayments are not allowed due to money laundering rules.  They need to be kept on the accounts and carried over from year to year.  Of course, if you do any further work for that client you can deduct the overpayment from the amount they owe.

RE Foreign currency transactions.

Overpayments or under payments need to be handled as foreign exchange gains or losses.  Therefore, we are going to get the rate right.

Say we're using GBP - here's are procedure for adding an invoice and payment for an invoice we have received in a foreign currency (EUR) and where the payment was made after the invoice.

Look up on the bank system or statement to see the actual amount in GBP which was used to pay the invoice.

Divide the amount paid by the amount in EUR on the invoice.  This gives us the exact rate which was used by the bank when making the payment.  Write this amount down.

Look up online the exchange rate for the date of the invoice.  Add this into the exchange rate table under 'Banking and General ledger'.

Enter the invoice.

(At this point we're all fine - invoice has been added and the system has worked out the equivalent in GBP which has been added to expenses).

Enter a payment - and overwrite the exchange rate with the calculated exchange rate.

Then allocate the payment - the amounts should match.

NB - it might be an idea to switch rates to six decimal places to get a better response.

If the calculated amount is wrong then void the payment and add a new one with a slightly tweaked rate.  Then use the new payment to pay the invoice.

The voided payment can be cleaned out from reconciliation by ticking the (voided) transaction when reconciling the next statement.

You are using the 'Bank General Ledger/Payment' page. I'm speaking of the 'Supplier/Payment' page. The difference is, in this page you have to  setup an exchange rate (and allocate the payment at the same time).

You either use 0.805
or .80 and set bank charge to 1

Don't need to do that

Don't need to do that, as the payment as already been allocated.

No, FA use the rate you entered when you entered the payment. ie .805 and that leave nothing unallocated.

Then FA will compute the P&L when reevaluating your stock. If this day the exchange rate was 0.75
you got 200 EUR of stock (for 161 GBP), but the value of this stock is 150 GBP (for that day). Therefore you lost £11 on Foreign Exchange P/L. That's done (pretty much) automatically by FA.

/Elax

I've just spoken to our accountant - and your patch seems to be closer to what should happen.  If there is an overpayment or an underpayment when paying a foreign exchange invoice then this should go into the foreign exchange gain/loss account.

I'm just looking into it further now.

Hmmmm....

Just checking here.

I thought the foreign exchange gain/loss occured when the exchange rate on the invoice date is different than the exchange rate on the payment date.

Yes and it's done automatically by FA as long as you enter the exchange rate on the invoice.

Not at all. My patch only propose a different way to enter the inputs (amount/amount instead of amount/exchange rate) which solve an exchange rate truncation problem. When there is no truncation problem (99.9 % of the time)  the GL entries for FA or my patch are strictly equivalent.

Once again, my patch is not about difference between real exchange rate and daily exchange but to solve exchange rate truncation (and also avoid the need of calculating the exchange rate).
What you propose is unnecessary, as you shouldn't care (that much) of the daily exchange rate and  FA  does it already when  you reevaluate your stock. (There is no need to mess around payment for that. It's a stock valuation issue not a payment issue).

/Elax

Just checking here.

I thought the foreign exchange gain/loss occured when the exchange rate on the invoice date is different than the exchange rate on the payment date.

http://www.allbusiness.com/glossaries/foreign-currency-transaction-gain-or-loss/4951388-1.html

Therefore - having only one exchange rate per day keeps things clean.

What your patch addresses is the fact that the actual rate used by the bank when making the payment is hardly ever an exact match to the rate for that day.

What I'm proposing is that the difference between the amount being allocated (worked out by the daily rate and the actual amount paid in the local currency) and the amount on the invoice should be treated as an over or under payment.

Thanks for your feedback - it's great to get the detail of this sorted out.

The thing is at that point, you don't care of the rate of the day. What you care is how much would be displayed on your bank statement (that is the amount in GPB : in your case £161) and how much will appears on the supplier statement 200 EUR.

That's it. If you do that everything is fine, you see 161 and your bank statement and when you reconciliate your supplier payment you have an invoice of 200 EUR and a payment of 200.

The fact that the rates can changes the days is at that point irrelevant. When you enter a payment in FA , you enter it with the rate you effectively got for the payment, not an hypothetic rate of the day, that you will never have exactly.

Having said that, we you do a currency reevaluation, FA will compare your rate with the "official" one and put the difference in the Foreign exchange P&L. (and this is the same with my patch or not).

The difference  is with my patch, you enter 161 GBP and 200 EUR and everything work perfectly (you can reconciliate your bank account EXACTLY and allocate you supplier transactions EXACTLY) or you use FA.
For this you need to enter 161 GBP and a rate  which will be 161/200 = 0.805 or the opposite 200/161 = 1.24223602484472
I never know which way it is, and I always get it wrong.
At that point you can get a rounding error and not get exactly 200 EUR.

With my patch you can get rounding error with the exchange rate, luckily FA fix it by balancing the transaction.
Let's say my payments are 10 X and 3 Y. The exchange rate will be 3.3333333333. However, the exchange rate stored in FA will be 3.33333 or something so when converting back the amount in company currency I can have 9.99 instead of 10. FA will work anyway, it's create a supplier payment of 10 (which is what I want) and add a 0.01 of Foreign exchange P&L to balance gl trans of 9.99 (or something like that).

/Elax

We've been thinking about this whole issue - here's where we've gotten to.

There may be an issue with your patch approach.  That is that FA holds a single rate for each day/date - what happens if there is more than one foreign exchange transaction on that date.  I know it works out the amount on the fly for each transaction but the amount may not match the rate stored for that day.  No biggie of a problem - but it may confuse the story of what's happening.  Also, it may make the Foreign Exchange gain/loss amounts incorrect.

So - this is our current plan.

We run our accounts in GBP.  We get an invoice in a foreign currency amount (EUR 200.00) - we add an exchange rate (from exchange rate historical records online) for that invoice date (say 0.75) - enter the invoice - expenses get increased by the equivalent amount in our local currency (GPB 150.00).

So far so good.

Several days later we then pay the invoice using online payment.

Now - we look at the bank statement/online banking and see the exact amount deducted in our local currency - say it comes out as a payment of GBP 161.00.

So, this is how we enter it on FA.

We look up the exchange rate for the payment date - say 0.8.
We enter that rate in the exchange rate table for the payment date.
We create a payment to that supplier for GBP 161.00.

OK so far - the payment of GBP 161.00 will match perfectly on the reconciliation screen.

Now we go to allocate the payment to the original EUR 200.00 invoice.

FA uses the payment date rate of 0.8 to figure out that is needs GBP 160.00 to fully pay the invoice.  This leaves GBP 1.00 of the payment as unallocated.

NB - it could be the other way depending on the rates - i.e. it may result in an invoice which is not fully paid.

(It's important to note that the exchange rate we enter for the payment date will hardly ever match the exact rate which was used when the bank made the payment.  Rates fluctuate during the day so an exact match is highly unlikely).

The important point to note is that at this point we have an overpayment or an underpayment.

We are going to treat this like any other overpayment or underpayment.  We may have a customer who pays us slightly too much - or one who pays slightly too little.  If they are a private customer they may not be interested in correcting the payment - and it's not worth our time to chase a small underpayment.  I've put up another post at https://frontaccounting.com/punbb/viewtopic.php?id=3982 to work out how to deal with over/under payments.

Please note - if the invoice date and the payment date are the same day then there is no foreign exchange loss or gain as the exchange rate is the same.  Any difference between the amount FA calculates for the invoice/payment and the actual payment made will be treated as an over/under payment.

The main point is this.  Foreign exchange losses/gains occur due to exchange rate differences between the invoice date and the payment date.  Differences in the actual amounts on the allocation screen should be treated as under/over payments.

The payment allocation will not match exactly because because the exchange rate on the payment date will hardly ever be an exact match for the rate the bank used when making the payment (rates fluctuate during the day).  I suggest that this difference should probably be handled as an overpayment or an underpayment.

Cheers,

Kevin B

The problem is there is no 'difference' becauce FA only allow you to enter one amount (and not the real one). Moreover the exchange rate P&L is calculating at currenty reevaluation and it's not here to correct rounding error which are there only because FA doesn't allow you to enter the real amounts.

/Elax

Thanks for the fast reply.

I would appreciate the patch - cos it works out from the amounts paid the actual rate which applied when the payment was made.

Thanks again.

Cheers,

Kevin B

Did anyone get to resolve this?

I have, in my patched version. Basically I modified FA so the supplier amount is entered in the supplier currency (and the payment in my currency).I don't need to care about exchange rate, just put the two amounts and it works .

I can send you the patch, alternatively, you can try to convince Joe, that my way is better and include my patch in FA ;-)

/Elax