How to calculate VAT in excel. How to do VAT in excel? How to calculate VAT including

— calculation of VAT, calculation of the amount with VAT, the amount without VAT, allocation of VAT from the total amount.

1. Formula for calculating VAT.

Let the amount S be known. We need to calculate the VAT amount, which is 18 percent of S.

VAT= S * 18 / 100.

Example. The amount is 10,000 rubles, VAT will be.

VAT = 10000 * 18 / 100 = 1800.

style="center">

2. Formula for calculating the amount including VAT.

Let the amount S be known. We need to calculate Sн - the amount including VAT. Using , we get:

Sн= S + S * 18/100.

Sн= S * (1 + 18 / 100) = S * 1.18.

In the Russian Federation, VAT accounting is carried out using a special document invoices. and the rules for filling it out are strictly regulated.

Example. The amount is 10,000 rubles, the amount including VAT will be.

Sn = 10000 * 1.18 = 11800

3. Formula for calculating the amount excluding VAT.

Let the amount including VAT Sn be known. We need to calculate S - the amount excluding VAT. Let's first write down the formula for calculating the amount with VAT, and then we will get from it the formula for calculating the amount without VAT.

Let us denote N = 18 / 100, then:

Sn = S * (1 + N)

Then

S = Sn / (1 + N) = Sn / (1+0.18) =
Sn / 1.18

Example. The amount including VAT is 1180 rubles, VAT 18 percent. The amount excluding VAT is:

A 2 = 1180 / (1 + 0.18) = 1000.

4. Formula for calculating (allocating) VAT from the total amount.

Let the amount including VAT Sn be known. We need to calculate the amount of VAT.

VAT = Sn - S

We use the formula for calculating the amount excluding VAT.

VAT = Sn - Sn / 1.18 =
Sn * (1 - 1/1.18)

Example. The amount including VAT is 11800. The VAT amount will be:

VAT= 11800 * (1 - 1 / 1.18) = 1800.

(1-1/1.18) is approximately equal to 0.152542, then
VAT= 11800 * 0.152542 = 1799.9956.

VAT calculation using Excel

When purchasing goods, you often have to determine the amount of VAT included in the price of the goods. To do this, it is enough to create a small table, which, when changing the data on the cost of a product, will instantly calculate the amount of VAT. The table shown in Fig. 2.31, conditionally divided into two parts: input area A1:B4, which contains the current VAT rate as a percentage (cell IN 1) and the cost of the goods including VAT (cell VZ), as well as the output area A5:B8.

In the output area, the specific amount of VAT in the cost of the product and the cost of the product without VAT are determined.

The VAT amount is calculated using the formula:

Calculating the cost of goods without VAT can be done in two ways. In the first case, almost the same formula is used as for determining the amount of VAT:

Please note that all formulas for calculations use the rounding function to two decimal places (to kopecks).

The second method is to subtract the pre-calculated VAT amount from the cost of the goods:

When subtracting, as a rule, it is not advisable to use rounding if the values ​​used have been previously rounded.

Of course, accountants usually do not have any difficulties with calculating VAT - they do not even use any special formulas, because for them it is a normal routine work, organized to the point of automation. However, the question of how to correctly make calculations often worries other company employees, such as sales managers, marketers, and even those in management positions. It is logical that managers should be well versed in financial documents and understand where this or that figure comes from, otherwise they can easily get into trouble during important negotiations.

Let's discuss what VAT is, where it is reflected and how to calculate it in different ways depending on the situation. For clarity, let's look at a few examples.

What is VAT?

The abbreviation stands for as follows: VAT is a value added tax; it is indirect, and with its help the state budget receives part of the cost of goods (works, services) created in the production process (performance of works, provision of services).

As a rule, people who are far from economics are interested in: who added value and how? The answer lies on the surface - every participant in the production cycle. That is, when purchasing a product, the buyer pays tax to the seller, but deductions to the budget occur even earlier than the final sale, since each link in the chain pays tax on that part of the cost of the product that was added by it.

