Rounding of figures in Manager when calculating Tax (BTW)

[Roeland]

How to deal with the rounding of figures in Manager when calculating Tax (in The Netherlands: 6% BTW and 21% BTW)?

I have a receipt where the figures are as followed:

Total figure (incl Tax)	€ 15,92

Breakdown into 6% BTW and 21% BTW (as stated on the receipt):

BTW-code	Revenue		BTW		|	Total incl Tax
6%			11,25		0,67	|	11,92
21%		 	3,31		0,69	|	 4,00

When I put this receipt into Manager and use the ‘breakdown figures’ and do not use ‘Amounts are tax inclusive’ in Manager, the calculation goes wrong:

for the figure with 6% BTW Manager calculates 0,68
for the figure with 21% BTW manager calculates 0,70
and for the Total (included Tax): € 15,94

When I calculate these figures myself with my calculater it shows:
for the figure with 6% BTW it calulates 0,675, rounded into 2 figures after the comma: 0,68
for the figure with 21% BTW it calculates 0,6951, rounded into 2 figures after the comma: 0,70
So the rounding in Manager is correct.

When I start with putting the receipt into Manager while using the Tax-included figures (11,92 for BTW 6% and 4,00 for BTW 21%) the calculation in Manager shows the same figures as in the original receipt: it shows 0,67 for 6% BTW and 0,69 for 21% BTW.

What is the best way to do this in Manager? I prefer using the figures as stated on the recept as they are already split into 6% BTW and 21% BTW. Otherwise I first have to add the 2 figures excl BTW and BTW into an included BTW figure, use this in Manager, where Manager again calculated the correct Taxes.

[Tut]

@Roeland

I prefer using the figures as stated on the recept as they are already split into 6% BTW and 21% BTW.

And you should do just that. But understand that the methodology for calculating tax-inclusive is different than for tax-exclusive. See this Guide: https://www.manager.io/guides/9502.

@Roeland

Otherwise I first have to add the 2 figures excl BTW and BTW into an included BTW figure, use this in Manager, where Manager again calculated the correct Taxes.

No, you do not. In your example, the supplier has clearly used tax-inclusive prices, so you should, too. Do not add things up only to back things out. Simply enter the tax-inclusive prices and check the box accordingly. Manager does all the rest.

[Hennie]

A very interesting topic.

A. the VAT as stated on the receipt of Roeland is not calculated in the correct way (according to the rules)
B. Manager doesn’t calculate the VAT in the correct way when the tax inclusive option is used.
C. Manager calculates the VAT in a correct way when the tax exclusive option is used but then the total payment is 0.02 too high 15.94 in stead of 15.92

I’ll explain things:
Dutch VAT law says, and I assume that the rules in other EU-countries aren’t different, that in case the third decimal is 5 or higher, VAT-amounts should be rounded up, when the third decimal is 4 or lower the VAT should be rounded down.
As proof of this I’ll copy part of the official website of the Dutch Ministry of Finance.
As an idea/suggestion I would recommend that the VAT, as calculated by Manager, is shown next to the VAT tax code and that there is also a possibility to overwrite the calculated VAT.
An extra argument for this is that in case a supplier charged to less VAT, you are not allowed to claim more than has been charged.

@tut can you put this in the bugs category

[Tut]

@Hennie

B. Manager doesn’t calculate the VAT in the correct way when the tax inclusive option is used.

I cannot recall ever disagreeing with you before, @Hennie, but yes, it does. Begin with the obvious fact that the tax-inclusive price, P, is the sum of the base amount, X, and the tax,T:

P = X+T

We do not initially know either X or T, but we know that T is related to X by the tax rate, R:

T = R*X

So P = X+R*X = X(1+R)

The formula for the base amount X included in a tax-inclusive price P at tax rate R is therefore:

X = P/(1+R)

So, for the first line on @Roeland’s transaction:

X = 11.92/(1+.06) = 11.24528…, rounded to 11.25, leaving 0.67 for tax.

Rearranging the first equation to instead find T:

T = P-X

Substituting for X:

T = P-P/(1+R) = P[1-1/(1+R)]

