Upcoming UK VAT Rate Change

[Matt]

We have started receiving enquiries about this and so this how-to provides a quick and easy way to perform the VAT Rate update.

 

As those of you who run businesses in the UK will no doubt be aware, the UK Government has announced it will increase the UK standard rate of VAT from 17.5% to 20% on Tuesday, 4th January 2011. For more information, please refer to http://www.hmrc.gov.uk/vat/forms-rates/rates/rate-increase.htm

 

This means that at midnight on Monday 3rd January, or at the latest before the cron run on the 4th for generating new invoices, you will want to update the tax rules in your WHMCS installation so that new orders and invoices use the higher rate. You can do this through the WHMCS admin interface in Setup > Tax Rules but a quicker and easier way of performing the update accross multiple separate rules is by running an SQL update query as follows on your WHMCS database via a tool such as phpMyAdmin:

 

UPDATE tbltax SET taxrate=20 WHERE taxrate=17.5;

 

Existing invoices will not be affected by this and will keep the 17.5% tax rates they were originally generated with. This will just mean that for any invoices generated after the change, use the new higher 20% tax rate.

 

 

If you also wish to update existing but unpaid invoices generated before the 4th but due on or after it, then you can use this query to do that:

 

UPDATE tblinvoices SET taxrate=20,tax=subtotal*0.2,total=subtotal+tax-credit WHERE status='Unpaid' AND taxrate=17.5 AND duedate>='2011-01-04';

 

 

Matt

[easyhosting]

This query has increased all VAT invoices even those due before 4th Jan 2011 to 20%

 

UPDATE tblinvoices SET taxrate=20,tax=subtotal*0.2,total=subtotal+tax-credit WHERE status='Unpaid' AND taxrate=17.5 AND duedate>='2010-01-04';

[othellotech]

This query has increased all VAT invoices even those due before 4th Jan 2011 to 20%

 

change duedate>='2010-01-04'; to 2011-01-04

[UH-Matt]

I assume we SHOULD be using that second query to do this properly? Already open unpaid invoices for service from 4th jan onwards should really be amended from 17.5% to 20% right?

[easyhosting]

I assume we SHOULD be using that second query to do this properly? Already open unpaid invoices for service from 4th jan onwards should really be amended from 17.5% to 20% right?

 

use the second query now to change any open unpaid invoices to be paid after the 4th to 20% and then midnight on 3rd use query 1 to change the VAT rate for all products.

[UH-Matt]

Surely it needs to be used on midnight not right now, as tomorrow for example more invoices will be opened for after the 4th but still with the 17.5% vat... both need to be used together at midnight.

[easyhosting]

no the second one will mean any invoices created today or tomorrow that are due after the 4th will be with 20% VAT.

the 1st one needs to be used at midnight on 3rd and will change the VAT rate for all your reoducts to 20%

[Matt]

Yes, if you want to make renewal invoices generating now but due after the change on the 4th use the new rate then you should be running that 2nd query every day until you make the updates to the default tax rules on the 4th as new invoices will be generating every day at the moment due after the 4th.

 

Matt

[UH-Matt]

Exactly what I meant, thanks for clarifying Matt

[othellotech]

I assume we SHOULD be using that second query to do this properly? Already open unpaid invoices for service from 4th jan onwards should really be amended from 17.5% to 20% right?

 

These are the questions you shoudl be asking your accountant

 

Answer will depend on ...

Are you on Cash or Accrual basis ?

Do you send *invoice* or *proformas* ?

[UH-Matt]

Yea, I am just reading the information our accountant sent to us, have not been on top of this, but think I have it sorted now!

[UH-Matt]

If any of you like us are ABSORBING the VAT increase for your clients, and use inclusive of tax pricing in WHMCS, then instead of the second query shown here, you need to use:

 

UPDATE tblinvoices SET taxrate=20,subtotal=total/1.2,tax=total-subtotal WHERE status='Unpaid' AND taxrate=17.5 AND duedate>='2011-01-04';

[othellotech]

Yea, I am just reading the information our accountant sent to us, have not been on top of this, but think I have it sorted now!

 

And now you've *altered* invoices, you need to renumber them all, send them to clients, then on a restored-backup of the previous DB change the *same* invoices to -ve, change the template to say "CREDIT NOTE" and resend those as well

 

Nicely thought through

[archer288]

Im not too familiar with scripts and wish to avoid doingit that way if i can to change the vat rate by doing more simply

 

Woudl there be any problems with just doing it by changing the rules?

 

I assume that all i do is delete the old tax rule and create a new one with 20% ?? Am i right ?

 

But would this change the VAT charged on invoices already sent last month ?

 

Thanks in advance for advice

[scurrell]

Woudl there be any problems with just doing it by changing the rules?

 

I assume that all i do is delete the old tax rule and create a new one with 20% ?? Am i right ?

 

Yes.

 

But would this change the VAT charged on invoices already sent last month ?

 

No.

[UH-Matt]

You will almost certainly need to use some scripting/sql to correct issued invoices. If you are not good at doing this yourself then you should contact whmcs support or some others on these forums for help with it. Should have all been done already by now!

[easyhosting]

Im not too familiar with scripts and wish to avoid doingit that way if i can to change the vat rate by doing more simply

 

Woudl there be any problems with just doing it by changing the rules?

 

I assume that all i do is delete the old tax rule and create a new one with 20% ?? Am i right ?

 

But would this change the VAT charged on invoices already sent last month ?

 

Thanks in advance for advice

 

use this

 

UPDATE tbltax SET taxrate=20 WHERE taxrate=17.5;

 

in a databse query to change the tax rule automatically to 20%

 

use

 

UPDATE tblinvoices SET taxrate=20,tax=subtotal*0.2,total=subtotal+tax-credit WHERE status='Unpaid' AND taxrate=17.5 AND duedate>='2011-01-04';

 

to change any allready issued invoices that have the old rate applied.

[stugster]

What a mess.

 

Can I suggest that for situations like this, an accountant is brought in to just quickly confirm that what's happening with the software is the correct thing?

 

We just did the "quick and easy" query posted in post #1 and yes, all our invoices previously raised are now wrong.

 

This is pathetic for software whose main intention is a billing system.

[easyhosting]

What a mess.

 

Can I suggest that for situations like this, an accountant is brought in to just quickly confirm that what's happening with the software is the correct thing?

 

We just did the "quick and easy" fix posted in post #1 and yes, all our invoices previously raised are now wrong.

 

This is pathetic for software whose main intention is a billing system.

 

we have used thes queries that Matt provided and all work fine and all invoices are correct

[UH-Matt]

What a mess.

 

Can I suggest that for situations like this, an accountant is brought in to just quickly confirm that what's happening with the software is the correct thing?

 

We just did the "quick and easy" fix posted in post #1 and yes, all our invoices previously raised are now wrong.

 

This is pathetic for software whose main intention is a billing system.

 

 

I doubt many billing systems have built in support for adjusting existing invoices to a new rate of tax.

 

It is a fairly easy fix, but you do need to check the queries before using them for your own situation.

 

We had to modify them as we put our pricing including VAT and also wanted to absorb the rise, so the second query which modifies the TOTAL was no good to us, we instead needed the SUBTOTAL modifying, TAX modifiying and the TOTAL leaving the same. I posted what we used, but thats just an example of checking specifically what you want to do and making sure you adjust the query as appropriate.

[stugster]

I doubt many billing systems have built in support for adjusting existing invoices to a new rate of tax.

 

No, I completely agree, they probably don't. It was my mistake for not reading Matt's post in its entirety, as he says "at the latest" for implementing the code.