Ask five payroll people what a Sunday shift costs and the answers will span from 125 percent to 200 percent of the base rate. Ask about a public holiday and the range narrows to 225 percent and 250 percent. Neither answer is a national standard, because Australia does not have one. The Fair Work Ombudsman states the position plainly: the penalty rates that apply depend on the award covering the employee, and where an enterprise agreement applies, the agreement carries its own numbers.

That single fact shapes how the spreadsheet has to be built. The multipliers belong in an input table, held outside the formulas, and each shift has to answer three questions: which band the hours sit in, what percentage applies to that band, and whether the hours are ordinary or overtime.

The figures below come from six modern awards at 1 July 2026, along with the two places where casual and salaried arrangements change the arithmetic, and a working Excel layout that turns a timesheet into a gross pay figure that can be reconciled to a payslip. If you want the dollar figure for a specific shift before reading any of it, the wages calculator takes the same inputs and returns the gross pay breakdown, a take-home estimate and super.

What the awards actually pay for ordinary hours

Rates are percentages of the minimum hourly rate for full-time and part-time adults, read from each award's schedule of rates effective 1 July 2026. Saturday is the tightest band, with four of the six awards at 125 percent, nurses at 150 percent, and building construction paying Saturday overtime at 150 percent for the first two hours and 200 percent after that. Sunday is where the system diverges, spanning 75 percentage points from the Fast Food level 1 rate to the Clerks and Building awards. Public holidays cluster into two camps, 200 percent in nursing and 225 to 250 percent in the rest.

The spread matters for anyone costing a roster. A business comparing its Sunday rate against a competitor in another industry is comparing two different award instruments, and a model built on a single house multiplier will misprice every second Sunday it touches.

Overtime keeps a more consistent shape

The staged structure repeats across the system, which is why "time and a half, then double time" is a reasonable default to type into a template. The differences sit in the detail. Retail runs the first band for three hours where the others run two. Hospitality lifts the weekday rate to 200 percent from midnight Friday through to midnight Sunday, so the weekend loading starts before the Saturday shift does. Hospitality pays 225 percent for public holiday overtime while the other five pay 250 percent. Nurses pay 200 percent for public holiday hours worked inside an ordinary shift and 250 percent when the same hours fall outside ordinary hours.

Two alternatives to paying overtime sit beside the tables, and both belong in the model as options. Some awards allow paid time off in lieu of overtime pay, taken under a written agreement, and the Retail Award sets the exchange at 1.5 hours of paid time off for each overtime hour worked at 150 percent, so two overtime hours accrue three hours off. Cashing out unused time off in lieu sits outside ordinary time earnings, which keeps the super line clean when the employee takes the time.

Shiftworkers need a branch of their own. The Retail Award pays shiftwork on a Sunday at 175 percent of the minimum hourly rate for full-time and part-time employees and 200 percent for casual employees, against 150 percent for non-shiftworkers. The Clerks Award pays its shiftworkers 115 percent for afternoon and night shift, 130 percent for permanent night shift and 150 percent for Saturday, Sunday or public holiday work. A single rate table applied across a mixed workforce will misprice every shiftworker on the roster, in either direction.

An annualised salary changes the picture again. Under the Professional Employees Award, from 16 September 2023, employees paid at least 25 percent above the minimum award wage for their classification are not entitled to overtime or penalty rates at all, and the award's own penalty schedule runs at 125 percent Monday to Saturday before 6am and after 10pm, 150 percent on Sundays and 150 percent on public holidays. A salary-based model that ignores the 25 percent threshold can either overpay a compliant arrangement or understate a liability.

Casual loading and penalty rates combine two different ways

The casual loading is commonly 25 percent, and how it meets the penalty rate is not uniform.

Most awards add the two. General Retail level 1 has a minimum hourly rate of $27.81, a casual ordinary rate of $34.76 (125 percent), a casual Sunday rate of $48.67 (175 percent) and a casual public holiday rate of $69.53 (250 percent). Each casual figure is the full-time percentage plus 25 points. Clerks follows the same pattern, with casual Sunday at 225 percent against 200 percent for permanent staff, and casual public holiday at 275 percent against 250 percent. Fast Food and Hospitality add the loading in the same way.

