DEV Community

Ashish Srivastava
Ashish Srivastava

Posted on

Sales tax / VAT on an Excel invoice — rates, inclusive prices, and rounding

#excel #spreadsheets #productivity #tutorial

Sales tax, VAT and similar taxes all work the same way in a spreadsheet: a percentage applied to an amount. What differs is the rate, whether prices include the tax, and how you round. This guide shows a simple, generic tax % column for an Excel invoice template and the logic behind it, using plain formulas that work in older Excel versions too.

Note: which rate applies, whether you must charge tax and what an invoice must show depend on where you operate. This is about spreadsheet logic only. It is not tax, legal or accounting advice; check the rules with a qualified adviser.

1. Keep one default rate in Settings

Make a Settings sheet and type your default tax rate in one cell, say Settings!B7 = 8%. Type 0% if you don't charge tax. Every formula points at this cell, so you never type the rate twice.

2. The invoice log

On an Invoices sheet, one row per invoice: B = date, C = customer, D = amount before tax, E = tax % (optional, leave blank to use the default).

Rate used (H2):

=IF($B2="","",IF(E2="",Settings!$B$7,E2))
Enter fullscreen mode Exit fullscreen mode

A blank means "use the default". A typed 0% means "no tax on this one", so the two cases stay different.

Tax (F2) and total (G2):

F2: =IF($B2="","",ROUND(D2*H2,2))
G2: =IF($B2="","",D2+F2)
Enter fullscreen mode Exit fullscreen mode

Quick check: 1,000 at 8% gives tax 80 and a total of 1,080. The same 1,000 with 5% typed in column E gives 50 and 1,050, and with 0% gives 0 and 1,000.

3. When your prices already include tax

Some businesses quote one price that includes tax. Then you work backwards from the price. In a spare area, put the tax-inclusive price in P2, the rate in Q2, and:

Net amount (R2): =ROUND(P2/(1+Q2),2)
Tax part (S2):   =P2-R2
Enter fullscreen mode Exit fullscreen mode

With 1,080 at 8%, the net is 1,000 and the tax part is 80. Use the tax part as a difference, not as a second rounded calculation, so net and tax always add back to the total.

4. Mixed rates on one invoice

If different items carry different rates, put the rate on each line instead of the invoice. On an Items list add a tax % column (blank = default). The line formula picks the item's rate if there is one and the default if not:

=IFERROR(IF(INDEX(Items!$C:$C,MATCH($A10,Items!$A:$A,0))="",Settings!$B$7,INDEX(Items!$C:$C,MATCH($A10,Items!$A:$A,0))),Settings!$B$7)
Enter fullscreen mode Exit fullscreen mode

5. Round each line, then add

Rounding can make totals differ by a cent. Take three items at 0.50 with 5% tax:

  • Round each line: 0.03 + 0.03 + 0.03 = 0.09
  • Add first, then round: 1.50 x 5% = 0.075, which rounds to 0.08

Neither is "wrong". What matters is picking one method and using it everywhere. Rounding each line means the tax column always adds up to what's printed on each line, which is why the formulas above round per line. If you need a particular rounding method, ask your adviser.

6. A tax summary by rate

If you use more than one rate, list the rates in a small table (say M2:M5) and use SUMIFS on the "rate used" column:

Amount at this rate: =SUMIFS($D$2:$D$500,$H$2:$H$500,M2)
Tax at this rate:    =SUMIFS($F$2:$F$500,$H$2:$H$500,M2)
Enter fullscreen mode Exit fullscreen mode

Add a check that the table's total equals =SUM(F2:F500). If it doesn't, a row uses a rate that isn't in your list.

7. Why every formula starts with IF($B2="","",…)

You'll fill formulas down 500 rows ahead of time. If they aren't wrapped, empty rows show 0 or errors, and your totals and charts get messy. Using the date cell as the "is this row in use?" check keeps unused rows blank.

8. Turn the log into a receivables list

Add columns for due date (I = date + payment terms) and amount received (J), then:

Balance (K):      =IF($B2="","",G2-N(J2))
Days overdue (L): =IF($B2="","",IF(AND(K2>0,TODAY()>I2),TODAY()-I2,0))
Enter fullscreen mode Exit fullscreen mode

A per-customer SUMIFS on balance and days overdue then gives 0-30 / 31-60 / 61-90 / 90+ buckets, which is your follow-up list for the week.

Common mistakes

  • Hard-coding the rate inside formulas. Keep it in a cell.
  • Mixing amounts before tax and after tax in one column.
  • Leaving the tax cell blank when you mean zero. Type 0%.
  • Using a rate from an old invoice for a new one. Check your rates when they change.

Written with AI tools. This is general spreadsheet guidance for Excel (.xlsx), not tax, legal or accounting advice.

Practical spreadsheet templates for small businesses, made with AI assistance.

Top comments (0)