Cumulative Tiered Bonus Structure

**dpleventhal** · 01-11-2011, 01:12 AM

Hi
HELP PLEASE!!! I've spend the better part of 8 hours trying to figure this out with no luck. I'm about to pull my hair out.

Here's what I'm trying to accomplish - I've got a spreadsheet with a tab called "Fees". The lender will get a quarterly bonus on the amount of fees he (or she) brings in during a given year. There are three tiers:
(1) The 1st $250K of fees ($0 - 250,000)
(2) The 2nd $250K of fees ($250,001 - $500,000)
(3) The 3rd tier for any fees brought in over $500,000 ($500,001+)

Here's where I'm stuck - the fee amounts accumulate over the course of the year. In other words - once the lender hits the $250K in fees in tier 1 then he's done with that tier.

So, if the lender brings in $300,000 in eligible fee revenue (there is a salary hurdle they have to clear which is build in to the spreadsheet) during the first quarter of the year - he would get paid 5% on the 1st $250K & 10% on the last $50K. If, during the 2nd quarter of the year he brought in $300K in fee revenue he would get paid $200K at 10% (he already earned the first $50K during Q1) & 15% on the last 100K. Any bonus he earned the rest of the year would be paid at the highest bonus amount (15%) since he's already maxed out the 1st two tiers. Is that confusing??

I'm having problems getting the fee amounts to aggregate correctly. I'm attaching a copy of the spreadsheet. I've been looking at it so long that i can't see the forest from the trees at this point.

I've uploaded the spreadsheet. I really appreciate any help you can offer. I would prefer NOT to use VBA.

Thanks
David

**DonkeyOte** · 01-11-2011, 05:13 AM

David, it's hard to determine what you want exactly.

The point I would make on these types of calculations is that's it's often easier to revert to a SUMPRODUCT approach - "cleaner" so to speak:

Please Login or Register  to view this content.

would give you the quarterly commission values I believe ?
(using the cumulative "eligible fees" value as basis for calculation)

**dpleventhal** · 01-11-2011, 08:20 AM

Originally Posted by DonkeyOte

David, it's hard to determine what you want exactly.

Thanks for your reply. Sorry I wasn't super clear on what I'm needing.

The area that I'm stuck on are cells calculating cells G14:J16.

I can't figure out how to make the eligible fees "build" on each other so that once you hit the bonus for that tier you are done for the year and any new fee revenue gets pushed to the higher tiers.

**DonkeyOte** · 01-11-2011, 08:33 AM

The point I was making was: that table (and in fact rows 14 to 20 inclusive) aren't required at all if you use the SUMPRODUCT suggestion.

**dpleventhal** · 01-11-2011, 08:36 AM

Gotcha....but the lenders need to (want to) see how their revenue is being distributed throughout the year. It makes them feel better if they can see, for example, that they've maxed out the 1st tier & are now earning a 10% bonus on all additional revenue.

It's a feel good thing for them (& a pain in the rear for me)....

Thanks for your help DonkeyOte!!

**DonkeyOte** · 01-11-2011, 08:49 AM

With some thought you would be able to shorten the below but I suspect they do what you want:

Please Login or Register  to view this content.

**dpleventhal** · 01-11-2011, 11:58 AM

DonkeyOte ~
You are a G-E-N-I-U-S! I'm still trying to sort through exactly what the formula does mathematically but it works even if I don't "get it"..