Tax Calculation

[Maps]

Good day.
I made a similar post with Excel Forum on the 11 May 2026 and it has not been solved.
Can you assist me with a matching formula to the taxman's calculation.
There is an annual tax table that has to be used get the tax payable after deducting the tax rebate and the tax deductible.
The tax calculation always differs from my calculated tax.

TAX CALCULATION PER TAXMAN'S PORTAL IS 634.81 which is presumably correct.

Whereas when I calculate it manually, it gives 652.99

I have every details on the spreadsheet including the tax calculation from the taxman's portal. The calculation is based on the number of days per month with no exception.
Can someone help me.

 

[shamimarita]

Hello Maps,

I checked the workbook you attached. The issue seems to be related to the annualization/proration calculation rather than the tax table itself.

Your sheet currently returns 652.99, but the taxman portal gives 634.81 because the portal likely:

• annualizes the salary using an exact daily factor,
• applies the deductible before annual tax computation,
• subtracts the rebate afterward, and
• performs intermediate rounding at specific stages.

Since your payroll is based on the number of days in the month, even small rounding differences can affect the final PAYE amount significantly.

You can try structuring the calculation in this order:

• Gross Pay
• Less deductible (5% capped at 215)
• Annualize taxable income
• Apply tax bracket formula
• Subtract annual rebate
• Divide back to monthly/daily PAYE

Also, check whether the portal uses:

• 365 days vs 360 days,
• monthly normalization,
• rounded taxable income before slab lookup.

The tax table itself appears correct. The mismatch is most likely caused by the annualization logic and rounding sequence.

 

[Maps]

Good morning Shamima.
Thank you for getting back to me.
Yes I agree that it has to do with annualization/proration.
The portal uses the actual number of days in the year which is 365 or 366 if it is a leap year..
Actually the portal tax is 652.99 not 634.81. I got the figures the other way round. I apologise.
Is it possible to work the figure backward with a view to figure out how the portal gets 652.99.

 

[shamimarita]

Good morning.

Thank you for the clarification, and no problem at all. That makes much more sense now.

Since the portal result is actually 652.99, your manual calculation is likely very close to the correct logic already. Yes, it should be possible to work backward from the final figure to identify the exact annualization and rounding method used by the portal.

Because the portal uses the actual number of days in the year (365/366), the difference will probably come from one of these areas:

• Exact daily-rate annualization formula
• Whether the deductible is applied before or after annualization
• Intermediate rounding to 2 decimals at each stage
• How the annual rebate is prorated
• Whether taxable income is rounded before applying the tax slab

In many payroll systems, even a small rounding adjustment during annualization can change the PAYE by several Rand.

One approach would be to:

• Start from the portal PAYE value (652.99)
• Reconstruct the annual tax before rebate
• Add back the rebate
• Reverse the slab formula
• Compare the resulting annual taxable income with your worksheet value

That should help identify the exact point where the variance occurs.

 

[Maps]

Thank you.
Can you please use the spreadsheet that I submitted as a practical example for me to understand.

 

[shamimarita]

Hello Maps,

Yes, using your spreadsheet as the practical example, the portal value of 652.99 can be matched.

In your sheet:

• Gross Salary = 6849.81
• Tax Deduction = 215.00
• Taxable Pay = 6849.81 - 215 = 6634.81
• Days in Month = 30
• Days in Year = 365

So:
Annualized Taxable Income:
= 6634.81 / 30 × 365
= 80723.52

Since this falls in the first tax bracket, the annual tax is:
80723.52 × 20% = 16144.70

Less rebate:
16144.70 - 8200 = 7944.70

Then prorate back to the month:
7944.70 / 365 × 30 = 652.99

So, in your spreadsheet, the formula should follow this order:

• Deduct the 5% deduction capped at 215 first
• Annualize the taxable amount using actual days in the year
• Apply the tax table
• Subtract the annual rebate
• Prorate the result back to the current month

