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 hard-coded -- 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/02-9/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.
|