In Russia, value added tax was introduced in 1992, and today in our country, according to Article 164 of the Tax Code, there are three rates:

• 18% – considered basic and used by default in most cases;
• 10% – used in the sale of some printed publications, children's goods, certain categories of medical equipment, as well as a number of food products (bread, milk, sugar, some meat products, etc.);
• 0% – applies to exported products and some specific things, such as licenses, duties and letter stamps.

Important: all the nuances relating to the calculation and payment of value added tax are covered in detail in Chapter 21 of the Tax Code of the Russian Federation. To keep your finger on the pulse and keep up to date with the latest information on debt obligations to the budget, it is best to access. Of course, this will take some time, and data on debts is sometimes required immediately - then you can use services that offer search.

How to calculate VAT?

Most business entities operating on the general taxation system are payers of value added tax. To avoid problems with fiscal authorities, it is very important to clearly understand how it is calculated. If you do not understand the calculation mechanism, then it is quite easy to find yourself in a situation where a completed transaction for the sale of any product will not bring profit due to the fact that the employee responsible for pricing simply forgot about the markup. It is necessary to have a good understanding of how to correctly make calculations in order to set adequate prices for the goods sold.

Important: As a rule, in practice, when calculating tax, two situations may arise:

• There is an amount that does not take into account the tax surcharge. How to calculate VAT on the amount?
• There is an amount including tax. How can I find out its value directly, that is, VAT included? And the amount without surcharge?

Thus, it is very important to decide: what do you have initially and what do you want to get as a result of the calculations? Let's look at how to calculate tax in different cases.

Calculation of VAT on the amount

Here, as a rule, no one has any problems, because even a junior school student can calculate 18 or 10 percent of the amount.

The calculation is carried out according to the formula: VAT = Amount without VAT * Tax rate / 100. Since we are considering the option when the rate is 18 percent, the formula can be simplified: VAT = Amount without VAT * 18 / 100 = Amount without VAT * 0.18.

VAT calculation including

Calculating VAT also means determining the amount of tax already included in the existing figure. For example, they say this: the price of a product is 1000 rubles, including VAT. In order to calculate value added tax in this case, you need to use the formula.

VAT= Amount including VAT * Tax rate / (100 + Tax rate). Knowing that the tax rate is 18 percent, it is easy to write the formula differently: VAT = Amount including VAT * 18 / 118.

• Amount excluding VAT = Amount including VAT – VAT;
• Amount excluding VAT = Amount including VAT / 1.18.

Of course, any of the formulas will lead to the same result; you just need to focus on how it is more convenient for you to make calculations.

Calculation of the amount including VAT

Why is it usually required and what formula to use? Let's imagine that there is a price excluding VAT, resulting from an increase in the cost of the product by the desired profit. Before selling, it is necessary to calculate the amount including VAT, which will be the final price for the buyer.

This can be done in several ways, for example, using one of two formulas:

• Amount with VAT = Amount without VAT + VAT;
• Amount with VAT = Amount without VAT * 1.18.

Examples of VAT calculation and allocation

Guided by the above formulas, let's look at examples of how to correctly allocate value added tax if it is already included in the price, and charge it when the price is indicated without a surcharge.

Example No. 1

Dream House LLC is engaged in trade in building materials. An order has been received for a batch of red bricks in the amount of 1200 pieces. The price of one brick is 9 rubles excluding VAT. Tax rate – 18%.

• Cost of a batch of bricks excluding VAT = 9 * 1200 = 10800 rubles;
• VAT on the amount = 10800 * 0.18 = 1944 rubles;
• Cost of a batch of bricks including VAT = 10800 + 1944 = 12744 rubles.

Advice: Usually, a faster calculation method is one that is performed in one step, so you can not calculate VAT separately, but immediately multiply the amount without VAT by 1.18. Then the cost of the batch including VAT = 10800 * 1.18 = 12744 rubles.

Example No. 2

Landysh LLC sells garden products. The buyer wants to purchase one greenhouse, its price list is 35,400 rubles including VAT. Let's calculate the amount of tax (at a rate of 18%) and the price of the greenhouse without a surcharge, referring to the formulas indicated above:

