Calculation required for tiered fees relating to cumulative investments

[alang1]

Hello Guys,

I am trying to put together a formula that will enable fees to be calculated on a tiered basis and allow for previous cumulative investments. I think I am OK for first year investments using IF function but it is subsequent years I am struggling with as the formula seems to be quite complex.

For example:
Implementation fee on 1st 100k is 2%, on next 150k is 1.5%, on next 250k is 1%, on next 500k is 0.5% and over 1,000,000 is 0.1%

So for first year investment of say 150k, fee would be 2,750, i.e. 2000 on first 100k @ 2% plus 750 on next 50k @ 1.5%

However for subsequent investments, say additional 200k the costs are reduced due to accumulation of previous investments:
For example where initial investment was £150k and subsequent investment £200k therefore total considered invested 350k. The new investment benefits from lower tiered fees due to the effect of adding in previous investments.

For the new investment of 200k the fee would be 2,500, i.e. 1500 on 100k @ 1.5% plus 1000 on next 100k @ 1.0%

I am getting into a tangle trying to do this with nested conditional formulae and wonder if there is an easier method. It becomes more awkward depending on the size of the initial or subsequent investments as I have to test whether initial or subsequent investments cross the tiered thresholds to apply appropriate tiered rate to relevant portion of new investment.

I hope that makes sense but have attached spreadsheet showing calculations for first year. Second year is blank and highlighted yellow is the section I am trying to resolve. I haven't included the formulae for the second year as I was getting in a mess.

If further information or clarification needed please let me know.

Any guidance would be appreciated.

TIA

[shg]

Welcome to the board.

Post a workbook with an example, including expected results.

[alang1]

I have re-attached amended spreadsheet to reflect the numbers used in example in original post, i.e. initial investment 150k subsequent investment 200k. Fee in year 2 should show 2,500. i.e. 1500 on 100k @ 1.5% plus 1000 on next 100k @ 1.0%.

These numbers I have typed into the sheet but am looking for help on formula to calculate them.

Hope this helps

Thanks.

[shg]

Row\Col	A	B	C	D	E
1		Amt	Fee	Delta
2		$ 0	2.0%	2.0%	D2: =C2-N(C1)
3		$ 100,000	1.5%	-0.5%
4		$ 250,000	1.0%	-0.5%
5		$ 500,000	0.5%	-0.5%
6		$ 1,000,000	0.1%	-0.4%
7
8	Period	Sold	Cumu	Fee
9	Year 1	$ 150,000	$ 150,000	$ 2,750	D9: =SUMPRODUCT( (C9 > $B$2:$B$6) * (C9 - $B$2:$B$6) * $D$2:$D$6) - SUM(D$8:D8)
10	Year 2	$ 200,000	$ 350,000	$ 2,500

[alang1]

Thank you so much shg, that provides exactly what I was trying to achieve.
You have provided a much simpler and neater solution all round.
Very much appreciate your rapid response and effort.

Thank you

[shg]

You're welcome.