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
Bookmarks