which format for 17.5%

[bazz]

Hi,

I am making a VAT table for a tax rate
|vat_id| vat_rate |

I thought decimal might be correct and then ENUM but they both round 17.5 up to 18.

what should I use for percentages?

bazz

[oesxyl]

Hi,

I am making a VAT table for a tax rate
|vat_id| vat_rate |

I thought decimal might be correct and then ENUM but they both round 17.5 up to 18.

what should I use for percentages?

bazz
hi,

float(4,2) work with procents.
I don't know if is a good idea to store something that is not change often, like a tax rate, into a data base, it's not exactly a variable. I think that a better idea is to hardcode somewhere.

best regards

[bazz]

lol yeh but, at least if its in a table, I'll be able to find it if I do need to change it :eek:

I remember when it was 8%, then 12-ish and then 15. now it's 17.5 and who knows what our the Govt will do next. they've brought in more taxes than a 10yr old has had hot diners and, still, they haven't any reserves.

[oesxyl]

lol yeh but, at least if its in a table, I'll be able to find it if I do need to change it :eek:

I remember when it was 8%, then 12-ish and then 15. now it's 17.5 and who knows what our the Govt will do next. they've brought in more taxes than a 10yr old has had hot diners and, still, they haven't any reserves.
it's important what you do with this data.
In this case I prefer to store in two column the fields:
1) product/order with vta
2) value of vta, in money, not %

that's in my opinion is the best bet.:)

to explain why:
column 2, fit better with real life because could be afected by two kind of error:
a) somebody compute wrong the vta, it's a real fact must deal with it but must be retrived from a storage sistem. Later correction to this mistake will afect another value.
b) if you multiply a value with 17.5% you must round, because a vta of $11.326 have no mining in reality it must be $11.33 or $11.32
If you sum the value of vta, real one, for thousends of rows and also sum of the base from where you start compute( multiply and round), assuming that you don't have any error of one mentioned at a), you will see that never fit.

now about political changes, :)
usualy there is a low or something that say "starting with 11/13/2007 the vta become 1.5%", :)
it's more easy to have a tax module with some conditionals inside like


vta = 17.5; # actual value
if date before x then vta = b0;
if date between x and y then vta = z;
....
here you can use the corect value of vta


this way you can deal easy with a long period of time.

about first field in table, with or without vta, I prefer with because so is the cash flow and reflect any posible mistake in the sistem, like for example, somebody pay less or more then it must pay.

best regards

[Andrew Johnson]

If you multiply a dollar amount by any percentage there's a possibility to round...

Any percentage of one penny excluding 0 and 100 is going to product a number with at least 3 decimal points

[masterofollies]

Will Ceil(); and Floor(); work with percents?

[oesxyl]

Will Ceil(); and Floor(); work with percents?
yes but not rounding is the problem.

to give an example:
you have a product $1
the vta for it is $0.175 => round up => $0.18
that means product with vta $1.18

take 1000 products with vta => $1180
1000 products without vta => $1000
vta for 1000 products => $175 if you apply to $1000
but the difference is $180

that ignore the fact that somebody by mistake compute for a product of $10 a vta of $3.5

best regards

[PappaJohn]

I often have to deal with multiple sales tax (VAT) rates. I generally store these in a separate tax table. Typical schema:

tax_id - primary key
location - location description for sales tax rate (needed for multiple rates)
rate - float(6,5) - I store the rate as a decimal not a percent
updated - date
current - true/false

For accounting purposes, I generally maintain a history of the tax rates. True, they don't change often, but they do change. So, when a rate is updated, the old one is flagged as current = false.

I store the tax rate as a decimal for calculations, I can always format it for display.

In the sales table, I store the tax amount, not the tax rate.


take 1000 products with vta => $1180
1000 products without vta => $1000
vta for 1000 products => $175 if you apply to $1000
but the difference is $180


This can, and does happen in the normal course of business. At least in the US, sales tax (VAT) is computed on a per-sale basis, with appropriate rounding. However, when the sales tax reports are filed, the total tax collected will, most likely, not equal the total sales times tax rate, because of the rounding errors introduced in the individual sales.

[bazz]

Thank you all for your replies.

@PappaJohn - that is pretty much how I am doing it and I just didn't know what format to use for the vat rate. I should have been clearer: it isn't actually percentage and perhaps isn't a decimal either but I would never have thought float. (That's what milk used to be delivered on isn't it? :D.)

I will try float and use the number 1.175 as the mulitplier and see what that does for me.

Anyway, I agree with you about the annual sales tax report but can't the actual amount of tax taken in sales be calculated from the sales table where all the actuall vat amounts are added up?

bazz