Excel Formula Required for Tax Calculation

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,516
Office Version
  1. 2016
Platform
  1. Windows
Dear Friends,

Please if someone can provide a formula to calculate tax
Formula required in cell B12 & C12
I have given 3 examples

Book3
ABCD
2YEARLY INCOME
3 TAX FROMTO
40.00%0600,000
52.50%600,0011,200,000
612.50%1,200,0012,400,000
722.50%2,400,0013,600,000
827.50%3,600,0016,000,000
935.00%6,000,001Above
10
11Example 11,115,000
120.00%600000
132.50%515000
14
15Example 2555,000
160.00%555000
17
18Example 32,450,000
190.00%600,000
202.50%600,000
2112.50%1,200,000
2222.50%50,000
tax
Cell Formulas
RangeFormula
C5:C9C5=+D4+1


Regards,

Humayun
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Try this

Book1
ABCD
1
2YEARLY INCOME
3 TAX FROMTO
401600000
50.0256000011200000
60.12512000012400000
70.22524000013600000
80.27536000016000000
90.356000001Above
10
11Example 1111500012875
120600000
130.025515000
14
15Example 25550000
160555000
17
18Example 32450000176250
190600000
200.025600000
210.1251200000
220.22550000
Sheet1
Cell Formulas
RangeFormula
D11,D18,D15D11=SUMPRODUCT((B11>$D$4:$D$8)*(B11-$D$4:$D$8)*($B$5:$B$9-$B$4:$B$8))
 
Upvote 0
I did not see the solution in post #2 before I posted the following.
The named arrays make the formula more concise.

Commission2022.xlsm
ABCD
1
22,450,000.00176,250.00
3 TAX Brackets
4
50%0
63%600,000
713%1,200,000
823%2,400,000
928%3,600,000
1035%6,000,000
8bb
Cell Formulas
RangeFormula
D2D2=SUMPRODUCT(--(B2>aB),B2-aB,aR)


Commission2022.xlsm
ABCDE
1Arithmetic
22,450,000.00176,250.00176,250.00
3 TAX Brackets
4
50%00.00
63%600,00015,000.00
713%1,200,000150,000.00
823%2,400,00011,250.00
928%3,600,0000.00
1035%6,000,000
11
12
13Tax
14Example 11,115,000.0012,875.0012,875.00
15
16
17
18Example 2555,000.000.000.00
19
20
21Example 32,450,000.00176,250.00176,250.00
22
8bb
Cell Formulas
RangeFormula
D2,D21,D18,D14D2=SUMPRODUCT(--(B2>aB),B2-aB,aR)
E2E2=SUM(E5:E10)
E5:E9E5=MAX(0,MIN(C6-C5,$B$2-C5)*B5)
C14,C21C14=SUMPRODUCT(--(B14>$C$5:$C$10),B14-$C$5:$C$10,$B$5:$B$10-$B$4:$B$9)
C18C18=SUMPRODUCT(--(B18>{0;600000;1200000;2400000;3600000;6000000}),B18-{0;600000;1200000;2400000;3600000;6000000},{0;0.025;0.1;0.1;0.05;0.075})
 
Last edited:
Upvote 0
Try this

Book1
ABCD
1
2YEARLY INCOME
3 TAX FROMTO
401600000
50.0256000011200000
60.12512000012400000
70.22524000013600000
80.27536000016000000
90.356000001Above
10
11Example 1111500012875
120600000
130.025515000
14
15Example 25550000
160555000
17
18Example 32450000176250
190600000
200.025600000
210.1251200000
220.22550000
Sheet1
Cell Formulas
RangeFormula
D11,D18,D15D11=SUMPRODUCT((B11>$D$4:$D$8)*(B11-$D$4:$D$8)*($B$5:$B$9-$B$4:$B$8))

Dear Phuoc,

Thanks for the formula.
Although it works perfectly when calculating the entire tax
But I would like to have break down as requested in my initial post > Formula required in cell B12 & C12
 
Upvote 0
I did not see the solution in post #2 before I posted the following.
The named arrays make the formula more concise.

Commission2022.xlsm
ABCD
1
22,450,000.00176,250.00
3 TAX Brackets
4
50%0
63%600,000
713%1,200,000
823%2,400,000
928%3,600,000
1035%6,000,000
8bb
Cell Formulas
RangeFormula
D2D2=SUMPRODUCT(--(B2>aB),B2-aB,aR)


