[Solved] Tax Calculation
- Thread starter Maps
- Start date
Hello Maps,
I’m glad the formula is working correctly in Office 365. Since the LET function is not available in older versions of Excel, you can use the same calculation by replacing the variable names with their actual cell references:
=ROUND(IF(DATEDIF(C21,C23,"Y")<60,8200,IF(DATEDIF(C21,C23,"Y")<75,10900,12100))/(D31-C31+1)*MAX(0,C23-MAX(C31,C24)+1),2)
This formula follows the same logic as the previous LET formula:
The MAX(C31,C24) part ensures that the rebate calculation starts from the later of the tax-year start date or the employee’s engagement date. Therefore, if the tax year starts on 1 July but the employee starts on 1 August, no rebate will be calculated for July.
This version should work in older Excel versions that support DATEDIF, IF, and ROUND.
I’m glad the formula is working correctly in Office 365. Since the LET function is not available in older versions of Excel, you can use the same calculation by replacing the variable names with their actual cell references:
=ROUND(IF(DATEDIF(C21,C23,"Y")<60,8200,IF(DATEDIF(C21,C23,"Y")<75,10900,12100))/(D31-C31+1)*MAX(0,C23-MAX(C31,C24)+1),2)
This formula follows the same logic as the previous LET formula:
- C21 = Date of Birth
- C23 = Pay Date
- C24 = Employee Engagement/Start Date
- C31 = Tax Year Start Date
- D31 = Tax Year End Date
The MAX(C31,C24) part ensures that the rebate calculation starts from the later of the tax-year start date or the employee’s engagement date. Therefore, if the tax year starts on 1 July but the employee starts on 1 August, no rebate will be calculated for July.
This version should work in older Excel versions that support DATEDIF, IF, and ROUND.
Hello Maps,
You are most welcome. Thanks for your feedback and appreciation, it really means a lot to me.
You are most welcome. Thanks for your feedback and appreciation, it really means a lot to me.
Good morning Shamimarata.
Trusting you are well.
I just need you to check if the the year to day tax is correct.
I have an employee who earns 4500 on the same tax principle. Her date of birth is 13/12/1992 and her date of engagement is 01/09/2026..
Her tax is 226.03 per our formula.
On the year to date earning less deductible of 215 and a rebate of 673.97 it shows a ZERO tax.
Could you please help.
Trusting you are well.
I just need you to check if the the year to day tax is correct.
I have an employee who earns 4500 on the same tax principle. Her date of birth is 13/12/1992 and her date of engagement is 01/09/2026..
Her tax is 226.03 per our formula.
On the year to date earning less deductible of 215 and a rebate of 673.97 it shows a ZERO tax.
Could you please help.
Hello Maps,
I checked this again, taking into account that you are using an older version of Excel.
The YTD rebate of 673.97 is correct. Since the employee started on 01/09/2026, the rebate should only be calculated from her engagement date:
8200 / 365 × 30 = 673.97
The reason the YTD tax is showing ZERO is that the previous YTD tax calculation counts the working days from the beginning of the tax year. For this employee, it must start from 01/09/2026, not from 01/07/2026.
In the YTD calculation, change the number-of-days part from:
=C23-C31+1
to:
=MAX(0,C23-MAX(C31,C24)+1)
where:
Since this employee joined on 1 September 2026 and September is her first tax period, her YTD earnings and tax calculation cover only September. Therefore, if the current-period tax is 226.03, the YTD tax should also be 226.03, rather than zero.
Please make this change in the YTD tax formula and check the result.
I checked this again, taking into account that you are using an older version of Excel.
The YTD rebate of 673.97 is correct. Since the employee started on 01/09/2026, the rebate should only be calculated from her engagement date:
8200 / 365 × 30 = 673.97
The reason the YTD tax is showing ZERO is that the previous YTD tax calculation counts the working days from the beginning of the tax year. For this employee, it must start from 01/09/2026, not from 01/07/2026.
In the YTD calculation, change the number-of-days part from:
=C23-C31+1
to:
=MAX(0,C23-MAX(C31,C24)+1)
where:
- C23 = Pay Date
- C24 = Engagement Date
- C31 = Tax Year Start Date
Since this employee joined on 1 September 2026 and September is her first tax period, her YTD earnings and tax calculation cover only September. Therefore, if the current-period tax is 226.03, the YTD tax should also be 226.03, rather than zero.
Please make this change in the YTD tax formula and check the result.