VAT calculation in Excel. VAT calculation using Excel

One of the many indicators that accountants, tax officials and private entrepreneurs have to deal with is value added tax. Therefore, the issue of calculating it, as well as calculating other indicators related to it, becomes relevant for them. You can make this calculation for a single amount using a regular calculator. But, if you need to calculate VAT for many monetary values, then doing this with one calculator will be very problematic. In addition, the adding machine is not always convenient to use.

Fortunately, in Excel you can significantly speed up the calculation of the required results for the source data that is entered into the table. Let's figure out how to do this.

Calculation procedure

Before proceeding directly to the calculation, let's find out what the specified tax payment is. Value added tax is an indirect tax paid by sellers of goods and services on the amount of products sold. But the real payers are the buyers, since the amount of the tax payment is already included in the cost of the purchased product or service.

In the Russian Federation, the tax rate is currently set at 18%, but in other countries of the world it may differ. For example, in Austria, Great Britain, Ukraine and Belarus it is 20%, in Germany – 19%, in Hungary – 27%, in Kazakhstan – 12%. But in our calculations we will use the tax rate that is relevant for Russia. However, by simply changing the interest rate, the calculation algorithms that will be given below can be used for any other country in the world where this type of taxation is applied.

In this regard, accountants, tax officials and entrepreneurs in various cases are faced with the following main tasks:

Calculation of VAT itself from the cost without tax;
Calculation of VAT from the cost in which the tax is already included;
Calculation of the amount excluding VAT from the cost in which the tax is already included;
Calculation of the amount including VAT from the cost without tax.

We will continue to perform these calculations in Excel.

Method 1: calculating VAT from the tax base

First of all, let's find out how to calculate VAT from the tax base. It's pretty simple. To complete this task, you need to multiply the taxable base by the tax rate, which in Russia is 18%, or by the number 0.18. Thus, we have the formula:

“VAT” = “Taxation base” x 18%

For Excel, the calculation formula will take the following form

Number*0.18

Naturally, the “Number” multiplier is a numerical expression of this very tax base or a link to the cell in which this indicator is located. Let's try to apply this knowledge in practice for a specific table. It consists of three columns. The first contains the known values of the tax base. The second will contain the desired values, which we should calculate. The third column will contain the amount of the product along with the tax amount. As you might guess, it can be calculated by adding the data in the first and second columns.

Select the first cell of the column with the required data. We put the “=” sign in it, and then click on the cell in the same row from the “Tax base” column. As you can see, its address is immediately entered into the element where we perform the calculation. After this, set the Excel multiplication sign (*) in the calculation cell. Next, enter the value “18%” or “0.18” from the keyboard. In the end, the formula from this example took the following form:
In your case, it will be exactly the same except for the first multiplier. Instead of “A3” there may be other coordinates, depending on where the user has placed the data that contains the tax base.
After this, to display the finished result in a cell, click on the Enter key on the keyboard. The required calculations will be immediately performed by the program.
As you can see, the result is output with four decimal places. But, as you know, the monetary unit ruble can only have two decimal places (kopecks). Thus, for our result to be correct, we need to round the value to two decimal places. Let's do this by formatting cells. In order not to return to this issue later, let’s format all the cells intended for placing monetary values at once.
Select a table range intended to contain numeric values. Right-click. The context menu launches. Select the “Format Cells” item in it.
After this, the formatting window is launched. Move to the “Number” tab if it was opened in any other tab. In the “Number Formats” parameter block, set the switch to the “Numeric” position. Next, check that the number “2” is in the “Number of decimal places” field on the right side of the window. This value should be the default, but just in case, it’s worth checking and changing it if any number other than 2 is displayed there. Next, click on the “OK” button at the bottom of the window.

You can also include a currency format instead of a number format. In this case, the numbers will also be displayed with two decimal places. To do this, move the switch in the “Number Formats” parameter block to the “Currency” position. As in the previous case, make sure that the number “2” is in the “Number of decimal places” field. We also pay attention to the fact that the ruble symbol is set in the “Designation” field, unless, of course, you deliberately intend to work with another currency. After that, click on the “OK” button.
If you use the number format option, all numbers are converted to values with two decimal places.

When using the currency format, exactly the same conversion will occur, but the symbol of the selected currency will be added to the values.
But, so far we have calculated the value of value added tax only for one value of the tax base. Now we need to do this for all other amounts. Of course, you can enter a formula using the same analogy as we did the first time, but calculations in Excel differ from calculations on a regular calculator in that the program can significantly speed up the execution of similar actions. To do this, apply copying using a fill handle.
Place the cursor in the lower right corner of the sheet element that already contains the formula. In this case, the cursor should transform into a small cross. This is the fill marker. Hold down the left mouse button and drag it to the very bottom of the table.
As you can see, after performing this action, the required value will be calculated for absolutely all values of the tax base that are available in our table. Thus, we calculated the indicator for seven monetary values much faster than it would have been done on a calculator or, especially, manually on a piece of paper.
Now we will need to calculate the total cost along with the tax amount. To do this, select the first empty element in the “Amount with VAT” column. We put the “=” sign, click on the first cell of the “Taxation base” column, set the “+” sign, and then click on the first cell of the “VAT” column. In our case, the element for displaying the result displayed the following expression:
But, of course, in each specific case the cell addresses may differ. Therefore, when performing a similar task, you will need to substitute your own coordinates of the corresponding elements of the sheet.
Next, click on the Enter button on the keyboard to get the finished calculation result. Thus, the value of the cost along with the tax for the first value is calculated.
In order to calculate the amount with value added tax and for other values, we use the fill marker, as we already did for the previous calculation.

Thus, we have calculated the required values for seven values of the tax base. It would take much longer on a calculator.

Lesson: How to change cell format in Excel

Method 2: tax calculation based on the amount including VAT

But there are cases when, for tax reporting, the amount of VAT should be calculated from the amount in which this tax is already included. Then the calculation formula will look like this:

“VAT” = “Amount including VAT” / 118% x 18%

Let's see how this calculation can be done using Excel tools. In this program, the calculation formula will look like this:

Number/118%*18%

The “Number” argument is the known value of the cost of the product along with the tax.

For an example of calculation, let's take the same table. Only now the “Amount with VAT” column will be filled in, and we will have to calculate the values of the “VAT” and “Taxation Base” columns. We will assume that the table cells are already formatted in a currency or number format with two decimal places, so we will not repeat this procedure.

Place the cursor in the first cell of the column with the required data. We enter the formula there (=number/118%*18%) in the same way that was used in the previous method. That is, after the sign we put a link to the cell in which the corresponding value of the cost of the product with tax is located, and then from the keyboard we add the expression “/118%*18%” without quotes. In our case, we got the following entry:
In the specified entry, depending on the specific case and the location of the input data on the Excel sheet, only the cell reference can change.
After this, click on the Enter button. The result has been calculated. Next, as in the previous method, using the fill marker, copy the formula to other cells in the column. As you can see, all the required values have been calculated.
Now we need to calculate the amount without tax payment, that is, the tax base. Unlike the previous method, this indicator is calculated not using addition, but using subtraction. To do this, you need to subtract the amount of the tax itself from the total amount.
So, we place the cursor in the first cell of the “Taxation base” column. After the “=” sign, we subtract the data from the first cell of the “Amount with VAT” column from the value that is in the first element of the “VAT” column. In our specific example, we get the following expression:

To display the result, do not forget to press the Enter key.
After this, in the usual way, using a fill marker, copy the link to other column elements.

Method 3: calculating the tax amount from the tax base

“Amount including VAT” = “Taxation base” + “Taxation base” x 18%

You can simplify the formula:

“Amount including VAT” = “Taxation base” x 118%

In Excel it will look like this:

Number*118%

The "Number" argument represents the taxable base.

For example, let’s take the same table, only without the “VAT” column, since it will not be needed for this calculation. Known values will be located in the “Taxation Base” column, and the required values will be located in the “Amount with VAT” column.

Select the first cell of the column with the required data. We put there the “=” sign and a link to the first cell of the “Taxation base” column. After that, enter the expression without quotes “*118%”. In our specific case, the expression was obtained:
To display the total on the sheet, click on the Enter button.
After this, we apply the fill marker and copy the previously entered formula to the entire range of the column with the calculated indicators.

Thus, the sum of the cost of the goods, including tax, was calculated for all values.

Method 4: calculating the tax base from the amount with tax

It is much less common to calculate the tax base from the cost with the tax included in it. However, such a calculation is not uncommon, so we will also consider it.

The formula for calculating the tax base from the cost, which already includes tax, is as follows:

“Taxation base” = “Amount including VAT” / 118%

In Excel, this formula will take the following form:

Number/118%

The dividend “Number” is the value of the product including tax.

For calculations, we will use exactly the same table as in the previous method, only this time the known data will be located in the “Amount with VAT” column, and the calculated data will be located in the “Taxation Base” column.