Commission2022.xlsm
ABCDE
1Arithmetic
22,450,000.00176,250.00176,250.00
3 TAX Brackets
4
50%00.00
63%600,00015,000.00
713%1,200,000150,000.00
823%2,400,00011,250.00
928%3,600,0000.00
1035%6,000,000
11
12
13Tax
14Example 11,115,000.0012,875.0012,875.00
15
16
17
18Example 2555,000.000.000.00
19
20
21Example 32,450,000.00176,250.00176,250.00
22
8bb
Cell Formulas
RangeFormula
D2,D21,D18,D14D2=SUMPRODUCT(--(B2>aB),B2-aB,aR)
E2E2=SUM(E5:E10)
E5:E9E5=MAX(0,MIN(C6-C5,$B$2-C5)*B5)
C14,C21C14=SUMPRODUCT(--(B14>$C$5:$C$10),B14-$C$5:$C$10,$B$5:$B$10-$B$4:$B$9)
C18C18=SUMPRODUCT(--(B18>{0;600000;1200000;2400000;3600000;6000000}),B18-{0;600000;1200000;2400000;3600000;6000000},{0;0.025;0.1;0.1;0.05;0.075})
Dear Dave,

Pls have a look at below tabel
I want the list to be generated as below (in green color)

Also, there seems to be some problem with the below formula
=SUMPRODUCT(--(B2>aB),B2-aB,aR)
"aB"

Book6
ABCD
1YEARLY INCOME
2 TAX FROMTO
300600000
40.02512000011200000
50.12524000012400000
60.22536000013600000
70.27560000016000000
80.35#VALUE!Above
9
10
11Example 32450000
120600000
130.025600000
140.1251200000
150.22550000
Sheet3
Cell Formulas
RangeFormula
C4:C8C4=+D4+1
 
Upvote 0
Maybe like this:

Book1
ABCD
1
2YEARLY INCOME
3 TAX FROMTO
40%0600000
53%6000011200000
613%12000012400000
723%24000013600000
828%36000016000000
935%6000001Above
10
11Change here =>60000001095000
120%6000000
133%60000015000
1413%1200000150000
1523%1200000270000
1628%2400000660000
1735%00
18
19
201095000
Sheet1
Cell Formulas
RangeFormula
C5:C9C5=+D4+1
D11D11=SUM(D12:D17)
C12C12=MIN($B$11,D4)
D12:D17D12=B12*C12
C13:C16C13=MIN($B$11-SUM($C$12:C12),D5-D4)
C17C17=$B$11-SUM($C$12:C16)
B20B20=SUMPRODUCT((B11>$D$4:$D$8)*(B11-$D$4:$D$8)*($B$5:$B$9-$B$4:$B$8))
 
Upvote 0
Solution
Maybe like this:
Book1
ABCD
1
2YEARLY INCOME
3 TAX FROMTO
40%0600000
53%6000011200000
613%12000012400000
723%24000013600000
828%36000016000000
935%6000001Above
10
11Change here =>60000001095000
120%6000000
133%60000015000
1413%1200000150000
1523%1200000270000
1628%2400000660000
1735%00
18
19
201095000
Sheet1
Cell Formulas
RangeFormula
C5:C9C5=+D4+1
D11D11=SUM(D12:D17)
C12C12=MIN($B$11,D4)
D12:D17D12=B12*C12
C13:C16C13=MIN($B$11-SUM($C$12:C12),D5-D4)
C17C17=$B$11-SUM($C$12:C16)
B20B20=SUMPRODUCT((B11>$D$4:$D$8)*(B11-$D$4:$D$8)*($B$5:$B$9-$B$4:$B$8))

Maybe like this:

Book1
ABCD
1
2YEARLY INCOME
3 TAX FROMTO
40%0600000
53%6000011200000
613%12000012400000
723%24000013600000
828%36000016000000
935%6000001Above
10
11Change here =>60000001095000
120%6000000
133%60000015000
1413%1200000150000
1523%1200000270000
1628%2400000660000
1735%00
18
19
201095000
Sheet1
Cell Formulas
RangeFormula
C5:C9C5=+D4+1
D11D11=SUM(D12:D17)
C12C12=MIN($B$11,D4)
D12:D17D12=B12*C12
C13:C16C13=MIN($B$11-SUM($C$12:C12),D5-D4)
C17C17=$B$11-SUM($C$12:C16)
B20B20=SUMPRODUCT((B11>$D$4:$D$8)*(B11-$D$4:$D$8)*($B$5:$B$9-$B$4:$B$8))
Thanks!
 
Upvote 0
quote
Also, there seems to be some problem with the below formula
=SUMPRODUCT(--(B2>aB),B2-aB,aR) "aB"
see
Commission2022.xlsm
D
180.00
8bb
Cell Formulas
RangeFormula
D18D18=SUMPRODUCT(--(B18>{0;600000;1200000;2400000;3600000;6000000}),B18-{0;600000;1200000;2400000;3600000;6000000},{0;0.025;0.1;0.1;0.05;0.075})


the array of the brackets information used is named aB with Name Manager
the array of rate differentials information used is named aR with Name Manager

quote But I would like to have break down as requested in my initial post > Formula required in cell B12 & C12

See the Arithmetic part is post #3
 
Upvote 0

Forum statistics

Threads
1,223,981
Messages
6,175,773
Members
452,668
Latest member
mrider123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top