BrutalLogiC
Active Member
 Joined
 Feb 26, 2006
 Messages
 260
 Office Version

 2016
 Platform

 Windows
Hello board, I'm struggling with a formula to calculate the tax contribution where there are a number of different salary bandings and rates. I've manually typed out in column F what the answer should be but trying to get a formula in yellow cells so I don't have to keep recalculating! Help much appreciated!
Tax calc.xlsx  

B  C  D  E  F  G  H  I  J  K  L  M  N  O  P  Q  R  
2  Annual salary banding  0.0%  2.5%  10.0%  15.0%  20.0%  22.5%  25.0%  
3  Above:  Up to and including:  Above:  Up to and including:  Above:  Up to and including:  Above:  Up to and including:  Above:  Up to and including:  Above:  Up to and including:  Above:  Up to and including:  Above:  
4  A    600,000    15,000  15,000  30,000  30,000  45,000  45,000  60,000  60,000  200,000  200,000  400,000  400,000  
5  B  600,000  700,000    30,000  30,000  45,000  45,000  60,000  60,000  200,000  200,000  400,000  400,000  
6  C  700,000  800,000    45,000  45,000  60,000  60,000  200,000  200,000  400,000  400,000  
7  D  800,000  900,000    60,000  60,000  200,000  200,000  400,000  400,000  
8  E  900,000  1,000,000    200,000  200,000  400,000  400,000  
9  F  1,000,000    400,000  400,000  
10  
11  Annual Salary  Tax paid  Tax paid  
12  600,000  127,125  
13  601,000  127,500  
14  650,000  140,000  
15  720,000  159,750  
16  810,000  184,500  
17  950,000  222,500  
18  2,000,000  490,000  
Sheet1 
Cell Formulas  

Range  Formula  
F12  F12  =(15000*H2)+(15000*J2)+(15000*L2)+(140000*N2)+(200000*P2)+(200000*R2) 
F13  F13  =30000*H2+15000*J2+15000*L2+140000*N2+200000*P2+200001*R2 
F14  F14  =30000*H2+15000*J2+15000*L2+140000*N2+200000*P2+250000*R2 
F15  F15  =45000*J2+15000*L2+140000*N2+200000*P2+320000*R2 
F16  F16  =60000*L2+140000*N2+200000*P2+410000*R2 
F17  F17  =200000*N2+200000*P2+550000*R2 
F18  F18  =400000*P2+1600000*R2 