You do not need to know programming to understand a payroll calculation. Every result starts with basic arithmetic: multiply, divide, subtract, choose the correct tax band, and round at the appropriate step. The crucial skill is knowing which rule applies to which type of payment.

Before you start

These lessons explain withholding estimates, not a legal determination of an employee's entitlements or the tax ultimately owed. A correct result depends on residency, tax file number declarations, the reason employment ended, service dates, preservation age and prior payments. If the classification is wrong, the maths can be perfectly executed and still give the wrong answer.

Lesson contents

  1. Ordinary PAYG withholding and weekly equivalents
  2. HELP and study-loan withholding
  3. Hourly earnings, overtime and salary sacrifice
  4. Bonuses and backpay under Schedule 5
  5. Unused leave and employment termination payments
  6. How to build a reusable Excel workbook

1. Calculate ordinary PAYG withholding

Australia's ATO Schedule 1 uses published coefficient bands. For the selected withholding scale, the mathematical core is y = ax − b, where x is the specially rounded weekly-equivalent earnings, a and b are the coefficients in the applicable band, and y is rounded as specified by the ATO. The coefficients are different across tax-free-threshold and other employee declarations. They are not the same as annual marginal income-tax rates.

  1. Start with gross taxable earnings for the pay period.
  2. Convert to a weekly equivalent using the ATO Schedule 1 period-conversion steps.
  3. Add 99 cents to the whole-dollar weekly amount.
  4. Find the appropriate coefficient band for the employee's withholding scale.
  5. Calculate a × x − b, never below zero, and round to whole dollars as directed.
  6. Convert the weekly withholding back into the employee's actual pay period using the schedule.

Excel structure: use B2 for the fortnightly gross amount, B3 for the ATO scale, B4 for the weekly equivalent, and B5/B6 for the official coefficients. The following example shows the core algebra for a fortnightly employee. Check the full ATO rounding and conversion notes before using it for actual payroll.

B2 = 3000
B4 = INT(B2/2)+0.99
B5 = 0.3227       [example coefficient only—select the correct official band]
B6 = 185.1935     [example coefficient only—select the correct official band]
B7 = MAX(0,ROUND(B5*B4-B6,0))
B8 = B7*2

For an actual spreadsheet, do not manually re-use the sample coefficients for all incomes. Create a lookup table for every bracket from the ATO source, or use the ATO tax tables. This guide is not a withholding certificate.

2. Calculate HELP / STSL withholding

Schedule 8 uses a separate set of coefficient bands. It follows a similar weekly-equivalent process, but the withholding is a separate component added to normal PAYG withholding. The student's ultimate annual compulsory repayment is calculated under a different income-based process, so the amount withheld during the year might differ from their actual annual repayment.

  1. Apply the Schedule 8 threshold and coefficient table for the correct withholding scale.
  2. Calculate the additional weekly STSL component.
  3. Round and convert the component to the employee's pay frequency.
  4. Add the component to ordinary Schedule 1 PAYG withholding; do not replace PAYG with STSL.
STSL_WEEKLY = MAX(0,ROUND(STSL_A*WEEKLY_EQUIVALENT-STSL_B,0))
TOTAL_WEEKLY_WITHHELD = PAYG_WEEKLY + STSL_WEEKLY

Always check the latest official tables. STSL is not withheld separately from unused leave paid on termination under Schedule 7.

3. Build ordinary wages, loading and overtime

This is one of the simplest calculations to reproduce. Suppose a worker earns $40 an hour, works 38 ordinary hours and completes 4 overtime hours at time-and-a-half. They also receive a $70 taxable allowance.

  1. Ordinary wages: 38 × $40 = $1,520.
  2. Overtime: 4 × $40 × 1.5 = $240.
  3. Add a taxable allowance: $1,520 + $240 + $70 = $1,830 gross taxable earnings.
  4. Apply the correct PAYG schedule to $1,830 for the specified pay frequency.
  5. Subtract PAYG withholding and any other applicable withholding. Add genuine non-taxable reimbursements only after the taxable-pay calculation.

If the same worker receives 25% casual loading on their ordinary wages, that is $1,520 × 25% = $380 in this illustration. A prospective and valid $200 salary-sacrifice-to-super arrangement reduces the relevant taxable cash wages by $200. It is not automatically an additional reduction of ordinary-time earnings for compulsory super.

