# charging 3 different fees on 1 value

#### ryzwan

##### New Member
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

thanks

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### VBA Geek

##### MrExcel MVP
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

##### Well-known Member
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

##### New Member

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

##### Well-known Member
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.

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

##### New Member
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.

Replies
0
Views
336
Replies
4
Views
417
Replies
10
Views
1K
Replies
12
Views
530
Replies
11
Views
830

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,714
Messages
5,833,276
Members
430,201
Latest member
Deepakpilla36

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back