Share via

Struggling with sales tax formula

Anonymous
2024-11-21T16:13:16+00:00

So I've got Cell A2 with a gross figure of (eg) £60.74, and would like a sales tax formula for B2 of 5% and, finally, C2 a formula for 60% of the 5%:

A2 Gross amount B2 Sales tax C2 Disallowed sales tax

£60.74 5% (of £60.74) 60% of cell B2

I know there's a percentage formula for 20% Sales Tax. So if A2 (gross amount) is £120, then B2 (VAT) is £20 (=A2/120*20). What I can't figure is how to work the formula after the forward slash being totally rubbish with maths and percentages.

Just to give some background for this requirement – in the UK most Sales Tax (VAT) on products are at 20% but domestic energy tariffs are at 5%.

Any help would be gratefully received.

Michael

Microsoft 365 and Office | Excel | For home | MacOS

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2024-11-21T23:09:59+00:00

    Hi,

    In cell B2, the formula will be

    =A2/105*5

    In cell C2, it will be

    =B2*0.6

    Hope this helps.

    1 person found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-11-22T10:58:42+00:00

    Cheers for all your help Riny. Greatly appreciated.

    Michael

    0 comments
  2. Anonymous
    2024-11-22T10:57:39+00:00

    Thanks so much Ashish. That really helps and will make entering up my tax account so much easier.

    Michael

    0 comments
  3. Anonymous
    2024-11-21T21:51:48+00:00

    Appreciate you getting back. Sorry is there any chance you could, using my example, show me how that formula would actually appear for B2 and C2:

    A2 Gross amount. B2 Sales tax C2 Disallowed sales tax

    £60.74

    0 comments
  4. riny 20,870 Reputation points Volunteer Moderator
    2024-11-21T17:21:21+00:00

    Not sure I see the problem.

    When the base number = 100 and you add 20% (i.e. 1/5th), the end result becomes 120. That is 1 + 1/5th = 6/5th of the base amount.

    Divide the end result (12) by 6 and you get the VAT amount of 20.

    Similarly, when the VAT = 5% (i.e 1/20th), you can divide the amount including VAT by 21 (i.e. 1 more than 20) to get the VAT.

    And then C2 will just be 0.6 * B2.

    Am I missing something?

    0 comments