[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.
Good morning Shamima.
I was thinking that maybe we can redo the spreadsheet which already has results from my previous engagement with you.
I have attached a new spreadsheet for you to assist me assistance as I seem to have gotten confused.
I am sorry for troubling you so much. I just wish to have to give the correct results.
Thank you.
I was thinking that maybe we can redo the spreadsheet which already has results from my previous engagement with you.
I have attached a new spreadsheet for you to assist me assistance as I seem to have gotten confused.
I am sorry for troubling you so much. I just wish to have to give the correct results.
Thank you.
Hello Maps,
Since you are using Office 365, we can use the complete LET formula rather than changing individual parts of the previous formula.
Based on the cell references from our earlier discussion, use this formula for your YTD tax:
=IF(C23<MAX(C31,C24),0,
LET(
DaysWorked,C23-MAX(C31,C24)+1,
DaysYear,D31-C31+1,
AnnualIncome,(D17-D19)/DaysWorked*DaysYear,
Rebate,IF(DATEDIF(C21,C23,"Y")<60,8200,
IF(DATEDIF(C21,C23,"Y")<75,10900,12100)),
Band,MATCH(AnnualIncome,$A$5:$A$8,1),
AnnualTax,INDEX($C$5:$C$8,Band)
+(AnnualIncome-INDEX($A$5:$A$8,Band))
*INDEX($D$5:$D$8,Band),
ROUND(MAX(0,AnnualTax-Rebate)/DaysYear*DaysWorked,2)
))
This formula uses your YTD earnings in D17, YTD deductible in D19 and the employee's engagement date in C24. It also adjusts for the applicable tax bracket, rebate and number of days employed.
However, I noticed something important in your example. Based on the 20% tax rate and the rebate of 673.97:
Calculation Tax
4,500 × 20% − 673.97 226.03
(4,500 − 215) × 20% − 673.97 183.03
This suggests that your current-month calculation may not be subtracting the 215 deductible, whereas your YTD calculation does.
If 4,500 is the gross salary and the 215 deduction applies, use the same deduction in both calculations. If 4,500 is already the taxable salary after the deduction, do not subtract 215 again.
For September, since the employee started on 1 September, both calculations should cover the same 30 days and produce the same result when using identical taxable earnings and tax rules.
Please also check whether the cell positions in your new spreadsheet are unchanged, as the formula above uses the references from your previous worksheet.
Since you are using Office 365, we can use the complete LET formula rather than changing individual parts of the previous formula.
Based on the cell references from our earlier discussion, use this formula for your YTD tax:
=IF(C23<MAX(C31,C24),0,
LET(
DaysWorked,C23-MAX(C31,C24)+1,
DaysYear,D31-C31+1,
AnnualIncome,(D17-D19)/DaysWorked*DaysYear,
Rebate,IF(DATEDIF(C21,C23,"Y")<60,8200,
IF(DATEDIF(C21,C23,"Y")<75,10900,12100)),
Band,MATCH(AnnualIncome,$A$5:$A$8,1),
AnnualTax,INDEX($C$5:$C$8,Band)
+(AnnualIncome-INDEX($A$5:$A$8,Band))
*INDEX($D$5:$D$8,Band),
ROUND(MAX(0,AnnualTax-Rebate)/DaysYear*DaysWorked,2)
))
This formula uses your YTD earnings in D17, YTD deductible in D19 and the employee's engagement date in C24. It also adjusts for the applicable tax bracket, rebate and number of days employed.
However, I noticed something important in your example. Based on the 20% tax rate and the rebate of 673.97:
Calculation Tax
4,500 × 20% − 673.97 226.03
(4,500 − 215) × 20% − 673.97 183.03
This suggests that your current-month calculation may not be subtracting the 215 deductible, whereas your YTD calculation does.
If 4,500 is the gross salary and the 215 deduction applies, use the same deduction in both calculations. If 4,500 is already the taxable salary after the deduction, do not subtract 215 again.
For September, since the employee started on 1 September, both calculations should cover the same 30 days and produce the same result when using identical taxable earnings and tax rules.
Please also check whether the cell positions in your new spreadsheet are unchanged, as the formula above uses the references from your previous worksheet.
You are welcome, Maps. It's my pleasure to help you.