Amount without VAT excel formula. Calculating VAT with Excel

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

Fortunately, in Excel you can significantly speed up the calculation of the required results for the original data that is entered in the table. Let's see how to do it.

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 products or services.

IN Russian Federation currently 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 the calculations we will use the tax rate relevant for Russia. However, simply by changing interest rate, those 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, the following main tasks are set for accountants, tax officials and entrepreneurs in various cases:

Calculation of VAT itself from the value without tax;
Calculation of VAT from the cost, in which the tax is already included;
Calculation of the amount without VAT from the cost in which the tax is already included;
Calculation of the amount with 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 accomplish this task, the taxable base must be multiplied by the tax rate, which in Russia is 18%, or by the number 0.18. Thus, we have a formula:

"VAT" = "Tax base" x 18%

For Excel, the calculation formula will take the following form

Number*0.18

Naturally, the multiplier "Number" is a numerical expression of this very tax base or a reference 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 goods along with the tax value. As you might guess, it can be calculated by adding the data of the first and second columns.

Select the first cell of the column with the required data. Put a sign on it «=» , and after that click on the cell in the same row from the column "The tax base". As you can see, its address is immediately entered into the element where we make the calculation. After that, in the calculation cell, set the Excel multiplication sign ( * ). Next, we drive in the value from the keyboard "eighteen%" or "0.18". 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 that, to display the finished result in a cell, click on the button Enter on keyboard. The required calculations will be immediately made by the program.

As you can see, the result is displayed with four decimal places. But, as you know, the monetary unit of the ruble can have only 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 with cell formatting. In order not to return to this issue later, we will format all the cells at once, designed to accommodate monetary values.
We select the range of the table intended for placing numeric values. We click with the right mouse button. The context menu is launched. Select an item in it. "Cell Format".

After that, the formatting window is launched. Moving to tab "Number" if it was open in any other tab. In the parameter block "Number Formats" set the switch to position "Numerical". Next, we check that in the right part of the window in the field "Number of Decimals" there was a figure "2". This value should be the default, but just in case, it is worth checking and changing it if any other number is displayed there, and not 2 . Next, click on the button OK at the bottom of the window.

You can also include currency instead of the number format. In this case, the numbers will also be displayed with two decimals. To do this, rearrange the switch in the parameter block "Number Formats" into position "Monetary". As in the previous case, we look to the field "Number of Decimals" there was a figure "2". We also pay attention to the fact that in the field "Designation" the symbol of the ruble was set, unless, of course, you are purposefully going to work with another currency. After that click on the button OK.

If you apply the number format option, all numbers are converted to two decimal places.

When using the currency format, exactly the same conversion will occur, but the symbol of the selected currency will also be added to the values.

But, so far we have calculated the value of value added tax for only one value of the tax base. Now we need to do this for all other sums as well. Of course, you can enter the formula in the same way as we did it 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 the same type of actions. To do this, apply copying using the fill handle.
Set the cursor to the lower right corner of the sheet element that already contains the formula. The cursor should change 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 be done on a calculator or, moreover, 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 column "Value with VAT". We put a sign «=» , click on the first cell of the column "Taxation base", set the sign «+» , and then click on the first cell of the column "VAT". In our case, the following expression was displayed in the element for displaying the result:
But, of course, in each specific case, the addresses of the cells may differ. Therefore, when performing a similar task, you will need to substitute your own coordinates for the corresponding sheet elements.

Next, click on the button Enter on the keyboard to get the finished calculation result. Thus, the cost value together with the tax for the first value is calculated.

In order to calculate the amount with value added tax for other values, we use the fill marker, as we did for the previous calculation.

Thus, we calculated the required values for seven values of the tax base. On a calculator, this would take much longer.

Method 2: tax calculation from the amount with VAT

But there are cases when for tax reporting it is necessary to calculate the amount of VAT from the amount in which this tax is already included. Then the calculation formula will look like this:

"VAT" = "Amount with 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%

As an argument "Number" the known value of the value of the commodity, together with the tax, appears.

For example, let's take the same table. Only now the column will be filled in it "Value with VAT", and the column values "VAT" And "Taxation base" we have to calculate. 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.

Method 3: calculating the tax amount from the tax base

"Amount with VAT" = "Tax base" + "Tax base" x 18%