EXCEL:
B2 = 38             [ordinary hours]
B3 = 40             [hourly rate]
B4 = 4              [overtime hours]
B5 = 1.5            [overtime multiplier]
B6 = 70             [taxable allowances]
B7 = B2*B3          [ordinary wages = 1520]
B8 = B4*B3*B5       [overtime = 240]
B9 = B7+B8+B6       [taxable gross = 1830]

Actual casual loading, overtime multipliers and the superannuation ordinary-time-earnings treatment depend on the applicable award, agreement and facts. The calculator demonstrates arithmetic; it does not determine employment entitlements.

4. Work through a bonus or a back payment

A bonus cannot always be treated as if it were ordinary wages in a single pay period. Under ATO Schedule 5, Method A apportions an additional payment across relevant pay periods and compares withholding on regular wages with withholding on the increased equivalent amount.

  1. Calculate PAYG withheld on ordinary earnings.
  2. Divide the additional payment by the number of pay periods to which the payment relates; disregard cents as directed.
  3. Add that apportioned amount to an ordinary pay period.
  4. Calculate withholding again on the increased pay amount.
  5. Subtract the ordinary withholding and multiply the difference by the number of apportioned periods.
  6. Apply the separate study-loan component and the maximum combined additional-payment withholding rule.
EXCEL:
B2 = 1400                 [normal weekly wages]
B3 = 900                  [bonus]
B4 = 52                   [periods over which apportioned]
B5 = INT(B3/B4)           [additional weekly amount = 17]
B6 = PAYG(B2)             [lookup: normal PAYG]
B7 = PAYG(B2+B5)          [lookup: increased PAYG]
B8 = MAX(0,(B7-B6)*B4)    [additional PAYG component]

The PAYG(...) notation above is a placeholder for your own Schedule 1 coefficient lookup, not a built-in Excel function. Method B(i) recalculates earlier affected periods separately; Method B(ii) accounts for average year-to-date earnings and prior Method B(ii) additional payments. Read the official Schedule 5 ↗ for the exact procedure and interaction of bonus and back-pay categories.

5. Work out termination and unused-leave withholding

Unused leave and employment termination payments are two different tax categories. Do not combine them into a single tax calculation. First classify each payment, then calculate its withholding.

5A. Unused annual leave and long service leave — Schedule 7

Schedule 7 distinguishes ordinary termination from genuine redundancy, invalidity and approved early retirement, and it distinguishes leave by accrual date. Some components are withheld at a fixed 32% rate; others use a marginal-rate method that effectively spreads the leave payment across 12 months. Old long-service-leave accruals have additional rules.

Worked example using the ATO's 2026 Norman scenario: Normal weekly gross pay is $1,155. Unused long service leave includes $780 earned before 16 August 1978, $6,420 earned from 16 August 1978 to 17 August 1993, and $14,820 earned after 17 August 1993. The ATO Schedule 7 calculation proceeds as follows:

  1. Only 5% of the pre-1978 component enters the marginal calculation: $780 × 5% = $39.
  2. Tax at 32% on the 1978–1993 component: $6,420 × 32% = $2,054.40, rounded to $2,054 under the schedule.
  3. Combine the post-1993 component with the applicable old-leave component: $14,820 + $39 = $14,859.
  4. Divide by 52 weeks and drop cents: INT($14,859 / 52) = $285.
  5. Baseline weekly PAYG on $1,155 = $188 (using the official withholding table).
  6. Weekly PAYG on $1,155 + $285 = $1,440 is $279.
  7. Marginal withholding = ($279 − $188) × 52 = $4,732.
  8. Total unused-leave withholding = $4,732 + $2,054 = $6,786.
EXCEL:
B2 = 1155       [normal weekly gross]
B3 = 780        [pre-16/8/1978 long service leave]
B4 = 6420       [1978–1993 long service leave]
B5 = 14820      [post-17/8/1993 long service leave]
B6 = B3*5%                  [39]
B7 = ROUND(B4*32%,0)        [2054]
B8 = B6+B5                  [14859]
B9 = INT(B8/52)             [285]
B10 = 188                   [official PAYG on 1155]
B11 = 279                   [official PAYG on 1440]
B12 = (B11-B10)*52          [4732]
B13 = B7+B12                [6786]

