Subtract VAT from the amount formula in Excel. VAT calculation using Excel. Calculation from total

Please note, the VAT rate has changed from 18 to 20%. The formula for calculating VAT 20 percent can be found in new article link:

Value added tax is an indirect tax levied on the sale of goods, works and services. IN Russian Federation The current rate is 18%. For some types there is a preferential rate of 10%.

How to calculate VAT 18 on the amount

Correct calculation of the rate is necessary when the cost is indicated without VAT and you need to add the indicator to the final value. You will also need this information to include in your tax return.

The calculation operation is carried out using several methods. Let's look at each of them in more detail.

Self-calculation

To determine the indicator yourself, you must use the following formula:

BdN x StN / 100, where:

BdN – taxable goods or services;

StN – tax rate for a certain category.

Below is the formula with an example.

Example No. 1: Equator LLC provided the client with goods worth 7,500 rubles. The rate is 18%. Having carried out a mathematical operation, we get: 7500 x 18 / 100 = 1,350 rubles.

We see a typical definition of VAT from above.

The company now issues an invoice to the client. It will include the basic cost and the added indicator.

The total price will be: 7,500 + 1,350 = 8,850 rubles.

Online calculator

Accrue

Select

Price without VAT:	0 ₽
VAT 18%:	0 ₽
Value with VAT:	0 ₽

VAT amount in words:

Calculation from total

To separate VAT from the total price, you can use the following formula:

Total cost x 18 /118, assuming the rate is 18 percent.

Example No. 2: Everest LLC pays the invoice issued to the company for work performed. The total amount to be paid is RUB 25,500. The regular rate of 18% applies to the services indicated in the invoice: 25,500 x 18 / 118 = RUB 3,889. 83 kop.

The calculated amount will be indicated in the tax return.

Quick calculation of the cost of a product or service using a coefficient

Often workers financial sector special coefficients are used.

For a clear explanation, we use the input data from example No. 1:

7,500 x 1.18 = 8,850 rub.

The reverse operation will look like this (example No. 2):

25,500 x 0.152542 = 3,889.82

The coefficients are not a true reflection of the meaning of the tax, but they simplify the process of calculating it. The coefficient number can only be used at the current tax rate. Any change in tax legislation makes the indicators irrelevant.

Another drawback is incorrect rounding of numbers to the second ten after the decimal point. Therefore, adherents of accurate calculations give preference to formulas that provide the correct result.

We come across value added tax (or VAT) all the time. Any product we purchase has a price, which consists of the cost of the product and VAT (this can be seen on any receipt).

VAT is one of the most confusing taxes for many. First you need to understand what added value is: any thing or work performed by human labor has its own value - it is this value that is called added value.

A tax is charged for the creation of any unit of goods. The tax does not depend on whether the item is sold or not, whether you made a profit or not. It is calculated only from the cost of the goods produced. Therefore, VAT can be calculated based on the cost of the goods.

Quick navigation through the article

We calculate VAT

By law, the tax is 18% of the cost of a product or service. Therefore, you can calculate it quite simply: divide the cost of the product by 100, and then multiply by 18 - in the end you will get the VAT amount. Thus, the buyer pays 118% of its value for the product.

We calculate the cost without VAT

Often the buyer only knows the amount with VAT already charged. To calculate the amount excluding VAT, you need to divide the total amount by 1.18. If you need to find out the VAT itself, then divide the total amount by 118 and multiply by 18. This will be the VAT price.

However, such a calculation will not show you the actual cost of the goods, since the buyer only sees the final cost.

As a rule, a product is produced in parts, and its component parts are produced by different manufacturers, and each of them charges its own VAT on the produced part of the product. Therefore, the final amount includes VAT on all components. Calculating the true amount excluding VAT is quite difficult.

VAT on imported goods

VAT is also charged on imported goods, and the tax is levied not on the value of the goods that you indicated, but on the value given in a special directory. Therefore, be careful when buying cheap goods abroad. Find out how much VAT will be charged when crossing the border.

How to calculate VAT 20%? 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 will not have problems explaining how VAT is calculated.

How to determine the amount of VAT (calculation)

When selling goods, works, services, transferring property rights VAT is charged to the buyer in addition to the sales price (clause 1 of 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 as well.

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 - tax base (that is, the amount without VAT), Nst - VAT rate: 20 percent ( until 01.01.2019 – 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.

Example of VAT calculation

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

Example 1

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 20% (clause 3 of Article 164 of the Tax Code of the Russian Federation), tax is not included in the price. How to calculate VAT 20% and the final cost of the shipment including tax?

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

55 rub. × 100,000 pcs. = 5,500,000 rub.

Let's calculate VAT from the amount:

5,500,000 × 20/100 = 1,100,000 rub.

Let's calculate the amount including VAT:

5,500,000 + 1,100,000 = 6,600,000 rub.

Or you can determine the total amount immediately, without preliminary calculation tax:

5,500,000 × 1.20 = 6,600,000 rub.

cost without VAT - 5,500,000 rubles,
VAT 20% - RUB 1,100,000,
total including VAT - 6,600,000 rubles.

Example 2

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

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

240 rub. × 10,000 pcs. = 2,400,000 rub.

We will calculate VAT including:

2,400,000 / 1.20 × 0.20 = 400,000 rub.

The amount remaining excluding VAT:

2,400,000 - 400,000 = 2,000,000 rub.

It can also be found without prior allocation of tax:

2,400,000 / 1.20 = 2,000,000 rub.

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

2,000,000 × 20/100 = 400,000 rub.

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

cost without VAT - 2,000,000 rubles,
VAT 20% - 400,000 rubles,
total including VAT - 2,400,000 rubles.

Results

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. 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 in this moment The tax rate is 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, just by changing interest rate, the calculation algorithms that will be given below can be used for any other country in the world where this type taxation.

In this regard, before accountants, employees tax services and entrepreneurs in various cases set 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 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 formula the calculation 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, currency unit The 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 do the calculation total amount 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 tax reporting you should calculate the amount of VAT from the amount in which this tax already turned on. 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. Let's assume that the table cells are already formatted in currency or number format with two decimal places, so repeat this procedure We won't carry it out.

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 you 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?

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 indirect tax, accrued on the surplus (added) value of a product (or service) in accordance with the approved tax rates (according to tax code RF).

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 point is tax payments remains the same.

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

Now let's move on to consider in various ways VAT calculation 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 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!

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.

Tax rate VAT 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 tax on Vehicle over 12 tons (Plato), bribe or something else, the essence does not change - the end consumer will pay in the store.

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 legal representation public goods, quality of 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 the VAT increase in the Russian Federation from high probability, 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 largest economies peace?

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

(Visited 28,856 times, 122 visits today)

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 VAT tax of 18% or 10% - you will get 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.

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.

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. in which are included current rate VAT 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 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 in the implementation 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.

To calculate VAT, you can use 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. General formula The calculation looks like this:

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.

Formula for allocating VAT 10%:	Formula for allocating VAT 18%:

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.