For this specific example, the formula logic is:
=ROUND(((((C16-MIN(C16*5%,215))/C22)*365)*20%-8200)/365*C22,2)

This returns 652.99.

 

[Maps]

Thanks once again Shamimarita for the formula, it is perfect.
The challenge now is if you have 100 employees with different rates and the tax falling into different tax brackets, and the ages, others falling on the rebate of 10900 instead of the 8200.
Is it possible to create a formula that can accommodate the different categories?

 

[shamimarita]

Hello Maps,

Yes, it is possible. You can use one dynamic formula that checks the employee’s salary bracket and age automatically.

For your current spreadsheet, use this formula in the PAYE/tax cell:
=LET(Gross,$C$16,DOB,$C$18,PayDate,$C$20,DaysMonth,$C$22,DaysYear,DATE(YEAR(PayDate)+1,1,1)-DATE(YEAR(PayDate),1,1),Deduct,MIN(Gross*5%,215),AnnualIncome,(Gross-Deduct)/DaysMonth*DaysYear,Age,DATEDIF(DOB,PayDate,"Y"),Rebate,IF(Age<60,$C$12,$C$13),RowNo,MATCH(AnnualIncome,$A$5:$A$8,1),Fixed,INDEX($C$5:$C$8,RowNo),Rate,INDEX($D$5:$D$8,RowNo),Excess,INDEX($A$5:$A$8,RowNo),ROUND(MAX(0,((Fixed+(AnnualIncome-Excess)*Rate)-Rebate)/DaysYear*DaysMonth),2))

This formula will automatically handle:

• different gross salaries,
• different tax brackets,
• employees below or above 60 years,
• 365/366 days depending on the year,
• and monthly proration.

For multiple employees, replace $C$16, $C$18, $C$20, and $C$22 with the employee row references, then copy the formula down.

 

[Maps]

Hello Shamima.
Thank you for your assistance into my query. The formula is perfect.
Just one or two queries pertaining to this:
1. How do you use the same formula to calculate tax for the ten tax periods from July to April 2026. The current tax year runs from July to June. I have added the figures on column D on my initial spreadsheet showing the year to date earning and the tax deductible.
2. Also help me with the formula for calculating the tax deductible value. This must shown separately.
3. Finally, what happens to the formula on the number of days if the overlap is a leap year like 2027/2028

 

[Maps]

Good day Shamima.
Can you also show a formula for the rebate value on a different cell.

 

[shamimarita]

Hello Shamima.
Thank you for your assistance into my query. The formula is perfect.
Just one or two queries pertaining to this:
1. How do you use the same formula to calculate tax for the ten tax periods from July to April 2026. The current tax year runs from July to June. I have added the figures on column D on my initial spreadsheet showing the year to date earning and the tax deductible.
2. Also help me with the formula for calculating the tax deductible value. This must shown separately.
3. Finally, what happens to the formula on the number of days if the overlap is a leap year like 2027/2028

Hello Maps,

Yes, this can be done. Since your tax year runs from July to June, use the tax period start and end dates in your sheet.

For the year-to-date PAYE calculation using your Column D values, use this formula:

=LET( YTDGross,$D$17, YTDDeduct,$D$19, DOB,$C$21, PayDate,$C$23, TaxStart,$C$31, TaxEnd,$D$31, DaysWorked,PayDate-TaxStart+1, DaysYear,TaxEnd-TaxStart+1, AnnualIncome,(YTDGross-YTDDeduct)/DaysWorked*DaysYear, Age,DATEDIF(DOB,PayDate,"Y"), Rebate,IF(Age<60,$C$12,$C$13), RowNo,MATCH(AnnualIncome,$A$5:$A$8,1), Fixed,INDEX($C$5:$C$8,RowNo), Rate,INDEX($D$5:$D$8,RowNo), Excess,INDEX($A$5:$A$8,RowNo), ROUND(MAX(0,((Fixed+(AnnualIncome-Excess)*Rate)-Rebate)/DaysYear*DaysWorked),2) )
With your example:

• YTD Gross = 68,515.71
• YTD Deductible = 2,060
• Tax period = 1 July 2025 to 30 April 2026
• Days worked = 304
• Tax year days = 365

This returns 6,461.55, which matches the portal value shown in your sheet.

For the tax deductible value separately, if you are calculating for the current month only:
=MIN(C17*5%,215)
For year-to-date, if each month has a separate salary column, use:
=SUMPRODUCT(--(MonthlyGrossRange*5%&gt;215),215)+SUMPRODUCT(--(MonthlyGrossRange*5%&lt;=215),MonthlyGrossRange*5%)
Or, in a simpler Excel 365 version:
=SUM(MAP(MonthlyGrossRange,LAMBDA(x,MIN(x*5%,215))))
Regarding leap years, the formula above already handles it because:
=TaxEnd-TaxStart+1
So if the tax year is 2027/2028 and includes 29 February 2028, Excel will automatically return 366 days instead of 365.

 

[shamimarita]

Good day Shamima.
Can you also show a formula for the rebate value on a different cell.

Good day.

You can place the rebate formula in a separate cell and then reference that cell inside the PAYE formula.

For example, if:

• Date of Birth is in C21
• Pay Date is in C23

Then the rebate formula can be:

=IF(DATEDIF(C21,C23,"Y")<60,8200,IF(DATEDIF(C21,C23,"Y")<75,10900,12100))

This formula will automatically return:

• 8200 → under 60
• 10900 → age 60 to 74
• 12100 → age 75 and above

You can place this in a separate cell, for example, C14, and then inside the PAYE formula simply use:

Rebate,$C$14

instead of embedding the rebate logic directly into the main formula.

This makes the spreadsheet much easier to maintain and audit.

 

[Maps]

Thank you once again Shamima.

I need the rebate to be equivalent to the current month which April 2026 (column C) and also show it as a year to date value on Column D, IF IT IS POSSIBLE.

On the tax deductible both formulas don't work. The first one reacts as though it's a TEXT and the second one gives #NAME?

 

[shamimarita]

Hello Maps,

It is possible to show both: the rebate applicable for the current month (Column C), and
the year-to-date rebate value (Column D).

For the monthly rebate (April 2026 in Column C), use:
=IF(DATEDIF(C21,C23,"Y")<60,8200,IF(DATEDIF(C21,C23,"Y")<75,10900,12100))/365*C22

Where:

• C21 = Date of Birth
• C23 = Pay Date
• C22 = Number of days in the current month/pay period

This prorates the annual rebate to the current month.

For the Year-To-Date rebate (Column D), use:
=IF(DATEDIF(C21,C23,"Y")<60,8200,IF(DATEDIF(C21,C23,"Y")<75,10900,12100))/365*(C23-C31+1)

Where:

• C31 = Tax year start date (e.g. 01-Jul-2025)
• C23 = Current pay date

This automatically accumulates the rebate from July up to April 2026.

Regarding the deductible formulas:
The first formula likely appeared as TEXT because the cell format is currently Text. Change the cell format to:
General
or
Number

Then re-enter the formula manually.

Use this corrected deductible formula:
=MIN(C17*5%,215)

For the second formula, the #NAME? error happens because functions like MAP() and LAMBDA() are only available in newer Excel 365 versions.

Use this universal version instead:
=SUMPRODUCT((MonthlyGrossRange*5%<=215)*(MonthlyGrossRange*5%))+SUMPRODUCT((MonthlyGrossRange*5%>215)*215)

Replace MonthlyGrossRange with your actual salary range, for example:
=SUMPRODUCT((E5:N5*5%<=215)*(E5:N5*5%))+SUMPRODUCT((E5:N5*5%>215)*215)

This will correctly calculate the cumulative deductible for all months.