We select the first element of the “Taxation base” column. After the “=” sign, enter the coordinates of the first cell of the other column. After this, enter the expression “/118%”. To carry out the calculation and display the result on the monitor, click on the Enter key. After this, the first cost value without tax will be calculated.
In order to perform calculations in the remaining elements of the column, as in previous cases, we will use the fill marker.

Now we have received a table in which the cost of goods without tax is calculated for seven items at once.

Lesson: Working with formulas in Excel

As you can see, knowing the basics of calculating value added tax and related indicators, coping with the task of calculating them in Excel is quite simple. Actually, the calculation algorithm itself, in fact, is not much different from calculations on a regular calculator. But, performing an operation in the specified spreadsheet processor has one undeniable advantage over a calculator. It lies in the fact that calculating hundreds of values will not take much more time than calculating one indicator. In Excel, literally within a minute, a user can calculate taxes on hundreds of items, using such a useful tool as a fill marker, while calculating a similar amount of data on a simple calculator can take hours. In addition, in Excel you can record the calculation by saving it as a separate file.

We are glad that we were able to help you solve the problem.

Ask your question in the comments, describing the essence of the problem in detail. Our specialists will try to answer as quickly as possible.

Did this article help you?

How to calculate VAT in Excel? First, let's figure out what VAT (value added tax) is, what documents are needed for this, and most importantly, what value added is. And then we’ll figure out how to calculate VAT in Excel.

What is VAT?

As Wikipedia tells us:

VAT (value added tax) is an indirect tax, a form of withdrawal to the state budget of part of the cost of a product, work or service, which is created at all stages of the process of production of goods, work and services and is contributed to the budget as it is sold.

The VAT tax rate in Russia is 18% and is applied by default if the transaction is not included in the list of taxable transactions at a rate of 10% or in the list with a rate of 0%.

I would like to immediately say who exactly pays this tax, so that no one has any misconceptions. It is not stores, not manufacturers, or even importers who pay VAT. You and I pay it - ordinary consumers, end users. Any indirect tax is paid by the end consumer and nothing else.

You can call it VAT, a fee on vehicles over 12 tons (Plato), a bribe or something else, the essence does not change - the end consumer in the store will pay.

Did you buy cottage cheese for 118 rubles kg? It was you who paid 18 rubles to the state, not at this very moment, but even earlier (the producer of milk, cottage cheese, the carrier, and so on paid for you), but this money is yours!

Therefore, in addition to the fact that you pay huge taxes and contributions as an employee or entrepreneur, you also pay VAT in stores. Don’t be lazy to ask your contractor (aka the state) for the provision of legitimate public goods about the quality of the services provided.

In fact, VAT is a creative way, as I said, of collecting taxes before paying the tax itself.

Let's figure out what this added value is, how the tax on it is calculated and what documents are needed.

Step-by-step calculation

You bought cottage cheese, its cost is 118 rubles. Of these, 18 rubles. (18%) VAT. But the entire chain of suppliers and sellers paid them even earlier.

The seller bought cottage cheese from a farmer for 100 rubles and added 18 rubles to this price. How much should he pay? 18% of what was added - from 18 rubles.

VAT on 100 rubles has already been paid by previous suppliers (farmer, transporter, cow feed manufacturer, etc.). In order for the seller to be able to prove to the state that he does not need to pay VAT on all 100 rubles (previous), he takes an invoice (received) from the supplier. The main function of the Invoice is to prove that there is no need to pay VAT on previous funds, i.e. make a tax deduction.

Invoice

The invoice received is correct - excellent, you can subtract the VAT of previous participants (from 100 rubles) and the seller pays only from 18 rubles (added value).

Is it worse if the SF is incorrect or if the supplier printed and gave you the SF, but did not give it to the state (deceived you)? Then pay VAT on all 118 rubles, or the tax office will quickly add additional taxes during the audit.

Now do you understand why accountants chase after invoices (closing documents) and treat them so carefully? 😉

This is if it's rude.

By the way, did you know that our favorite invoice document is a completely optional printed form? For example, I didn’t know about this for a long time! But an invoice is very mandatory for VAT payers!

What can be considered added value for the company?

Added value for goods and services will consist of 4 main points.

Company profit
Salary + taxes and fees for salary
Depreciation
Other intangible expenses (you can say costs excluding VAT)

Those. if you take the sum of these items and multiply by 0.18, you will get the company's VAT.

How to calculate VAT in Excel?

I often get out of the situation by deducing a solution for myself from formulas.

‘Cost without VAT’ * 1.18 = ‘Cost with VAT’

