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

Get FREE Advanced Excel Exercises with Solutions!

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.


Download Practice Workbook

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


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.


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

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.

tally vat invoice format in excel


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.

Read More: How to Create a Tally GST Invoice Format in Excel (with Easy Steps)


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.

tally vat invoice format in excel

Read More: Tally Sales Invoice Format in Excel (Download Free Template)


Similar Readings


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:

Read More: Tally Bill Format in Excel (Create with 7 Easy Steps)


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.

tally vat invoice format in excel

  • 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:

tally vat invoice format in excel

  • 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 function The total Payable Amount is $28,352.00.

tally vat invoice format in excel

  • 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.

tally vat invoice format in excel

Read More: How to Make Tally Purchase Order Format in Excel (With Easy Steps)


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.


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.


Related Articles

Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo