charging 3 different fees on 1 value

ryzwan

New Member
Joined
Sep 23, 2014
Messages
2
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
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
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
Joined
Feb 22, 2013
Messages
1,161
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
Joined
Sep 23, 2014
Messages
2
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

Well-known Member
Joined
Feb 22, 2013
Messages
1,161
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

New Member
Joined
Feb 1, 2013
Messages
48
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,205
Messages
5,527,399
Members
409,760
Latest member
zeeshansyed

This Week's Hot Topics

Top