# Excel spreadsheet for calculating tiered charges

Member

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

• Member

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

• Member

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

• Member

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.

• Member

Here you go.

• Member

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

• Member

Noted that the example uploaded contained formula error, Doh!

Hopefully this one doesn't.

• Member

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

• Moderator

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.

Benjamin Fabi FPFS
Chartered Financial Planner