OmniDocHow to set the personal income tax calculation formula in Excel
Use a tax rate table and a search formula to create a transparent and modifiable personal tax calculation table in Excel. It explains how to fill in the tax rate table, how to write the formula, and how to check the results. It is suitable for people who need to calculate or check their own taxes.
Key points of this article
- Put the tax rate, lower limit and quick deduction amount into a separate table. The formula only refers to this table, and only the table is modified when the policy changes.
- Use VLOOKUP's approximate matching to find the bracket where the taxable income is located, and then use "tax rate × amount - quick calculation deduction" to get the tax amount.
- Add IF judgment before the formula. When the taxable income is zero or a negative number, 0 will be returned directly to avoid error reporting.
- Use several sets of sample data with known results to check one by one and confirm that the formula is consistent with the manual calculation before applying it to all data.
Many people need to calculate their own personal income tax: check salary slips, measure the impact of year-end bonuses, and conduct salary simulations for their teams. Manual calculations are prone to errors, and calculators found online cannot see the intermediate process. It is a safer way to put the calculation process in Excel so that every step can be seen and modified.
This article is written for readers who can basically use Excel, but are not familiar with nested formulas. After reading, you will get a reusable personal tax calculation table: the tax rate table is stored independently, and there is only one calculation formula. You can get the result by changing the taxable income. What we talk about here is the formula construction method, not tax advice. Please refer to the official policy of the year for tax rate brackets.
How can personal income tax be calculated transparently and changeable? One sentence conclusion
First answer in one sentence: Make a table of tax rate brackets, then use the search function to extract the tax rate and quick deduction number according to the taxable income, and substitute them into a formula. This is easier to check than nested IFs, and only the tax rate table needs to be modified when the policy is adjusted.
| your situation | Recommended practices | reason |
|---|---|---|
| Only one person’s one-time tax is calculated | Write the formula directly in the cell and put the tax rate table in the same workbook | The structure is simple and can be verified by changing just one number. |
| To calculate in batches for many people | There is one column for taxable income, one column for tax amount, and the formula is filled down. | The same formula applies to all rows, reducing manual errors |
| Tax rates may be adjusted | The tax rate and quick calculation deduction are in separate tables, and the formula refers to the table area | When policies change, only the table is changed, not the formula. |
| Need to explain calculation procedures to others | Keep the tax rate table and sample rows and write a note next to them | The other party can gradually understand the path from income to tax amount |
Before you start: Confirm these points first
There are a few things to think about before you start, they will determine the structure of the table.
- First determine what is entered. The formula calculates "taxable income," not pre-tax wages. The pre-tax income must first be deducted from the basic deduction expenses and various deductions stipulated in the current policy. The amount after deductions should be shown in the table.
- Confirm if the tax rate table matches your scenario. The applicable tax rate tables and calculation methods for monthly withholding of wages and salaries, year-end bonuses, labor remuneration, etc. may be different. Separate tables for different scenarios to avoid mixing them.
- Check the policy for the current year. Tax rate brackets and quick calculation deductions will be adjusted according to policies. Before filling out the form, check the new official regulations and do not directly use the old form.
- Prepare several sets of examples with known results. Comparing the results obtained manually or with official calculation tools is a reliable way to check the formula.
- If the tax rate table is a PDF file. You can use the PDF editor of the OmniDoc desktop client to do OCR, export it to Excel, and then manually check the numbers. The numbers after identification on the scanned document need to be checked item by item and should not be used directly.
Steps to set up personal tax calculation formula in Excel
The following takes the calculation of monthly wages and salaries as an example. The structure of the form can be copied. Please fill in the numbers in each file one by one according to the official regulations of the year.
- Create a new "Tax Rate Table" worksheet. Fill in the "Lower Limit", "Tax Rate" and "Quick Calculation Deduction" in A1 to C1 respectively, starting from the 2nd line and filling them in line by line from low to high.
- Fill in the data for each file. The lower limit, tax rate and quick deduction amount are filled in according to the officially announced range of the year, and the tax rate can be set in percentage format. The lower limit column must be sorted in ascending order, otherwise the search results will be wrong.
- Go back to the main worksheet and create an input column. Enter "Taxable Income" in column A, starting with A2 and entering values, with each row representing a person or situation.
- Enter the tax amount formula in B2. Enter:
=IF(A2<=0,0,ROUND(A2*VLOOKUP(A2,税率表!$A:$C,2,TRUE)-VLOOKUP(A2,税率表!$A:$C,3,TRUE),2)). The meaning of the formula is: first use approximate matching to find the tax rate of the current bracket, multiply the amount, then subtract the corresponding quick calculation deduction, and finally keep two decimal places. - Understand every part of the formula. The fourth parameter of VLOOKUP, TRUE, indicates an approximate match, which will find the row in the lower limit column with a large value that is no greater than the amount. The second column takes the tax rate, and the third column takes the quick calculation deduction. IF handles zero and negative numbers.
- Fill the formula down. Select B2 and drag the fill handle to the required number of rows. Excel automatically adjusts row numbers so that each row uses the same set of rules.
- Check the results with samples. Enter different amounts of taxable income on separate lines and compare the results with manual calculations or official calculation tools. If they are consistent, the formula is correct.
- Protect formula areas when necessary. Lock or mark the tax rate table and formula columns to avoid accidental deletion or modification.
Check each item after completion
After the form is set up, don’t rush to deliver the results. First check them one by one according to the list below.
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Sample result | Compare with two or three groups of figures calculated by hand | Check tax table figures and gear order in case of inconsistency |
| Gear boundary | Enter the value of each lower limit and the value of one element before and after it. | Results if the jump is abnormal, confirm whether the lower limit is filled in ascending order. |
| Zero sum negative number | Enter 0 and negative number, the result should be 0 | When an error occurs, confirm that IF determines whether it is included in the formula. |
| Decimal place | Check to see if the tax keeps two decimal places | If you need to round by element, use the corresponding rounding function instead, and explain the rules |
| Citation range | Make sure that the area of VLOOKUP covers all gears and there are no missing lines. | Change the reference range to the entire column when missing a row, such as the tax rate table! $A:$ C |
| Policy version | Check the year and applicable scenarios of the tax rate table | Replace the tax table figures after the policy update, and the formula does not need to be changed. |
If the form is used for formal declaration or important decision-making, it is recommended to check the results with the official personal tax calculation channels or the opinions of professionals.
# Summary
Using a tax rate table and a search formula in Excel, you can build a transparent and modifiable personal income tax calculation table. The key is the ascending order of the lower bound, the approximate matching parameters in the formula can not be saved, and the sample results must be verified. Separate the tax table from the formula and only replace the numbers when the policy changes, which also applies to other staged calculation scenarios.
# frequently asked questions (FAQ)
1. Why does the TRUE parameter have to be used in the formula?
TRUE indicates an approximate match, and VLOOKUP will find a large lower limit that is not greater than that amount, thus getting the correct gear. If omitted or changed to FALSE, an error value is returned unless the amount is exactly equal to a lower limit.
2. Can I use multi-tier IF instead of lookup tables?
Yes, but when there are more gears, the formula will be very long, it is not easy to check, and it is not easy to adjust the policy. The advantage of the lookup table is that the tax rate figures are concentrated in one place, and the formula itself remains the same.
3. How can taxable income be calculated from pre-tax wages?
First subtract the basic deduction expenses stipulated in the current policy, and then subtract the items allowed to be deducted such as social insurance and housing accumulation fund, and the remaining amount is the taxable income. For specific deductions and standards, please follow the official regulations.
4. Is the tax rate for the year-end bonus the same as the salary?
The method of calculation may be different from the tax table, and year-end bonuses are usually stipulated separately. It is recommended that you store it in two tables and do not mix the tax rates in different scenarios.
5. What if the result of the formula is different from that of the official tool?
First check whether the taxable income entered is consistent, then check the figures and years of the tax rate table, and finally confirm whether there are any omissions in the citation range of the formula. If there are still differences, the official calculation or professional opinion shall prevail.
