[GNC] rounding of Invoice vs Accounts Receivable
Hi, I created a Sales Tax Table entry of 20%. When I create an Invoice of EUR 109,95 with tax included and select my Tax table, GNUCash calculates EUR 18,33 tax and EUR 91,63 in net revenue. My Accounts Receivable however will not increase by EUR 109,95 but by 109,96. So 1 cent difference. (the precise amounts are: sales is EUR 91,625 and tax EUR 18,325 and thus both are rounded up) Is there a way to have the tax rounded down (by 0,5cent) so that the total stays EUR 109,95? I really prefer to have accounts being 0 at the end of a period, instead of all these loose cents adding up (makes reconciliation harder). Thanks, Etienne ___ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: https://lists.gnucash.org/mailman/listinfo/gnucash-user - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
Re: [GNC] rounding of Invoice vs Accounts Receivable
On 2022-10-17 08:03, gnuc...@boeziek.nl wrote: > I created a Sales Tax Table entry of 20%. > When I create an Invoice of EUR 109,95 with tax included and select my Tax > table, GNUCash calculates EUR 18,33 tax and EUR 91,63 in net revenue. > My Accounts Receivable however will not increase by EUR 109,95 but by 109,96. > So 1 cent difference. (the precise amounts are: sales is EUR 91,625 and tax > EUR 18,325 and thus both are rounded up) > > Is there a way to have the tax rounded down (by 0,5cent) so that the total > stays EUR 109,95? > I really prefer to have accounts being 0 at the end of a period, instead of > all these loose cents adding up (makes reconciliation harder). You may be able to force it; I can't speak to that because I've never used reconciliation _or_ tax tables in GC. But as a matter of mathematics, the process of starting from price including tax, then computing tax and net, will inevitably yield cases like this one where it is impossible to get a self-consistent result. Let me explain... Given: Gross amount 109,95 including 20% tax on the net. Tax = 109,95 * 0,20/1,20 = 18,325 --> 18,33 Net = 109,95 * 1,00/1,20 = 91,625 --> 91,63 or 109,95 - 18,325 --> 91,63 (I don't know which one GnuCash did, but it was almost certainly one of those two.) As you said, tax + net =/= gross: 18,33 + 91,63 = 109,96 =/= 109,95 The "obvious" solution is to subtract _after_ rounding: net = gross - tax = 109,95 - 18,33 = 91,62. However, the obvious solution is wrong (which is why I used sneer quotes). 20% of 91,62 is 18,324 --> 18,32 not 18,33, which means the total should be 109,94! And whether you allocate the odd cent to tax or to net, you still have an inconsistency. (If you make the net 91,63, then tax is 18,326 --> 18,33 and the gross sale amount is 109,96.) Is it necessary, as a mater of regulation or of business practice, to start with an amount including tax and work backward in this fashion? If you do, such anomalies are going to arise from time to time, depending on the numbers involved. This problem vanishes if you start with the net amount, compute the tax, and add the two to come up with the amount billed. That is the practice here in the US, but I don't have any idea of whether the EU requires the practice you described. If that is indeed required in the EU, there must be a standard way of handling these one-cent differences, because you are surely not the first vendor to run up against them. Hopefully one of our European colleagues on the list will shed some light. Stan Brown Tehachapi, CA, USA https://BrownMath.com ___ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: https://lists.gnucash.org/mailman/listinfo/gnucash-user - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
Re: [GNC] rounding of Invoice vs Accounts Receivable
Thank you Stan, Indeed inside EU it is compulsory to advertise pricing including VAT. So I have to work backwards. Inside the Invoice function in GNC you can’t manually edit the amounts after the Tax Table calculated them. It would be great if GNUCash will either (1) allow you to edit the tax or revenue amount in Invoice or (2) remove the odd-cent from the “largest” amount (percentage wise will be the smallest deviation), in this case the net revenue. > On 17 Oct 2022, at 18:23, Stan Brown wrote: > > On 2022-10-17 08:03, gnuc...@boeziek.nl wrote: >> I created a Sales Tax Table entry of 20%. >> When I create an Invoice of EUR 109,95 with tax included and select my Tax >> table, GNUCash calculates EUR 18,33 tax and EUR 91,63 in net revenue. >> My Accounts Receivable however will not increase by EUR 109,95 but by >> 109,96. So 1 cent difference. (the precise amounts are: sales is EUR 91,625 >> and tax EUR 18,325 and thus both are rounded up) >> >> Is there a way to have the tax rounded down (by 0,5cent) so that the total >> stays EUR 109,95? >> I really prefer to have accounts being 0 at the end of a period, instead of >> all these loose cents adding up (makes reconciliation harder). > > You may be able to force it; I can't speak to that because I've never > used reconciliation _or_ tax tables in GC. But as a matter of > mathematics, the process of starting from price including tax, then > computing tax and net, will inevitably yield cases like this one where > it is impossible to get a self-consistent result. Let me explain... > > Given: Gross amount 109,95 including 20% tax on the net. > Tax = 109,95 * 0,20/1,20 = 18,325 --> 18,33 > Net = 109,95 * 1,00/1,20 = 91,625 --> 91,63 or 109,95 - 18,325 --> 91,63 > (I don't know which one GnuCash did, but it was almost certainly one of > those two.) > As you said, tax + net =/= gross: 18,33 + 91,63 = 109,96 =/= 109,95 > > The "obvious" solution is to subtract _after_ rounding: > net = gross - tax = 109,95 - 18,33 = 91,62. However, the obvious > solution is wrong (which is why I used sneer quotes). 20% of 91,62 is > 18,324 --> 18,32 not 18,33, which means the total should be 109,94! > > And whether you allocate the odd cent to tax or to net, you still have > an inconsistency. (If you make the net 91,63, then tax is 18,326 --> > 18,33 and the gross sale amount is 109,96.) > > Is it necessary, as a mater of regulation or of business practice, to > start with an amount including tax and work backward in this fashion? If > you do, such anomalies are going to arise from time to time, depending > on the numbers involved. > > This problem vanishes if you start with the net amount, compute the tax, > and add the two to come up with the amount billed. That is the practice > here in the US, but I don't have any idea of whether the EU requires the > practice you described. If that is indeed required in the EU, there must > be a standard way of handling these one-cent differences, because you > are surely not the first vendor to run up against them. Hopefully one of > our European colleagues on the list will shed some light. > > Stan Brown > Tehachapi, CA, USA > https://BrownMath.com > ___ > gnucash-user mailing list > gnucash-user@gnucash.org > To update your subscription preferences or to unsubscribe: > https://lists.gnucash.org/mailman/listinfo/gnucash-user > - > Please remember to CC this list on all your replies. > You can do this by using Reply-To-List or Reply-All. ___ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: https://lists.gnucash.org/mailman/listinfo/gnucash-user - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
Re: [GNC] rounding of Invoice vs Accounts Receivable
On 2022-10-17 09:53, gnuc...@boeziek.nl wrote: > Thank you Stan, > > Indeed inside EU it is compulsory to advertise pricing including VAT. So I > have to work backwards. Ah -- I'm guessing that's because VAT is imposed at several stages between raw material and final sale? Here in the US, as you may know, we have sales tax (different in every state, and in subdivisions of many states), but it is paid only by the final consumer. > It would be great if GnuCash will either (1) allow you to edit the tax > or revenue amount in Invoice or (2) remove the odd-cent from the > “largest” amount (percentage wise will be the smallest deviation), in > this case the net revenue. That seems like a reasonable enhancement request, but hopefully someone will jump in to tell us how it can already be done in GC. I'll mention in passing that I _wish_ it were compulsory here to advertise the actual total price including sales tax and other taxes. That is usual for gasoline (petrol) here in the US, but as industry practice rather than by regulation. However, just about every other product is advertised with a base price excluding sales tax. To make it worse, many products -- air fares, hotel rates, and cable, Internet, and telephone, to name just a few -- advertise an attractive low price but then charge "fees" that add a substantial increment to the advertised price. Such deceptive practice is unfortunately quite legal here in California, and in every other state as far as I'm aware. Stan Brown Tehachapi, CA, USA https://BrownMath.com ___ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: https://lists.gnucash.org/mailman/listinfo/gnucash-user - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
Re: [GNC] rounding of Invoice vs Accounts Receivable
On 10/17/2022 11:03 AM, gnuc...@boeziek.nl wrote: Hi, I created a Sales Tax Table entry of 20%. When I create an Invoice of EUR 109,95 with tax included and select my Tax table, GNUCash calculates EUR 18,33 tax and EUR 91,63 in net revenue. My Accounts Receivable however will not increase by EUR 109,95 but by 109,96. So 1 cent difference. (the precise amounts are: sales is EUR 91,625 and tax EUR 18,325 and thus both are rounded up) Is there a way to have the tax rounded down (by 0,5cent) so that the total stays EUR 109,95? I really prefer to have accounts being 0 at the end of a period, instead of all these loose cents adding up (makes reconciliation harder). Thanks, Etienne This is NOT a rounded-up rounded-down issue. It is a when calculated issue if both a set of items and their sum. Rounded (A+B) does not (necessarily) equal (Rounded A) + (Rounded B) And how far the distance can become depends on the number of items involved. If there were a couple dozen items the range of "answers" could vary by several units. The calculation of "fuzz" is a fine art (when a computer program is checking for equality between amounts calculated in different ways the program decides they are equal if the difference between them is less than the "fuzz" -- and I have had to figure what the correct fuzz should be in my days in the cypher mines) SO --- what are the actual tax rules of the jurisdiction? If several items are bought at the same time is the correct VAT the sum of the VAT due for each item or is it the VAT due on the sum of the items? Michael D Novack ___ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: https://lists.gnucash.org/mailman/listinfo/gnucash-user - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
Re: [GNC] rounding of Invoice vs Accounts Receivable
We encounter the same problem in jurisdictions with tax computed after. Which is to say this problem is not unique to having tax-in pricing advertised. Where I see it is in bills where the GnuCash rounding differs from that used by the vendor. And it only happens when there are multiple line items in the bill. GnuCash appears to total tax the taxable items and then round and sum the tax, whereas most vendors hereabouts add the taxable items and then multiply by the tax rate and round. Because the “bill” I create within GnuCash is only for internal use, I add a pair of entries - one taxable and one non-taxable, that balance out the amount in Accounts Payable, while adjusting the amount of VAT paid to match that on the bill from the vendor. In your case, is it making the invoice have the wrong total, or just your internal Accounts Receivable and/or VAT payable? In the latter case you can make an adjusting transaction to fix it. Ugly, and error prone, but possible. At least it only happens of the time. > On Oct 17, 2022, at 3:50 PM, Michael or Penny Novack > wrote: > > On 10/17/2022 11:03 AM, gnuc...@boeziek.nl wrote: >> Hi, >> I created a Sales Tax Table entry of 20%. >> When I create an Invoice of EUR 109,95 with tax included and select my Tax >> table, GNUCash calculates EUR 18,33 tax and EUR 91,63 in net revenue. >> My Accounts Receivable however will not increase by EUR 109,95 but by >> 109,96. So 1 cent difference. (the precise amounts are: sales is EUR 91,625 >> and tax EUR 18,325 and thus both are rounded up) >> >> Is there a way to have the tax rounded down (by 0,5cent) so that the total >> stays EUR 109,95? >> I really prefer to have accounts being 0 at the end of a period, instead of >> all these loose cents adding up (makes reconciliation harder). >> >> Thanks, >> Etienne > > This is NOT a rounded-up rounded-down issue. It is a when calculated issue if > both a set of items and their sum. > > Rounded (A+B) does not (necessarily) equal (Rounded A) + (Rounded B) And > how far the distance can become depends on the number of items involved. If > there were a couple dozen items the range of "answers" could vary by several > units. The calculation of "fuzz" is a fine art (when a computer program is > checking for equality between amounts calculated in different ways the > program decides they are equal if the difference between them is less than > the "fuzz" -- and I have had to figure what the correct fuzz should be in my > days in the cypher mines) > > SO --- what are the actual tax rules of the jurisdiction? If several items > are bought at the same time is the correct VAT the sum of the VAT due for > each item or is it the VAT due on the sum of the items? > > Michael D Novack > > ___ > gnucash-user mailing list > gnucash-user@gnucash.org > To update your subscription preferences or to unsubscribe: > https://lists.gnucash.org/mailman/listinfo/gnucash-user > - > Please remember to CC this list on all your replies. > You can do this by using Reply-To-List or Reply-All. ___ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: https://lists.gnucash.org/mailman/listinfo/gnucash-user - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
Re: [GNC] rounding of Invoice vs Accounts Receivable
Yes and no for me... with each city in our (US) state allowed to add some percentage points to the sales tax, it would be difficult for a store to include the sales tax... but I have shopped in Europe and very much like that the price in the store is what I pay at the register. In addition, some states charge "sales tax" on food and others don't... when I was growing up, we lived right along a state border; one charged sales tax on food and one didn't. With a larger family, my mother absolutely did the bulk of her shopping in the state that didn't tax food. While I would prefer a consumption tax over an income tax, as things stand now, I don't want a national sales tax and think that we'll just have to muddle along as we are. However, I am absolutely appalled by places (usually internet or mail-order) that charge "shipping and handling", and use this to increase the price. I've paid $5.95 for "shipping and handling", only to find that the shipping costs were actually something like $2.40. I assume that "handling" is pure profit, and really should be part of the price. I understand that shipping varies by which state the merchandise is destined for, but I would think a seller could come up with a reasonable average shipping cost and just give me the price. I would like a slight variation of your idea -- all products should be what the seller gets; their product should include fees and shipping and handling costs. However, taxes are a different matter. Hotels have special hotel taxes in nearly every city, but these, I assume, differ. I don't mind the taxes being spelled out separately, because these are mandated by government, with none of the funds going to the hotel. On Mon, Oct 17, 2022 at 2:38 PM Stan Brown wrote: > On 2022-10-17 09:53, gnuc...@boeziek.nl wrote: > > I'll mention in passing that I _wish_ it were compulsory here to > advertise the actual total price including sales tax and other taxes. > That is usual for gasoline (petrol) here in the US, but as industry > practice rather than by regulation. However, just about every other > product is advertised with a base price excluding sales tax. To make it > worse, many products -- air fares, hotel rates, and cable, Internet, and > telephone, to name just a few -- advertise an attractive low price but > then charge "fees" that add a substantial increment to the advertised > price. Such deceptive practice is unfortunately quite legal here in > California, and in every other state as far as I'm aware. > > Stan Brown > Tehachapi, CA, USA > https://BrownMath.com > -- _ Richard Losey rlo...@gmail.com Micah 6:8 ___ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: https://lists.gnucash.org/mailman/listinfo/gnucash-user - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
Re: [GNC] rounding of Invoice vs Accounts Receivable
The problem with advertising 'inclusive' sales tax is, except in limited circumstances, usually impossible. This is because in many jurisdictions, there are taxes that scale with the amount of the total purchase, or on the day of the purchase (tax holidays anyone?) or even who the buyer is, (exempt, partially?) or where they 'live'. (some taxes charged at point of sale, others the rate is based on the point of delivery) Sales Taxes here are enough of a nightmare precisely because courts have allowed this mess and not forced everything to be 'point of sale'. Having to include them in pricing is practically impossible in many, many cases. From the perspective of someone who's worked in both the business end of retail and service sectors, I'd give a big thumbs down to inclusive pricing being a requirement. It can be done in a few limited cases, but certainly not universally. Regards, Adrien On 10/17/22 2:37 PM, Stan Brown wrote: I'll mention in passing that I _wish_ it were compulsory here to advertise the actual total price including sales tax and other taxes. That is usual for gasoline (petrol) here in the US, but as industry practice rather than by regulation. However, just about every other product is advertised with a base price excluding sales tax. To make it worse, many products -- air fares, hotel rates, and cable, Internet, and telephone, to name just a few -- advertise an attractive low price but then charge "fees" that add a substantial increment to the advertised price. Such deceptive practice is unfortunately quite legal here in California, and in every other state as far as I'm aware. ___ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: https://lists.gnucash.org/mailman/listinfo/gnucash-user - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
