Excel-Skills Australia



26 Income Tax Calculations Template

Perform a computation of income tax in Excel based on multiple tax brackets (also referred to as a sliding income tax scale). Template design incorporates five default tax brackets but additional tax brackets can be added if required. All income tax calculations are automated and you only need to specify the basic monthly salary and annual bonus to calculate monthly income tax amounts.

  • Flexible design accommodates any income tax calculation
  • Includes 5 default income tax brackets (you can create more)
  • Includes income tax calculations for non-residents
  • All tax calculations are automated including tax offsets
  • Automated calculation of Medicare salary deductions
  • Automated calculation of employer super contribution
  • Calculates income tax based on variable monthly remuneration
  • Calculates tax and take-home pay on annual bonus

How to use the Income Tax Calculations template

Open the sample or trial version when reviewing these instructions

This template enables users to perform income tax calculations based on multiple tax brackets (also referred to as a sliding income scale). The template design incorporates six default tax brackets, but you can add additional tax brackets if required. All the income tax calculations are automated and user input is limited to defining the appropriate tax bracket values and income tax percentages.

The template includes two sheets:
TaxCalc - this sheet contains the income tax brackets and enable users to perform income tax calculations by simply entering the appropriate monthly remuneration amount.
Monthly - this sheet enables users to perform monthly income tax calculations based on variable monthly remuneration amounts.

Tax Brackets Setup

Our default tax bracket setup provides for six tax brackets in the cell range from cell A3 to cell E8. All the cells with a light green cell background contain formulas and the cells with a light yellow cell background require user input.

If the six default tax brackets are sufficient for your income tax calculations, all you need to do is to enter the appropriate tax bracket values in column B and the appropriate income tax percentages that relate to each tax bracket in column C. The other cells in the tax bracket cell range will be updated automatically and the income tax calculation from row 12 to 15 will automatically be calculated from the values that you've entered.

If you require more than six income tax brackets for your calculation, simply insert the required number of additional rows anywhere between row 4 and 8. Then copy the formulas from one of the existing cells in columns A, D and E (the cells with the light green cell background) in order to update the tax bracket formulas. After entering the appropriate tax bracket values and income tax percentages, your tax bracket setup will be adjusted and will now consists of more than six tax brackets. The income tax calculations are also automatically updated.

Users also have to specify the total tax offsets amount in cell B10. This amount is deducted after calculating the income tax that is applicable to the specified remuneration amount and has the effect of reducing the effective income tax rate.

Income Tax Calculation

The only user input that is required in order to perform an income tax calculation is to specify a gross monthly remuneration amount in cell B15. The annual remuneration, annual income tax, monthly income tax, annual & monthly net remuneration and the effective income tax percentage are all calculated based on this amount.

Monthly Income Tax Calculations

The Monthly sheet contains a monthly income tax calculation that can be performed by simply entering the appropriate gross monthly remuneration amounts in column B. You can also specify the Start Date in cell B3 in order to change the month end periods in column A.

An annual gross remuneration equivalent, cumulative income tax amount and monthly income tax amount are calculated based on the monthly remuneration amounts that are entered in column B.

These calculations are especially useful if the monthly remuneration is variable and enables the user to calculate the monthly income tax that needs to be deducted under these circumstances.