• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

Excelypedia

The Excel Encyclopedia of The Internet

  • Tutorials
  • Functions
  • Formulas
  • Templates

Formula for Income Tax Calculation

October 7, 2021 by Andrew Childress
Income tax calculation in Excel

Ever tried to calculate how much tax you owe on your income by hand? Let’s learn how to use the formula for income tax calculation in Microsoft Excel.

Ever tried to calculate how much tax you owe on your income by hand? If you have, you know how difficult and error-prone the technique is. Fortunately, by using a spreadsheet formula, you can figure out how much income tax you owe automatically. Let’s learn how to use the formula for income tax calculation in Microsoft Excel.

How to Use the Formula for Income Tax Calculation in Excel

Income taxes are structured in brackets. This means that you pay a certain percentage for a given range of income. To use the formula for income tax calculation, you’ll need three key inputs:

  • The maximum income for each bracket
  • The tax rate for each bracket
  • The cumulative tax owed

Arrange your data in a table like the one below. The bracket with a maximum of $9,875 has a tax rate of 10%; the bracket with $40,125 max is 12%, and so on. The Cumulative Tax Owed is computed with the formula you see in the formula bar.

Income tax calculation in Excel

To build the formula for income tax calculation, click into cell F2. You want to find how much your income tax will be if you make $53,125.

The formula consists of a trio of XLOOKUP functions. These incorporate your cumulative tax, along with the tax rate for your exact level of income:

=XLOOKUP(F1,A2:A5,C2:C5,,-1)+(F1-XLOOKUP(F1,A2:A5,A2:A5,,-1))*XLOOKUP(F1,A2:A5,B2:B5,,-1)

Income tax formula in Excel

The easiest way to input this formula is to paste it into the formula bar in cell F2. If you have additional income brackets listed, simply adjust the referenced ranges to incorporate the entirety of columns A, B, and C, respectively.

Hit Enter on your keyboard when you have the formula inputted, and your tax value returns: $7,477.50. Note that the formula will work for any value you place in cell F1, as long as it doesn’t exceed the $85,525 range of your tax table.

Formula for income tax calculation in Excel

As you can see, Microsoft Excel makes it a breeze to use the formula for income tax calculation.

Category: UncategorizedTag: Excel, How To, Instructions, Tutorial

About Andrew Childress

Previous Post:RANDARRAY formula in ExcelRANDARRAY in Excel
Next Post:Backtesting VaR in ExcelBacktesting Var in Excel

Sidebar

Recent Posts

  • How to Do a Contingency Table in Excel
  • Format Cell Alignment in Excel
  • Default Width in Excel
  • Translate in Excel
  • Switch Windows in Excel

Categories

  • Basics
  • Examples
  • Excel Formulas
  • Excel Functions
  • Excel Shortcuts
  • Excel Templates
  • Excel Tutorials
  • Uncategorized

Excelypedia

Master Excel to add a highly valuable asset to your employability portfolio. Learn how to analyze data in Excel.

    • Facebook
  • Twitter
  • Instagram
  • YouTube

Learn

  • Functions
  • Formulas
  • Shortcuts

Legal

  • Privacy Policy
  • Cookie Policy
  • Terms & Conditions

Help

  • FAQ
  • Contact Us

Copyright © 2023 · Excelypedia · All Rights Reserved

Back to top