The Book-keepers Forum (BKF)
The Book-keepers Forum (UK) -> Book-keeping Software -> VAT formulas on Excel
Post Info TOPIC: VAT formulas on Excel
graemelamb


Newbie

Status: Offline
Posts: 1
Date:
VAT formulas on Excel
Permalink Closed

Hi,

I create invoices for my hotel business using Microsoft Excel, at the moment I don't show VAT on invoices however this needs to be detailed.

All my prices are inclusive of VAT just now, does anyone know a formula that I can use which will show the breakdown of the actual product, and how much the VAT on the product is?

Any help would be greatly appreciated!



__________________
Robert Pearce


Senior Member

Status: Offline
Posts: 266
Date:
Permalink Closed

If you have the gross price, then the VAT will be a sixth of this and the net figure will be the balance (or five sixths of the gross price).  This assumes that VAT is being charged at 20%. 



__________________

Pearce & Co - Chartered Accountant and Chartered Tax Adviser 

www.pearceandcoaccountants.co.uk

These comments are outline only and are not a substitute for specific professional advice.
MRSurrey


Member

Status: Offline
Posts: 10
Date:
Permalink Closed

If your Gross is CELL, then the VAT will be =CELL/6 and your Net will be =CELL/6*5

Hope that helps, Graeme.

__________________
MR
Shamus


Forum Moderator & Expert

Status: Offline
Posts: 11981
Date:
Permalink Closed

Both of the previous posters are correct but just to add my two penneth...


lets put your VAT inclusive amount in cell A1

Cell B1 enter =a1*1/6 (VAT amount)

Cell C1 =a1-b1 (Non VAT amount)

If you have any old data that you needed to convert at 17.5% VAT rate the multiplier back then was *7/47

HTH

Shaun.


__________________

Shaun

Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
Wella


Expert

Status: Offline
Posts: 2256
Date:
Permalink Closed

And to add my two penneth as well

Cell A1 = full amount

B1 =A1 / (100+current VAT rate) x current VAT rate gives VAT amount

C1 =A1 x 100/ (100+ current VAT rate) gives pre VAT amount

 

That way if the vat rate changes, or you need to calculate a different rate you don't need to remember different fractions just one formula.

So at current rate

B1 =A1/ (100+20) x 20 (If it was 5% then it would be B1 =A1 / (100+5) x 5

C1 =A1 x 100/ (100+20) (If it was 5% then it would be C1 =A1 x 100/ (100+5) )



__________________

 

 
Don Tax


Expert

Status: Offline
Posts: 1716
Date:
Permalink Closed

Yep and its also handy to store various rates in cells to point formulas at

Cell       %
x1   =   20
y1   =   15
z1   =   17.5




__________________
webpiter


Member

Status: Offline
Posts: 6
Date:
Permalink Closed

This is very amazing, I truly appreciate this post about vat calculation. I have been looking all over for this instant vat calculation formula in ms-excel ! Thank goodness I found it on this site. You have made my day! Thank you again!

__________________

Internships
Page 1 of 1  sorted by
 
Quick Reply

Please log in to post quick replies.
The Book-keepers Forum (UK) -> Book-keeping Software -> VAT formulas on Excel
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