Extracting VAT from a cell or group of cells

[Duckyb]

I wonder if anyone knows how to extract VAT from an amount in a cell. Can you set up a formulae, which if you place an amount in a cell it then asks you for the VAT amount within that figure?
Basically I am trying to do book keeping and have downloaded a chart from Microsoft. I list everything in Gross amounts, I am now wondering if I can show a better picture by also having one vAT column at the end, but that I can automatically get that figure there by being asked a question at each cell input.
Sorry if im not making much sense, I am relatively new to Excel and only have a limited knowledge of what exactly can be performed.
http://www.excelforum.com/images/smilies/confused.gif

[p24leclerc]

If VAT stands for Value added tax, is it not supposed to be fixed at 20% for UK?
If so, then your formula is:

Please Login or Register  to view this content.

Assuming your amount is in cell A1.
If VAT is not fix, I would suggest to add a column to state the VAT in % and then your formula would be:

Please Login or Register  to view this content.

Assuming your amount is in cell A1 and VAT in cell B1.

[AB33]

If your VAT is already being included with the amount, you can simple use this formula which gives you the amount of VAT:
(20/120)*Gross amount. Gross amount is inclusive of VAT. Or simple divide 20/120=0.166666667 and then multiply it by the gross figure.

[Duckyb]

Thanks so much for the replies, unfortunately it has highlighted that as suspected I did NOT give you enough information. I will now try to explain proporly.

Cell A1 Has amounts from i.e. Fuel bills for May 2012, which not all these bills are subject to 20% VAT, they may also be vatable at a rate of 5%. Is there anyway which I can differentiate as I am inputting how much VAT is within the Gross figure? THEN transfer the VAT amount to another column. (I suspect that I mihgt just have to do a VAT column and set up the basic SUM formulae and just add to it each month.

Or anyone who is accounts trained, do you have a way when your doing books/accounts where you differentiate within a workbook?

[AB33]

Yes, You can. You should set two separate VAT rates, one for standard rate, call it S.D and the other one for Reduced Rate, call it R.D. You should also have another column which holds the type of VAT rates, so that instead of selecting which VAT type, you can include it with a formula. I think the easiest way to explain is to show in excel, but you need to attach your sample book, so that I can do the formulae and attach back.

[Duckyb]

Here is a workbook, for you to be able to show me

[AB33]

Ducky,
Thank you!
Okay, I can see you set up a cash flow template, under the receipts and payments headings. You have sales and at the bottom you have also VAT, but not on the payment side. Are these figures Net or Gross? When you do your cash flow forecast, do you just include the gross figure and wanted to calculate the VAT and Net amount? I also know not all payments attract VAT, some are VAT zero, others may be exempt, like insurance and payroll. So, tell me how would you like forecast?

[Duckyb]

Hello AB33,

Apologies for the late reply, we are really busy at the mo.

Firstly, the sheets although downloaded were Cash flows, I use them as Accounts/Book keeping sheets, as I didn't know where to get hold of an accounts one (I did search Microsofts downloads, but with no success)
2; the VAT in the 'Rows' is VAT reclaimed from HMRC.
3; all figures are input Gross - I dont mind inputting net rather than gross if this is more usual???
4; Our Feed we order is VAT exempt but this could go under Zero rated. So probably if we had the VAT broken down into columns? With 20%, 5% and Zero/Exempt. I am not usre also whether at osme times during the year we have something that is VAT rate 8% (can't remember definately).

I hope this is the infomation you need, please could I just say a HUGE thank you for this I really do appreicate it.

[AB33]

Ducky,
Okay, If you want a cash receipts/payment book, I will make you one, just give me another day.

[AB33]

Ducky,
See attached.
I do not know if the formulae are complicated, or not. We can improve it. You need to choose if the item is Vatable(Y/N) from the drop down menu. That is where the calculation begins. If It is not Vatable, it is 0, but If yes(Y), then the next question is it Standard rate, or Reduced rate, you choose either S, or R from the drop down menu again? You need to put a figure on the Gross line and all the calculations are done auto based on Y/N and S/R options.