Quite the conundrum...

[Nauseo]

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...

• 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.

 

[jasonb75]

Welcome to MrExcel

I think that the table below should give you the required results based on the amount entered in A7.

Book2
	A	B
1	Bracket	Rate
2	0	18000
3	49500.00	17.50%
4	169079.20	1%
5	408237.60	2%
6
7	1000000.00	35153.192
Sheet3

Cell Formulas
Range	Formula
B7	B7	=CHOOSE(MATCH(A7,$A$2:$A$5),18000,(A7-A3)*B3,(A4-A3)*B3+(A7-A4)*B4,(A4-A3)*B3+(A5-A4)*B4+(A7-A5)*B5)

Hope this helps.

 

[KRice]

Or, as an alternative, if you'd like to eliminate the marginal rate table altogether, you could do this with a single formula as shown in row 8, 9 , and 10. The marginal rate array requires some explanation. Rather than using an array of the actual marginal rates (currently {0;0.175;0.01;0.02}), this formula relies on the difference in marginal rates as one moves from one tier to the next, so it begins with a 0 rate, and then lists the marginal rate change required to give the next tier's marginal rate. So for example, going from 17.5% to 1 % leads to a -0.165 term. I ran a comparison with @jasonb75 's suggestion and think that the 18000 base fee is missing (if I'm understanding the fee table correctly). That could easily be added to the B7 formula to resolve, if you prefer to keep the rate table handy.

MrExcel_20200401.xlsm
	A	B
1	Bracket	Rate
2	0	18000
3	49500	0.175
4	169079.2	0.01
5	408237.6	0.02
6
7	49500	0
8	49500	18000.00
9	100000	26837.50
10	1000000	53153.19
Sheet1

Cell Formulas
Range	Formula
B7	B7	=CHOOSE(MATCH(A7,$A$2:$A$5),18000,(A7-A3)*B3,(A4-A3)*B3+(A7-A4)*B4,(A4-A3)*B3+(A5-A4)*B4+(A7-A5)*B5)
B8:B10	B8	=18000+SUMPRODUCT(--(A8>={0;49500;169079.2;408237.6}),(A8-{0;49500;169079.2;408237.6}),{0;0.175;-0.165;0.01})

 

[jasonb75]

(if I'm understanding the fee table correctly)

I think that you are, I was going to wrap my formula with MIN(18000,...) but forgot to add it on after making some other changes, although this would still give different results to your formula. Reading the question again, I would say that your interpretation is the correct one.

I have seen the method that you have suggested before and did attempt it with little success, hence the alternative. Looking at your formula I now see where I was going wrong, I had the differences in the Bracket array instead of the Rate array could have sworn that was how I've done it before.

Quick fix on my formula in case the table method is preferred, although I would personally use a variation of your formula with a table for the task.

=CHOOSE(MATCH(A7,$A$2:$A$5),0,(A7-A3)*B3,(A4-A3)*B3+(A7-A4)*B4,(A4-A3)*B3+(A5-A4)*B4+(A7-A5)*B5)+B2

 

[Nauseo]

Wow, this really works thank you both so much. This website is amazing! :D

 

[KRice]

You're welcome...we're glad to help.