Commission Calculation Help

Hi All,

need some help devising a formula or function to calculate a commission scheme. I have tried messing with IF and various others, but this is beyond my skills, any help greatly apppreciated

Scenario is as follows:-

Commission is paid Quarterly in arrears

Quarterly targets are £100,000 per quarter

Salary is £10,000 per annum

Band A <80% Sales = No Commission
Band B>80%-90% Sales = 0.5% of salary for each % Sales achieved in the band
Band C>90%-100% Sales = 1 % of Salary for each % sales achieved in the band
Band D> 100% Sales = 2% of salary for each % Sales achieved in the band

Retention Policy

Q1 Retention of 60% is held back against the year to date commission earned
Q2 Retention of 40% is held back against the year to date commission earned
Q3 Retention of 20% is held back against the year to date commission earned
Q4 Full commission earned is paid

So the example i work out is the following

Qtr 1 Target £100,000 Achievment £100,000 pays out the following:-

0.5% of salary x 10 = £50.00 x 10 = £500
1% of salary x 10 = £100.00 x 10 = £1000.00

Total commision earned is £1500.00

Total paid is £1500.00 less 60% of year to date commission £1500.00 - £900.00 = £600.00 paid out

Q2 Achievment £100,000 so pays out the following:-

0.5% of salary x 10 = £50.00 x 10 = £500
1% of salary x 10 = £100.00 x 10 = £1000.00

Cumulative earned = £3000.00 x 40% retained = £1200.00

Paid out is £1800.00 - £600.00 alreasdy paid in Q1 = £1200.00

Q3 Achievment £100,000 so pays out the following:-

0.5% of salary x 10 = £50.00 x 10 = £500
1% of salary x 10 = £100.00 x 10 = £1000.00

Cumulative earned = £4500 x 20% retained = £900.00

Paid out is £3600.00 - £600.00 Q1 & £1200.00 Q2 already paid = £1800.00

Q4 Achievment £100,000 so pays out the following:-

0.5% of salary x 10 = £50.00 x 10 = £500
1% of salary x 10 = £100.00 x 10 = £1000.00

Cumulative earned = £6000 no retention

Paid out is £6000.00 - £600.00 Q1 & £1200.00 Q2 & £1800.00 Q3 already paid = £2400.00

Total commission for the year Earned & Paid is £6000.00


The issue i have is sorting a formula to accurately calculate the payments due in each band and then add them together. If the acheivement for Q1 is 97% then actual earned is £500.00 + £700, based on the % of salary in each band.


Thanks in advance for you help

Hi Davesexcel,

Thanks for the pointer, unfortunately that IF statement and tip uses the actual sales ammount to determine the commission level paid, this is not like my example.

The example i have posted is based on the percentage of sales against target between specific bands, and actually the sales amount is not relevant, it is purely the % difference between the amount achieved and the target, and therfore the amount of salary payable for each % achievment in each band

Any other pointers?