LinkBack URL
About LinkBacks
Hi guys
I have an annoying problem trying to work out a stepped charge.
To explain what I mean, consider the following:
Start End Charge applicable (%)
£0.00 £149,999.99 0.30%
£150,000.00 £999,999.99 0.25%
£1,000,000.00 £2,499,999.99 0.20%
£2,500,000.00 £5,000,000.00 0.15%
£5,000,000.00 £10,000,000.00 0.10%
I want to find out the $ charge that applies to X.
Examples:
If X = $20,000, then the charge tier is 0.30%, and the charge is equal to £20,000 * 0.30% = $60.
If X = $200,000, then the charge tier is 0.25%, and the charge is equal to £200,000 * 0.25% = $500.
If X = $2,000,000, then the charge tier is 0.20%, and the charge is equal to £2,000,000 * 0.20% = $4000.
In the UK we call this "stepped charging", and financial institutions use it as a method of incentivising clients to invest more with them.
I think I could work it out in a series of iterative steps e.g. if X = $2,000,000, then $ charge = ($ max value of first tier) + ($ max value of second tier) + (($2,000,000 - $1,000,000) * 0.20%) etc.
However, I'm sure a more elegant and adaptable solution exists. The annoying thing is, I worked this out previously and I'm sure I used a nested IF SUMPRODUCT array. It looked hellish, but it did the job. I just can't get my brain to work...
Register To Reply
Originally Posted by windknife
Bookmarks