Excel spreadsheet for calculating tiered charges

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? :smile:

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

Comments

  • 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 :)

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

    Late to the party but looking to launch a new platform due diligence tool and consultancy business later this year - watch this space and feel free to message if you would like any initial info.

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

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

  • Noted that the example uploaded contained formula error, Doh!

    Hopefully this one doesn't.

    Late to the party but looking to launch a new platform due diligence tool and consultancy business later this year - watch this space and feel free to message if you would like any initial info.

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

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

    http://twitter.com/benjaminfabi 
Sign In or Register to comment.