The Nurses Award multiplies instead. Clause 21 pays a casual 150 percent of the casual hourly rate for Saturday ordinary hours and 175 percent of the casual hourly rate on Sundays, so the loading is already inside the base figure and the penalty percentage applies on top of it.

The gap is easy to see on a retail Sunday. A loaded rate of $34.76 multiplied by the 150 percent permanent Sunday penalty gives $52.14 an hour, while the award specifies $48.67. That is a seven percent overstatement on every casual Sunday hour, and it compounds across a roster. Run that shift through the wages calculator with casual loading on and you get the award figure rather than the overstated one, which is the quickest way to see the size of the error before rebuilding anything. The lesson for the spreadsheet is the same in both cases: store the percentage the award actually specifies for that employee type, and treat the loading as one of the inputs that produces it.

Four inputs to settle before writing a formula

Ordinary hours. Full-time ordinary hours are 38 a week under most awards, and the Retail Award allows the average to be worked across two, three or four weeks, or over a longer period agreed between the employer and the employee. A time and attendance export that shows a 40 hour week has already mixed ordinary hours with overtime.

The overtime trigger. Most awards trigger overtime on hours outside the ordinary span or beyond 38 hours in a week. The Clerks Award also triggers it after 10 ordinary hours in a day. A weekly test alone will miss daily overtime in that award. Part-time employees carry their own threshold, and the Clerks Award triggers overtime on hours beyond the agreed part-time hours as well as beyond the weekly figure.

Coverage. Full-time, part-time and casual employees each carry different percentages and thresholds. Salaried staff sit on an annualised wage arrangement, which has to leave the employee no worse off than the award, or above the high income threshold of $190,100 from 1 July 2026 with a written guarantee of annual earnings, which is the formal step that removes award application. Paying above the threshold without the guarantee leaves the award in force.

The overtime and penalty interaction. The Fair Work Ombudsman treats the higher rate as the one that applies where overtime hours also attract a penalty rate, and several awards write the outcome into the clause itself, as Hospitality does with weekend overtime at 200 percent. Multiplying an overtime multiplier by a penalty multiplier is the mistake to avoid, because it produces percentages that appear in no award.

The spreadsheet: a rate table, banding, and one SUMPRODUCT

Keep the percentages in a table and the hours in a shift log, and the pay figure becomes arithmetic.

Rate table (on its own sheet, one row per band)
Band              Percent   Rate
Ordinary            100%    =$B$2*C3
Overtime early      150%    =$B$2*C4
Overtime late       200%    =$B$2*C5
Saturday            125%    =$B$2*C6
Sunday              150%    =$B$2*C7
Public holiday      225%    =$B$2*C8

Banding handles the staged rates without a nested IF:

=MIN(OvertimeHours, 2)
=MAX(OvertimeHours - 2, 0)
=MAX(SUM(OrdinaryHours) - 38, 0)

The percentage comes from the table, looked up by band name:

=INDEX(RateTable[Rate], MATCH(BandName, RateTable[Band], 0))
=XLOOKUP(BandName, RateTable[Band], RateTable[Rate])

Gross pay for the period is one line:

=SUMPRODUCT(HoursByBand, RateByBand)

Two more formulas make the model operational. The day label that drives the band comes from a date, with a holiday table tested first:

=TEXT(ShiftDate, "ddd")
=IF(COUNTIF(HolidayDates, ShiftDate) > 0, "Public holiday", TEXT(ShiftDate, "ddd"))

And the reconciliation line that turns the model into a control:

=SUMPRODUCT(HoursByBand, RateByBand) - GrossPaid

A residual here is a banding error, a missed penalty band, or a payroll adjustment that belongs in the reconciliation column. One row per shift, hours and rate in separate columns, and a single percentage table per employee type covers the structure. Working with Excel Tables and structured references keeps the formulas readable as bands are added, and the percentages sit in one place when the annual wage review lands.

