ERPNext Conference 2019* ERPNext.com Blog

Rounding still wrong?


#1

Hello,
I’m debugging rounding errors in tax calculation and found out that 73.9948 rounded to two decimals is 73.99 instead of 74.00 in Frappe’s opinion. Yes, I have increased precision for “rate” and “price_list_rate” field but not “amount” fields of Purchase Order/Receipt/Invoice Item which is what I want. However I think this has nothing to do with the following strange behavior in general frappe’s rounding.

The files in question are:
frappe/public/js/frappe/misc/number_format.js
frappe/utils/data.py

Versions:
ERPNext: v6.16.4
Frappe Framework: v6.17.5

Thanks,
Matej


ERPNext in Australia
#2

OK, obviously just about every rounding algorithm does this the same way so this is not a bug.

However, I think there should be an option somewhere to choose the tax calculation order:
a) calculate individual items’ taxes and then sum them
b) sum items’ amounts and then calculate tax

As I recall, countries may specify the procedure to be used. Any other opinion on this?

Thanks,
Matej


#3

@mte we are trying to fix rounding issues as much as we can. Like you said, dividing on amounts rather than on rates etc. Its done in most places, but if you find a case, please post. @nabinhait will be happy to fix.


#4

Hi,
Thanks for reply. Glad to hear you try to fix as much as you can.
Let me first explain the rules in my country. You can either:

  • sum all items’ net amounts and then calculate the tax on the sum (which is more logical in case of dealing mostly with B2B where all the prices are excluding VAT), or
  • get tax for each item and then sum individual item’s taxes (more appropriate with B2C where prices are mostly VAT inclusive). HOWEVER in this case you need to apply tax to precision of item’s rate (which is 4 in my case), not amount (which is 2 in my case). If item rate’s precision is 2 decimals, all tax calculation has to be done with AT LEAST 3 decimal places. In this case you also have to display each item’s individual tax amount on the invoice.

Let’s take a look at the method calculate_taxes() in erpnext/controllers/taxes_and_totals.py. You can see there is no code to calculate taxes like the first way i describe above. All taxes are calculated as individual item’s taxes and then summed together. BUT the real issue is that the method get_current_tax_amount() rounds each item’s tax amount to precision of tax_amount, which is usually 2. If item’s rate precision is 2, the precision of tax calculating before summing should be at least 3 or the same as item rate precision (I like to set it to 4 so the calculation should be done with precision 4).
Only after the tax amounts are summed together should it be rounded to the precision of tax_amount (which is usually 2 decimal places).
Sorry if I was not clear enough but please let me know…
Thanks,
Matej


#5

Your description is very much clear. Case 1 will not be possible, because in that case it will not honour item-wise tax rate and also inclusive tax.

And I think case 2 is also very specific, rounding of tax amount based on item rate, it may create problem to others.


#6

Hi,
I am having a similar tax rounding issue on a number of documents that I cannot seem to solve, and I re-created it at a basic level on a quotation on the demo site under QTN-00090. Also, screenshot below. The rounding yields improper tax calculation for where I live (United States, California), off by $0.01. Tax rate is 7.500%. When one item is $100, the tax is calculated properly as $7.50. However, if there there are two items, one item for $99 and one item for $1, totaling exactly $100, the tax is calculated improperly as $7.51. I’m guessing it’s rounding tax on each item, and since the tax fraction is exactly half a cent on each item, it rounds up for both. In California, the tax must not be rounded until the final tax total.

If we send quotations like this to our customers, they will not do business with us because to their accounting departments it looks like an elementary error.

I’ve increased the currency precision by customizing the “Quotation” and the “Sales Taxes and Charges” forms, but it does not change the tax values. Also, it typically displays an extra decimal that is unwanted.

Any insight on how to mitigate this issue would really be appreciated. Thanks in advance.
-Scott


#7

I have exactly the same issue.
How is it possible to either have erpnext round the item tax to 3 or more decimals or have the tax directly calculated on net total.
My example two line items and tax template 10.7%.

Item 1: 2 pieces - 11 euros - 2.354 tax
Item 2: 2 pieces - 11 euros - 2.354 tax

Correct total tax is 4.708 = 4.71

