C
Chris
Hi...having a problem with an excel formula, maybe you can help. I am willing
to pay for an answer via paypal, just let me know your price. Email me at
(e-mail address removed)
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 invested 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
Chris 9/1/02 3/1/05 2.5 yrs $20,000
Y2 Comm Y3 Comm
$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.
to pay for an answer via paypal, just let me know your price. Email me at
(e-mail address removed)
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 invested 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
Chris 9/1/02 3/1/05 2.5 yrs $20,000
Y2 Comm Y3 Comm
$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.