Hello!
I'm a bit stuck trying to find a formula that will calculate the fee associated with a certain product based on total assets working in thresholds/brackets. I was hoping someone here may be able to help me.
Ideally I would be able to enter someone's assets into A1 and have the fee associated with that income pop up in B1. Here's where I am stuck...
1) The brackets are: 0 to 49,500
49,500 to 169,079.2
169,079.2 to 408,237.6
408,237.6 and above
2) The fee must always start at $18,000
3) + 17.5% of assets between 2nd bracket.. 49,500 and 169,079.2 (figures are inclusive) MINUS the first bracket (49,500). So it's 17.5% of the difference here.
4) PLUS 1% of anything between 169,079.2 and 408,237.6 after subtracting the preceding bracket, in this case 169,079.2 (So at this point we would have 18,000 + 17.5% of value exceeding bracket 1 + 1% of value in bracket 2.
5) A further 2% is added to the fee if assets are above the final bracket
6) The fee is the additive amount of all the above tests if they apply to the individual (someone may only have 30,000 - meaning the fee caps at 18,000)
note.. keep in mind the differences between brackets.. i.e. if calculating that 2% extra fee for someone whose assets exceed 408,237.6 then be sure to calculate it on the difference between current asset value and previous bracket.
I know this is confusing and a long shot asking around here if anyone can help but thought i'd try. I've been told it can't be done in excel but I figured if it can be calculated by hand then why not? If you have any questions I'll do my best to clarify...
I'm a bit stuck trying to find a formula that will calculate the fee associated with a certain product based on total assets working in thresholds/brackets. I was hoping someone here may be able to help me.
Ideally I would be able to enter someone's assets into A1 and have the fee associated with that income pop up in B1. Here's where I am stuck...
1) The brackets are: 0 to 49,500
49,500 to 169,079.2
169,079.2 to 408,237.6
408,237.6 and above
2) The fee must always start at $18,000
3) + 17.5% of assets between 2nd bracket.. 49,500 and 169,079.2 (figures are inclusive) MINUS the first bracket (49,500). So it's 17.5% of the difference here.
4) PLUS 1% of anything between 169,079.2 and 408,237.6 after subtracting the preceding bracket, in this case 169,079.2 (So at this point we would have 18,000 + 17.5% of value exceeding bracket 1 + 1% of value in bracket 2.
5) A further 2% is added to the fee if assets are above the final bracket
6) The fee is the additive amount of all the above tests if they apply to the individual (someone may only have 30,000 - meaning the fee caps at 18,000)
note.. keep in mind the differences between brackets.. i.e. if calculating that 2% extra fee for someone whose assets exceed 408,237.6 then be sure to calculate it on the difference between current asset value and previous bracket.
I know this is confusing and a long shot asking around here if anyone can help but thought i'd try. I've been told it can't be done in excel but I figured if it can be calculated by hand then why not? If you have any questions I'll do my best to clarify...
- 17.5% of assets between the Asset Free Area (Currently $49,500) and the first asset threshold -Currently $169,079.20- So that can be up to $119,579.20 x 17.5% =$20,926.30
- 1% of the value of assets over the First Asset Threshold and up to the Second Asset Threshold (Currently $408,237.60) The difference can be up to $239,158.40 x 1% = $2391.84
- 2% of the value of assets above the second asset threshold (Currently $408,237.60) This can be anything depending on client’s assets.