However erpnext does (i think) per item 2.35 times 2
Wrong total tax 4.70

What is the issue here? Float changes or changes to 3 decimals in customize make only the number longer but do not help.

@rmehta


#8

Hey @rmehta, @nabinhait,

Can you tell me where I can find the source code for this? Then I can have a look into it.

I am currently in spreading ERPNext to Austrian/German business but this is a severe issues.

There must be an option that the calculated item-wise tax is NOT rounded before it is aggregated.
Please, how can we fix this issue very fast?
I am willing to help to dig into the source code, so that you could fix that on the short track.


#9

/home/frappe/frappe-bench/apps/erpnext/erpnext/controllers/taxes_and_total.py

If there was a way we could have the “Add/Deduct” function in the sales taxes the same as purchase taxes, that way we don’t need to mess up with the code. Choose “Actual” and deduct it from the grand total and it will fix everything, at least it did that for me using this custom script for purchasing.

frappe.ui.form.on("Purchase Order","validate", function(){ if (cur_frm.doc.taxes_and_charges = 'Inclusive Tax'){ for (var i =0; i < cur_frm.doc.taxes.length; i++){ cur_frm.doc.taxes[i].tax_amount = cur_frm.doc.grand_total * 7 / 107 }

where 7 is the tax rate and I chose “actual” and “Deduct” in the template


#10

Thank you for your help!

I use the hosted version

So this script only works for purchasing? What is this add/deduct?

And in purchasing the grand total is the net (before tax) amount?


#11

Add/Deduct is in Purchase tax and charges template
Add = will add VAT % on top of the total
Deduct = will deduct VAT % from the total
Sorry I meant Total, not Grand Total. Please see image


#12

Hey @nikolx:

@umair helped to solve that problem - what is to do:

  1. Go to Customize Form “Sales Taxes and Charges”
  2. Go to field “Amount” tax_amount (currency field) and change the precision to 3 decimal places- this is the field “Amount (USD)” and will be displayed in the table with three decimals (not a nice display variant but ).
  3. The field “Total Taxes and Charges” will still be only two decimals and properly rounded.

Let me know if this works out.


#13

@nikolx Thanks for your help. Just tested it and it worked for me too, but I had to add the precision for 3 more fields aside from tax_amount

Do the same as mentioned above for net_rate and net_amount in Sales Order Item
and net_total in Sales Order
when all those field are 3 decimals, it will work perfectly and if you need to print 2 decimals you can use this for the print format
{{'{:20,.2f}'.format(doc.net_total,'')}}


#14

@tomier1991 @mrmo Thank you guys for the help, I have gotten it working on our Quotation form, which is great.
In addition to your suggestions, I also had to increase the precision of “total” in “Sales Taxes and Charges” to get it to work. Hoping this fix trickles through to all the forms. I couldn’t seem to get the print format to change on the “Amount (USD)” column, but considering the rest of the form is 2 decimal places it’s not a problem for us.

Minor issue: When I click the “Show tax break-up” under the tax table, the column listing “Sales Tax Collected” still seems to round to two decimal places (i.e. “$ 0.0800” instead of “$ 0.0750”), see the screenshot below. I cannot seem to fix this with the precision setting anywhere. This is a much more minor issue that I can work around, but if anyone knows how to fix it I’m all ears.

In looking to fix “Show tax break-up”, I did see in customizing “Sales Tax and Charges” the “Item Wise Tax Detail” is Type “Code”, but I have not looked into the code nor know just where to find it. Guessing the rounding happens in the code and modifying the code might be required. Or could be related to the “Total Taxes and Charges” of type HTML in customizing “Quotation”.


#15

@nikolx in the Sales Taxes and Charges you have to change the precision decimals for some more fields as follows:

  • tax_amount
  • tax_amount_after_discount_amount
  • total
  • base_tax_amount
  • base_tax_amount_after_discount_amount
  • base_total

Ps. I had some issue with some transaction using the 3 decimals, so changed to 6 decimals. 100% working.


#16

ERP ver. 11
on salary_slip.py file line 627

I replace
amount, additional_amount = rounded(amount), rounded(additional_amount)
to be
amount, additional_amount = rounded(amount,1), rounded(additional_amount,1)