I have attached 3 calculation options for you.

Known ‘Cost without VAT’, you need to calculate ‘VAT’ and ‘Amount with VAT’

‘VAT’ = ‘Cost without VAT’ * 0.18

‘Cost with VAT’ = ‘Cost without VAT’ * 1.18

Known ‘Cost with VAT’, you need to calculate ‘Amount without VAT’ and ‘VAT’

‘Cost without VAT’ = ‘Cost with VAT’ / 1.18

‘VAT’ = ‘Cost including VAT’ / 1.18 * 0.18

Known ‘VAT’, you need to calculate ‘Amount without VAT’ and ‘Amount with VAT’

‘Cost without VAT’ = ‘VAT’ / 0.18

‘Cost including VAT’ = ‘VAT’ / 0.18 * 1.18

More about VAT

In the wake of a high probability increase in VAT in the Russian Federation, I read a little about indirect taxes in developed countries. As I already wrote, indirect taxes, incl. VAT has a greater impact on the final buyer, so an increase in VAT to 20% in the Russian Federation will primarily lead to an increase in prices in stores. What is the situation with VAT in the world's largest economies?

Friends, write your questions! I'll try to answer.

(Visited 36,512 times, 18 visits today)

Let's learn different ways calculating value added tax in Excel and study the formulas in detail, including learning how to allocate, add and subtract VAT.

Greetings to all, dear readers of the TutorExcel.Ru blog.

Before calculating VAT in Excel, let’s remember what it is and what kind of tax it is.
As the name suggests, value added tax is an indirect tax charged on the surplus (added) value of a product (or service) in accordance with approved tax rates (according to the Tax Code of the Russian Federation).

In Russia, tax rates for calculating VAT are of 3 types depending on the type of product: 18% (applied by default, standard rate for most items), 10% And 0% (reduced rate for certain categories of goods).

If you need to calculate VAT not for Russia, but, for example, for Ukraine, Belarus or Kazakhstan, then the following tax rates apply in these countries (data for other countries can be found here):

Ukraine. 20% (standard), 0% (reduced);
Belarus. 20% (standard), 10%, 0.5% or 0% (reduced);
Kazakhstan. 12% (standard).

In general, the rate may vary depending on the country, but the meaning of tax payments remains the same.

Therefore, in subsequent examples we will consider calculations only for the Russian tax system, but if necessary, the formulas can be easily adjusted (by changing the rate) for any other country.

Now let's move on to consider the different ways to calculate VAT in Excel.

Method 1. Manually entering formulas

Let's think about what options there are for calculating the tax deduction formula.
We have 3 options - we can either calculate the tax separately, or add or subtract.
As a result, after choosing a calculation option, the resulting result can be manually entered into the input field using a formula.

Let's look at each of the three options in more detail.

Allocation of VAT

Suppose we have the sum S(excluding tax) and we want to allocate the amount of value added tax for it.
To allocate the tax, we need to multiply this amount by the tax rate, i.e. S*18%:

Let's consider another option, albeit in the amount S tax already included.
In this case, since in the initial value S contains an 18% rate, dividing the amount by 118% and multiplying by 18% we get a dedicated value added tax, i.e. (S/118%) * 18%:

Adding VAT

The initial conditions are the same for the sum S We will calculate the full amount including tax. To add VAT, you need to add the allocated tax (18%) to the amount (100%), in other words, multiply it by 118%, i.e. S*118%:

VAT deduction

Initial conditions - we know the amount S, but with the condition that it already takes into account the tax.
Since the final amount is 118% (as in the example above), to subtract VAT and get the amount excluding tax, we need to divide it by 118%, i.e. S/118%:

As you can see, we have 3 options for the calculation formula, depending on what exactly needs to be calculated and how.

Method 2: Custom Function

Therefore, as another approach to calculating a formula, we will write a custom function that would combine all calculation options and automate the calculations to reduce the likelihood of errors, for example, when typing a formula.

As we have already found out, the calculation depends on 3 parameters: original amount(for which we will make calculations), bet size(18%, 10% or 0%) and type of calculation(selection, subtraction or addition).
Therefore, we will set the specified parameters as function arguments.

Go to the Visual Basic editor window (keyboard shortcut Alt + F11), add a new module (via the menu Insert -> Module) and then copy our function code there:

Public Function VAT(ByVal Amount As Double, Optional ByVal Rate As Double = 0.18, Optional ByVal TypeVAT As Integer = 0)

If TypeVAT = 0 Then

VAT = Amount * Rate

