Help with sliding commission with rolling annual total

jcmarx

New Member
Joined
Jan 20, 2015
Messages
2
Excel 2007
Windows 8.1


Hello everyone, and thank you in advance!!!


I am creating a commission worksheet for my real estate agents so they can easily track their earnings for every deal. I have essentially found the solution (thanks to the second-half of a YouTube post from MrExcel), but do not know how to implement it for my specific needs.


MrExcel's Learn Excel #757 - Sliding Commission
http://youtu.be/r1nqJR_UAFc


The problem:


Agents earn a gross commission per deal, generally between 2.5% and 3.0% of the total sale. For example, If an agent sells a home for $250K, they generate a gross commission of 3.0%, or $7,500. My business collects this check from the title company, deducts brokerage and admin fees, then pays the agent their net commission in three tiers based on ANNUAL sales volume:


>$0 but <$1,000,000 = 65% of 3%
>=$1,000,000 but <$2,000,000 = 75% of 3%
>=$2,000,000 = 90% of 3%


The two tricky items are as follows:
1) Agents' sales volumes accumulate over the course of the year (they don't reset until the next fiscal year) i.e. a rolling total.
2) The agents must completely meet each threshold before getting paid a higher percentage on the remainder. For example, if an agent's first few deals total $800,000, then they sell a home for $1.25mm, they will be paid on the deal as follows: 3.0% of $200K @ 65%, 3.0% of $1mm @ 75%, and 3.0% of $50K @ 90%.


Assuming each agent tracks their rolling annual volume total, and we know their gross commission, how can we find their net commission?


I've used nested IFs with AND... but I can't get a unified formula or process to handle all scenarios across all three tiers. I'm pretty sure nested VLOOKUPs will work if I create a commission split table on a separate sheet within the workbook. That said, my brain can only handle two moves ahead, not three. Any help you can give will be GREATLY appreciated.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Do you mean something like the below? where for example cell D10 breaks down to
Excel Workbook
5workings for d10
6(1000000*0.03)*0.65 = 19500
7(1000000*0.03)*0.75 = 22500
8(173954*0.03)*0.9 = 4696.758
9total = 46696.758
Excel 2010
<p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet3</p>

Excel Workbook
ABCD
1Dealsrolling totalDateCommission
21,316,21101/11/20130
31,871,31801/12/20130
42,025,31701/01/20140
51,164,60001/02/20140
61,187,38101/03/20140
7845,654845,65401/04/201416490.253
8100,000945,65401/05/201418440.253
9100,0001,045,65401/06/201420527.215
101,128,3002,173,95401/07/201446696.758
111,883,1304,057,08401/08/201497541.268
121,993,2346,050,31801/09/2014151358.586
131,338,8757,389,19301/10/2014187508.211
141,808,9669,198,15901/11/2014236350.293
151,085,78010,283,93901/12/2014265666.353
16953,36311,237,30201/01/2015291407.154
171,588,13112,825,43301/02/2015334286.691
Sheet3




Obviously the fiscal year in the formula is from 1st April (UK date format) and column B has no part in the formula, it is just there for illustration
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top