I can't seem to get the formula right on this. Here is the example: I have a
field for gross wage and a field for federal tax. The year to date field
increases with each pay period. The YTD spans 2 tax brackets i.e.; <$15,100
is taxed at 10%, $15,100 at 15%. I have been trying to work with the "if"
function. Everything is fine until I hit the threshold then all previous
numbers reset to the higher rate. I'd like to be able to use this as a
template so I really don't want to lock the value in each cell. Should I be
approaching this with a different function?
--
Bob

This may be useful:

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

In article ,
Bob wrote:

I can't seem to get the formula right on this. Here is the example: I have a
field for gross wage and a field for federal tax. The year to date field
increases with each pay period. The YTD spans 2 tax brackets i.e.; <$15,100
is taxed at 10%, $15,100 at 15%. I have been trying to work with the "if"
function. Everything is fine until I hit the threshold then all previous
numbers reset to the higher rate. I'd like to be able to use this as a
template so I really don't want to lock the value in each cell. Should I be
approaching this with a different function?

Yes, a different method is probably better. (Not paying taxes would be
even better.)


At each cutoff point the tax is some number (X), plus a percentage of
income above the cutoff point.

So, you probably want a lookup table for the base amount, plus a
percentage calculation for the income above the base amount.

For example

col A col B col C
col D
Income Range Tax at Bottom of Range
Incremental %
Low High

20,000.01 25,000.00 500.00 15%

25,000.01 30,000.00 1,250.00 20%



Assume the taxable income is in cell Z99.

Tax can be found by
=Vlookup(Z99,A:D,3,TRUE) + ' tax at
bottom of range Vlookup(Z99,A:D,4,TRUE)*
' incremental rate
(Z99-VLOOKUP(Z99,A:D,1,TRUE)) '
incremental taxable income


--
rsenn
------------------------------------------------------------------------
rsenn's Profile: http://www.excelforum.com/member.php...o&userid=29050
View this thread: http://www.excelforum.com/showthread...hreadid=491290

Hi Bob

With gross wages in A1
=MIN(15100,A1)*10%+MAX(0,A1-15100)*15%

Regards

Roger Govier


Bob wrote:
I can't seem to get the formula right on this. Here is the example: I have a
field for gross wage and a field for federal tax. The year to date field
increases with each pay period. The YTD spans 2 tax brackets i.e.; <$15,100
is taxed at 10%, $15,100 at 15%. I have been trying to work with the "if"
function. Everything is fine until I hit the threshold then all previous
numbers reset to the higher rate. I'd like to be able to use this as a
template so I really don't want to lock the value in each cell. Should I be
approaching this with a different function?