Public holidays are a state dataset

A fixed national list of dates will fail as soon as a state adds a substitute day. The Fair Work Ombudsman's calendar shows the differences: Boxing Day falls on a Saturday in 2026 with an additional public holiday on Monday 28 December in most states and territories, Christmas Eve is a part-day holiday from 7pm in South Australia and the Northern Territory and from 6pm in Queensland, and regional variation applies to the Royal Queensland Show in the Brisbane area, the Melbourne Cup in parts of regional Victoria, the King's Birthday in parts of regional Western Australia, the Royal Hobart Show and Recreation Day in Tasmania.

Several awards also allow an employer and employee to agree on a substitute day for a public holiday, which shifts the band to the substitute date and removes it from the original one. Build the holiday list as a table with Date, State and Name, and key the lookup on the employee's state as well as the date. A weekday rule such as "Sunday work is always at the Sunday rate" will price a substituted public holiday on a Monday at the wrong band, and December and January are exactly the months where that error is most expensive.

Super and tax sit outside the penalty arithmetic

Overtime payments are not ordinary time earnings and not qualifying earnings, provided the employee's ordinary hours are clearly identified in an award or agreement, so the super guarantee does not apply to the overtime component. Casual loading sits in both categories. Annual leave loading that is clearly linked to a lost opportunity to work overtime also sits outside. The practical effect is a super line driven by ordinary hours and casual loading, with the overtime bands excluded.

Payday super from 1 July 2026 moves the payment into each pay cycle, so a model that accrues super quarterly no longer matches the bank account. The ExcelWhiz wages calculator applies the same multipliers and returns a gross pay breakdown, a take-home estimate and a super figure worked out on ordinary time earnings, so the overtime bands drop out of the super line in the same way they do here.

Tax is the last input, and there is no separate overtime rate. From 1 July 2026 resident rates run at 15 percent from $18,201 to $45,000, 30 percent to $135,000, 37 percent to $190,000 and 45 percent above that, plus the 2 percent Medicare levy. A big week is annualised by the withholding tables, so a payslip in a heavy overtime month withholds more than the year will owe, and the difference returns at assessment. A timesheet model that reports both the pay period figure and the annualised figure shows the employee why the two numbers differ.

Records that support the calculation

Record keeping belongs inside the same worksheet as the calculation. The Fair Work Ombudsman sets out the hours that must be recorded for employees whose pay depends on them, including hours worked beyond 38 in a week and hours worked before 6am or after 10pm. A shift log holding date, start, finish, unpaid break, hours and the band label produces the record and the pay figure from one source, which means an audit or a wage enquiry can be answered from the same sheet that priced the roster.

Where to confirm the rate

Four sources settle the multipliers. The Fair Work Ombudsman Pay and Conditions Tool returns the rate for a selected award. The pay guides on the same site provide downloadable schedules for each award. The award text itself, published at awards.fairwork.gov.au under the award code, carries the clauses and the schedule of rates, and it is the only source that settles an ambiguous band. Enterprise agreements sit with the Fair Work Commission, and where one applies, its rates replace the award figures.

The multipliers default to the common award levels in the wages calculator, so a retail casual Sunday prices at 175 percent with casual loading on, and an award that applies the penalty to the casual rate instead can be entered as a combined percentage. A roster that runs every week also has to land in the cash forecast, because the penalty load is what moves the weekly wage bill, and that is the number the weekly cash flow forecast picks up. The pattern to take into a build is that overtime keeps a common structure while day-based penalties do not, so a template with editable percentages and a real holiday table answers more questions than a workbook with hard-coded multipliers. The percentages move every July with the annual wage review, and the structure underneath them rarely does.

Related reading

Run the numbers without building anything:

• All ExcelWhiz calculators — 15 free tools covering pricing, break-even, cash gap, valuation and more

Build it yourself, or extend the model:

• Automating Payroll
• Excel for Rostering
• Weekly Time Tracker Template
• Excel for HR
• Australian Tax Models in Excel