# Rounding the calculated VAT

Hi

Student bookkeeper (explains apparent ignorance) ...

Excel spreadsheets seems to round up by default,
but I understand that VAT is rounded down.

Do I need to use something like :   =ROUNDDOWN((formular),2)   ?

cheers

### .

Excel doesn't round at all unless the user specifies such whether by absolute modification of the value (ie the formula you quoted in your post) or simply via what is displayed (but not the actual value in the cell) eg by specifying a particular number format (I suspect this is what you are referring to).

If you want it specifically round down then yes you should use use a formula to do so like Rounddown.

### ROUND v. ROUNDOWN

Although, as Richard points out, Excel doesn't round the number used in calculations unlesss you tell it to by using one of the rounding functions, the 'standard' ROUND() function will round numbers from 0.5 up, and numbers up to, but below 0.5, down (note that -0.5 goes to -1 with ROUND()). ROUNDDOWN() will round 0.5s down, but will also round .99 down.

The bit I found on the HMRC site suggested that it would only be appropriate to round down amounts less than 1p, otherwise 'normal' rounding would seeem to apply:

17.5.1 Calculation based on lines of goods or services

If you wish to work out the VAT separately for a line of goods or services, which are included with other goods or services in the same invoice, you should calculate the separate amounts of VAT either by rounding:

• down to the nearest 0.1p - for example, 86.76p would be rounded down to 86.7p; or
• to the nearest 1p or 0.5p - for example, 86.76p would be rounded up to 87p.

Whatever you decide, you must be consistent.

The final total amount of VAT payable may be rounded down to the nearest whole penny.

If you need to round to the nearest 1p or 0.5p you could use MROUND() which rounds to a multiple eg: =MROUND(E15,0.5)

### Rounding the calculated VAT - practicalities of obtaining paymen

Whether it's Excel or most accounting systems: Where VAT is calculated on individual lines and totaled, this total can often differ from the VAT calculated on the total invoice (or the total of items at each VAT rate). Whilst this can cause annoying matching problems, I've also seen it stop payment on a large invoice where the difference was too big to pass validation at the customer. The resulting delay on payment of this  invoice was painful! Worth thinking of splitting larger invoices into smaller ones, where practical. This also helps avoid payment delays on the whole amount if there is a query on one item.

### Rounding the calculated VAT - practicalities of obtaining paymen

Hi

That is where I have been having problems with some test papers.

I understand that some invoicing is done that way because items on an invoice can be at different VAT Rates.
Would have thought the answer was to group them and apply VAT to their sub totals.
Can't be that difficult;  can it ?

cheers