charging 3 different fees on 1 value

[ryzwan]

Hi,

I want to calculate the fee

An annualized rate of 45% on first $100 million
then 40% on the next $100 million
and 35% on remaining value thereafter

can someone please help me with this
thanks

[ChemistB]

Try this

=MIN(100000000,A1)*0.45 + MIN(MAX(0, A1-100,0,0),100,0,0)*0.4+MAX(0,A1-200,0,0)* 0.35

[ryzwan]

@ChemistB thanks for your reply but its not working

by doing the individual calculation like following i am getting the total of 46,000. where as your formula giving me 106,917

A1= 46,000,000 = (A1*.0045/12)
A2= 146,000,000 = (A2-100000000)*.0040/12
A3=246,000,000 = (A3-200000000)*.0035/12

[ChemistB]

Sorry, I used 45%, not 0.45% annual compounded monthly and also put in comma separators which messed up the formula. It should be

=MIN(100000000,A1)*0.45%/12 + MIN(MAX(0, A1-100000000),100000000)*0.4%/12+MAX(0,A1-200000000)* 0.35%/12

See attached

[shg]

Another way:

	A	B	C
1	Amount	Fee
2	100,000,000	37,500.00	B2: =SUMPRODUCT((A2 > {0;1;2}*100000000) * (A2 - {0;1;2}*100000000) * ({45;-5;-5})%%) / 12
3	200,000,000	70,833.33
4	543,123,000	170,910.88

[ChemistB]

That's pretty cool, SHG. I just learned something new. Thanks

[Richard Buttrey]

Hi,

One way

Please Login or Register  to view this content.

[samba_ravi]

=lookup(a1,{0.1,100000000.1,200000000.1},{45,40,35}*a1%+{0,5000000,15000000})/12
sorry for earlier post

[boopathiraja]

try this

Please Login or Register  to view this content.

[Richard Buttrey]

...correction to typo in #7

Please Login or Register  to view this content.