ElseIf TypeVAT = 1 Then

VAT = Amount / (1 + Rate) * Rate

ElseIf TypeVAT = 2 Then

VAT = Amount * (1 + Rate)

VAT = Amount / (1 + Rate)

Let's take a closer look at the function arguments:

Amount(required argument) - the amount for which the tax is calculated;
Rate(optional argument) - tax rate (default value 18%);
TypeVAT(optional argument) - type of tax calculation, 0 - selection (option when in the variable Amount VAT is not taken into account), 1 - selection (option when in the variable Amount VAT has already been taken into account), 2 - addition, any other number - subtraction (default value 0).

A few words about the parameters of function arguments.

Parameter ByVal allows you to access a variable (in this case SUM) by value, i.e. after calling the function, the value of the variable will remain unchanged (even if the function changes their value).
To make it possible to skip some variables when calling a function, we add a parameter Optional before the variable name, making it optional.

Let's move on to the next step.
The function has been written, now we just have to learn how to use it. Let's return to Excel and calculate the tax for the examples already discussed.
We can call a function either directly by typing it in the formula bar, or by finding it in the category User defined via the function wizard:

As a result, we find that the results of calculating the formula and applying the function coincided, as required to be shown.

Now let’s complicate the task a little and add the bet size as a variable parameter (for example, let’s take the bet size in different countries):

Download an example with calculations.

Thank you for your attention!
If you have thoughts or questions, share them in the comments.

Good luck to you and see you soon on the pages of the TutorExcel.Ru blog!

Master (1528), closed 6 years ago

example: 215 rubles with VAT, you need to find without VAT 18% if you count on a calculator it comes out 215-18% = 176.30
if in Excel then 215-18% = 214.83. how so?

How to calculate VAT 18%? As a rule, this question arises not among accountants, but among other company employees - for example, sales specialists or managers. We will try to answer it in such a way that the accountant does not have problems explaining how is VAT calculated? .

How to determine the amount of VAT (calculation)

When selling goods, works, services, or transferring property rights, VAT is charged to the buyer in addition to the sales price (Clause 1, Article 168 of the Tax Code of the Russian Federation).

In practice, when calculating VAT, 2 questions are possible:

If there is an amount without VAT - how to calculate VAT from the amount ?
If there is an amount including VAT - how to calculate VAT including .

How to calculate VAT from the amount

How to calculate VAT on the amount? It's not difficult: you just need to mathematically calculate the percentage. The calculation is made using the formula:

VAT = NB * Nst / 100,

where NB is the tax base (that is, the amount excluding VAT), Nst is the VAT rate (18 or 10 percent).

How to calculate VAT including

VAT = C / 1.10 * 0.10 - if the tax rate is 10%,

where C is the amount including VAT.

This calculation does not mean applying estimated rates of 18/118 or 10/110 - it is simply a way of separating the tax from the total. More details about settlement rates - "".

How to calculate the amount including VAT

C = NB * 1.10 - at a rate of 10%,

where NB is the tax base, that is, the amount without tax.

Example of VAT calculation

Using the above formulas, we will analyze, how to calculate VAT correctly(in excess of the amount, including), with examples.

LLC "X" sells a batch of concrete blocks in the amount of 100 thousand pieces. at a price of 55 rub. a piece. The VAT rate is 18% (clause 3 of Article 164 of the Tax Code of the Russian Federation), tax is not included in the price. How to calculate VAT 18% and the final cost of the lot including tax?

First, we determine the cost of the batch without VAT (tax base):

55 rub. * 100000 pcs. = 5,500,000 rub.

5,500,000 * 18/100 = 990,000 rub.

5500000 + 990000 = 6490000 rub.

Now you can determine the total amount immediately, without first calculating the tax:

5500000 * 1.18 = 6490000 rub.

cost without VAT - 5,500,000 rubles,
VAT 18% - 990,000 rub.,
total including VAT - 6,490,000 rubles.

According to the price list of X LLC, the price of a curbstone including VAT is 236 rubles. for 1 piece The buyer ordered a batch of 10 thousand pieces. Let's calculate VAT 18%.

First, let's determine the final cost of the batch:

236 rub. * 10000 pcs. = 2360000 rub.

2360000 / 1.18 * 0.18 = 360000 rub.

The amount remaining excluding VAT:

2360000 - 360000 = 2000000 rub.

It can also be found without prior allocation of tax:

2360000 / 1.18 = 2000000 rub.

You can check the correctness of the calculation by charging VAT on the received value without tax:

2000000 * 18/100 = 360000 rub.

