Hi...having a problem with an excel formula, maybe you can help.
Price is just a start, and I will pay for follow up and tip at the
end.
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 person 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. |
