Skip to main content
InvoicingTemplate.com main content
Sharing Is Caring! We do appreciate your efforts to let others know us!

Screenshots and Images

The printed version.

Multiple Tax Rates on One Invoice

Multiple Tax Rates on One Invoice (c5016)

c5016 now includes the customized invoice template for the latest version of Uniform Invoice Software. This is what you will see when you open multiple-tax-rates-on-one-invoice-uis.xlsx.

Multiple Tax Rates on One Invoice (UIS Edition)

Multiple Tax Rates on One Invoice - UIS edition

Download

License: Private Use (not for distribution or resale). See our Terms of Use.

You are safe to download the resources. We've added our digital signatures to the files.

DescriptionSizeDownload

Without database file. How to download and use

117 KBDownload

Download this if you have already installed UIS, but are looking for additional customized templates.

347 KBDownload

Download this if you want this design to be set as the default template by the installer program.

2.82 MBDownload

Detail

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, Uniform Invoice Software 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 Uniform Invoice Software 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.

Like other form designs here on InvoicingTemplate.com, Multiple Tax Types on One Invoice is free (offered to you at a price of 0.00USD). It works on Windows XP and all newer versions of Windows, and Excel 2007, 2010, 2013 or 2016.

Format and Specification

Template#c5016
NameMultiple Tax Rates on One Invoice
CategorySales Invoice Template
Release DateFriday, March 17, 2017
Format (XLS or XLSX).xlsx
Excel Version Excel 2003 / Excel 2007 / Excel 2010 / Excel 2013 / Excel 2016
Feature Gallery
Columns5
Lines12
Line Height (Points)19.50
Print Area$F$3:$M$41
Papaer Size / OrientationPortrait
Default Margins (Points)
Left22.68
Right22.68
Top45.35
Bottom45.35