Progress Tax Calculator
Ah, I see
Thanks David
Matt
" wrote:
Matt wrote:
"David Biddulph" wrote:
Try
=5%*B3+1%*MAX(B3-20000,0)+1%*MAX(B3-40000,0)+1%*MAX(B3-60000,0)+
1%*MAX(B3-80000,0)+1%*MAX(B3-100000,0)
[....]
Helped me crack this problem, though to make it work I had to use MIN and MAX
=(5%*MIN(H$5,20000)+6%*MIN(MAX(H$5-20000,0),20000)+7%*MIN(MAX(H$5-40000,0),20000)+
8%*MIN(MAX(H$5-60000,0),20000)+9%*MIN(MAX(H$5-80000,0),20000)+10%*MAX(H$5-100000,0))
You overlooked the key to the simplicity of David's solution (which is
the one I always use myself): except for the first pct factor (5%),
you multiply MAX(...) by the __incremental__ pct factor. For example,
if the "marginal rates" were 5%, 6%, 8%, 11% and 15%, you would write
=5%*... + 1%*max(...) + 2%*max(...) + 3%*max(...) + 4%*max(...).
Returning to your example, consider the amount 30000. David's
expression would evaluate to:
=5%*30000 + 1%*max(30000-20000,0)
=5%*30000 + 1%*10000
=5%*20000 + 5%*10000 + 1%*10000
=5%*20000 + 6%*10000
That is the same as your more complicated expression, which would
evaluate to:
=5%*min(30000, 20000) + 6%*min(max(30000-20000,20000), 20000)
=5%*20000 + 6%*10000
|