DESKTOP EDITION CLOUD EDITION SERVER EDITION GUIDES FORUM

Rounding of figures in Manager when calculating Tax (BTW)


#1

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.


#2

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.

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.


#3

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.

image

@tut can you put this in the bugs category


#4

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.


#5

@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?


#6

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.


#7

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)


#8

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.


#9

My actual receipt


#10

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.