For a genuine redundancy or qualifying special termination, unused annual leave and most post-1993 leave components generally use the separate 32% rule. The employer must correctly establish the reason for termination first. Also note the special treatment for certain normal-termination post-1993 unused-leave amounts below $300.

5B. Employment termination payments (ETPs) — Schedule 11

ETPs may contain tax-free and taxable components. Withholding is applied only to the taxable component. For 2026–27, the ETP cap is $270,000 and the whole-of-income cap is $180,000. Some qualifying payments use only the ETP cap, while ordinary severance or a golden handshake can be limited by the smaller of both caps.

For an eligible life-benefit ETP paid to an Australian resident with a TFN within the allowable time, the concessional withholding rate is generally 32% below preservation age or 17% at or above preservation age, with 47% above the relevant cap.

Worked example from the ATO's 2026 Chris scenario: Chris received $100,000 of other taxable payments, and his taxable ETP was $120,000. He is below preservation age. The ETP is an ordinary severance payment subject to both caps.

  1. Whole-of-income cap = MAX(0, $180,000 − $100,000) = $80,000.
  2. ETP cap is $270,000, so the smaller cap is MIN($270,000, $80,000) = $80,000.
  3. Taxable ETP within the concessional cap = MIN($120,000, $80,000) = $80,000.
  4. ETP beyond cap = $120,000 − $80,000 = $40,000.
  5. Below-cap withholding = $80,000 × 32% = $25,600.
  6. Above-cap withholding = $40,000 × 47% = $18,800.
  7. Combined withholding = $44,400. The amount remaining from this $120,000 taxable ETP is $75,600.
EXCEL:
B2 = 120000                 [taxable ETP]
B3 = 100000                 [other taxable pay]
B4 = 270000                 [ETP cap in 2026–27]
B5 = 180000                 [whole-of-income cap]
B6 = MAX(0,B5-B3)           [80000]
B7 = MIN(B4,B6)             [80000]
B8 = MIN(B2,B7)             [80000]
B9 = B2-B8                  [40000]
B10 = ROUND(B8*32%+B9*47%,0) [44400]
B11 = B2-B10                [75600]

A genuine redundancy's tax-free limit is calculated separately. The 2026–27 base is $13,598 plus $6,801 for each completed year of service. For five completed years: $13,598 + (5 × $6,801) = $47,603. That does not by itself prove a payment is legally genuine redundancy; eligibility and component classification come first.

Important: Age, residency, ETP code, multiple termination payments, prior cap use and payments made outside 12 months can change the outcome. Death-benefit ETPs, non-residents and people without a TFN require different rules. The calculator deliberately does not invent outputs for those unsupported cases.

6. Make an audit-friendly Excel payroll workbook

Instead of burying formulas in one cell, build a workbook containing four worksheets:

  1. Inputs: annual income year, period, gross wages, relevant employee declarations, termination category, and each payment component. Add data-validation lists to prevent unknown categories.
  2. Rates_2026_27: copy the official withholding coefficient tables, ETP caps and levy rules from the ATO publication. Include the official URL, date checked and legal status in cells at the top.
  3. Working: one calculation per row, with descriptive labels. Display intermediate weekly equivalents, selected coefficient bands, floor/round steps, cap comparisons and incremental tax separately.
  4. Results: total taxable gross, ordinary PAYG, STSL, extra-payment withholding, amount after withholding, and the assumptions and exclusions.

Useful Excel functions include INT for dropping cents, ROUND(...,0) for whole-dollar rounding, MAX(0,...) for a floor, MIN(...) for the lower cap, and XLOOKUP or a controlled bracket lookup for tax coefficients. Test every sheet with the ATO's published example figures before trusting it.

A spreadsheet can make the rules visible, but payroll software must additionally handle employer obligations, awards, recordkeeping, Single Touch Payroll reporting and complex statutory exceptions. Treat this workbook as an educational exercise rather than a production payroll system.

Official sources and related learning

Working Wealth editorial content. General financial education only, not personal financial, legal or tax advice. Independently verified professional editorial review is pending. Calculations and thresholds may change with legislation.