Again, for @Roeland’s example:

T = 11.92[1-1/1.06] = 11.92[.05660377] = 0.6747…, rounded to 0.67, leaving 11.25 for the base amount.

So, no matter whether you calculate base amount or tax first, Manager’s tax-inclusive calculation is correct. Now, if you begin part way through the process with a base amount calculated from a tax-inclusive price, it is true that you will come up with a price one cent higher due to rounding differences accumulated by running the calculation in both directions. But @Roeland’s initial post said the price was tax-inclusive.

Manager is functioning correctly, as designed. I will not put this into the bugs category. The user needs to use the same tax-calculation method on purchase invoices and payments as the supplier/seller.

[Hennie]

@Tut
I agree that when P is the basis to use for the Tax calculation, Manager is calculating the VAT in a correct way and indeed I think we must start using P as the basis.
This brings me however to the following observation:
When you book a cash or bank payment and you want to calculate the VAT, only the tax-inclusive option should be allowed to use. When the user then starts booking the transaction, using the amount ex-VAT, it could lead to an incorrect transaction total because of improper rounding. To avoid this possible mistake, it would be desirable to disable the possibility of booking exclusive amounts.

What is your opinion about this?

[Tut]

@Hennie

When you book a cash or bank payment and you want to calculate the VAT, only the tax-inclusive option should be allowed to use.

I do not support this idea. Suppose you receive a sales invoice from a supplier, but rather than enter a purchase invoice, you will pay immediately (using a payment transaction). If the supplier’s sales invoice was constructed ex-VAT, there is no reason not to construct your payment the same way. This is especially true when there are many line items, such as when you buy inventory. That ensures you will get the same result. On the other hand, if the supplier’s sales invoice was inclusive, enter it that way.

Now, if your payment is against a purchase invoice, the question is irrelevant, because the tax was applied on the purchase invoice, not the payment.

[Hennie]

If possible, make things idiot/monkey proof, bearing in mind that, as Murphy said, things that can go wrong, will go wrong.

Like you said, when the payment is against a purchase invoice, the question is irrelevant as you probably will have booked the invoice via the purchase ledger.

Now let’s take Roelands example. The receipt is a business expense. An unexperienced user or a user that doesn’t understand the mechanics of Manager that well, pays this expense with his bankcard.
On his bankstatement he sees the amount of 15.92. When the box, for whatever reason, “Amounts are tax inclusive” is switched off", he ends up with a payment that is 0.02 higher than on his bankaccount. The worst thing is that probably he will notice this difference at a much later moment when he checks that his bank balance in Manager is different from what is printed on his banstatement. And then the question raises where did things go wrong and why. And that is exactly the reason why I recommend that the option “Amounts are tax inclusive” is going to be deleted.
That brings me to the next suggestion/question: Why doesn’t Manager show us the total amount of ex-VAT and VAT when we book a purchase invoice. We can immediately check if the total is according to what is printed on the purchase invoice. (check, check, double-check)

[Tut]

@Hennie

Why doesn’t Manager show us the total amount of ex-VAT and VAT when we book a purchase invoice.

First, remember that the exclusive/inclusive option can apply only to an entire transaction. There is never a mix.

If the transaction is exclusive, the base amount(s) and total tax for each tax code are shown. So what you mention is already available.

If the transaction is inclusive, the amount(s) of tax included are also shown, so no problem on that front. What is not shown is the base amount; but that is an inferred amount calculated from the total inclusive price. What I find strange in @Roeland’s example is the possibility that the receipt actually showed the base amount for a tax-inclusive transaction. In my experience, receipts from merchants for tax-inclusive transactions show only the total price and tax. In fact, they sometimes do not show the tax in a non-offsettable tax regime. That’s the entire point of a tax-inclusive price. But I suppose practices vary in different locations. It would be interesting to see an image of @Roeland’s actual receipt. We don’t know for certain that it was truly a tax-inclusive transaction. The seller may have gotten things wrong.

[Roeland]

My actual receipt

[Tut]

