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

 

[VBA Geek]

have u tried


<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=SUM(<font color="Blue">MIN(<font color="Red">A1,100000000</font>)*0.45,MEDIAN(<font color="Red">0,100000000,A1-100000000</font>)*0.4,MAX(<font color="Red">0,A1-200000000</font>)*0.35</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Redwolfx]

There might be an easier way, but...

If your Value is in A1 try

=(0.45*(IF(A1>100000000,A1-(A1-100000000),A1)))+(0.4*IF(A1>200000000,100000000,IF(A1>100000000,A1-100000000,0)))+(0.35*IF(A1>200000000,A1-200000000,0))

 

[ryzwan]

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 formulas giving me different values

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

 

[Redwolfx]

First we need to clarify, Your formulas are .45%, .40% and .35% not 45%, 40% and 35% Then you are dividing them all by 12.

In Addition

A1= 46,000,000 = (A1*.0045/12) <---This should work properly
A2= 146,000,000 = (A2-100000000)*.0040/12 <---This is only going to give you .4% of 46000000/12
A3=246,000,000 = (A3-200000000)*.0035/12 <---This is only going to give you .35% of 46000000/12

The below updated formula takes a number and breaks it up, you get .45% of the first 100Mil .4% of the next 100Mil and .35% of the remainder. Then I've divided by 12 as it appears you are looking to do.

=(0.0045*(IF(A1>100000000,A1-(A1-100000000),A1)))+(0.004*IF(A1>200000000,100000000,IF(A1>100000000,A1-100000000,0)))+(0.0035*IF(A1>200000000,A1-200000000,0))/12

 

[GreasySpot]

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

You are calculating .45%, .40%, and .35% by 46,000,000 in all three of your formulas. How do those relate to 100,000,000? Using your formula (1,000,000*.0045/12) you get 37,500.