Excel Formula Required for Tax Calculation

H

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,505
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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
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
Phuoc said:
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))
Click to expand...

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
Dave Patton said:
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})
Click to expand...
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
You must log in or register to reply here.

Similar threads

D
  • Question
Excel-97 (workaround for VBA or Visual Basic)
Replies
1
Views
169
Rijnsent
Rijnsent
L
  • Solved
Need to find data within a specified range when there are multiple ranges with similar data
Replies
4
Views
348
KRice
K
A
  • Question
Help with scanning barcodes into an excel invoice
2
Replies
12
Views
785
RoryA
RoryA
A
  • Solved
Conditional macro
Replies
8
Views
259
Fluff
Fluff
H
  • Question
Excel Formula
Replies
0
Views
163
hrayani
H

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

Share this page

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