The Book-keepers Forum (BKF)
The Book-keepers Forum (UK) -> VAT, Payroll & Tax -> VAT formula in excel not working?
Post Info TOPIC: VAT formula in excel not working?
adrift


Newbie

Status: Offline
Posts: 2
Date:
VAT formula in excel not working?
Permalink Closed

Hi all

Just doing accounts and  changing to 20% VAT. I use excel and the old formula of *7/47 but can not get the 1/6 to work.

IE 

Cell A1 £200 Cell A2 =Sum(A1*1/6) and this then works out the VAT as
£33.33. But I want the VAT of £200 ie £40

Sorry if this is a simplistic mistake issue and thanks in advance for any help.


__________________
Wella


Expert

Status: Offline
Posts: 2256
Date:
Permalink Closed

Hi Adrift

The formula you are using, is the one to find the VAT value of a VAT inclusive total. All you need to do is 200*20%  A1 = 200 A2 = A1*20%

Also the 7/47 formula results in 29.79

HTH

Bill


__________________

 

 
tonys


Senior Member

Status: Offline
Posts: 226
Date:
Permalink Closed

You use the *7/47 to get the figure for VAT that is included in a gross figure:
Ie £240 gross*7/47 gives you a figure of 35.74 fo the VAT included in that 240, hence net was 240-35.74=204.26

From the above posting you seam to be wanting to add VAT to a net figure?
If so, the calculation would be simply 200*20/100= 40

I can't work out why you were using th *7/47 unless you had a gross figure and wanted to work out the VAT included inthat figure?

Regards,

Tony.

__________________
tonys


Senior Member

Status: Offline
Posts: 226
Date:
Permalink Closed

Sorry Bill you posted while I did!
Tony.

__________________
Wella


Expert

Status: Offline
Posts: 2256
Date:
Permalink Closed

Hi Tony

That make a change, it's usually me that gets pipped to the post biggrin

Bill

__________________

 

 
adrift


Newbie

Status: Offline
Posts: 2
Date:
Permalink Closed

Thank you. By reading your post and having a coffee i can see where I was going wrong, just having one of those days.

appreciate your help especially as I won't be able to return the favour, as on a steep learning curve regarding book keeping atm.



__________________
Page 1 of 1  sorted by
 
Quick Reply

Please log in to post quick replies.
The Book-keepers Forum (UK) -> VAT, Payroll & Tax -> VAT formula in excel not working?
Tweet this page Post to Digg Post to Del.icio.us
Members Login
Username 
 
Password 
    Remember Me  
©2007-2024 The Book-keepers Forum (BKF). All Rights Reserved. The Book-keepers Forum (BKF) is a trading division of Bookcert Ltd. Registered in England Company Number 05782923. 2 Laurel House, 1 Station Rd, Worle, Weston-super-Mare, North Somerset, BS22 6AR, United Kingdom. The Book-keepers Forum and BKF are trademarks of Bookcert Ltd. This forum is a discussion forum only. There will usually be more than one opinion to any question and any posting should not be viewed as a definitive solution. No responsibility for loss occasioned to any person acting or refraining from action as a result of any posting on this site is accepted by the contributors or The Book-keepers Forum. In all cases, appropriate professional advice should be sought before making a decision. We reserve the right to remove any postings which are offensive, libellous, self-promoting or engaged in covert marketing. We will not notify users of removals. The views expressed in the forum posts are those of the individual and do not necessary reflect or agree with those of The Book-keepers Forum. Any offensive or unsuitable posts will be removed by the moderators. Any reader of this forum can request for a post to be looked into by sending an email to: bookcertltd@gmail.com.

Privacy & Cookie Policy  About