How to Create a Tally VAT Invoice Format in Excel (with Easy Steps)

In real life, we need to create a tally VAT in invoice format. Using Microsoft Excel, you can easily create a tally VAT in invoice format. This is a time-saving task also. Today, in this article, we’ll learn four quick and suitable steps to create a tally VAT in invoice format in Excel effectively with appropriate illustrations.

Introduction to Value Added Tax

VAT stands for Value-Added Tax. It is one type of indirect tax levied on products at every point of supply chain i.e. beginning from the raw materials and all the way to the retail price. The VAT that has been paid by the user is calculated by deducting the product’s cost from any already taxed costs of the product’s constituents. You can calculate VAT in two ways. One formula is to calculate VAT from Initial Price or the Net Amount, and the other formula will help you to calculate VAT from the Price with VAT or the Gross Amount.

Arithmetic Formula for Calculating VAT from Initial Price:

VAT Amount = IP * VAT %

Where,

VAT Amount = The amount of VAT in currency units.

IP = Initial price or net amount in currency units.

VAT % = The percentage of VAT imposed on the product or service.

Arithmetic Formula for Calculating VAT from Price with VAT:

VAT Amount= (FP/(1+VAT%))*VAT%

Where,

VAT Amount = The amount of VAT in currency units.

FP = Price with VAT or the Gross Amount in currency units..

VAT % = The percentage of VAT imposed on the product or service.

How to Create a Tally VAT Invoice Format in Excel: 4 Easy Steps

Let’s say, we have a dataset that contains information about the invoice of the Armani group. From our dataset, we will create a tally VAT invoice. We can easily create a tally VAT invoice in Excel by using the SUM, and ROUND functions. Here’s an overview of the dataset for our today’s task.

Step 1: Create Tally VAT Invoice Header with Company Name

In this portion, we will enter company details such as Company Logo, Name, Address, Contact Details, eTIN No., VAT Reg. No. etc.

Step 2: Add Customer Details to Tally VAT Invoice

Now, in this portion, we will input the customer’s details. For instance, Customer Name, Contact details, eTIN No., and VAT Reg. No. Besides you have to add a unique customer ID in this section.

Step 3: Description of Items Provided to Customer

We will create the product description that has been offered to the customer. A brief description is given below:

Step 4: Calculate Tally VAT Invoice Format in Excel

Last but not the least, in this step, we will calculate the VAT by using the SUM, and ROUND functions. Let’s follow the instructions below to create a tally VAT invoice!

First of all, select a cell After that, write down the SUM function in that cell. The SUM function is,

=SUM(F23:F27)

Hence, simply press Enter on your keyboard. As a result, you will get the Subtotal of the products which is the output of the SUM function. The Subtotal is $24,654.25.

After calculating the Subtotal, we will calculate the VAT. to do that, type the below mathematical formula.

=F28*E29

While pressing the ENTER on your keyboard, you will get $3,698.14 as the output of the mathematical formula which is our desired VAT.

Now, we will calculate the payable amount. To do that, write down the below functions in cell F30. The functions are,

=ROUND(SUM(F28:F29),0)

Formula Breakdown:

The SUM function, sum up the value of the range F28:F29.
SUM(F28:F29) is the number for the ROUND function, and 0 is the num_digits which returns an integer value.

Hence, press Enter on your keyboard. As a result, you will get the Payable Amount of the products which is the output of the ROUND and SUM functions. The total Payable Amount is $28,352.00.

Most of the time companies add the signature of the authorized person to the invoice. Often, companies include concluding messages and brief contact details of the company. If you want you can add that too in your tally VAT invoice format.

Things to Remember

👉 #N/A! error arises when the formula or a function in the formula fails to find the referenced data.

👉 #DIV/0! error happens when a value is divided by zero(0) or the cell reference is blank.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Tally VAT Invoice Format.xlsx

Conclusion

I hope all of the suitable methods mentioned above to create a tally VAT in invoice format will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.