Function to calculate total cost based on a tiered fee structure

[Trevorino]

Capture.JPG

Hello everyone

I have a problem where I need to calculate one big number into different fee percentage structures.

For example, in the month of April we did $ 8,817,247.28 worth of deposits.

Our vendor will charge us as follow.

TIERS

Deposits Volume
0-100k 4.8%
100-500k 4.6%
500-7.5M 3.9%
7.5-10M 3.6%
>10M 3.3%


any idea or help how to solve this using a formula, I looked at other types but its confusing, ideally I would like a table where every month the team will just insert the new deposits volume and they will find out how much we should be charged.

thank you in advance,

regards
Treevor

[The_Snook]

Morning Trevorino,

This article describes what you need to do. Link

Regards,

Snook

[XLent]

You don't provide your expected result but, assuming this should be 343,621 then, to illustrate:

enter following values into cells A2:A6: 0, 100000, 500000, 7500000, 10000000
enter following values into cells B2:B6: 4.8%, 4.6%, 3.9%, 3.6%, 3.3%

Then if we assume D1 holds your value of 8,817,247.28, we can calculate the output in E1 using:

=SUMPRODUCT(--(D1>A2:A6),D1-A2:A6,N(+B2:B6)-N(+B1:B5))

logic of this tiered method is detailed here
{edit: above link is the same as that shared by @TheSnook}

[Trevorino]

Thank you sooo much

[Trevorino]

First of all thank you so much for a full and simple explanation

One question for you?

Why in the formula there is +B2:B6)-N(+B1:B5)) when all the information is Between A1 to A6 and B1 to B6?

[XLent]

the key is that this is a marginal calculation so, the % multipliers need to be marginal rate change -- e.g. the % for tier 2 "element" is, in fact, tier 2 % less tier 1%

the use N(+...) is that we expect the first % to reside in row 2, with header in row 1, so we use this to ensure we don't hit a #VALUE! error
e.g. 4.8%-"Percentage" --> #VALUE! whereas N(4.8%)-N("Percentage") = 4.8%

[Trevorino]

Thank you kindly for your explanation

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.