This template enables users to perform annual income tax & monthly salary calculations based on multiple tax brackets (also referred to as a sliding income scale) and a number of other income tax & salary calculation variables. The template design incorporates seven default tax brackets but you can add additional tax brackets if your region requires more tax brackets. All the income tax & monthly salary calculations are automated and user input is limited to only a few earnings & deduction amounts.
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 calculations need to be specified on this sheet. This includes the income tax rates at the top of the sheet, tax rebate amounts, medical tax credit amounts, unemployment insurance limit and percentage, pension fund tax deduction limits and the travel allowance inclusion percentage. All of 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.
Note: This template has been designed for income tax and monthly salary calculation purposes and does not represent a full payroll solution. If you are interested in a payroll solution which includes pay slips and much more comprehensive functionality, please refer to our Monthly Payroll template.
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 the 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. At the start of a new tax year, you need to update all of the values on this sheet 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, 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 cell 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 therefore provide guidance on how each of these items are included in the template calculations:
Earnings
We include 4 earnings items in this template namely the basic monthly salary, monthly travel allowance, other monthly allowances and the annual bonus. The earnings item amounts need to be specified by the user by entering the appropriate values on the TaxCalc or Monthly sheets. The first three require monthly amounts to be entered and the bonus requires the annual amount.
Travel allowances are not typically taxed fully for monthly income tax (PAYE) purposes and an income tax inclusion percentage needs to be used to include only the specified percentage in the income tax calculations. This percentage needs to be specified on the Values sheet.
Note: If travel allowances are only paid to employees with a high percentage of business travel, the norm is to use an inclusion rate of 20% for travel allowances. If travel allowances are paid to employees who also have a lot of private travel, the inclusion percentage should be 80%. If in doubt, use the higher percentage of 80%.
Note: At the end of a tax year, most employees who receive a travel allowance need to submit their vehicle logbooks as part of their income tax return and the actual exempt portion of their travel allowance would then be determined based on the actual total business mileage travelled. The inclusion rate which is used in this template is therefore for monthly PAYE purposes and does not reflect the actual tax return amounts.
Pension Deduction
The monthly pension deduction amounts (if any) need to be entered by the user on the TaxCalc and Monthly sheets. Pension contributions are deductible for income tax purposes but limited to a maximum allowable percentage and/or maximum annual amount as specified on the Values sheet.
Medical Tax Credits
Medical aid tax credits are calculated as a rebate (therefore after tax payable has been calculated) and the calculation is based on the number of dependents which are included in a person's medical aid. The number of dependents needs to be specified by the user on the TaxCalc and Monthly sheets and should include the tax payer or primary member. The monthly allowed tax credit amounts per dependent are included on the Values sheet and these values are multiplied by the number of dependents to determine the amount of the tax credit.
Note: If medical tax credits are not applicable and should not be included in the income tax calculations, entering zero dependents will result in a zero medical tax credit amount.
Age
The tax payer age which is specified on the TaxCalc and Monthly sheets is used only in calculating which income tax rebate (primary, secondary or tertiary) is applicable to the tax payer. The minimum age and amount of each rebate category can be maintained on the Values sheet.
Unemployment Insurance Fund (UIF)
The rate and annual contribution limit for UIF calculation purposes is specified on the Values sheet. If you are in a region which does not have UIF, you can enter zero percent as the rate to exclude this salary deduction from the template calculations.
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 the UIF 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 UIF 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 which is 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 age and the number of medical aid dependents at the top of the sheet. The income tax rebate amount next to this section is calculated based on the age and the medical tax credits amount is calculated based on the number of dependents. Both of these amounts are annual amounts which are included in the annual income tax payable calculation.
User input is also required for the monthly earnings amounts and the monthly pension contributions. 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 all of the earnings columns.
Income Tax (PAYE) - 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.
Unemployment Insurance (UIF) - this is the calculation of the UIF salary deduction based on the maximum percentage and maximum contribution value which is specified on the Values sheet.
Total Deductions - the sum of the income tax, pension and UIF deductions.
Net Pay - this is the net amount paid to the employee and is the difference between the total earnings (gross pay) and total deductions.
Pay Period - the pay periods start from the first month where the gross pay is not nil. If you therefore 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.
Pension Tax Deduction - this is the allowable tax deduction for the pension contribution based on the maximum percentage and maximum amount values which are included on the Values sheet. These annualized amounts are deducted from the annual taxable earnings to calculate the annual tax payable.
Annual Taxable Income - this is the annualized total of all earnings. Note that the travel allowance is multiplied by the inclusion percentage on the Values sheet and the amount in this column may therefore differ from the annualized gross pay amount.
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 which are 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.
