ExcelBanter - Calculating commission schedule (will pay for help!)

Ok, thanks very much. Here goes.

1) The ONLY thing this spreadsheet needs to do is to calculate the
payment for THIS quarter. By that, I mean that what happens in the
future doesn't matter. What has happened in the past only matters to
the extent that it will determine what commission gets paid for this
quarter (if this quarter is in the 1st year, then 20%, if this quarter
is in the second year, then 10% commission, if this quarter is in the
3rd, 4th, 5th year etc, then commission is 1%).

2) So, what I have been trying to do is set up a calculation that says:
ok, this person started on X date, how much do we owe a sales rep for
this quarter. That's pretty easy if the quarter is entirely within
year 1, or year 2, or year 3. But, what is hard is if the quarter is
split in two, here's a couple of examples.

Example 1:

Joe invests $100K on January 1,2004. We are in the quarter ending
March 31, 2005, and trying to figure out how much to pay the sales rep.
Well, this quarter is entirely in Joe's 2nd year, so we pay 10%, or
$10K. Easy.

But, how about Jane? Jane invests on January 15, 2004. We are in the
quarter ending March 31, 2005 - so, the sales rep gets 15 days of year
1 commission at 20%, and 2.5 months at 10%.

That's the hard part.

Well, I should say, that's ONE of the hard parts. Making it pretty and
easy is another part, but the calculations are the key.

I'm actually just trying to help out my accountant friend, who thinks
i'm an excel guru. I told him hours ago that 'I give up', but I've
been in front of the computer off and on all day trying to figure it
out. Now I am just mad ;-) that I can't beat it. I've even gone to
trying brute force (month by month by month), but it's not working

Anyway, if you can figure this out, his boss will make a nice donation
to the site in your name! Or at least, I hope he will, and if not I
will.

Further question - here or

And thanks again.

Just realized I never did the pull out part - yeah, so say Jane above
pulled out on January 20, 2005. So for this quarter (ending March 31,
2005), sales rep would get 15 days at 20%, and 5 days at 20%.

--
bootsy
------------------------------------------------------------------------
bootsy's Profile: http://www.excelforum.com/member.php...o&userid=15795
View this thread: http://www.excelforum.com/showthread...hreadid=273006