• VAT = 35400 * 18 / 118 = 5400 rubles;
• The price of the greenhouse without VAT = 35,400 – 5,400 = 30,000 rubles.

You can carry out the calculation in another way - simply divide the existing figure by 1.18, and then subtract the resulting number from the original one to find out the tax amount. Then:

• Price without VAT = 35,400 / 1.18 = 30,000 rubles;
• VAT = 35400 – 30000 = 5400 rubles.

To check the correctness of the calculation Let's charge tax on the price received: 30,000 * 1.18 = 35,400.

In what document is the allowance indicated?

Usually, when selling a product, performing work or providing services, a package of documents is collected, which includes an invoice and a delivery note (or a certificate of completion, etc.). The documents that are drawn up upon sale indicate the following information:

• Cost without VAT;
• The amount of tax charged to the buyer;
• Total payable including VAT.

Important: companies applying the general taxation system need to carefully monitor the documentation: if a product is purchased for the purpose of subsequent resale, and there is no invoice with allocated VAT, then it is impossible to obtain a tax deduction for this operation under the law. Therefore, before concluding a deal, it would be useful to assess the reliability of a potential partner - you can find information about a legal entity or individual entrepreneur using.

Let's sum it up

Organizations located on OSNO work with VAT and are payers of this tax, unless otherwise provided by law for any reason. That is, tax must be included in the final cost of the product offered to the consumer. Calculating it is not difficult if you have minimal knowledge of mathematics. In fact, there is no point in memorizing formulas; it is enough to have logical thinking and a calculator. It is important not to get confused and understand the difference between the price without VAT and the price with the surcharge included. Companies that plan to receive a tax deduction at the end of the reporting period should take a responsible approach to collecting documents - all transactions must be accompanied by invoices.

Of course, sometimes situations arise when some points are overlooked, resulting in the formation of tax debts, which can be found out online if you monitor the Federal Tax Service service. Today, such information is easy to find out even for individuals - it can actually be discovered in a couple of minutes; Legal entities and individual entrepreneurs most often use their TIN to track outstanding obligations.

