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

Get FREE Advanced Excel Exercises with Solutions!

U.S. government revenue comes primarily from the federal income tax. As a result of federal income tax revenues, a variety of expenses are met, including the construction and repair of the country’s infrastructure, the improvement of education and public transportation, and disaster relief. No matter where you live or work, the same federal income tax rates apply to everyone, regardless of income and filing status. In this article, you will learn an example to calculate federal tax rate in Excel.


What Is Federal Income Tax?

Federal income tax is a tax levied by the Internal Revenue Service on an individual, corporate, trust, and other legal entities’ earnings. Wages, salaries, commissions, bonuses, tips, investment income, and certain unearned income all qualify as taxable income for federal income tax purposes.

Tax rates for individual incomes in the United States are progressive, meaning that they increase with taxable income. There are specific income thresholds at which federal income tax kicks in. Federal income tax rates range from 10% to 37%. Tax brackets are income ranges for which rates apply. In each bracket, income is taxed according to its corresponding rate.


How to Calculate Federal Tax Rate in Excel?

Microsoft Excel offers several standard functions that can be used to calculate your effective tax rate using your income breakdown by tax bracket. Your taxable income needs to be divided into the correct cells according to IRS tax brackets.

IRS Tax Brackets:

A new tax bracket, standard deduction amount, and cost-of-living adjustment are announced annually by the IRS. Changes announced will take effect on January 1 of the following year.

The announced 2021 tax rates do not apply if you prepare your 2020 tax returns in March 2021. The rates from the previous year (2020) are still applicable; they should have been announced in 2019.

Segregating Earned Income:

Consider dividing the tax brackets by 10% every $25,000 of earned income for simplicity’s sake. During the year, you earned $80,000. For the first $25,000, 10% will be applied, then 20%, 30%, 40%, and 50% for $50,000 to $75,000, respectively.

The first cell should be $25,000 times 10%, the last $5,000 times 40%, etc. A cell for every income tax rate should be created and multiplied by the amount of income you have in the different brackets. Each bracket represents your lost income.

Finding Effective Tax Rate:

Your marginal tax rate would be 40% in the above scenario. Every dollar you earn above $80,000 will be confiscated by the government for 40 cents.

You have a different effective tax rate. It is a measure of how much tax you paid on each income source. Divide your lost income by your earned income to calculate this rate.

A total of $17,000 has been taken by the government ($2,500 + $5,000 + $7,500 + $2,000). You would have an effective rate of 21.25% if you earned $80,000 ($17,000 / $80,000).


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 tax calculation for individuals in the US.

An Example to Calculate Federal Income Tax Rate in Excel

As an example, let us say 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 an income of $80,000 that is taxable.

Now, to calculate the effective tax rate for Robert, follow the steps below.


Step 1: Collect Data 

Let’s first 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 1st 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 2nd 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

In the same way, we will determine the taxable income for the third bracket.

Calculation of Taxable Income

Read More: How to Calculate Social Security Tax in Excel


Step 3: Find Tax Expense

Our goal is to determine his effective tax rate by calculating his tax expenses based on the above brackets.

To calculate tax expense, we have to multiply the taxable income (Cell F5) with the tax rate (Cell C5)

Finding Tax Expense to Calculate Federal Income Tax Rate in Excel

In a similar manner, we can easily calculate Tax Expenses for other brackets by dragging the Fill Handle to the remaining cells.

Finding Tax Expense

Finally, here are the tax expenses of Robert for all brackets.

Finding Tax Expense


Step 4: Calculate Total Taxable Income and Total Tax Expense

Using the SUM function, we can easily compute the total taxable income and total tax expense.

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

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

Finally here, the effective tax rate is 19.67%

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


Things to Remember

Progressive taxation means that all income will not be taxed equally. Therefore, different levels of income will result in different tax rates for individuals and companies. Comparing companies and taxpayers is easier when we know the effective tax rate. Although individuals might not be able to reduce their effective tax rates much, corporations play a very important role in reducing theirs and take various measures in order to do so.


Download Practice Workbook

You can download the following practice workbook that we have used to prepare this article


Conclusion

In this tutorial, I have discussed an example to calculate the federal tax rate in Excel. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo