Calculating commission schedule (will pay for help!)

[bootsy]

Having trouble with this, so I'm willing to pay for an answer...jus
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

 

[Celtic_Avenger]

I'm working out a simple spreadsheet to calculate this for you. But
few questions first.


Are investments allways entered into reps commission accounts on th
1st of the month?

Do you want commission calculated by total full months invested? Or b
total Days invested? Or by total months and then fraction of las
month? In other words, what happens if investor invests half wa
through a month or pulls out half way through a month. Or is there
clause that states money would be paid back to investor at end o
month.

Does investment increase per year, month or quater due to interest o
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 re
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 kee
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 overpaymen
from next commission created by other investments or cut losses?
Or is there a clause that states investor must give a minimum of
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.

(e-mail address removed)

 

[Myrna Larson]

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