You can simplify the formula:

"Amount with VAT" = "Tax base" x 118%

In Excel it will look like this:

Number*118%

Argument "Number" constitutes the tax base.

For example, let's take the same table, only without a column "VAT", since it is not needed in this calculation. Known values will be placed in a column "Taxation base", and the desired ones are in the column "Value with VAT".

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

Method 4: calculation of the tax base from the amount with tax

It is much less common to calculate the tax base from the value 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, where the tax is already included, is as follows:

"Tax base" = "Amount with VAT" / 118%

In Excel, this formula will look like this:

Number/118%

As a dividend "Number" is the value of the goods, including tax.

For calculations, we use exactly the same table as in the previous method, only this time the known data will be located in the column "Value with VAT", and the calculated ones are in the column "Taxation base".

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

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

Value Added Tax is one of the mandatory fees paid by legal entities And individual entrepreneurs operating under the general tax regime. In practice, its calculation raises many questions and inaccuracies. accountant different companies every now and then they ask themselves the question of how to allocate VAT and how to calculate it. For these operations, there is a clear algorithm that will not allow you to make a mistake.

Value added tax is an effective tool for regulating the country's economy and a significant source of income for the state budget. The prototype of this collection was first introduced in Saxony in the 18th century. The official “date of birth” of the VAT is considered to be the 1950s, when it was introduced in France on the recommendation of the economist Maurice Loret. The experience of this country was so successful that other European powers borrowed the idea.

The key difference between VAT and other fees is the opportunity. In other words, it is calculated only from the value added, that is, the delta in the price added during production or resale.

Expenses made by the organization reduce the amount of tax payable. For example, VAT on advances issued is recoverable. Income, on the contrary, increases the tax base.

All business entities operating in Russia under the OSNO are recognized as VAT payers:

legal entities;
importers and exporters.

Companies and individual entrepreneurs that have switched to special tax regimes are exempted from transferring tax: the simplified tax system, "imputation" and a patent, and those legal entities whose quarterly revenue was less than 2,000,000 rubles.

When does it become necessary to allocate VAT from the amount?

According to the current practice, invoices, bills and waybills always write down the "cleaned" value of the goods, the amount of tax and the total amount. VAT calculation involves a large number of operations:

purchase and sale of goods, works, services;
construction work;
import operations;
transfer of ownership of products that does not involve payment;
transfer of products for own needs, if this operation was not included in the income tax base.

If the contract, waybill or invoice indicates the cost without specifying VAT, the accountant has a reasonable question how to allocate VAT from the amount. It is impossible to do without this step: you need to know the amount of the transferred tax in order to correctly calculate the amount of tax payable to the budget based on the results of the quarter.

You can use different methods to perform calculations. There are online tax calculators that perform transactions in a fraction of a second. Many accounting staff prefer to use Excel or a regular calculator.

How to allocate VAT 18 from the amount: formula

To calculate the tax, it is enough to use simple logic. The amount already includes VAT, the rate of which is 18%. So the number written in the account is 118% of the original. To find the amount of tax, it is enough to divide the original number by 118% and multiply by 18%. A similar line of reasoning can be used for the 10% rate.

For those who do not have time for logical reasoning, there is a ready-made formula for allocating VAT. She looks like this:

Tax amount = Amount including VAT* 18/118 or

Tax amount = Amount including VAT* 10/110.

By substituting the values written in the account, you can find the required amounts in two steps.

Try ours, which allows you to make these calculations.

Separating VAT from the amount: practical examples

Company A, which purchases medical supplies, was billed for 100,000 rubles. The VAT rate is 10%. How to determine the amount of tax included in the invoice?

VAT \u003d 100,000 * 10/110 \u003d 9,090.91 rubles.

Company "B" bought building materials, the supplier invoiced her for 200,000. The tax rate is 18%. How to allocate VAT from the given amount?

VAT \u003d 200,000 * 18/118 \u003d 30,508.48 rubles.

To find the "cleaned" value of the goods, it is enough to subtract the found amount of tax from the invoice amount. In the first example, it is

100,000 - 9,090.91 \u003d 90,909.09 rubles.

In the second example:

200 000 – 30508,48 = 169491,52.

VAT allocation is a simple operation that every accountant can master. To carry out calculations, you can use online services or a regular calculator.

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

