Working out VAT [TAX] using numbers '09

Question

Level 1

0 points

Working out VAT [TAX] using numbers '09

Hi guys......hope someone can help!

I am using numbers for an expenses spread sheet and cannot get it to calculate [properly] one particular element.

I input the following data horizontally:

Date, Description, categories [8 in total] Gross, VAT, Net

It is the VAT calculation that is causing me problems, i am currently using: =17.5/100*(GROSS)

It calculates but the VAT % is incorrect [too much]

i am sure that this is a simple task but it is getting the better of me!

27 inch imac, iPad, 20 inch imac, 13 inch macbook, Mac OS X (10.6.5), MacBook 2Ghz Intel Core 2 Duo 4GB 667 Mhz DDR2 SDRAM

Posted on Jan 2, 2011 3:00 PM

Reply

Answer 1

Badunit

Level 7

30,708 points

Jan 2, 2011 3:23 PM in response to Airsteal

17.5/100 is 17.5%. Is 17.5% of gross the VAT?

Reply

Answer 2

Airsteal Author

Level 1

0 points

Jan 2, 2011 3:34 PM in response to Badunit

Thanks for responding, the variations you sent me give the same result in my spreadsheet?

17.5% of the gross would be the VAT element at that rate

Reply

Answer 3

Badunit

Level 7

30,708 points

Jan 2, 2011 3:43 PM in response to Airsteal

I don't know what I was thinking when I posted. I deleted it and posted something different a few seconds later. You must have seen the original.

So, if 17.5% is correct, what is leading you to believe the answer is incorrect? Is your header name "GROSS" in the correct column?

Reply

Answer 4

Airsteal Author

Level 1

0 points

Jan 2, 2011 3:57 PM in response to Badunit

Must have - hopefully someone will have an answer...ah, you must have altered your answer.

My calculation is wrong because 17.5% of £117.50 is £17.50 but the answer in my spreadsheet is £20.56 and the net amount at £96.94

I enter an amount in a column, misc for example, this is then repeated in Gross which then calculates the VAT and the Net but in this case both the VAT and the net amount are incorrect.

Not sure if this will show up

/tmp/ABI Exp Accounts 10-11.test.numbers.jpg

Message was edited by: Airsteal

Reply

Answer 5

Badunit

Level 7

30,708 points

Jan 2, 2011 3:51 PM in response to Airsteal

Can you supply us with a row of data?

Reply

Answer 6

Badunit

Level 7

30,708 points

Jan 2, 2011 4:09 PM in response to Airsteal

To post a document so we can see it requires posting it to a hosting site and providing a link to it.

17.5% of 117.5 is equal to 20.56
17.5% of 100 is 17.5
Looks like the formula is working correctly.

Reply

Answer 7

Badunit

Level 7

30,708 points

Jan 2, 2011 4:17 PM in response to Badunit

If GROSS includes the VAT and you want to find out what part of it is VAT,

Sale + VAT = GROSS
VAT = Sale$*(VAT%) => Sale = VAT/(VAT%)
VAT/(VAT%) + VAT = Gross
VAT(1/(VAT%)+1) = Gross
and, finally, the answer:
VAT = Gross/((1/(VAT%)+1)

With this formula, if Gross = 117.5 then VAT = 17.5

Reply

Answer 8

Airsteal Author

Level 1

0 points

Jan 4, 2011 4:09 PM in response to Badunit

Absolutely spot on! Thank you soooo much!

Reply