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.

An Example to Calculate Federal Income Tax Rate in Excel

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.

Creating a Dataset 

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.

Calculation of Taxable Income to Calculate Federal Income Tax Rate in Excel

  • 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.

Calculation of Taxable Income

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

Calculation of Taxable Income

  • 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).

Finding Tax Expense to Calculate Federal Income Tax Rate in Excel

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

Finding Tax Expense

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

Finding Tax Expense

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.

Calculating Total Taxable Income and Total Tax Expense to Calculate Federal Income Tax Rate in Excel

Step 5 – Calculate Federal Tax Rate

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

Effective Federal Tax Rate = Total Tax Expenses / Total Taxable Income

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

Calculating Effective Tax Rate to Calculate Federal Income Tax Rate in Excel

Download the Practice Workbook

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Hafizul Islam
Hafizul Islam

Hafizul Islam is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role,... Read Full Bio

  1. Hafizul,
    Thank you so much for making calculating the Federal Tax Rate easy.

Leave a reply

Advanced Excel Exercises with Solutions PDF