How to Calculate Marginal Tax Rate in Excel (2 Quick Ways)

Get FREE Advanced Excel Exercises with Solutions!

The Marginal Tax Rate is the tax rate that applies to the next dollar of taxable income. The taxable income is the total income subject to tax. The marginal tax rate is also known as the progressive tax rate. In this article, you will learn 2 ways to calculate the marginal tax rate in Excel with ease. So without having any further discussion, let’s get started.


What Is Marginal Tax Rate?

The amount of tax paid on each additional income is known as the Marginal Tax Rate. With rising income, an individual’s Marginal Tax Rate will also rise. The rate at which an additional dollar of income is taxed is known as the Marginal Tax Rate.

The Marginal Tax Rate for an individual in the United States is based on their tax bracket. The range of income that is subject to a specific tax rate is known as the tax bracket. For instance, a person in the 10% tax bracket would have a marginal tax rate of 10%. A person in the 15% tax bracket pays taxes at a marginal rate of 15%.

When choosing an investment, it’s crucial to take the Marginal Tax Rate into account. How much of the profit on an investment is taxed depends on the Marginal Tax Rate. The investor will only keep 5% of the return after taxes, for instance, if the Marginal Tax Rate is 50% and the return on investment is 10%.


How to Calculate Marginal Tax Rate in Excel: 2 Quick Ways

In the following picture, I have a dataset of four columns. The first column is Bracket. This column has two sub-columns, Lower Bound and Upper Bound. Here, I have taken some sample tax brackets ranging from $0 to $60,001 and above.

The next column is Tax Rate which has all the marginal tax rates ranging from 0% to 50%. The next two columns are Taxable Income and Tax Payable.

I will use this dataset to demonstrate the two methods of calculating marginal tax rates in Excel.


1. Calculate Marginal Tax Rate Without Deduction

First, I will show you calculate the marginal tax rate without deduction.

Here, I have Gross Income Per Annum is $52,000 and a Total Deduction is 0.

To calculate the Total Taxable Income Per Annum,

  • Insert the following formula in cell D6.
=D4-D5
  • Then press ENTER.

The formula returns the Total Taxable Income Per Annum is $52,000.

Calculate Marginal Tax Rate in Excel Without Deduction


Allocate Total Taxable Income

So, I have a total taxable income of $52,000. Now, I’m distributing this amount to the corresponding tax brackets.

  • The first tax bracket ($0 to $10,000) gets $10,000.
  • Next tax bracket ($10,001 to $20,000) gets $10,000.
  • Third tax bracket ($20,001 to $40,000) gets $20,000.
  • And the forth tax bracket ($40,001 to $60,000) gets $12,000.

To calculate the sum of all taxable income insert the following formula in cell E15. Then press the ENTER button.

=SUM(E10:E13)

The formula also returns $52,000. This means the taxable income distribution is okay.

Calculate Marginal Tax Rate in Excel Without Deduction

The basic formula for calculating Tax Payable is,

Tax Payable = Tax Rate * Taxable Income
  • Now write the following formula in cell F10.
=D10*E10

Here, D10 refers to the Tax Rate and E10 refers to the Taxable Income.

  • Then press ENTER.

Calculate Marginal Tax Rate in Excel Without Deduction

Now drag the Fill Handle up to cell F13 to copy down the formula.

After dragging down the formula, it returns the corresponding Tax Payable amount for all tax brackets.

To calculate the total amount of Tax Payable,

  • Use the following formula in cell F15.
=SUM(F10:F13)
  • Then press ENTER.

Calculate Marginal Tax Rate in Excel Without Deduction

The total Tax Payable amount is $9,800. So, for a total taxable income amount of $52,000, the marginal tax rate is 40%.  It’s because the 4th portion of the Taxable Income falls under the 4th tax bracket of 40% Marginal Tax Rate.

Read More: How to Calculate Income Tax on Salary with Old Regime in Excel


2. Calculate Marginal Tax Rate with Deduction

Now, I will show you calculate the marginal tax rate considering all deductions.

Here,

  • Gross Income Per Annum is $52,000.
  • The total Deduction is $12,000.

To calculate the Total Taxable Income Per Annum,

  • Use the following formula in cell D6.
=D4-D5
  • Then press ENTER.

The formula returns $40,000. So the total taxable income per annum is $40,000.

Calculate Marginal Tax Rate in Excel with Deduction


Allocate Total Taxable Income

So, I have a total taxable income of $40,000. Now, I’m distributing this amount to the corresponding tax brackets.

  • The first tax bracket ($0 to $10,000) gets $10,000.
  • Next tax bracket ($10,001 to $20,000) gets $10,000.
  • Third tax bracket ($20,001 to $40,000) gets $20,000.

To calculate the sum of all taxable income insert the following formula in cell E15. Then press the ENTER button.

=SUM(E10:E12)

The formula also returns $40,000. This means the taxable income distribution is okay.

Calculate Marginal Tax Rate in Excel with Deduction

The basic formula for calculating Tax Payable is,

Tax Payable = Tax Rate * Taxable Income
  • Now write the following formula in cell F10.
=D10*E10

Here, D10 refers to the Tax Rate and E10 refers to the Taxable Income.

  • Then press ENTER.

Calculate Marginal Tax Rate in Excel with Deduction

Now drag the Fill Handle up to cell F12 to copy down the formula.

After dragging down the formula, it returns the corresponding Tax Payable amount for all tax brackets.

To calculate the total amount of Tax Payable,

  • Use the following formula in cell F15.
=SUM(F10:F12)
  • Then press ENTER.

Calculate Marginal Tax Rate in Excel

The total Tax Payable amount is $5,000. So, for a total taxable income amount of $40,000, the marginal tax rate is 20%.  It’s because the 3rd portion of the Taxable Income falls under the 3rd tax bracket of 20% marginal tax rate.

Read More: How to Calculate Federal Tax Rate in Excel


Practice Section

You will get an Excel sheet like the following screenshot, at the end of the provided Excel file where you can practice all the topics discussed in this article.


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


Conclusion

To sum up, we have discussed 2 ways to calculate the marginal tax rate in Excel. Please don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap.


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.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo