How to Calculate Federal Tax Rate in Excel (with Easy Steps)

There are various tax brackets in most countries, and each bracket has a different rate of tax. For example, let’s look at the sample tax calculation for individuals in the U.S.

As an example, Robert works in finance. He earns $100,000 a year and invests some of it in tax-saving financial instruments. Let’s say that amount is $20,000. As a result, he has a taxable income of $80,000.

Step 1 – Collect Data 

• Create a dataset as shown in the following image. Our first goal is to calculate Robert’s taxable income.

Read More: Formula for Calculating Withholding Tax in Excel

Step 2 – Calculate Taxable Income

• For the first bracket, due to the zero lower limit, the maximum Taxable Income will be 9,315.

• To calculate the taxable income for the second Bracket, we need to subtract the lower limit (Cell D6) from the upper limit (Cell E6) and then add the result with 1.

• Determine the taxable income for the third bracket in the same way.

• If the income goes into the next bracket, continue until it’s under the upper limit for the bracket.

Read More: How to Calculate Social Security Tax in Excel

Step 3 – Find the Tax Expense

• Multiply the taxable income (Cell F5) with the tax rate for that bracket (Cell C5).

• Calculate the Tax Expenses for other brackets by dragging the Fill Handle to the remaining cells.

• Here are the tax expenses for Robert for all brackets.

Step 4 – Calculate the Total Taxable Income and Total Tax Expense

• Use the SUM function in the bottom-right cell to sum cells above it.

Step 5 – Calculate Federal Tax Rate

We will compute the Effective Tax Rate by applying the following formula:

The total tax expense is $15,738.75 (Cell G8), and the total taxable income is $80,000 (Cell F8).

Download the Practice Workbook

Related Articles

• How to Calculate Income Tax in Excel Using IF Function
• Reverse Tax Calculation Formula in Excel
• How to Calculate Income Tax on Salary with Example in Excel
• How to Calculate Sales Tax in Excel

<< Go Back to Excel Formulas for Finance | Excel for Finance | Learn Excel