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 and specifying the appropriate rebates and medical aid deduction amounts.
Monthly - this sheet enables users to perform monthly income tax calculations based on variable monthly
remuneration amounts and also takes medical aid deductions into account in the calculation of the appropriate monthly income tax amounts.
Tax Brackets Setup
Our default tax bracket setup provides for six tax brackets in the cell range from cell A3 to cell E9. All the cells with a light blue 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 19 to 21 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 9. Then copy the formulas from one of the existing cells in columns A, D and E (the cells with the light blue 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 need to specify the total rebates amount in cell B11. This amount is deducted after calculating the income tax that is applicable to the specified remuneration amount and also has the effect of reducing the effective income tax rate.
Medical aid deductions have also been added to the template. In order to calculate the correct monthly & annual medical aid deductions, you need to specify the number of dependents and the medical aid deduction rates that are applicable to the tax payer & first dependent and the additional dependents. These deductions are deducted from the monthly & annual income tax amounts because the medical aid deductions should be deducted for pay-as-you-earn (PAYE) purposes.
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 B21. The annual remuneration, annual income tax, monthly income tax, medical aid deductions, net income tax, annual & monthly net remuneration and the effective income tax percentage are all calculated based on the amount that is specified in this cell.
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 a Start Date in cell B3 in order to change the month end periods in column A.
An annual gross pay equivalent, the cumulative income tax amount and monthly income tax amount are calculated based on the monthly remuneration amounts that are entered in column B. The medical aid deductions are calculated based on the number of dependents which is specified in column F and the medical aid deduction rates that are specified on the TaxCalc sheet.
The calculations on this sheet 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.
This template remains the intellectual property of www.excel-skills.com and is protected by international copyright laws. Any publication or distribution of this template outside the scope of the permitted use of the template is expressly prohibited. In terms of the permitted use of this template, only the distribution of the template to persons within the same organisation as the registered user or persons outside the organisation who can reasonably be expected to require access to the template as a direct result of the use of the template by the registered user is allowed.
Subsequent distribution of the template by parties outside of the organisation is however expressly prohibited and represents an infringement of international copyright laws.