Accordingly, the following must be indicated in settlement documents and invoices:

cost without VAT - 2,000,000 rubles,
VAT 18% - 360,000 rub.,
total including VAT - 2,360,000 rubles.

15000 / 1.18 = 12712 rubles

VAT 18% = 2288 rubles

Value added tax (VAT) is an indirect tax that requires companies to transfer part of the added value of goods and services to the state budget. In Russia, the VAT rate can be either 10% or 18%, depending on the type of activity of the company.

The payment of VAT to the budget is determined as the difference between the amount of tax calculated at a rate of 10% or 18% of the tax base and 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.

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

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 18%, then by 1.18.

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.

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

The amount is multiplied by 1.18, the result is the amount including VAT.
If you multiply the amount by 0.18, you will be able to find the VAT amount separately.

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

Party cost = 10 * 1500 = 15,000 rubles

Allocated VAT = 15000 / 1.18 * 0.18 = 2288 rubles (rounded)

Amount without VAT = 15000 - 2288 = 12712 rubles

15000 / 1.18 = 12712 rubles

In settlement documents and invoices we indicate:

Cost without VAT = 12712 rubles

VAT 18% = 2288 rubles

Cost including VAT = 15,000 rubles.

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

The payment of VAT to the budget is determined as the difference between the amount of tax calculated at a rate of 10% or 18% of the tax base and the amount of tax deductions.

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 18%, then by 1.18.

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.

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

The amount is multiplied by 1.18, the result is the amount including VAT.
If you multiply the amount by 0.18, you will be able to find the VAT amount separately.

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

Party cost = 10 * 1500 = 15,000 rubles

Allocated VAT = 15000 / 1.18 * 0.18 = 2288 rubles (rounded)

Amount without VAT = 15000 - 2288 = 12712 rubles

15000 / 1.18 = 12712 rubles

In settlement documents and invoices we indicate:

Cost without VAT = 12712 rubles

VAT 18% = 2288 rubles

Cost including VAT = 15,000 rubles.

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. 1, conditionally divided into two parts: input area A1:B4. which contains the current VAT rate as a percentage (cell B1) and the cost of goods with VAT (cell B3), 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.

Rice. 1. Table for calculating the amount of VAT (with numerical data)

The VAT amount is calculated using the formula: =ROUND(Acquisition cost × (VAT rate. (100% + VAT rate));2). which when entered into a cell looks like this: =ROUND(B3*(B1/(1+B1));2) .

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: =ROUND(Acquisition cost x (100%. (100% + VAT rate));2) or in tabular form: =ROUND(B3*(1/ (1+B1));2). Please note that all formulas for calculations use the rounding function to two decimal places (to kopecks).

Rice. 2. Table for calculating the amount of VAT (with formulas)

The second method is to subtract the pre-calculated VAT amount from the cost of the goods: = Cost of goods – VAT amount or in tabular form: = B3–B5.

When subtracting, as a rule, it is not advisable to use rounding if the values used have been previously rounded. Tables for calculating the amount of VAT (with numerical values and formulas) are shown in Fig. 1 and 2.

www.khl.ru. ""/"KHL", -. /""/"KHL""". www.khl.ru.

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.

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.

Master (1528), closed 6 years ago

example: 215 rubles with VAT, you need to find without VAT 18% if you count on a calculator it comes out 215-18% = 176.30
if in Excel then 215-18% = 214.83. how so?

How to calculate VAT 18% ? As a rule, this question arises not among accountants, but among other company employees - for example, sales specialists or managers. We will try to answer it in such a way that the accountant does not have problems explaining how is VAT calculated? .

How to determine the amount of VAT (calculation)

When selling goods, works, services, or transferring property rights, VAT is charged to the buyer in addition to the sales price (Clause 1, Article 168 of the Tax Code of the Russian Federation).

In practice, when calculating VAT, 2 questions are possible:

If there is an amount without VAT - how to calculate VAT from the amount ?
If there is an amount including VAT - how to calculate VAT including .

How to calculate VAT from the amount

How to calculate VAT on the amount? It's not difficult: you just need to mathematically calculate the percentage. The calculation is made using the formula:

VAT = NB * Nst / 100,

where NB is the tax base (that is, the amount excluding VAT), Nst is the VAT rate (18 or 10 percent).

How to calculate VAT including

VAT = C / 1.10 * 0.10 - if the tax rate is 10%,

where C is the amount including VAT.

This calculation does not mean applying estimated rates of 18/118 or 10/110 - it is simply a way of separating the tax from the total. More details about settlement rates - "".

