marginal tax rates

[gab2409]

hi, I hopefully have an easy question. here's the situation:
I have to figure marginal tax rates. after viewing this page: http://www.mcgimpsey.com/excel/variablerate.html,
I have been able to figure the bulk of it. but, what if there is a flat fee in addition to the marginal tax? the fee changes with each tax bracket. how do I add the range of flat fees to my formula? basically, my spreadsheet has:
m12-m15=threshold
n12-n15=marginal rate
o12-o15=differential rate
p12-15=flat fee per rate
my formula is:
=SUMPRODUCT(--(F9+E17>M12:M15), (F9+E17-M12:M15), O12:O15)

how do I incorporate the p12:p15 range? is it possible? thank you very much in advance.
greg

[gab2409]

I have found a different problem with the formula I listed. it works, but it takes the amount under the threshold and multiplies that by the previous rate(true marginal formula). what I need to do is multiply the amount over the threshold by the marginal rate, and then add a specified fee. for example:

(didnt show well on screen)
http://www.smbiz.com/sbrl001.html#pis05

any ideas?

[Domenic]

Can you provide an example, along with expected results?

[gab2409]

sure.
for an income of 100k, I would want:
(100,000-59,400)(.25)+8180=18,330

for 150,00, I would want:
(150,000-119,950)(.28)+23,317.5=31,731.5

for 200,000, I would want:
(200,000-182,800)(.33)+40,915.50=46,591.5

is this possible? should I use a function other than sumproduct? thanks in advance.

[Domenic]

Try...

=SUMPRODUCT(--(A1>{0,14600,59400,119950,182800,326450}),A1-{0,14600,59400,119950,182800,326450},{0.1,0.05,0.1,0.03,0.05,0.02})

...where A1 contains the 'Income Amount'. Also, note that the above is based on tax rates for 'Married Individuals Filing Joint--2005'.

Hope this helps!

[gab2409]

actually, all I had to do was input the entire table into the spreadsheet, not just the brackets that affected me. I cant believe I didnt see that before. thank you very much for your response.