Master (1528), closed 6 years ago

Example: 215 rubles with VAT
if in excel then 215-18% = 214.83. how so?

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

How to determine the amount of VAT (calculation)

When selling goods, works, services, transferring property rights, VAT is presented to the buyer in addition to the selling 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 from the amount? It's easy: you just need to mathematically calculate the percentage. The calculation is made according to the formula:

VAT = NB * Nst / 100,

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

How to calculate VAT including

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

where C is the amount including VAT.

This calculation does not mean applying the estimated rates of 18/118 or 10/110 - it's just a way to separate tax from the total. Details about settlement rates - "".

How to calculate the amount with VAT

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

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

VAT calculation example

Using the above formulas, let's look at how to calculate VAT(in excess of the amount and including), on examples.

X LLC sells a batch of concrete blocks in the amount of 100 thousand pieces. at a price of 55 rubles. a piece. VAT rate - 18% (clause 3, 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 party, including tax?

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

55 rub. * 100000 pcs. = 5500000 rubles.

5500000 * 18/100 = 990000 rubles

5500000 + 990000 = 6490000 rubles

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

5500000 * 1.18 \u003d 6490000 rubles.

cost without VAT - 5500000 rubles,
VAT 18% - 990,000 rubles,
total with VAT - 6490000 rubles.

According to the price list of X LLC, the price of a curb stone, 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 total cost of the batch:

236 rub. * 10000 pcs. = 2360000 rubles.

2360000 / 1.18 * 0.18 \u003d 360000 rubles.

Remaining amount without VAT:

2360000 - 360000 \u003d 2000000 rubles.

It can also be found without prior tax allocation:

2360000 / 1.18 \u003d 2000000 rubles.

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

2000000 * 18/100 = 360000 rubles

Accordingly, in settlement documents and invoices, you must specify:

cost without VAT - 2,000,000 rubles,
VAT 18% - 360,000 rubles,
total with 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 a part of the value added of goods and services to the state budget. In Russia, the VAT rate can be either 10% or 18%, depending on the type of company's activity.

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

VAT is calculated for each of the rates 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 allocate and calculate 18% or 10% VAT - you will get the result in numerical value (not in words). To calculate the tax or calculate the allocated amount, the following formula is used:

How to do it on a calculator:

Step 1: If the percentage of VAT for your type of activity is 10, then you need to divide the number by 1.1, if 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 resulting result to the nearest cent.

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

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

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

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

Party cost \u003d 10 * 1500 \u003d 15,000 rubles

Allocated VAT \u003d 15000 / 1.18 * 0.18 \u003d 2288 rubles (rounded up)

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 with VAT = 15,000 rubles.

is a simple and convenient web service for keeping 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 the amount of VAT to the budget is determined as the difference between the amount of tax calculated at the rate of 10% or 18% of the tax base and the amount of tax deductions.

How to do it on a calculator:

Step 1: If the percentage of VAT for your type of activity is 10, then you need to divide the number by 1.1, if 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 resulting result to the nearest cent.

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

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

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

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

Party cost \u003d 10 * 1500 \u003d 15,000 rubles

Allocated VAT \u003d 15000 / 1.18 * 0.18 \u003d 2288 rubles (rounded up)

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 with VAT = 15,000 rubles.

When purchasing goods, it is often necessary to determine the amount of VAT included in the cost of the goods. To do this, it is enough to create a small table that, when data on the cost of goods changes, instantly calculates the amount of VAT. The table presented 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 unit amount of VAT in the cost of goods and the cost of goods 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) .

Calculation of 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: \u003d ROUND (Acquisition cost x (100%. (100% + VAT rate)); 2) or in tabular form: \u003d 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 consists in subtracting the pre-calculated amount of VAT from the cost of the goods: \u003d Cost of goods - VAT amount or in tabular form: \u003d B3 - B5.

When subtracting, as a rule, it is not advisable to use rounding if the values \u200b\u200bused in this case were 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

How to calculate VAT 20%? As a rule, this question arises not from accountants, but from other employees of the company - 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 VAT is calculated.

How to determine the amount of VAT (calculation)

When selling goods, works, services, transferring property rights, VAT is presented to the buyer in addition to the selling 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 as well.

How to calculate VAT from the amount

