If you are creating an invoice and need to account for taxes, usually you just need to multiply the subtotal by the percentage due for taxes. However, it gets trickier when the tax amount is already included within the invoice total and you need to work out what the amount relating to tax is. This is important if you need to determine how much in taxes you need to claim on an expense or how much you need to collect if you’re the seller. Below, I’ll go over a sample invoice calculation to show how can determine the tax amount whether it is included in the total or not.
Calculating taxes on an invoice
Let’s start with the basic calculation. This is how you might normally determine the taxes on an invoice and the total invoice value:
The calculation is straightforward as what you do is just take the subtotal, multiply that by the tax rate, and add that back to the subtotal. Another way is to just take the subtotal and multiply it by a factor of 1 + the tax rate. In this case, it would $100 x 1.10. But let’s pretend we don’t know the subtotal and just know that the invoice total is $110.00 and the tax rate is 10%. In order to calculate the pre-tax amount, we need to do the steps in the opposite order. To prove this out, let’s use a bit of algebra:
$100 + ($100 x 10%) = $110
This can be simplified as follows:
$100 (1 + 10%) = $110
Now let’s solve for $100 which I will assign a variable of ‘y’ to:
y (1 + 10%) = $110
To solve for y, all we need to do is move the factor of 1 + the tax rate and divide $110 by that:
y = $110/(1 + 10%)
Taking $110 and dividing by 1.1 will give us a value of $100. And so what our end result comes out to is essentially this:
invoice total / (1 + tax rate) = pre-tax amount
To calculate the tax, all that’s needed then is to take the total and subtract the pre-tax amount.
Now that the logic is set up, let’s convert this into an Excel formula:
Similar to how multiplying by a factor of the pre-tax amount by 1.1 (when the tax rate is 10%) would get you to the invoice total, dividing the total by 1.1 would get you to the amount before taxes. If the tax rate were 5%, then you would use 1.05, etc.
If you liked this post on How to Calculate the Tax Amount When it Is Included in the Total, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.