Tax/Rounding Invoice Improvements

[gnucash at charleslyons.co.uk]

Hi,

While entering an invoice on GNUCash I noticed a difference between my  
hand-calculated VAT and the way GNUCash calculates it. I noticed the  
same observations in the Business bug database:

http://bugzilla.gnome.org/buglist.cgi?product=GnuCash&bug_status=NEW&bug_status=REOPENED&bug_status=ASSIGNED&bug_status=UNCONFIRMED&component=Business

specifically #502853 ("Incorrect tax rounding in invoice") and #504954  
("Wrong calculation of TAX"). Also #520547 ("Taxes and discounts are  
not calculated correctly") and #443967 (" Wrong TAX Calculation?") are  
partially affected by this. Here are my observations and possibly  
fixes detailed.


Problems
========

(1) The simple part is how you do rounding. The current code always  
uses GNC_RND_ROUND to do "round-to-even" rounding. But EC regulations  
1103/97 and 2595/2000 state that for a calculation which "gives a  
result which is exactly half-way, the sum is rounded up".

(2) The tricky part is when you do rounding. Currently GNUCash  
calculates the tax for each line of the invoice, rounds to the nearest  
monetary unit (e.g. cent or penny) and sums those rounded figures to  
obtain the total tax on the invoice. This is the standard European way  
of accounting as it allows each line to have a separate tax rate  
applied; different commodities have different rates in Europe. Each  
line should use the correct rounding to obtain a total tax amount *per  
line*. But in the US, a uniform sales tax is applied to an entire  
invoice. While it's acceptable to apply this individually to each  
line, it is not appropriate to round on each line.

(3) A similar problem exists when calculating the amount of tax per  
line (for per-line calculated tax): do you calculate it per unit and  
round then multiply by quantity, or calculate total ex-tax price for  
quantity*unit_price and then apply tax to the total? I would do the  
former because then the total tax is identical regardless of whether I  
add the same item on multiple lines, or aggregate them together into  
larger quantity on a single line; otherwise the calculation may be a  
penny/cent out for each unit (which can multiply up significantly). At  
the moment GNUCash does the latter. Which is correct depends on  
personal/state-enforced accounting, so perhaps a global configuration  
option in the user preferences would be good?


Solutions
=========

(1) For European currencies (at least), GNUCash should be using  
GNC_HOW_RND_ROUND_HALF_UP for all rounding operations. If it isn't  
appropriate to hard-code this, then the currencies need to be updated  
to contain a rounding field which can be queried when a rounding  
operation is required. This allows us in Europe to use ROUND_HALF_UP  
implicitly with EUR, GBP currencies etc., while retaining  
backward-compatibility for other currencies. Alternatively, this can  
be a global option in the user's preferences.

(2) For every invoice, offer two options with radio buttons on the  
invoice entry page: "Tax per-line" (default?) and "Tax per-invoice".  
The first causes GNUCash to calculate the tax amount per line and sum  
the already-rounded amounts to obtain the total invoice tax (this is  
what already happens). The second method causes GNUCash to calculate  
the tax amount per line exactly, sum together, then round to obtain  
the total invoice tax. Offering both options on each invoice is useful  
for cross-border trade.

(3) Fix the rounding on each line so the quantity is applied *after*  
rounding the tax for the item on that line. So the calculation for  
per-line tax becomes: round(tax_percent*pretax_amount)*quantity. This  
doesn't apply to per-invoice tax which involves only one rounding for  
the entire invoice anyway. A global configuration option to choose  
between the two methods may be desired.


Code
====

I spent a few hours digging around in the 2.2.8 code  
(gnucash-2.2.8.tar.gz from 14th December 2008). Here are my skeleton  
suggested improvements for each; I haven't supplied complete patches  
as I don't have time to learn all about GNUCash development, but I  
hope these suggestions are accurate enough to allow the regular  
GNUCash developers more familiar with the code to enhance it.

(1) In the gncEntryComputeValue() function in  
business/business-core/gncEntry.c, the affected lines are those  
containing GNC_RND_ROUND. Change to GNC_HOW_RND_ROUND_HALF_UP in each  
case. This can be made more complicated---basing the decision on  
properties of the currency or a global configuration option in the  
user preferences. I leave that choice to the regular GNUCash  
developers who are far more familiar with the code.

(2) This problem is caused by the rounding lines in  
gncEntryComputeValue() in business/business-core/gncEntry.c. But the  
fix is actually in the gncEntryRecomputeValues() in the same file,  
when the gncEntryComputeValue() method is called there. Currently it  
looks like this:

/* Compute the invoice values */
   gncEntryComputeValue (entry->quantity, entry->i_price,
			(entry->i_taxable ? entry->i_tax_table : NULL),
			entry->i_taxincluded,
			entry->i_discount, entry->i_disc_type,
			entry->i_disc_how,
			denom,
			&(entry->i_value), &(entry->i_disc_value),
			&(entry->i_tax_values));

   /* Compute the bill values */
   gncEntryComputeValue (entry->quantity, entry->b_price,
			(entry->b_taxable ? entry->b_tax_table : NULL),
			entry->b_taxincluded,
			gnc_numeric_zero(), GNC_AMT_TYPE_VALUE, GNC_DISC_PRETAX,
			denom,
			&(entry->b_value), NULL, &(entry->b_tax_values));

It is the "denom" lines which need changing. These should be set to 0  
for "tax per-invoice" and left as denom for "tax per-line". You'd need  
to add some way to convey this information to the function here, and  
add a pair of radio buttons on the invoice GUI.

(3) Again this is in gncEntryComputeValue() in  
business/business-core/gncEntry.c. You calculate the aggregate price  
(qty*price) early on. You should probably leave the aggregating  
(multiplying by qty) until the end, so the pretax value throughout the  
rest of the method is the pretax value *per unit*. This involves the  
following line patches:

(a) Remove aggregate = gnc_numeric_mul (qty, price, GNC_DENOM_AUTO,  
GNC_DENOM_LCD);
(b) Replace pretax = gnc_numeric_sub (aggregate, tvalue, GNC_DENOM_AUTO,
        with pretax = gnc_numeric_sub (price, tvalue, GNC_DENOM_AUTO,
(c) Remove else {
                pretax = aggregate;
            }
(d) Replace *discount_value = discount;
        with *discount_value = gnc_numeric_mul (qty, discount,  
GNC_DENOM_AUTO, GNC_DENOM_LCD);
(e) Replace *value = result;
        with *value = gnc_numeric_mul (qty, result, GNC_DENOM_AUTO,  
GNC_DENOM_LCD);
(f) Replace taxes = gncAccountValueAdd (taxes, acc, amount);
        with taxes = gncAccountValueAdd (taxes, acc, gnc_numeric_mul  
(qty, amount, GNC_DENOM_AUTO, GNC_DENOM_LCD));
(g) Replace taxes = gncAccountValueAdd (taxes, acc, tax);
        with taxes = gncAccountValueAdd (taxes, acc, gnc_numeric_mul  
(qty, amount, GNC_DENOM_AUTO, GNC_DENOM_LCD));

I believe that to be a correct and complete list of changes, but you  
would need to ensure that makes sense before applying the changes, and  
pad out any other enhancements. Sorry I couldn't provide in svn diff  
format.


I would like to add that similar problems also affect new versions of  
QuickBooks (2008+) and has annoyed several of their customers, so to  
fix it in GNUCash would be a definite plus!

Thanks all, and keep up the great work.