How to calculate VAT from the amount? It's easy: you just need to mathematically calculate the percentage. The calculation is made according to 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 \u003d C / 1.10 × 0.10 - if the tax rate is 10%,

Where: C is the amount including VAT.

VAT calculation example

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

Example 1

X LLC sells a batch of concrete blocks in the amount of 100 thousand pieces. at a price of 55 rubles. a piece. The VAT rate is 20% (clause 3, 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 batch including tax?

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

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

Calculate VAT from the amount:

5,500,000 × 20/100 = 1,100,000 rubles

Let's calculate the amount with VAT:

5,500,000 + 1,100,000 = 6,600,000 rubles

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

5,500,000 × 1.20 = 6,600,000 rubles

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

Example 2

According to the price list of X LLC, the price of a curb stone, 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 total cost of the batch:

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

We calculate VAT including:

2,400,000 / 1.20 × 0.20 \u003d 400,000 rubles.

Remaining amount without VAT:

2,400,000 - 400,000 \u003d 2,000,000 rubles.

It can also be found without prior tax allocation:

2,400,000 / 1.20 = 2,000,000 rubles

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

2,000,000 × 20/100 = 400,000 rubles

Accordingly, in settlement documents and invoices, you must specify:

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

Results

Fortunately, in Excel you can significantly speed up the calculation of the required results for the original data that is entered in the table. Let's see how to do it.

Calculation procedure

In the Russian Federation, the current tax rate is 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 the calculations we will use the tax rate relevant for Russia. However, by simply changing the interest rate, the calculation algorithms that will be given below can also be used for any other country in the world where this type of taxation is applied.

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

Calculation of VAT itself from the value without tax;
Calculation of VAT from the cost, in which the tax is already included;
Calculation of the amount without VAT from the cost in which the tax is already included;
Calculation of the amount with VAT from the cost without tax.

We will continue to perform these calculations in Excel.

Method 1: calculating VAT from the tax base

"VAT" = "Tax 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 reference 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 goods along with the tax value. As you might guess, it can be calculated by adding the data of 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 line from the “Tax base” column. As you can see, its address is immediately entered into the element where we make the calculation. After that, in the calculated cell, set the Excel multiplication sign (*). Next, we drive in 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 that, to display the finished result in a cell, click on the Enter key on the keyboard. The required calculations will be immediately made by the program.
As you can see, the result is displayed with four decimal places. But, as you know, the monetary unit of the ruble can have only 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 with cell formatting. In order not to return to this issue later, we will format all the cells at once, designed to accommodate monetary values.
We select the range of the table intended for placing numeric values. We click with the right mouse button. The context menu is launched. Select the "Format Cells" item in it.
After that, the formatting window is launched. We move to the "Number" tab, if it was open in any other tab. In the "Numeric formats" parameter block, set the switch to the "Numeric" position. Next, we 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 is worth checking and changing it if any other number is displayed there, and not 2. Next, click on the “OK” button at the bottom of the window.

You can also include currency instead of the number format. In this case, the numbers will also be displayed with two decimals. To do this, move the switch in the "Number formats" parameter block to the "Currency" position. As in the previous case, we look for the number "2" 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 are purposefully going to work with another currency. After that, click on the "OK" button.
If you apply the number format option, all numbers are converted to two decimal places.

When using the currency format, exactly the same conversion will occur, but the symbol of the selected currency will also be added to the values.
But, so far we have calculated the value of value added tax for only one value of the tax base. Now we need to do this for all other sums as well. Of course, you can enter the formula in the same way as we did it 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 the same type of actions. To do this, apply copying using the fill handle.
Set the cursor to the lower right corner of the sheet element that already contains the formula. The cursor should change 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 be done on a calculator or, moreover, 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 following expression was displayed in the element for displaying the result:
But, of course, in each specific case, the addresses of the cells may differ. Therefore, when performing a similar task, you will need to substitute your own coordinates for the corresponding sheet elements.
Next, click on the Enter button on the keyboard to get the finished calculation result. Thus, the cost value together with the tax for the first value is calculated.
In order to calculate the amount with value added tax for other values, we use the fill marker, as we did for the previous calculation.

Thus, we calculated the required values for seven values of the tax base. On a calculator, this would take much longer.

Lesson: How to change cell format in Excel

Method 2: tax calculation from the amount with VAT

But there are cases when for tax reporting it is necessary to calculate the amount of VAT from the amount in which this tax is already included. Then the calculation formula will look like this:

"VAT" = "Amount with 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 goods along with the tax.

For example, let's take the same table. Only now the column “Amount with VAT” will be filled in it, and we have to calculate the values of the columns “VAT” and “Taxation base”. 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.

We set the cursor to 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 goods 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 record, depending on the specific case and the location of the input data on the Excel sheet, only the reference to the cell can change.
After that, click on the Enter button. The result has been calculated. Further, as in the previous method, using the fill marker, we copy the formula to other cells in the column. As you can see, all required values are 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 set 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 of 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 that, in the usual way, using the fill marker, copy the link to other elements of the column.

Method 3: calculating the tax amount from the tax base

"Amount with VAT" = "Tax base" + "Tax base" x 18%

You can simplify the formula:

"Amount with VAT" = "Tax base" x 118%

In Excel it will look like this:

Number*118%

The "Number" argument is the taxable base.

For example, let's take the same table, only without the "VAT" column, since this calculation will not need it. Known values will be placed in the "Taxation base" column, and the required values - in the "Amount with VAT" column.

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

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

Method 4: calculation of the tax base from the amount with tax

It is much less common to calculate the tax base from the value 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, where the tax is already included, is as follows:

"Tax base" = "Amount with VAT" / 118%

In Excel, this formula will look like this:

Number/118%

As a divisible "Number" is the value of the value of the goods, taking into account the 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 ones - in the "Taxation base" column.

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

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

Lesson: Working with formulas in Excel

We are glad we were able to help you resolve the issue.

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

Did this article help you?

Learn different ways calculate value added tax in excel and we will 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 how this tax generally happens.
As the name implies, value added tax is an indirect tax charged on the surplus (added) value of a product (or service) in accordance with the approved tax rates (according to the tax code of the Russian Federation).

In Russia, there are 3 types of tax rates for calculating VAT, depending on the type of goods: 18% (applied by default, standard rate for most products), 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 the following examples, we will consider calculations only for the Russian taxation system, but if necessary, the formulas can be easily adjusted (by changing the rate) for any other country.

Now let's take a look at various ways VAT calculation in Excel.

Method 1. Manual entry of 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 result can be manually entered into the input field using a formula.

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

VAT allocation

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

Let's consider one more variant, let in the sum S already included tax.
In this case, since in the initial value S contains an 18% rate, dividing the amount by 118% and multiplying by 18% we get the allocated value added tax, i.e. (S / 118%) * 18%:

Addition of VAT

The initial conditions are the same, for the sum S calculate the total amount including tax. To add VAT, it is necessary 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 sum S, but with the condition that the tax is already taken into account in it.
Since the total amount is 118% (as in the example above), in order to subtract VAT and get the amount without 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 and how to calculate.

Method 2. Custom function

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

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

Go to the Visual Basic editor window (key combination 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(mandatory argument) - the amount for which the tax is calculated;
rate(optional argument) - size tax rate(default value 18%);
TypeVAT(optional argument) - type of tax calculation, 0 - selection (option when in a variable Amount VAT not included), 1 - selection (the 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 the function is called, the value of the variable will remain unchanged (even if the function changes their value).
To be able to skip some variables when calling a function, we add a parameter optional in front of the variable name, making it optional.

Let's move on to the next step.
The function is written, now we just need to learn how to use it. Let's go back to Excel and calculate the tax for the examples already analyzed.
We can call the function either directly by typing it in the formula bar, or find it in the category User Defined via the function wizard:

As a result, we get that the results of calculating the formula and applying the function coincided, which was what was required to be shown.

Now let's complicate the task a bit 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.

Thanks for attention!
If you have thoughts or questions - share them in the comments.

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

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 is value added. And then we will analyze 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 a part of the cost of goods, work or services, which is created at all stages of the production process of goods, works and services and is paid 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 at a rate of 10% or in the list with a rate of 0%.

I want to say right away who exactly pays this tax, so that no one has any misconceptions. VAT is paid not by stores, not by manufacturers, and not even by importers. We pay it with you - ordinary consumers, end users. Any indirect tax is paid by the final consumer and nothing else.

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

Bought cottage cheese 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. Do not be lazy to ask your contractor (aka the state) for the provision of legitimate public goods, the quality of the services provided.

In fact, VAT is an ingenious way, as I said, to levy taxes before paying the tax itself.

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

Step by step calculation

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

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

VAT from 100 rubles has already been paid by previous suppliers (farmer, carrier, 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 (SF) received is correct - excellent, you can deduct the VAT of the previous participants (from 100 rubles) and the seller pays only from 18 rubles (value added).

Is it worse if the SF is wrong, or if the supplier printed out 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 authorities will quickly charge them additionally during the check.

Now you understand why accountants are chasing invoices (closing documents) and treat them so reverently? 😉

This is if it's rude.

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

What can be considered added value for the company?

Value added for goods and services will consist of 4 main items.

Company profit
RFP + taxes and fees on RFP
Depreciation
Other intangible expenses (you can say costs without VAT)

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

How to calculate VAT in Excel?

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

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

I have attached 3 options for you.

'Value without VAT' is known, it is necessary to calculate 'VAT' and 'Amount with VAT'

‘VAT’ = ‘Price without VAT’ * 0.18

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

'Cost with VAT' is known, it is necessary to calculate the 'Amount without VAT' and 'VAT'

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

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

'VAT' is known, it is necessary to calculate the 'Amount without VAT' and 'Amount with VAT'

‘Price without VAT’ = ‘VAT’ / 0.18

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

More about VAT

In the wake of the VAT increase in the Russian Federation, with a 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 end 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 largest economies of the world?

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
if in excel then 215-18% = 214.83. how so?

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

How to determine the amount of VAT (calculation)

When selling goods, works, services, transferring property rights, VAT is presented to the buyer in addition to the selling 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 from the amount? It's easy: you just need to mathematically calculate the percentage. The calculation is made according to the formula:

VAT = NB * Nst / 100,

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

How to calculate VAT including

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

where C is the amount including VAT.

This calculation does not mean applying the estimated rates of 18/118 or 10/110 - it's just a way to separate tax from the total. Details about settlement rates - "".

How to calculate the amount with VAT

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

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

VAT calculation example

Using the above formulas, let's look at how to calculate VAT(in excess of the amount and including), on examples.

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

55 rub. * 100000 pcs. = 5500000 rubles.

5500000 * 18/100 = 990000 rubles

5500000 + 990000 = 6490000 rubles

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

5500000 * 1.18 \u003d 6490000 rubles.

cost without VAT - 5500000 rubles,
VAT 18% - 990,000 rubles,
total with VAT - 6490000 rubles.

According to the price list of X LLC, the price of a curb stone, 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 total cost of the batch:

236 rub. * 10000 pcs. = 2360000 rubles.

2360000 / 1.18 * 0.18 \u003d 360000 rubles.

Remaining amount without VAT:

2360000 - 360000 = 2000000 rubles

It can also be found without prior tax allocation:

2360000 / 1.18 \u003d 2000000 rubles.

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

2000000 * 18/100 = 360000 rubles

Accordingly, in settlement documents and invoices, you must specify:

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

15000 / 1.18 = 12712 rubles

VAT 18% = 2288 rubles

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

How to do it on a calculator:

Step 1: If the percentage of VAT for your type of activity is 10, then you need to divide the number by 1.1, if 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 resulting result to the nearest cent.

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

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

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

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

Party cost \u003d 10 * 1500 \u003d 15,000 rubles

Allocated VAT \u003d 15000 / 1.18 * 0.18 \u003d 2288 rubles (rounded up)

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 with VAT = 15,000 rubles.

This is a simple and convenient web service for keeping 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 the amount of VAT to the budget is determined as the difference between the amount of tax calculated at the rate of 10% or 18% of the tax base and the amount of tax deductions.

How to do it on a calculator:

Step 1: If the percentage of VAT for your type of activity is 10, then you need to divide the number by 1.1, if 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 resulting result to the nearest cent.

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

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

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

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

Party cost \u003d 10 * 1500 \u003d 15,000 rubles

Allocated VAT \u003d 15000 / 1.18 * 0.18 \u003d 2288 rubles (rounded up)

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 with 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 consists in subtracting the pre-calculated amount of VAT from the cost of the goods: \u003d Cost of goods - VAT amount or in tabular form: \u003d B3 - B5.