Fractional Discrepancies in VAT-Inclusive Pricing

XML and business rules
1

Hi everyone,

In our ERP we use VAT-inclusive pricing which occasionally leads to fractional discrepancies.

For instance, let’s consider the price of a litter of gasoline, which is 1.18 (including VAT). If the quantity is 44.42, then the total amount should be:
44.42 * 1.18 = 52.41.

However, when expressed in XML tags:

cac:InvoiceLine.cbc:InvoicedQuantity = 44.42
cac:InvoiceLine.cac:Price.cbc:PriceAmount = 1.03 (excluding VAT, calculated as 1.18/1.15)
cac:InvoiceLine.cac:Price.cbc:BaseQuantity = 1
Then, the tax amount (cac:InvoiceLine.cac:TaxTotal.cbc:TaxAmount) is calculated as follows:

cac:InvoiceLine.cbc:LineExtensionAmount = cac:InvoiceLine.cac:Price.cbc:PriceAmount (1.03) * cac:InvoiceLine.cbc:InvoicedQuantity (44.42) = 45.75
cac:InvoiceLine.cac:TaxTotal.cbc:TaxAmount = cac:InvoiceLine.cbc:LineExtensionAmount (45.75) * cac:InvoiceLine.cac:ClassifiedTaxCategory.cbc:Percent (15%) = 6.86

So, the total amount in this case is cac:InvoiceLine.cac:TaxTotal.cbc:RoundingAmount = cac:InvoiceLine.cbc:LineExtensionAmount + cac:InvoiceLine.cac:TaxTotal.cbc:TaxAmount
cac:InvoiceLine.cbc:RoundingAmount = 45.75 + 6.86 = 52.61

This total is not equal to the previous total (52.41), which was calculated from the price including VAT. There is a difference of 0.19.

How can we address this fractional discrepancies? Is there an error in our calculation or rounding?

Thanks in advance.

1 Like
2

Don’t calculate both amount. Either calculate the total before VAT and get the VAT by substracting the total before VAT from the total or vice versa.

3

Hi,
Your calculation is right. I think you can work around this problem by changing the base amount and price.

Applying this to your case example :

cbc:InvoicedQuantity = 44.42
No change

cac:Price.cbc:BaseQuantity
1 -----> 1000

cac:Price.cbc:PriceAmount
1.18/1.15 = 1.03 -----> 1000 * 1.18/1.15 = 1026.09

cbc:LineExtensionAmount
1.03*44.42 =45.75 ----> (1026.09 /1000) * 44.42 = 45.58

cac:TaxTotal.cbc:TaxAmount
45.75*0.15 = 6.86 -----> 45.58 * 0.15 = 6.84

cbc:RoundingAmount
45.75 + 6.86 = 52.61 -----> 45.58+6.84 = 52.42

Now you need to calculate the Differance between Expected amount and Calculated amount
D = E - C (1.18 * 44.42 - 52.42 = 0)

IF D > 0 then

        <cac:AllowanceCharge>
          <cbc:ChargeIndicator>true</cbc:ChargeIndicator>
          <cbc:Amount currencyID="SAR"> D </cbc:Amount>
        </cac:AllowanceCharge>

ELSE IF D < 0

        <cac:AllowanceCharge>
          <cbc:ChargeIndicator>false</cbc:ChargeIndicator>
          <cbc:Amount currencyID="SAR"> D </cbc:Amount>
        </cac:AllowanceCharge>

Notes :

  • This method works only if D in range [0.01 … 0.03].
  • I didn’t try this before and I’m not sure if it is accepted.
4

1- UnitPrice …Qty …Percentage… you don’t have to rounded to 2 decimal …But Other figures u have to round up like Line total before tax, tax amount and Total Inclusive, …
2- So calc Is 44.42 * 1.18 * 100 / 115 = 45.5787… 45.58 ( as unit price is 1.02608… with franction )
3- Tax amount = 45.58 * 15 / 100 = 6.837 … 6.84 …Not 6.86
4- Total Include Tax 45.58 + 6.84 = 52.42 … Not 52.62

image
image980×515 95.6 KB

For Accounting purpose if there is a differ of 1 hallah between details & Total Tax calc, many solutions for that
you can refer to as Ideas

5

Thanks, everyone

I have missed that fields (UnitPrice, Quantity, Percent) don’t need rounding up. Settings them up without rounding resolved the issue.

1 Like
6

Boss, can you advise ,

10 SAR inclusive of 15% taxes still gives me 0.01 halala difference.
(QTY) 1 * (price) 10.00 * 100 / 115 = 8.695652 = 8.70
8.70 * 15/100 = 1.305000 = 1.31
Thus total = 8.70 +1.31 = 10.01
How can we handle in XML ?
Thanks in advance!