GNOME Bugzilla – Bug 504954
Where and how should the rounding happen in invoices with VAT?
Last modified: 2018-06-29 21:57:11 UTC
The setup of GnuCash uses the SKR04 data frame. By entering a bill with a lot entries the resulting TAX value is wrong. The TAX value is set to 19% (German TAX value). The following bill: .. Description ... Amount | Unit Price | ... | Amount | 1. Entry 20 | 0.95 | | 19.00 | 2. Entry 10 | 0.35 | | 3.50 | 3. Entry 50 | 0.151 | | 7.55 | 4. Entry 57 | 0.101 | | 5.76 | 5. Entry 57 | 0.101 | | 5.76 | 6. Entry 20 | 1.55 | | 31.00 | 7. Entry 100 | 0.22 | | 22.00 | results in Total: 112.52; Subtotal: 94.57; TAX: 17.95 But 94.57 * 0.19 = 17.9683 which should be 17.97 so there is a difference of 0.02. Sometimes the calculation is correct and sometime not. I guess that the calculation of TAX is done for each row first and then each of the single TAX values are added to the result. I think it must be done at the end after adding the sum of each row. Maybe the heading of the table is wrong, but I work with the german version and translated the heading to english. The problem happens also with version 2.2.1 and with the linux version. Thomas Weber
Yes, each row is calculated and then they are summed. Changing this would be... challenging. Keep in mind that each row could have a different tax table, so you still need to perform a sum ( row * taxtable ) operation. Perhaps the problem is that we're not rounding at the correct time? I'd have to look at the code to figure out when we're rounding and when we're not, but I suspect that we're NOT rounding on each row computation where we should be (or maybe we are when we shouldn't be)?
You should save rounding until the total gst amount is calculated. By rounding each line item gst amount you are creating rounding errors.
*** Bug 557876 has been marked as a duplicate of this bug. ***
This is still an issue with GnuCash 2.3.15, and not only on Windows, so changing the OS to All.
I traced this back to r14531. Due to some of the changes in this revision, the function gncEntryGetValue returns rounded values although the function's documentation explicitly say it shouldn't. I have corrected this in r19475, this fix will first appear in GnuCash 2.3.16. Thank you for reporting this.
Unfortunately I had to revert this fix as it introduced another rounding error, which was reported in bug 628903. I have looked a bit deeper into this, but it looks I'm hitting a design limitation I'm not sure will be easy to solve. This report (bug 504954) suggests invoice total and tax should be calculated from unrounded entry values. Bug 628903 on the other hand posits the exact opposite: the German import tax invoice requires invoice total and tax to be calculated from rounded entry values. To be honest, I have seen both methods applied here in Belgium, although I believe the official way is indeed to calculate taxes from unrounded values. So presuming both methods are valid depending on the country you live in, we should have a way for the user to select his preference. But unfortunately that's not the end of it. Suppose the preference is set to round each entry. Then as in the example of bug 628903, what happens is this: In the invoice window, the total of the entries is calculated from the *unrounded* entry values, but at the same time the entries themselves are shown on screen *rounded* to 1ct. So if you sum the entries as you see them on screen this will give a different result from the calculation GnuCash uses internally. Next when you post this invoice, a transaction is added to the corresponding AP/AR account. This transaction will have the invoice's total in one split (the total as calculated from the *unrounded* invoice entries) and all the invoice's entries in the other splits. These splits are *always* automatically rounded to the currency's accuracy. So for this transaction the sum of the splits will not be zero, and hence GnuCash adds a balancing split automatically. This problem is inherent to the current design of the register. I'm not sure at this point how this can be solved properly. I'm sorry.
I have done some research as to which method should be considered correct (round per line or round on the total amount). The summary is, we should support both. I found a Dutch law text that explicitly allows both methods although you should choose one and apply it to all invoices you create consistently [1]. This means the code should be amended to cater for both possibilities and the GUI should allow to choose one. It should probably be set up as a. an option per vendor (since each vendor should use one consistent scheme) and b. an business preference on book level (the assumption here is that each book is for one company, and a company should use consistent rules for all invoices it sends). [1] See article 3.5.5 on this Dutch law: http://www.minfin.nl/Actueel/Besluiten_beleidsregels/2009/02/Omzetbelasting_Administratieve_en_factureringsverplichtingen The page is also avialable in English if you like (there's a button on top).
I found the following, when we discussed bug 502853, but here is the proper place: To explain "sum save rounding" (Summenerhaltendes Runden): If you sell to consumers in the EC, you must show them the all tax included price. An invoice of tax included prices is a matrix of the form n1 + t1 = g1 n2 + t2 = g2 : nm + tm = gm ------------ sum(ni) + sum(ti) = sum(gi) with n: net amount = round (g * 100 / (100 + taxrate)) t: tax amount = round (g * taxrate / (100 + taxrate)) g: gross amount i = 1..m but because of the rounding the sum line sometimes violates at least one condition. To avoid this, there are different rounding algorithms, which do some "error diffussion". More details are in http://de.wikipedia.org/wiki/Rundung and links.
When I last checked in the UK it is acceptable for VAT to be calculated on either a per line basis or from net. Perhaps there should be an option to select the preferred method.
Any updates on this? I just ran into this problem, as well, with an incoming bill that has 4 entries of the same product with a different description. If I enter these 4 items separate from each other, the total amount of VAT calculated is €0,01 off. If I just enter on entry and set the amount to 4, the total amount of VAT is correct. This workaround will do for now, but a fix or a setting would be nice.
The problem with VAT is that it can be calculated in different ways depending on the country the VAT is being calculated in. In the UK, for example, VAT can be calculated using one of the following: 1. Vat calculated per unit, then summed 2. Vat per line (quantity * units), then summed or 3. Vat of total cost per transaction. When calculating using the different methods there is an inherent rounding error depending on which way you go around. This is why there is the 0,01 Euro error. To solve this I think it would be best to add a set of radio options in the preferences page and use the to determine the calculation to use. If another developer can add the radio options with the appropriate flags I will try to re-arrange the calculations ready for the next available release. Any other developers able to do this?
I am a programmer, but have never worked on GnuCash (or even looked at the source code) before, so I'm probably not the best person to do this on a relatively short notice. If I have some spare time in the following time, I may have a look at it, but I wouldn't count on that happening in the next weeks.
Hi Alex and Egbert. Thank you for offering to help here. I will certainly consider any patches attached here. Keep in mind though that these patches will not be used for 2.6. Adding radio buttons adds new translatable strings. Since we are currently in string freeze those will have to wait until we reopen the development tree after the 2.6 release. You don't have to wait that long to work on this though. As for the radio options themselves: since VAT can be calculated in all three ways in the UK alone, I'm tempted to work with two sets of radio buttons: - one set in the business preferences (found under "File->Properties"). These are settings per file. And I think each file should be allowed to have its own rounding defaults. - one set per vendor. Each vendor may use a different preference. This per vendor preference can either be set to use the global file preference, or override it.
When you fix this, you may also have a look at bug 724857 for a related problem that can only occur, when you use the option "tax included". Provided that the smallest fraction of the currency used is 1/100, then all tax rates which are an odd multiple of 0.16% (0.16%, 0.48%, 0.80%, 1.12%, ...) can cause the problem described there. Relevant examples of such tax rates are 4%, 8%, and 20%. IMHO, a fix for both bugs would be easier, if the option "tax included" became a per-invoice option, instead of a per-row option.
The original issue has been fixed for gnucash 3.2 so I'll close this bug now. For the ongoing wider discussion on how rounding could be handled I have opened a new enhancement request: bug 796391. Please follow up there.
GnuCash bug tracking has moved to a new Bugzilla host. This bug has been copied to https://bugs.gnucash.org/show_bug.cgi?id=504954. Please update any external references or bookmarks.