Magento Forum

Page 2 of 2
Fun with VAT - Pre-Planning the 2.5% Hike
 
TonySpace48
Jr. Member
 
Avatar
Total Posts:  3
Joined:  2010-11-30
Warrington, Cheshire, England, UK
 

I’m afraid you’ve got a problem with your calculations…

J.T. - 06 December 2010 03:32 AM

Say we have a product that now, pre-hike costs £9.99 inc the 17.5% VAT.

£9.99 = 117.5%
£8.50 = 100%
£1.49 = 17.5%

So the end price is made up of £8.50 plus £1.49 VAT = £9.99

That £1.49 part is rising.

20% * 9.99 = 1.998

Now it’s £8.50 + £1.998 = £10.498 - or £10.50 after rounding.

So what was £9.99 with 17.5% VAT included should now become £10.498 with 20% VAT included.

The first bit is correct to find out the original (100%) value, but you go on to add the 20% VAT on to the old value (effectively 117.5% value) instead of the 100% value…

J.T. - 06 December 2010 03:32 AM

20% * 9.99 = 1.998

..it should be..

20% * 8.50 = 1.70

So the total should be £10.20 with 20% VAT, not £10.498!

You can check that I’m correct by using the online VAT calculator tool at : http://www.thevatcalculator.co.uk/index.php

Secondly, with your SQL statement you are changing ALL product attributes with the type of DECIMAL, that means you will also be increasing the Weight and Cost attributes for example (other Decimal attributes may have also been added by the user). So really you need the SQL to specifically target the Price attribute only. You can do that with the following SQL…

UPDATE catalog_product_entity_decimal val
SET val
.value = (((val.value 47) * 40) * 1.2)
WHERE val.attribute_id = (
    
SELECT attribute_id
    FROM eav_attribute eav
    WHERE eav
.entity_type_id 4
    
AND eav.attribute_code 'price'
)

For clarity, that code will increases prices from having 17.5% VAT included to having 20% VAT included.

If you want to make the prices look nice you can change the SET line to…

SET val.value CEILING(((val.value 47) * 40) * 1.2)
..which will (always) round up to the near pound.

If you didn’t want your prices to be to the whole pound (say .95 instead) you could change the SET line to…

SET val.value CEILING(((val.value 47) * 40) * 1.2) - 0.05
..which essentially does the same rounding up but then takes 5p off.

The above SQL will only change the price, if you need to change the special price as well you just need to change the attribute_code line…

AND eav.attribute_code 'special_price'
.. and re-run.

As always when hitting the DB directly like this it would be best to work on a test copy first to ensure it works as expected, then also make a backup before running on live!

 
Magento Community Magento Community
Magento Community
Magento Community
 
BHModelling
Jr. Member
 
Total Posts:  13
Joined:  2010-11-29
UK
 

Hey guys, figured this may be the best place to post a related problem in hope someone can help!

I am simply trying to change the VAT rate to 20%. I have changed the rate in ‘Manage VAT rates’ but it has had no affect on the VAT amount on products or through checkout. Have I missed something?

Any help is much appreciated.

Regards,

Mike

 
Magento Community Magento Community
Magento Community
Magento Community
 
TonySpace48
Jr. Member
 
Avatar
Total Posts:  3
Joined:  2010-11-30
Warrington, Cheshire, England, UK
 
BHModelling - 03 January 2011 01:49 PM

I am simply trying to change the VAT rate to 20%. I have changed the rate in ‘Manage VAT rates’ but it has had no affect on the VAT amount on products or through checkout. Have I missed something?

Hi,

It depends on how you are currently storing your prices, the above posts were related to installs that have been configured to store the prices including the VAT. This is a problem because when you change the VAT rate in the settings the prices stay the same.

The place you need to look at to see how you have things setup is in the admin go to..

System > Configuration > Sales > VAT (or Tax)

..here you will want to look at..

Calculation Settings > Catalog Prices

..and..

Price Display Settings

It is possible (and recommended) for new installs to store the prices ex VAT and set them to display them including VAT on the frontend, that way you can change the VAT rate setting and the prices will change as appropriate on the frontend.

Hope that helps.

 
Magento Community Magento Community
Magento Community
Magento Community
 
elfling
Enthusiast
 
Avatar
Total Posts:  901
Joined:  2008-10-21
 

Nicely done.

id = 4 depends per database, had one database where it was an id 10.

 
Magento Community Magento Community
Magento Community
Magento Community
 
J_T_
Moderator
 
Avatar
Total Posts:  1961
Joined:  2008-08-07
London-ish, UK
 
OptimiseInternet - 03 January 2011 09:41 AM

I’m afraid you’ve got a problem with your calculations…

J.T. - 06 December 2010 03:32 AM

Say we have a product that now, pre-hike costs £9.99 inc the 17.5% VAT.

£9.99 = 117.5%
£8.50 = 100%
£1.49 = 17.5%

So the end price is made up of £8.50 plus £1.49 VAT = £9.99

That £1.49 part is rising.

20% * 9.99 = 1.998

Now it’s £8.50 + £1.998 = £10.498 - or £10.50 after rounding.

So what was £9.99 with 17.5% VAT included should now become £10.498 with 20% VAT included.

The first bit is correct to find out the original (100%) value, but you go on to add the 20% VAT on to the old value (effectively 117.5% value) instead of the 100% value...

Yeah, you are right, that’s what you get when you quickly want to solve something that deserves more attention than a quick look. Indeed, obviously, £8.50 + 17.5% VAT was £10, £8.50 + 20% VAT is now £10.20

Which of course cocks up the rest of the calcs. This is why you never trust a random geezer on the internet! Sorry!

I simply changed the rates last night 23:00 as I couldn’t be bothered to stay up till midnight (flu anyone?). Of course one person managed to squeeze in an order at 23:30 which is now a few pence off the mark officially.

 
Magento Community Magento Community
Magento Community
Magento Community
 
darkedge3
Jr. Member
 
Total Posts:  24
Joined:  2009-03-13
 

This is hilarious, I was googling this very problem and found the same posts where I commented on finding the result the first time!

you can check out my results here:

MDF Skirting Boards
Oak Skirting Boards
Ash Skirting Boards
Skirting

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top
Page 2 of 2