Excel Skills | Income Tax Calculation Template

Instructions
www.excel-skills.com

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.

Page 3 of 10
Excel Skills | Income Tax Calculation Template
Instructions
www.excel-skills.com

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.

Page 4 of 10
Excel Skills | Income Tax Calculation Template
Instructions
www.excel-skills.com

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.

Page 5 of 10
Excel Skills | Income Tax Calculation Template
Instructions
www.excel-skills.com

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.

Page 6 of 10
Excel Skills | Income Tax Calculation Template
Instructions
www.excel-skills.com

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.

Help & Customization

If you experience any difficulty while using this template and you are not able to find the appropriate guidance in these
instructions, please e-mail us at [email protected] for assistance. This template has been designed with flexibility
in mind to ensure that it can be used in most business environments. If however you need an Excel based template that is
customized specifically for your business requirements, please e-mail our Support function and provide a brief explanation
of your requirements.

© Copyright

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.

Page 7 of 10
Salary & Income Tax Calculator
© www.excel-skills.com
Monthly Salary Amounts Annual Income Tax Calculation Monthly Salary Calculation

Basic Monthly Salary 50,000 Taxable Earnings: Earnings:

Basic Salary 600,000.00 Basic Salary 50,000.00
Travel Allowance 0 Travel Allowance (only 80%) - Travel Allowance -
Other Allowances - Other Allowances -
Other Allowances 0 Annual Bonus 50,000.00 Gross Pay 50,000.00
Total Taxable Earnings 650,000.00
Annual Bonus 50,000 Less: Taxable Pension - Deductions:
Taxable Income 650,000.00 Income Tax (PAYE) 12,582.21
Pension Deduction 0 Unemployment Insurance (UIF) 148.72
Total Taxation 184,706.50 Pension Fund -
Medical Dependents 0 Less: Rebate 14,220.00 Total Deductions 12,730.93
Less: Medical Tax Credits -
Your Age 30 Annual Tax Payable 170,486.50 Net Monthly Pay 37,269.07
Income Tax % 26.2% Annual Eqv - Monthly Pay 600,000.00
Annual Bonus Payment
On Annual Bonus 50,000.00
Onthis
thissheet:
sheet:
Our
Our salary
salary &
& income
income taxtax calculator
calculator calculates
calculates thethe annual
annual income
income tax
tax payable,
payable, monthly
monthly Less: Tax On Bonus 19,500.00
salary
salary including
including tax
tax and
and net
net pay
pay and
and anan annual
annual net
net bonus.
bonus. Only
Only the
the cells
cells with
with yellow
yellow cell
cell Less: UIF On Bonus -
backgrounds
backgrounds require
require user
user input.
input. All
All tax
tax &
& deduction
deduction calculations
calculations are
are based
based onon the
the tax
tax
tables Net Annual Bonus 30,500.00
tables &
& variables
variables which
which areare included
included onon the
the Values
Values sheet.
sheet.

Page 8 of 10
Salary & Income Tax Calculator PERIOD START AGE MED DEPENDENTS REBATE MED TAX CREDIT
Monthly Calculations 3/1/2019 30 0 14,220 0
© www.excel-skills.c 600,000 - - 50,000 650,000 170,487 1,785 - 172,271 477,729
Basic Monthly Other Annual Income Tax Unemployment Pension Total
Period Travel Allowance Gross Pay Net Pay
Salary Allowances Bonus (PAYE) Insurance (UIF) Fund Deductions
March-2019 50,000 - - - 50,000 12,582 149 - 12,731 37,269
April-2019 50,000 - - - 50,000 12,582 149 - 12,731 37,269
May-2019 50,000 - - - 50,000 12,582 149 - 12,731 37,269
June-2019 50,000 - - - 50,000 12,582 149 - 12,731 37,269
July-2019 50,000 - - - 50,000 12,582 149 - 12,731 37,269
August-2019 50,000 - - - 50,000 12,582 149 - 12,731 37,269
September-2019 50,000 - - - 50,000 12,582 149 - 12,731 37,269
October-2019 50,000 - - - 50,000 12,582 149 - 12,731 37,269
November-2019 50,000 - - - 50,000 12,582 149 - 12,731 37,269
December-2019 50,000 - - 50,000 100,000 32,082 149 - 32,231 67,769
January-2020 50,000 - - - 50,000 12,582 149 - 12,731 37,269
February-2020 50,000 - - - 50,000 12,582 149 - 12,731 37,269
On
Onthis
thissheet:
sheet:
Enter
Enter your
your monthly
monthly earnings
earnings and and pension
pension fund
fund deduction
deduction amounts
amounts in in the
the cells
cells with
with yellow
yellow
column
column headings.
headings. Also
Also specify
specify the
the period
period start,
start, age
age and
and medical
medical aid
aid dependents
dependents at at the
the top
top
of
of the
the sheet.
sheet. All
All other
other calculations
calculations areare automated.
automated. TheThe monthly
monthly calculations
calculations on on this
this sheet
sheet
are
are especially
especially useful
useful ifif the
the earnings
earnings amounts
amounts are
are not
not consistent
consistent from
from month
month to to month
month or or ifif
an
an annual
annual bonus
bonus is is paid.
paid.

Page 9 of 10
Income Tax Calculator Variables
© www.excel-skills.com
Income Tax Rates

Income Tax Brackets Tax Bracket Value Tax Rate Equivalent Value Effective Rate
From 0 to 195,850 195,850 18.0% 35,253 18.0%
From 195,850 to 305,850 305,850 26.0% 63,853 20.9%
From 305,850 to 423,300 423,300 31.0% 100,263 23.7%
From 423,300 to 555,600 555,600 36.0% 147,891 26.6%
From 555,600 to 708,310 708,310 39.0% 207,447 29.3%
From 708,310 to 1,500,000 1,500,000 41.0% 532,040 35.5%
From 1,500,000 and above 45.0%

Rebates Amount Min Age

Primary 14,220 0 On
Onthis
thissheet:
sheet:
All
All the
the default
default values
values which
which areare used
used inin the
the income
income tax
tax && salary
salary calculations
calculations need need to
to be
be specified
specified
Secondary 22,014 65
on
on this
this sheet.
sheet. This
This includes
includes thethe income
income taxtax rates
rates at
at the
the top
top of
of the
the sheet,
sheet, tax
tax rebate
rebate amounts,
amounts,
Tertiary 24,615 75 medical
medical tax credit amounts, unemployment insurance limit and percentage, pension fund tax
tax credit amounts, unemployment insurance limit and percentage, pension fund tax
deduction
deduction limits
limits and
and the
the travel
travel allowance
allowance inclusion
inclusion percentage.
percentage. All All these
these default
default values
values need
need to
to
Medical Aid Tax Credits
be
be updated
updated at at the
the start
start of
of aa new
new tax
tax year
year byby entering
entering the
the new
new values
values in in the
the cells
cells with
with yellow
yellow cell
cell
backgrounds.
backgrounds.
Tax payer & first dependent 310
Additional dependents 209

Unemployment Insurance Fund (UIF)

Contribution % 1.0%
Annual contribution limit 178,464

Pension Deduction
Maximum tax deduction value 350,000
Maximum tax deduction % 27.5%

Travel Allowance
Taxable portion of allowance 80.0%

Page 10 of 10