Total Incl. VAT Rounding in NAV 2013 R2

Reedberg

Hello experts,
I am using NAV 2013 R2 Cronus. I want to sell an item A for 100 euros.
I go to Item Card and set the value in the "Unit Price" field to 84,74576 (100 euros / 1.18 VAT).
I create new Sales Order, add item A into it, change quantity to 1. Open Statsitics form and see value 100,01 in "Total Incl. VAT" field. This rounding is not correct.
Ok, I modified the value in the "Unit Price" field to 84,74, validate Item A in Sales Order, now Statistics form shows 99.99 in "Total Incl. VAT" field.
Ok, I remove the value in "Unit Price" field of Item Card and create new Sales Price (through Item Card -> Navigate tab -> Sales Prices. I add new Sales Price with "Unit Price" equal to 100 and flag "Price Includes VAT" installed.
I validate Item A in Sales Order and run Statistic form, once again the value in the "Total Incl. VAT" field indicates 100,01.

How can I sell item A exactly for 100 euros?

In fact I found this issue first on my client's DB and he demands the solution to be provided in the nearest future. But as long as this behaviour is the same for default Cronus db, I decided to ask your help. How can I sell item A for 100 euros with settings/development?

The solution when I change "Amount Rounding Precision" to 1 instead of 0.01 can not be used, because such rounding is to rough.

Any help will be really appreciated!

jglathe

Hi Reedberg,

this is complicated. The simple answer is you can't, more or less. I have tested your example with NAV2013R2 (W1 CU14, Build 38801) and it shows this behaviour. However, when you set "Prices including VAT" in the sales order, the unit price and the total amount including VAT will be correctly calculated as 100,00.
The root cause of the issue is "rounding granularity". The sum of the document lines must be the total amount, for every part of it - net amount, VAT amount, discounts (for the line and for the whole document). Every amount will be rounded by the currency-specific amount rounding precision. When calculations other than additions are done with this value afterwards, bad things happen - the error from the rounding will be multipled and leads to greater differences than necessary.
Let's take your example: The unit price including VAT is 100,00, multiplied by 1 (quantity) is 100,00, rounded for EUR is 100,00. The input into the sales line is 84,74576 (Unit-Amount Rounding precision is 0,00001). NAV calculates the price including VAT from it:

  "Unit Price" :=
    ROUND(
      "Unit Price" * (100 + "VAT %") / (100 + xRec."VAT %"),
      Currency."Unit-Amount Rounding Precision");

This is also rounded by Unit-Amount rounding precision. And then, the line amount (including VAT) is calculated by rounding again after calculating quantity * price:

IF "Line Amount" <> ROUND(Quantity * "Unit Price",Currency."Amount Rounding Precision") - "Line Discount Amount" THEN BEGIN
  "Line Amount" := ROUND(Quantity * "Unit Price",Currency."Amount Rounding Precision") - "Line Discount Amount";
  "VAT Difference" := 0;
END;

Now, the line amount will be rounded, no matter what. This is also true for the case where the VAT will be calculated based on this line amount excluding VAT, and this is where human logic and NAV logic deviate.
You would simply calculate: VAT Amount := (Unit Price * Quantity - Discount Amount) * VAT% and round the result.
NAV does this (simplified): VAT Amount := (round(Unit Price * Quantity) - Discount Amount) * VAT% and rounds the result again.
In your example, the NAV line amount will be 84,75 after rounding. Multiplied with VAT% (0,18) this becomes 12,255, and 12,26 after rounding. This totals to 100,01.
Basically rounding line amount at all is a bad idea, IMO. Yes, it is a field that is shown in the field/page, and no, it will not be printed in documents. For this, the fields "Amount" and "Amount including VAT" are there, and they will only be filled when releasing a document. So the way to rectify this would be to edit the properties of the line amount field in the pages (but you cant change the decimals property dynamically, so: bad idea if you have multiple currencies with different rounding precisions), and to rework the whole logic on not rounding line amount (and discount amount, but that's another bunch of problems), but that's quite a task. It hasn't improved in the newer incarnations of NAV, either. In fact, the normal calculation logic contains quite a few unnecessary roundings that lead to problems.

with best regards

Jens

Reedberg

jglathe,
Thank you very much for your reply, I checked how rounding is handled in NAV 2013 and in my client's environment and found a lot of differences. However, having in mind your comment, now I understand how it should work in standard Cronus and at least I managed to explain to my client the root cause of the problem.

Once again, thank your, your reply did me a really good service!