Multiple Tax Rates on One Invoice

How can you tax items at different rates on the same invoice? This Excel sales invoice template offers a simple sample which allows you to define a tax group / type for each item, multiple tax rates is then applied according to the tax type of each item.

This template was originally published on office-kit.com as C5-016, and here on InvoicingTemplate.com there were already customized layouts created based on C5-016, such as c5034 Mixed Tax Rates (5 Columns), and c5035 Mixed Tax Rates in an Invoice (7 Columns). For more samples and formats related to taxing, visit tax invoice template.

One thing to note is that, since the formulas and relationship of fields on the form are deeply customized, the options on the "Taxes" tab of the "Settings" window may not work. So if you want to further customize such templates / forms, you may need to do that manually -- i.e. without using the options on the "Taxes" tab.

While not as complex and expensive as Quickbooks, Invoice Manager for Excel is able to handle invoices with mixed tax rates. This is a great sample that shows the advantages of Excel invoicing over Word invoicing, as you can use Excel formulas to search and calculate values on the form. With Microsoft Word, that could hardly be done without macro or VBA code.

Unlike c5034 and c5035, this template (c5016) has exactly the same layout as C5-016 on office-kit.com. Of course we made necessary modifications to make it meet the requirements and specifications of Invoice Manager for Excel and InvoicingTemplate.com.

The following description from office-kit.com still provides useful information for you to apply this template to your business.

The default invoice template does allow you to choose tax-type with 3 options: no-tax, one tax and two taxes, but this option is applied to all the items on the invoice form. This slightly-customized invoice template differs from the default invoice template in that it allows you, on the single invoice form, to have some products with no tax, some products with one tax (such as GST) and some products with two (such as GST and PST).

Setting up and using the Invoice Template

When using the customized template for the first time, you should firstly set up the tax names and rates, as detailed below.

1. Click the Settings button on the Invoice worksheet.
2. Go to the Taxes tab.
3. Modify the tax names and rates as you like. Don't modify any other options on the tab.
4. Save the invoice template by clicking Excel menu File -> Save in Excel 2003, or by clicking the Save button on the ribbon in Excel 2007 and later versions.

To test the template:

1. When creating new products, you click the Products button on the Invoice worksheet, and then click Add Product.
2. On the Custom Fields tab, specify the tax type for the current product: TAX0, TAX1, and TAX2.
   • TAX0 - tax exempt
   • TAX1 - only the first tax will be calculated for the product
   • TAX2 - Both the taxes will be calculated for the product
3. When creating a new invoice, you click the icon button to select a product. The tax type of the product is retrieved automatically.
4. The invoice template calculates taxes based on the types of the products.

Implementation Detail

This tax invoice template uses only one custom field, ProductType, which is added to both the Product and the Invoice Body database tables.

By adding the ProductType field to the Product database table, you don't have to reenter the tax type of the specified product each time it is picked on creating invoices, because the information is loaded automatically when you pick up the product. By adding the ProductType field to the Invoice Body database table, the tax type information is saved along with other product data, as well as customer data, when you click Save To DB, so that you can easily restore a previously saved invoice at a later time.

You specify the tax type of a product on the Product Edit window, with the value TAX0, TAX1 or TAX2. When the invoice template loads such a product into the worksheet, it uses the following formula to calculate the first tax:

=ROUND(SUMIF(oknProductType_1:oknProductType_12,"=TAX1",
oknLineTotal_1:oknLineTotal_12)*oknTax1Rate+
SUMIF(oknProductType_1:oknProductType_12,"=TAX2",
oknLineTotal_1:oknLineTotal_12)*oknTax1Rate,2)

The SUMIF() function tests the value of oknProductType_1, oknProductType_2, ... oknProductType_12 cells, and if a value of the cells is "TAX1" or "TAX2", the corresponding "oknLineTotal_?" cell value is added. The sum is then multiplied by oknTax1Rate to get the amount of the first tax.

The second tax formula works in a similar way.

=ROUND(SUMIF(oknProductType_1:oknProductType_12,"=TAX2",
oknLineTotal_1:oknLineTotal_12)*oknTax2Rate,2)

In the second tax formula, the SUMIF() function tests the value of oknProductType_1, oknProductType_2, ... oknProductType_12 cells, and if a value of the cells is "TAX2", the corresponding "oknLineTotal_?" cell value is added. The sum is then multiplied by oknTax2Rate to get the amount of the second tax.