Tiered Charges Calculation

Hi all,

I am new to the forum, joined about 10 mins ago, so forgive me I am do anything wrong!
I am starting to pull my hair out over a spreadsheet I am trying to build and could really do with some help! Disclosure; I am not Excel expert by any means but I am very good at following instructions!

I have attached the spreadsheet I am working on (I hope!) and a colleague has attempted a try at this but I KNOW the formulae is just not right somewhere a long the lines but I cannot work out where.

Basically, the company charges a tiered rate, decreasing, according to the amount of money invested with them.

The rates are as follows; £0 - £30,000 is 0.35%, the next £30,001 to £250,000 is 0.30%, the next £250,001 - £400,000 is 0.20% and the remaining amount (£400,001 +) is 0.10%

My colleague and I have used an IF formulae but it does not work for amounts under £250,000 (I cannot work out why) and it also doesn't split the investment amount correctly between the different tiers.
The total does not equal the actual amount invested either.

Please let me know if I am have made zero sense - I am happy to expand more on the issue, I am just rage typing as this has been going on for roughly a week now

I appreciate all the help in anticipation and thank you for taking your time to help us!