How to calculate the amount including VAT

C = NB * 1.10 - at a rate of 10%,

where NB is the tax base, that is, the amount without tax.

Example of VAT calculation

Using the above formulas, we will analyze, how to calculate VAT correctly(in excess of the amount, including), with examples.

First, we determine the cost of the batch without VAT (tax base):

55 rub. * 100000 pcs. = 5,500,000 rub.

5,500,000 * 18/100 = 990,000 rub.

5500000 + 990000 = 6490000 rub.

Now you can determine the total amount immediately, without first calculating the tax:

5500000 * 1.18 = 6490000 rub.

cost without VAT - 5,500,000 rubles,
VAT 18% - 990,000 rub.,
total including VAT - 6,490,000 rubles.

According to the price list of X LLC, the price of a curbstone including VAT is 236 rubles. for 1 piece The buyer ordered a batch of 10 thousand pieces. Let's calculate VAT 18%.

First, let's determine the final cost of the batch:

236 rub. * 10000 pcs. = 2360000 rub.

2360000 / 1.18 * 0.18 = 360000 rub.

The amount remaining excluding VAT:

2360000 - 360000 = 2000000 rub.

It can also be found without prior allocation of tax:

2360000 / 1.18 = 2000000 rub.

You can check the correctness of the calculation by charging VAT on the received value without tax:

2000000 * 18/100 = 360000 rub.

Accordingly, the following must be indicated in settlement documents and invoices:

cost without VAT - 2,000,000 rubles,
VAT 18% - 360,000 rub.,
total including VAT - 2,360,000 rubles.

15000 / 1.18 = 12712 rubles

VAT 18% = 2288 rubles

The payment of VAT to the budget is determined as the difference between the amount of tax calculated at a rate of 10% or 18% of the tax base and the amount of tax deductions.

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 18%, then by 1.18.

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.

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

The amount is multiplied by 1.18, the result is the amount including VAT.
If you multiply the amount by 0.18, you will be able to find the VAT amount separately.

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

Party cost = 10 * 1500 = 15,000 rubles

Allocated VAT = 15000 / 1.18 * 0.18 = 2288 rubles (rounded)

Amount without VAT = 15000 - 2288 = 12712 rubles

15000 / 1.18 = 12712 rubles

In settlement documents and invoices we indicate:

Cost without VAT = 12712 rubles

VAT 18% = 2288 rubles

Cost including VAT = 15,000 rubles.

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.

The payment of VAT to the budget is determined as the difference between the amount of tax calculated at a rate of 10% or 18% of the tax base and the amount of tax deductions.

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 18%, then by 1.18.

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.

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

The amount is multiplied by 1.18, the result is the amount including VAT.
If you multiply the amount by 0.18, you will be able to find the VAT amount separately.

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

Party cost = 10 * 1500 = 15,000 rubles

Allocated VAT = 15000 / 1.18 * 0.18 = 2288 rubles (rounded)

Amount without VAT = 15000 - 2288 = 12712 rubles

15000 / 1.18 = 12712 rubles

In settlement documents and invoices we indicate:

Cost without VAT = 12712 rubles

VAT 18% = 2288 rubles

Cost including VAT = 15,000 rubles.

Rice. 1. Table for calculating the amount of VAT (with numerical data)

The VAT amount is calculated using the formula: =ROUND(Acquisition cost × (VAT rate. (100% + VAT rate));2). which when entered into a cell looks like this: =ROUND(B3*(B1/(1+B1));2) .

Rice. 2. Table for calculating the amount of VAT (with formulas)

The second method is to subtract the pre-calculated VAT amount from the cost of the goods: = Cost of goods – VAT amount or in tabular form: = B3–B5.

www.khl.ru. ""/"KHL", -. /""/"KHL""". www.khl.ru.

Each commercial organization operating on a common taxation system is required to correctly calculate VAT and transfer it to the state budget. The question of the rules for carrying out calculations is relevant for specialists in various fields of activity: accountants, economists, sales managers and executives. Errors made when calculating tax may result in a violation of current legislation, which will result in penalties.

The tax calculation algorithm consists of three basic actions:

You need to find out what tax rate is applicable in a particular case. In Russia, three rates are used: 0%, 10% and 18%. To make your choice, you need to study Article 164 of the Tax Code.
Calculate the tax base. This is the original cost of goods and services sold, increased by the amount of excise taxes (if we are talking about excisable products).
Use the VAT formula, which will be discussed below.

