Home 
Search 
Today's Posts 
#1




Calculating commission schedule (will pay for help!)
Having trouble with this, so I'm willing to pay for an answer...just tell me what you think is fair. Here are the basics (numbers are examples for simplicity): 1) Scenario is a sales rep is trying to get people to invest money. The sales rep gets paid a commission when that happens. 2) The commission is based on how long the client keeps their money in (amount of money does not change). For the first year, 20%. 2nd year, 10%. 3rd year and on, 1%. Let's do an example. Say I am a sales rep, and I get someone to invest $100,000 on September 1, 2002. So, for 9/1/029/1/03, I get 20%, or $20K. Easy. Next year, I get 10%, or $10K. Say the person takes their money out after 2.5 years, that means for year 3 I get only $500 (because I get 1% for year 3, but the customer closed the account halfway through year three, so I only got half). That's all pretty straightforward, but here's a summary: Client Start Date End Date Total Years Y1Commision Y2 Y3 Chris 9/1/02 3/1/05 2.5 yrs $20,000 $10,000 $500 So, those are all pretty easy to do in excel. Here is where it gets tricky. See, the payroll department has to calculate what to pay the sales rep on a quarterly basis. So, at the end of each quarter, they need to know what to give a rep. Here's an example, based on the earlier example. So, Chris brought in this client on 9/1/02. The quarter that has 9/1/02 ends on 9/30/02. So he needs to get paid for those 30 days of commissions  so he gets a fraction of the $20K since $20K would be what he would earn for the first year. The fraction is 30 days/365 days * $20K. Another example, same sales rep, Chris. This time, jump forward to 12/31/03. So, what does Chris get paid in this quarter? Well, we are now in year 2 for this client (year 1 ended on 9/1/03). In year 2, reps get 10%. So, for the quarter ending 12/31/03, Chris gets 1/4 of $10K, or $2500. It gets tricky if you tweak the example above a little: What if payroll was writing checks for the quarter ending 9/30/03. In that case, Chris would get 2 months (July/August) at the 20% rate, and 1 month at the 10% rate (September). You see where I'm going...and how this works. I'm trying to come up with a few elegant formulas (and, if that doesn't work, brute force will do). Please let me know your thoughts. I think it's a pretty simple one for someone who knows excel well, but I could be wrong.  bootsy  bootsy's Profile: http://www.excelforum.com/member.php...o&userid=15795 View this thread: http://www.excelforum.com/showthread...hreadid=273006 
#2




I'm working out a simple spreadsheet to calculate this for you. But a few questions first. Are investments allways entered into reps commission accounts on the 1st of the month? Do you want commission calculated by total full months invested? Or by total Days invested? Or by total months and then fraction of last month? In other words, what happens if investor invests half way through a month or pulls out half way through a month. Or is there a clause that states money would be paid back to investor at end of month. Does investment increase per year, month or quater due to interest on that account? And if so by what percentage per month, year, or quater? A scenario for you. Mr Blogs invests £100,000 on 1st January 2004. He keeps it in for at least 1 full year. You pay commision to sales rep for that year in four quaterly installments as normal. You pay commission for first quater of second year on March 31st. Commission is payed on the assumption that investor will keep investment in for rest of year. Investor then pulls out on the 31st April. What do you want to do in this scenario? would you recure overpayment from next commission created by other investments or cut losses? Or is there a clause that states investor must give a minimum of 3 months notice? In which case this would not then be a worry. Let me know the answer to these questions as soon as possible. Thanks Celtic_Avenger ps. Reply by email if you prefer.  Celtic_Avenger  Celtic_Avenger's Profile: http://www.excelforum.com/member.php...o&userid=14101 View this thread: http://www.excelforum.com/showthread...hreadid=273006 
#3




I have written a VBA macro that seems to handle all of the quirks. Are you
interested? Here are some details as to what I did: a) the percentges are hardcoded  20%, 10%, 1% (this could be modified to accept a range containing a table to be used with VLOOKUP) b) the period for which commission is earned may be less than a full quarter if the account was opened or closed during the quarter c) if the period is a full quarter, all at the same rate, I divide the annual rate by 4 and don't calculate actual days in the period d) for a period less than a full quarter, I calculate the fraction of a year between the start date and end date, inclusive, taking leap years into account. e.g. if the quarter starts on 4/1/2004, and the account is closed on 4/15/2004, the fraction of a year is 15/365. I treat the dates as inclusive on both ends, so that's 15 days. 4/1/2004 to 4/1/2005 is 365 days. Even though 2004 is a leap year, the "leap day" has already passed. OTOH, if we are talking about the quarter that begins on 1/1/2004 and the account is closed on 1/15/2004, the fraction is 15/366 because there are 366 days from 1/1/2004 to 1/1/2005. e) two calculations are done if an anniversary date (and hence a change in rate) occurs during the quarter: 1st part at initial rate, 2nd part, beginning on the anniversary date, at the new rate; the two parts are necessarily both less than a full quarter, so the comments in d) above apply On Wed, 27 Oct 2004 17:07:29 0500, bootsy wrote: Having trouble with this, so I'm willing to pay for an answer...just tell me what you think is fair. Here are the basics (numbers are examples for simplicity): 1) Scenario is a sales rep is trying to get people to invest money. The sales rep gets paid a commission when that happens. 2) The commission is based on how long the client keeps their money in (amount of money does not change). For the first year, 20%. 2nd year, 10%. 3rd year and on, 1%. Let's do an example. Say I am a sales rep, and I get someone to invest $100,000 on September 1, 2002. So, for 9/1/029/1/03, I get 20%, or $20K. Easy. Next year, I get 10%, or $10K. Say the person takes their money out after 2.5 years, that means for year 3 I get only $500 (because I get 1% for year 3, but the customer closed the account halfway through year three, so I only got half). That's all pretty straightforward, but here's a summary: Client Start Date End Date Total Years Y1Commision Y2 Y3 Chris 9/1/02 3/1/05 2.5 yrs $20,000 $10,000 $500 So, those are all pretty easy to do in excel. Here is where it gets tricky. See, the payroll department has to calculate what to pay the sales rep on a quarterly basis. So, at the end of each quarter, they need to know what to give a rep. Here's an example, based on the earlier example. So, Chris brought in this client on 9/1/02. The quarter that has 9/1/02 ends on 9/30/02. So he needs to get paid for those 30 days of commissions  so he gets a fraction of the $20K since $20K would be what he would earn for the first year. The fraction is 30 days/365 days * $20K. Another example, same sales rep, Chris. This time, jump forward to 12/31/03. So, what does Chris get paid in this quarter? Well, we are now in year 2 for this client (year 1 ended on 9/1/03). In year 2, reps get 10%. So, for the quarter ending 12/31/03, Chris gets 1/4 of $10K, or $2500. It gets tricky if you tweak the example above a little: What if payroll was writing checks for the quarter ending 9/30/03. In that case, Chris would get 2 months (July/August) at the 20% rate, and 1 month at the 10% rate (September). You see where I'm going...and how this works. I'm trying to come up with a few elegant formulas (and, if that doesn't work, brute force will do). Please let me know your thoughts. I think it's a pretty simple one for someone who knows excel well, but I could be wrong. 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
I would like to make a schedule for my dept. Who works what days .  Charts and Charting in Excel  
I would like to make a schedule for my dept. Who works what days .  Charts and Charting in Excel 