Formula needed help from a genius!!!
 

I need a formula for calculating the tax on a profit amount based on the
following scales so I can quickly calculate tax payable to accrue in my
accounts
0-25k 5.3%
25-200k 15.3%
200-400K 22.4%
400-1M 28.8%
1M-5M 33.8%
eg if profit was 200k then 1st 25K @5.3 and then 175K @15.3%
Would appreciate anyone who is able to give me some help on this

Formula needed help from a genius!!!
 

See this:

http://mcgimpsey.com/excel/variablerate.html


--
Biff
Microsoft Excel MVP


"SueG123" wrote in message
...
I need a formula for calculating the tax on a profit amount based on the
following scales so I can quickly calculate tax payable to accrue in my
accounts
0-25k 5.3%
25-200k 15.3%
200-400K 22.4%
400-1M 28.8%
1M-5M 33.8%
eg if profit was 200k then 1st 25K @5.3 and then 175K @15.3%
Would appreciate anyone who is able to give me some help on this

Formula needed help from a genius!!!
 

Hi Sue,

I'd be inclined to approach the problem this way:
First, construct a 3-column table as shown-
Threshhold Base Rate
0 0 5.30%
25000 1325 15.30%
200000 28100 22.40%
400000 72900 18.80%
1000000 245700 33.80%
5000000 1597700 33.80%
Then name rows 2-7 in columns A-C as 'Threshhold','Base' & 'Rate', respectively.
Assuming your table occupies A1:C7, including the headings, the 'Base' values for rows 2 - 6 are calculated by the formula
'=(A3-A2)*C2+B2' in B3 and copied down to B7.

Now, suppose your value is in A9. A formula you could use to calculate the tax is-
=ROUND(LOOKUP(A9,Threshhold:Base)+(A9-LOOKUP(A9,Threshhold))*LOOKUP(A9,Threshhold:Rate), 2)

--
Cheers
macropod
[MVP - Microsoft Word]


"SueG123" wrote in message ...
I need a formula for calculating the tax on a profit amount based on the
following scales so I can quickly calculate tax payable to accrue in my
accounts
0-25k 5.3%
25-200k 15.3%
200-400K 22.4%
400-1M 28.8%
1M-5M 33.8%
eg if profit was 200k then 1st 25K @5.3 and then 175K @15.3%
Would appreciate anyone who is able to give me some help on this

Formula needed help from a genius!!!
 

Thank you very much - your help so quickly is really appreciated

"T. Valko" wrote:

See this:

http://mcgimpsey.com/excel/variablerate.html


--
Biff
Microsoft Excel MVP


"SueG123" wrote in message
...
I need a formula for calculating the tax on a profit amount based on the
following scales so I can quickly calculate tax payable to accrue in my
accounts
0-25k 5.3%
25-200k 15.3%
200-400K 22.4%
400-1M 28.8%
1M-5M 33.8%
eg if profit was 200k then 1st 25K @5.3 and then 175K @15.3%
Would appreciate anyone who is able to give me some help on this

Formula needed help from a genius!!!
 

Great!
Thanks very much - you guys rock !

"T. Valko" wrote:

See this:

http://mcgimpsey.com/excel/variablerate.html


--
Biff
Microsoft Excel MVP


"SueG123" wrote in message
...
I need a formula for calculating the tax on a profit amount based on the
following scales so I can quickly calculate tax payable to accrue in my
accounts
0-25k 5.3%
25-200k 15.3%
200-400K 22.4%
400-1M 28.8%
1M-5M 33.8%
eg if profit was 200k then 1st 25K @5.3 and then 175K @15.3%
Would appreciate anyone who is able to give me some help on this