Practice shows that carrying out calculations is a simple stage of work. To avoid mistakes, just master the formula and use a calculator or calculation program. It can be more difficult to decide on the choice of rate and base.

The tax amount obtained through mathematical calculations must be included in the invoice. The document is transferred to the customer or buyer, depending on the method of sale of goods or services. Also, the amount should be highlighted as a separate line in the invoice issued to the counterparty.

If payments are made in foreign currency, the accountant is still required to calculate VAT in rubles. According to current legislation, the tax is denominated in the currency of the Russian Federation and in no other.

How to calculate VAT: determine the appropriate rate

According to the provisions of the Tax Code (Article 163), three rates apply on the territory of the Russian Federation:

0% – applies to goods exported for sale outside the territory of Russia (export), as well as logistics services provided as part of foreign trade activities.
10% – used for book products, food products, goods intended for children or healthcare institutions.
18% – applies to all cases not covered by the first two points.

How to calculate VAT: basic scheme

According to the current legislation (Article 168 of the Tax Code of the Russian Federation), value added tax is imposed on counterparties in addition to the sale price of goods and services.

In practice, employees of commercial structures are faced with one of two questions:

How to determine the tax that should be added to the price of a product?
How to “clear” the amount with VAT and find out the amount of tax included in it?

Let's look at the formula for calculating VAT in each case.

To determine the total invoice amount issued to the buyer, you must use the formula:

Tax amount = Product cost excluding VAT* Rate/100%

To “clear” the cost of goods that include indirect tax, you must use the formula:

Tax amount = Invoice cost of goods with VAT*0.18/1.18 – if the rate is 18%;

Tax amount = Cost of goods invoiced with VAT*0.1/1.1 – for goods and services taxed at a rate of 10%.

If the accountant does not need to calculate the amount of tax, he can determine the price of goods presented to clients using the following formula for calculating VAT:

Final amount = Original product price * 1.18 – for a rate of 18%;
Final amount = Original product price * 1.1 – for a 10% rate.

All of the above calculations can be done using a calculator, Excel or special online applications designed to automate accounting work.

VAT calculation 2016: example

Examples from real practice will help you better understand the procedure for performing calculations.

Example No. 1

Stroymaterialy LLC sells concrete blocks, the cost per unit is 50 rubles. It entered into a deal with Stroilit LLC for the supply of a batch of 50,000 blocks. How do you determine what the tax is and how much you should invoice the buyer for?

According to the provisions of Article 164 of the Tax Code of the Russian Federation, goods are taxed at a rate of 18%. The accountant’s task is to use the rule to calculate VAT of 18% of the amount. To do this, we will carry out the following calculations:

Let's find the total price of a batch of concrete blocks - the tax base:

NB = 50 *50,000 = 2,500,000 rub.

Let's determine the tax amount from this amount:

VAT = 2,500,000 *0.18 = 450,000 rub.

Let's add the two resulting numbers to find out the total bill amount:

Amount = 2,500,000 + 450,000 = 2,950,000 rubles.

If the accountant does not need the amount of tax included in the invoice, he can use the simple VAT formula above and combine the three steps into one:

Invoice amount = 2,500,000 *1.18 = 2,950,000 rub.

In the invoice and other documents, the accountant of Stroymaterialy LLC is required to indicate the following information:

Price without tax – RUB 2,500,000.
The amount of VAT is 450,000 rubles.
Total cost – 2,950,000 rubles.

Example No. 2

Home Interiors LLC sells wallpaper at a price of 745 rubles per roll. It entered into an agreement with IP Ivanov I.I. for the supply of 1,000 rolls. How do you determine how much to invoice the buyer?

To do this, the accountant needs to remember a simple algorithm for how VAT is calculated.

Let's determine the cost of goods sold without tax

NB = 745 *1,000 = 745,000 rub.

Let's calculate the amount of VAT

Tax = 745,000 *0.18 = 134,100 rub.

Add the two values to get the bill amount

Amount = 745,000 + 134,100 = 879,100 rubles.

Or use a quick calculation formula:

Invoice amount = 745,000 * 1.18 = 879,100 rubles.

Based on the above example of VAT calculation, the accountant must indicate the following data in the documents:

Price without tax – 745,000 rubles.
The amount of VAT is 134,100 rubles.
Total cost – 879,100 rubles.

Every practicing accountant must know how to correctly calculate VAT. Failure to comply with current regulations and computational errors can lead to problems with the law. For those who pay tax late or do not transfer it in full, there are penalties and fines.

If you find an error, please highlight a piece of text and click Ctrl+Enter.