Alok Paul
Tax is the amount of money paid by the customer. It is mandatory to pay tax while purchasing a product except for duty-free products. In this article, we will show how to calculate sales tax in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
4 Ways to Calculate Sales Tax in Excel
While calculating tax usually two situations seen. One is tax is included with the price, or this may be exclusive. We will discuss both cases here. We can see a sample receipt of the product purchase.
1. Get Sales Tax by Simple Subtraction
In this section, we will determine tax using simple subtraction. When we buy a product price, the tax rate and the total price are mentioned usually on the receipt. From that information, we can get the tax simply through a subtraction process.
We can see in the receipt the price, tax rate, and the total price are given.
📌 Steps:
- We simply subtract the price value from the total price and get the tax amount. Go to Cell C7 and put the following formula.
=C6-C4
- Then, press the Enter button.
We get the amount of tax.
2. Calculate Sales Tax Not Included in Price
In this section, we can see the tax is exclusive of the price. The tax rate is given here. We will calculate the tax based on the product price and tax rate.
📌 Steps:
- Go to Cell C6. Put the following formula based on the multiplication of the product price and tax rate.
=C4*C5
- Finally, press the Enter button.
This is the standard method to calculate sales tax.
Similar Readings
- Forecast Sales Using Regression Analysis in Excel (3 Methods)
- How to Forecast Sales in Excel (5 Easy Ways)
- Calculate Percentage of Sales in Excel (5 Suitable Methods)
- How to Calculate Sales Growth over 3 Years in Excel (2 Methods)
- Calculate Annual Sales in Excel (4 Useful Methods)
3. Calculate Sales Tax Included in Price
In this section, a different situation appears. Here, the product price is included with tax. We do not know the actual price of the product. We know only the tax rate. In this situation, we have two ways to determine the sales tax. See below for details.
Case 1:
- Put the following formula on Cell C6.
=C4-C4/(1+C5)
- Hit the Enter button.
In the second part of the formula, we calculate the price without tax and then subtract that to get the tax.
Case 2:
- Copy and paste the formula below on Cell C6.
=(C4/(1+C5))*C5
- Hit the Enter button again.
In the section, we determine the price without tax first. Then, multiply that with the tax rate to get tax.
4. Calculate Two-Tier Sales Tax
In this section, we discuss how to calculate two-tier sales tax in Excel. In this system, up to a certain limit, the authority fixes a tax rate. Above that value, the tax rate increases. This system is applied to luxury products.
The IF function checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.
In the data, we can see one box for sale amount and tax. And another box for tax rates. We set that those values are below or equal to $1000, and the tax rate is 5%. And, the above amount is taxable at a rate of 8%. We used the IF function in the calculation.
📌 Steps:
- Put the formula on Cell C5.
=IF(B5<=$F$7,B5*$F$5,$F$7*$F$5+(B5-$F$7)*$F$6)
- Press the Enter button for execution.
- Finally, drag the Fill Handle icon downwards.
In the formula first, we check if our price value is below or above the limit. If within limit then simply calculate tax with Tier 1 rate. Or if the value is above the limit, then follow execute the second part of the formula. Here, we calculate the tax to the Tier 1 rate and extra value after the limit, multiplied by the Tier 2 rate.
Conclusion
In this article, we how to calculate sales tax in Excel in different situations. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.
Related Articles
- How to Calculate Average Sales in Excel (5 Simple Approaches)
- Forecast Sales Using Historical Data in Excel (6 Methods)
- Calculate Average Sales per Month in Excel (2 Easy Methods)
- How to Calculate Total Sales in Excel (With Easy Steps)
- Calculate Projected Sales in Excel (2 Easy Ways)
- How to Calculate Sales Growth over 5 Years in Excel (3 Ideal Examples)
- Forecast Sales Growth Rate in Excel (6 Methods)