Thanks, @Roeland. This is a fairly typical tax-inclusive receipt. The individual line items are listed at their tax-inclusive prices. No separate tax amount is added for any tax code. The summarized figures below the total, which you transcribed in your first post, are merely for convenience. Note that individual line item items are not repeated. So the clear inference is that the revenue number has been backed out of the tax-inclusive price.

Had this transaction been for a business purchase of inventory from a supplier instead of groceries, it would have been necessary to include separate line items in Manager for each inventory item. You would have had no choice except to enter the tax-inclusive prices.

[Roeland]

I got a similar event, but now both Manager as the supplier has made errors in calculating TAX, see the images and my calculation by hand:

Receipt of supplier:

Calculation done in Manager:

Calculation done by hand:

first calculated by each line separate, than adding and than rounding

5,77 x 21% = 1,2117
0,40 x 21% = 0,084
           --------
	         1,2957 = 1,30 (rounded)

now by first adding, than calculating and than rounding

5,77 + 0,4 = 6,17
6,17 x 21% = 1,2957 = 1,30 (rounded)

+++++++++++

first calculated by each line separate, than adding and than rounding

80,99 x 9% = 7,2891
 5,52 x 9% = 0,4968
   	       --------
	         7,7859 = 7,79 (rounded)

now by first adding, than calculating and than rounding

80,99 + 5,52 = 86,51
86,51 x 9%   = 7,7859 = 7,79 (rounded)

Conclusion:
Manager makes a wrong rounding of the 21% TAX percentage, but a correct rounding when 9% TAX is used, while the supplier makes a mistake in both TAXES. The end result is a difference of 0,01. How can I correct this in Manager?

[Roeland]

Or should I use the following: rounding by item and than adding:

5,77 x 21% = 1,2117 = 1,21 (rounded)
0,40 x 21% = 0,084 =  0,08 (rounded)
                    -------
                      1,29

Than the rounding in Manager would be correct, but still: how to solve the 0,01 difference?

[d3mad]

Hi @Roeland, may I suggest to you not to worry about this? Perhaps it may be an issue, but I would like to suggest it really isn’t. You see, does $0.01 really make any difference? I say this because I too am very particular about these things and have spent many an hour and day over just $0.01 for the tax office and one day it dawned on me that no-one really cares. Only me!

By the time all is said and done, and with rounding to whole dollar amounts that will most likely be employed at the time of submitting the quarterly/yearly documents, a few cents here and there are really not going to matter, and if they do, create a “rounding” journal entry.

If you search the forums for a few years ago you will find many thread discussions from me about rounding errors too as I am incredibly obsessive about that sort of thing, but in this case, I have learned to just let it go.

[Mark]

It’s clearly a rounding error of the supplier 'cos 86,51 x 0,09 = 7,7859, which rounds to 7,79 and definitely not to 7,80. You can submit a claim for that 0,01 to the supplier.

[Tut]

@Roeland, your third method is the one Manager uses. Each line item causes completely separate transactions to be entered into the general ledger, so each is individually controlled by your definition of the currency involved (2 decimal places in this case).

Your supplier, on the other hand, has determined subtotals by tax rate and calculated tax amounts based on that. So a different result is obtained.

Solutions are discussed in this Guide: Adjust rounding differences on purchase invoices | Manager.

[maarten]

I have a problem with correct rounding,

When I calculate taxes for each line seperately, the result per line is the same in Manager.

However when I add up all the tax amounts I arrive at a different tax total than Manager (70,98 instead of 70,99 euros)

Why is this happening?

Running manager 21.5.31 on windows 10

[Joe91]

Calculating by line, rounding and adding is the way most software programs calculate tax and is generally accepted as the correct way.

Any rounding discrepancies are usually not material and will cancel out over time.

[Tut]

@maarten

when I add up all the tax amounts I arrive at a different tax total than Manager

On every line, the calculated numbers in the shaded fields are rounded to the number of decimal places you have specified for the currency. In this case, that is two decimal places. You cannot, after all, charge a customer or pay a supplier a fraction of a cent. And each line must stand alone. If you add the numbers shown in your screen shot, you get the same total as displayed.

[maarten]

You’re correct, I was looking at the wrong document there. Thanks for responding though