Excel spreadsheet for calculating tiered charges

Andy_Schleider

Hi - I'm trying to work out how to create a spreadsheet which works out the platform charge for a given value, and also shows the amount charged within each tier. We use Zurich a lot and they have tiered charges, and I have to show in the report the amount that is charged at each tier. Obviously I can do it manually with a calculator but a spreadsheet would make life easier and quicker, and reduce the possibility of errors. I tried using =SUMPRODUCT but I don't really understand how it works and I'm tying myself up in knots - I don't really want to use loads of nested =IF arguments as this would probably overcomplicate things. It gets complicated in the higher tiers, especially where we will have a range of values for different clients so some of the tiers will not be needed.

Alternatively, if anyone has such a spreadsheet would you be willing to share?

I hope this makes sense but happy to explain further if needed

Rebecca_Tuck

Hi Andy - I don't have one for Zurich but do have a one that does exactly what you're after for Transact! Unfortunately, IF arguments tend to be a necessity for things like this! If you wanted to message over the tiers and their relevant charges, I'll happily update mine and send it over

Jamie_Barnes

@Andy_Schleider , I find it is better to set a tiered structure out in a table, then use if statements for each banding. Use a sheet for each platform then put them together into one comparison page on another sheet (if you use multiple platforms).

I've attached a very simple example for just Zurich but the charges might be out of date as i don't use their platform. hope it helps.

Jona

I went down the nested IF statement route to compare various platform charges in one place for a given investment amount - probably longhand so I too would be interested in alternatives.

Works fine tho and saves time in the long run for the initial time cost.

richardgough

Here you go.

arongunningham

How would you go about incorporating tiered charges into a 'cumulative effect of charges on return' illustration?

Jamie_Barnes

Noted that the example uploaded contained formula error, Doh!

Hopefully this one doesn't.

Andy_Schleider

Thanks for all your help and replies - as Jona said the initial time investment will be worth it going forward

benjaminfabi

Nested IFs the way to go. they aren't too complex. Here's my go to spreadsheet for the simple charging structures.

@arongunningham take the data from the provider's illustration or contract an IT company to build you something.

Rcaisley

A little bit messy but one I made to compare charges between providers. Has all the information there, easy enough to play about with.

Nathan

I made this for Informed Choice