How to Calculate Pre-Tax Price from a Tax-Inclusive Price

In many B2B sales scenarios, you may need to calculate both the "sales tax" and the "total price including tax" on your quotes, sales orders, or invoices.

This can be done using simple formulas based on the following rules:

(1) Sales Tax = Pre-Tax Price*Tax Rate (for example, 7.5% sales tax)

(2) Total (Tax Included) = Pre-Tax Price + Sales Tax

Many of Ragic’s templates already include these formulas by default.

Calculating the Tax-Inclusive Total

For example, in a "Quotation" sheet:

(1) The "Sales Tax" field can use the formula "E8*0.075", where E8 refers to the subtotal and 0.075 represents a 7.5% sales tax.

(2) The "Total (Tax Included)" field can use "E8*1.075" or "D13+D14", depending on your layout.

Once these formulas are applied, the system automatically calculates the "Sales Tax" and "Total (Tax Included)" without manual input.

Calculating Pre-Tax Price from a Tax-Inclusive Total

In some cases, you may need to enter the "Total (Tax-Included) " amount first and then calculate the "Pre-Tax Price" from it.

The formula rule is:

Total (Tax Included) = Pre-Tax Price*(1 + Tax Rate) 

For example, with a 7.5% sales tax:

Total (Tax Included) = Pre-Tax Price*1.075

To find the "Pre-Tax Price", simply rearrange the equation:

Pre-Tax Price = Total (Tax Included)/1.075

If the "Total (Tax Included)" is recorded in field D15, and you want to calculate the "Pre-Tax Price" before tax in field D13, use the formula: D15/1.075.

The "Sales Tax" field can then be calculated as: D15 - D13.

When the Tax Rate Varies

If your tax rate isn’t fixed (for example, different states or products have different tax rates), avoid entering fixed values like 0.075 or 1.075 directly in the formula.

Instead, create a separate "Tax Rate" field and set it to decimal format for easier input.

For example, if the "Tax Rate" is stored in field B13, you can adjust your formulas as follows:

(1) Pre-Tax Price = Total (Tax Included)/(1 + B13)

(2) Sales Tax = Pre-Tax Price*B13

Other Related Applications

This calculation method can also be applied in similar cases:

(1) To calculate the original price from a discounted price and discount rate.

(2) To determine the required cost when the target profit margin and quoted price are known.

If your sheet uses formulas that calculate values from a tax-inclusive total, make sure all users understand the correct data entry process to avoid overwriting calculated fields. To prevent errors, you can also set these formula fields as Read Only.