Contents
Often, users working in the Excel spreadsheet editor need to perform such a procedure as deducting VAT. Of course, this action can be performed using a conventional calculator, but if you need to perform such a calculation many times, then it is more advisable to use the functions built into the editor. In the article, we will analyze in detail all the methods that allow you to implement the VAT deduction in a spreadsheet document.
Formula for calculating VAT from the tax base
Initially, we will determine how to calculate VAT from the tax base. It is quite easy to implement this procedure. It is necessary to multiply the tax base by the rate of eighteen percent. We get the following formula: “VAT” = “Taxation base” * 18%. In the spreadsheet editor, the formula looks like this: =number*0,18.
The variable “Number” is the numerical value of the tax base. Instead of a number, you can specify the coordinate of the cell in which the indicator itself is located.
Let’s take a look at a specific example. We have three columns. The 1st column contains indicators of the tax base. In the 2nd column are the desired indicators that need to be calculated. The 3rd column contains the amount of production together with VAT. The calculation will be made by adding the values of the 1st and 2nd columns.
Detailed instructions look like this:
- We select the 1st cell with the required information. Enter the symbol “=”, and then click the left mouse button on the field located in the same line of the first column. The coordinates are entered into the formula. Add the symbol “*” to the calculated field. Using the keyboard, we write “18%” or “0,18”. As a result, we get the following formula: =A3*18%.
- Press the “Enter” key on the keyboard to display the result in the selected cell. The spreadsheet editor will perform all the necessary calculations.
- Note that the total is displayed with 4 decimals. The currency value must have only 2 decimal characters. In order for the displayed result to look correct, it must be rounded to 2 decimal places. This procedure is implemented by a formatting operation. For convenience, we will format all cells in which a similar indicator will be displayed. We select a range of such cells by holding down the left mouse button. Right-click anywhere in the selected range. A small special context menu appeared on the display. We find the element that has the name “Cell Format …”, and click on it with the left mouse button.
- A window was displayed on the spreadsheet editor screen, allowing you to perform the formatting procedure. We move to the subsection “Number”. We find the list of commands “Numeric formats:” and select the element “Numeric” here. We set the value “2” to the line that has the name “Number of decimal places”. To apply all changes, click on the “OK” button located at the bottom of the table editor interface.
- An alternative option is to use the monetary format. It also allows you to display the total with 2 decimals. We move to the subsection “Number”. We find the list of commands “Number formats:” and select the element “Currency” here. We set the value “2” to the line that has the name “Number of decimal places”. In the “Designation” parameter, we set the ruble. Here you can set absolutely any currency. To apply all changes, click on “OK”.
- Result of conversions with number format:
- Result of conversions with currency format:
- We copy the formula to the remaining cells. Move the pointer to the lower right corner of the cell with the formula. The pointer took the form of a small plus sign of a dark shade. With the help of the left mouse button pressed, we stretch the formula to the end of the table.
- Ready! We stretched the formula to all the cells of this column.
- It remains to implement the procedure for calculating the total amount of the price together with VAT. We click LMB on the 1st cell of the column “Amount with VAT”. Enter the “=” symbol, click on the 1st field of the “Taxation base” column. We drive in the “+” symbol, and then click LMB on the 1st field of the second column. As a result, we get the following formula: = A3+V3.
- Press the “Enter” key to display the result in the selected cell. The spreadsheet editor will perform all the necessary calculations.
- Similarly, we copy the formula to the remaining cells. Move the pointer to the lower right corner of the cell with the formula. The pointer took the form of a small plus sign of a dark shade. With the help of the left mouse button pressed, we stretch the formula to the end of the table.
There are several more formulas that allow you to implement the formula for deducting VAT. Immediately, we note that the sequence of actions is the same as in the above example. With other formulas, only the original plate itself changes, and all actions related to changing the format and stretching the formula to other cells remain the same.
The formula for calculating the amount of VAT on the amount in which the tax is already included looks like this: “VAT” = “Amount with VAT” / 118% x 18%. In the spreadsheet editor, the formula looks like this: =number/118%*18%.
The formula for calculating the tax amount from the tax base looks like this: “Amount with VAT” = “Tax base” x 118%. In the spreadsheet editor, the formula looks like this: =number*118%.
The formula for calculating the tax base from the amount with tax looks like this: “Taxation base” = “Amount with VAT” / 118%. In the spreadsheet editor, the formula looks like this: =number/118%.
Conclusion and conclusions on the VAT deduction procedure in a spreadsheet editor
The spreadsheet editor allows you to quickly complete the VAT deduction procedure. The program allows you to apply absolutely any formula that exists to calculate this indicator. The main thing is to be able to change the cell format and work correctly with the line for entering formulas.