This template enables users to perform annual income tax & monthly salary calculations based on income tax rates for residents and non-residents, tax offsets for low income and low and middle income, medicare levies and employer super contributions. All the income tax & monthly salary calculations are automated and user input is limited to only a few earnings & deduction amounts. The template can be used for multiple tax years by saving a new copy for the new tax year and updating the default calculation variable values where needed.
The template includes three sheets:
TaxCalc - this sheet contains the annual income tax, monthly salary and annual bonus calculations. Only the cells with yellow cell backgrounds require user input and all calculations are automated.
Monthly - this sheet enables users to perform monthly income tax calculations based on variable monthly earnings and annual bonus amounts. Only the cells with yellow cell backgrounds require user input - all other calculations are automated.
Values - all the default values which are used in the income tax, salary and bonus calculations need to be specified on this sheet. This includes the income tax rates at the top of the sheet (tables for residents and non-residents), tax offsets for low income and low & middle income, Medicare levies and employer contributions for Super. All these default values need to be updated at the start of a new tax year by entering the new values in the cells with yellow cell backgrounds (if needed).
How to calculate annual income tax payable
You can calculate your annual income tax payable on the TaxCalc sheet by simply entering values in all the cells with yellow cell backgrounds. The annual income tax payable is then automatically calculated in columns D & E based on the specified user input values and the default tax calculation variable amounts which are included on the Values sheet (including the tax brackets at the top of the sheet).
How to calculate your net monthly salary
The TaxCalc sheet also includes a section for the monthly salary calculations in columns G & H. These calculations are also based on the user input values on this sheet and the default tax brackets and other variable values on the Values sheet. If your monthly salary does not consist of the same earnings and deduction amounts each month, you can use the Monthly sheet to perform your salary calculations.
How to calculate your net annual bonus
The TaxCalc sheet also includes a section for the calculation of the net annual bonus amount. You can also use the Monthly sheet to display all of the income tax and net pay calculations for the full 12 months.
What about annual changes in income tax rates?
The income tax brackets which are used in all income tax calculations in this template are included at the top of the Values sheet. There are two tables - the first for Australian residents and the second for non-residents. At the start of a new tax year, you need to update all of the values for the new tax year including the income tax bracket values and percentages.
Only the cells with yellow cell backgrounds need to be updated - all the other cells contain formulas which will be updated automatically when the user input values are amended. If you need to add more income tax brackets, you can insert a new row anywhere between the first and last rows of the appropriate table, copy the formulas from one of the existing rows (not the first or last) and enter the new bracket value and percentage in the cells with yellow backgrounds.
You can also delete tax brackets if less than the standard number of tax brackets are required by simply deleting one of the existing tax brackets (the entire row) excluding the first or last tax bracket. When you delete a row, the other tax bracket values will update automatically.
Note: All our templates are sold on a once-off basis and therefore do not include any annual updates. You can however use this template for multiple tax years by simply updating the default tax calculation variables on the Values sheet at the beginning of each new tax year.
Income Tax & Monthly Salary Calculation Guidance
Aside from the income tax brackets, this template includes a number of other variables in the calculation of monthly & annual income tax and net pay. We will provide guidance on how each of these items are included in the template calculations:
Earnings
We include 2 earnings items in this template namely the basic monthly salary and the annual bonus. The earnings item amounts need to be specified by entering the appropriate values on the TaxCalc or Monthly sheets. The total taxable income is calculated by multiplying the monthly salary by 12 and adding the annual bonus amount.
Note: You can also elect whether the amounts that you enter include super annuation or not. If you select the Yes option from the "Salary Includes Super?" list box, it will be assumed that the earnings amounts entered include the super contribution percentage and the calculation of the total taxable income will be adjusted by reducing the amounts entered by the super percentage which is specified on the Values sheet.
Residential Status
The template also includes the option of performing the calculations for non-residents. If you select the Yes option from the "Australian Resident?" list box, the calculations will be applied on a non-resident basis which means that the non-resident tax table will be used for income tax calculations, no medicare levy will be deducted from the specified salary and no employer super contribution will be calculated. Tax offsets will also not be applied.
Tax Offsets
The income tax calculations in this template provide for two types of tax offsets namely the low income tax offset and the low and middle income tax offset. These offsets are calculated based on the values and limits specified on the Values sheet and deducted from all tax calculations. If you want to see the values of these offsets separate from the calculated tax amounts, you can refer to the annual income tax calculation in columns D & E on the TaxCalc sheet where these amounts are deducted separately.
The low income tax offset is calculated based on a base income value below which the entire base amount is applied and above which the full base amount is reduced by the adjustment percentage until the offset value is zero. The maximum offset value will be the same as the base amount and the low income tax offset calculation is only based on one set of base income and adjustment percentage values.
The low and middle income tax offset calculation is slightly different in that it uses two sets of base income and adjustment percentages and the first adjustment percentage actually increases the tax offset value above the first base income amount. The maximum tax offset amount is higher than the base amount and represents a capped amount or ceiling for the calculated tax offset.
The purpose of the second set of base income and adjustment percentage values is to reduce the value of the tax offset above the income level set in the second base income field. The intention of the tax offset is that it only benefits low and middle income earners and the maximum offset value is therefore reduced by the adjustment percentage above the second base income amount until it reaches a point where the tax offset is zero.
Note: The tax offset values, limits and percentages on the Values sheet may need to be updated at the start of a new tax year. If you are uncertain about what these values should be, you can download our sample version of the template where we update the relevant values in the second week of every new tax year.
Salary Deductions - Medicare Levy
The Medicare levy salary deduction is calculated by multiplying the appropriate monthly or annual income by the Medicare levy percentage which is specified on the Values sheet. Note that no medicare levy or employer super contributions are applicable for non-residents.
Note: In order to keep calculations relatively simple, our template does not include calculations of the Medicare Levy Surcharge and the salary deduction amounts therefore do not include the Medicare Levy Surcharge.
Employer Contributions - Super
The Super employer contribution is calculated by multiplying the appropriate monthly or annual income by the Super percentage specified on the Values sheet. Note that if the "Includes Super" list box is set to the Yes option, the entered salary amounts will be adjusted by the Super percentage thereby reducing the income because it includes the employer contribution for Super.
Annual Bonus
If you enter an annual bonus amount, this earnings amount will be included in the annual tax payable calculation on the TaxCalc sheet and also included separately below the monthly salary calculation section. The annual bonus therefore does not form part of the monthly salary calculation section on the TaxCalc sheet.
If you therefore want to know what the net bonus amount after tax and Medicare deduction would be, refer to this separate section for the calculated net bonus amount and the income tax which will be deducted from the bonus.
If you use the Monthly sheet, the annual bonus needs to be included in the appropriate month (which can be any of the 12 monthly periods) and the income tax and Medicare amounts attributable to the bonus payment will also be included in this month.
Monthly Income Tax Calculations
The Monthly sheet can be used for calculating income tax and net monthly salary for each of the 12 months which form part of the appropriate tax year. This is especially useful where the monthly earnings amounts are not consistent from month to month and also provides a clearer picture of earnings, deductions and income tax calculations for the entire tax year.
Only the cells with yellow cell backgrounds require user input - all the other cells contain formulas which automatically calculate the appropriate amounts. The period start date specified at the top of the sheet determines which monthly periods are included in column A. The date which should be entered in this cell should therefore be the first day of the appropriate tax year. If you leave the period start cell blank, the template will default to the current date and the first period would start from the end of the current month.
The Monthly sheet also contains input cells for indicating that the earnings amounts include Super and that the calculations should be based on being a non-resident. If you select the Yes option from the includes Super list box, the total income will be calculated by deducting the employer contribution for Super. If you select the No option from the resident list box, the calculations will be based on not being a resident which means that the second tax table on the Values sheet will be used and that the Medicare and Super amounts will be zero.
User input is also required for the monthly salary and annual bonus amounts. All the other columns (with blue cell backgrounds) contain formulas which should not be edited or replaced otherwise the template calculations may become inaccurate.
These calculated columns contain the following calculations:
Gross Pay - the total of the monthly salary and annual bonus columns. If the includes Super selection is set to the Yes option, the total will be reduced as it is assumed to include the employer contribution for Super.
Income Tax (PAYG) - the monthly income tax amount which is based on the tax payable monthly EQV and tax payable annual columns. These columns are included for the detailed income tax calculations from the tax brackets and the formula in this column basically just combines the monthly calculations and the tax on the annual bonus so that the tax on the bonus is not spread over the remaining months in the tax year.
Medicare Levy - this is the calculation of the Medicare salary deduction based on the Medicare levy percentage which is specified on the Values sheet.
Total Deductions - the sum of the income tax and Medicare deductions.
Net Pay - this is the net amount paid to the employee and is the difference between the total earnings and total deductions.
Super (Employer) - this is the calculation of the employer contribution for Super which is based on the employer Super contribution percentage which is specified on the Values sheet.
Company Contributions - the sum of the all employer salary contributions (which is basically only the Super).
Pay Period - the pay periods start from the first month where the gross pay is not nil. If you start entering earnings amounts in month 6, the first pay period will be in month 6 and there will be less than 12 pay periods in the tax year. The annual taxable income calculation will still be based on a full 12 months in the tax year to ensure that the correct income tax amounts are calculated.
Annual Taxable Income - this is the annualized total of all earnings.
Taxable Income Monthly EQV - this is the taxable income on all earnings amounts excluding the annual bonus.
Tax Payable Total - this is the total annual tax payable.
Tax Payable Monthly EQV - this is the total annual tax payable based on earnings and deductions which are paid on a monthly basis (only the annual bonus is therefore excluded).
Tax Payable Annual - this is the tax payable on the annual bonus amount and is the difference between the total annual tax payable and the tax payable on monthly equivalents (amounts paid every month).
Note: The Monthly sheet also includes totals above the column headings for all columns which contain monthly values. These totals will agree with the amounts calculated on the TaxCalc sheet. The Monthly sheet therefore provides a more comprehensive view of annual earnings, income tax and deductions especially if an annual bonus forms part of remuneration.