Value added tax (VAT) is one of the key taxes for removing part of the cost of a product. VAT payers are: enterprises, foreign companies, individual entrepreneurs, non-profit organizations when carrying out commercial activities. The tax rate is 18%, 10% (for special goods: food, children's, etc.) and 0% (for exported goods).

VAT calculator online

The VAT calculator allows you to calculate and allocate VAT: calculate the amount with and without VAT. In the table below, enter the values ​​of the amount on which you want to allocate VAT or charge it.

In order to calculate VAT, you can use the online calculator presented above. But if you don’t have an online calculator for calculating VAT at hand, you can easily calculate it yourself using a simple formula.

How to independently calculate VAT from the amount: formula

This is the simplest case of calculation, when there is an amount excluding VAT, you need to calculate the tax on it. The general calculation formula is as follows:

Example #1 of calculating VAT from the amount

We have a cost excluding VAT = 100,000 rubles. Rate = 10%. Let's calculate the tax.

N = 100,000 * 10 / 100 = 10,000 rub.

Price in cash = 100,000 + 10,000 = 110,000 rub.

Example #2 of calculating VAT from the amount

We have the same 100,000 rubles. Rate = 18%. Let's calculate the tax.

H = 100,000 * 18 / 100 = 18,000

Price in cash = 100,000 + 18,000 = 118,000

That is, at a rate of 10%, to calculate VAT you need to multiply the amount by 0.1; at a rate of 18%, you need to multiply the amount by 0.18.

How to calculate VAT from the amount: formula

This case is a little more complicated. There is an amount with tax, you need to find its value, that is, separate VAT from the price or cost.

Example #3 calculation of VAT on the amount

We have a cost with tax = 100,000 rubles. Rate 10%.

N = 100,000 * 10 / 110 = 9090.91 rub.

Cost without cash. = 100,000 – 9090.91 = 90909.09 rub.

Example #4 calculating VAT on the amount

We have the same 100,000 rubles. The rate is 18%.

N = 100,000 * 18 /118 = 15,254.24 rubles.

Cost without cash. = 100,000 – 15254.24 = 84745.76 rubles.

Download the calculator for calculating VAT in Excel

The mechanism for calculating value added tax sometimes raises many questions, but everything is quite simple. All you need to do is use a suitable formula or an online calculator. If you want to have a VAT calculator on hand, you can download it below in Excel format. The figure below shows the table view for calculating VAT.

Value added tax (VAT) is an indirect tax, according to which companies transfer to the budget part of the added value of goods and services. Law No. 303-FZ dated 03.08.18 amends the VAT rate specified in clause 3 of Art. 164 Tax Code of the Russian Federation. From January 1, 2019, the tax rate increased from 18% to 20%.

The increase in VAT did not affect all taxpayers: there is a preferential list of goods, their sellers will continue to work at a rate of 10%.

Now in Russia the VAT rate can be 0%, 10%, or 20%, depending on the type of company’s activity.

The amount of VAT to the budget is equal to the amount of tax calculated at a rate of 10%, 20% of the tax base, minus the amount of tax deductions.

VAT calculation is done for each rate separately. VAT paid on the purchase of goods, performance of work and provision of services that are not subject to taxation is not deductible. The price of goods and services is determined by companies based on VAT.

Allocation of VAT

Our free online calculator will help you easily highlight and calculate the VAT tax of 18% (effective in 2018), 20% or 10% - you will get the result in numbers, not in words. To calculate the tax or calculate the allocated amount, use the following formula:

Amount: (1+ VAT rate: 100)

How to do this on a calculator:

Step 1. If the VAT percentage for your type of activity is 10, then you need to divide the number by 1.1, if the VAT is 20%, then by 1.2.

Step 2. You need to subtract the initial amount from the resulting value. Remember: the accrual cannot be negative, so we remove the minus.

Step 3. If you do not need an ultra-precise calculation, you can round the result to the nearest kopeck.

If you are not a value added tax payer (you work without VAT), then you do not need to allocate VAT.

VAT calculation

You can calculate VAT using a simple formula or use our calculator for free:

• The amount is multiplied by 1.2, the result is the amount including VAT.
• If you multiply the amount by 0.2, you can find the VAT amount separately.

Example of VAT calculation

At a rate of 18%, valid until 2019

Romashka LLC sells bricks. The price per piece including VAT is 10 rubles, a batch of 1,500 pieces has been ordered. Let's calculate VAT and the amount including VAT 18%.

Batch cost: 10 × 1,500 = 15,000 rubles

Allocated VAT: 15,000: 1.18 × 0.18 = 2,288 rubles (rounded)

Amount without VAT: 15,000 − 2,288 = 12,712 rubles

15,000: 1.18 = 12,712 rubles

Cost without VAT = 12,712 rubles

VAT 18% = 2,288 rubles

Cost including VAT = 15,000 rubles.

At a rate of 20%, effective from 2019

Limonnik LLC sells knitted mittens. One pair, including VAT, costs 300 rubles. The counterparty ordered a batch of mittens of 1,000 pieces. We will calculate VAT and the amount including VAT 20%.

Cost of the batch including VAT: 300 × 1,000 = 300,000 rubles

Dedicated VAT: 300,000: 1.2 × 0.2 = 50,000 rubles

Amount without VAT: 300,000 − 50,000 = 250,000 rubles

300,000: 1.2 = 250,000 rubles

In settlement documents and invoices we indicate:

Cost without VAT = 250,000 rubles

VAT 20% = 50,000 rubles

Cost including VAT = 300,000 rubles.

Did you like the VAT calculator? Try other services from Kontur.Accounting

Kontur.Accounting is a simple and convenient web service for maintaining records, calculating salaries, sending reports via the Internet and paying taxes and fees. The service is suitable for individual entrepreneurs and small businesses.