Trying to Find the proper Date Formula for my Job

Howdy! I am a leasing agent, and with my job we make commission off of places we lease! That being said, Every 25 properties I lease, the percentage of my cut goes up by 5%. That being said, it increases with move in dates. Not actual lease counts if that makes sense. On top of that, each properties rent varies greatly! That's besides the point, Im pretty sure I can figure out the calculations as far as that goes. What Im trying to do is set up a nice Date Formula. If Someone were to move in to a property on any day other than the first of the month, I get payed the following month on the 7th day. If They move in on the 1st of the month, I get paid the 7th of that month. I'd like to integrate the move in date with a count. That way Any time I add a new lease, it either adjusts the percentage accordingly, or remains the same. I also want to be able to enter in the date under "move in" and have the "Payment Due" date pop up! Example is listed below!

Address Leasee Move In Rent Commission Owed Payment Due Lease Count to Date Percentage

555 Goldie lane Tom Cruise 5/1/2013 1,000 300.00 5/7/2013 1 30%

555 Brownie Lane Rob Cruise 5/2/2013 1,000 300.00 6/7/2013 2 30%


Further down the road.... Lets say 25 leases later!

444 Goldie Lane Rob Cruise 5/1/20131,000 300.00 5/7/2013
(Since this propertys date is before 555 Brownie Lane, Its "lease count" would be reset in a way, and it would be #2 and Brownie Lane would be #3

Please help me if you can!!! much appreciated

Wow! you guys responded quickly! Here is a copy of what Im working with. Not sure if I attached it correctly.
Progress Report - Leasing 2013.xlsx

@Melvin, thank you for that template. You definitely have the right idea. I tried adding some more dates, but it didn't work and was giving me some random numbers.

Yes sir, I think it should show what its doing when I paste it on here. As far as percentages go, What I was aiming for is almost having it auto fill in the information. All I'd have to do is input the move in date as well as the rent . Once the move In date is set, the payment date , lease ctd and percentage would be able to update and change themselves accordingly, and commission would fill itself out. I doubt this can even be done haha but it would make my life a little more organized if it could! That's for sure! Especially budget wise. I've tried a lot of things and I honestly don't know how to organize dates on here that well.Lease date - Solution.xlsx

You sir, are one very smart individual ha! Im shocked at how well that works! As far as percentages and commission owed. The percentages value is directly related to the Lease CTD value. Every 25 leases, I go up 2.5% in commission percentage starting from 30%. That's 30% of one months rent. (really its 5%, but its easier to do 2.5%, long story) . So that being said, I type in the move in date, and it labels the lease ctd in order from earliest move in to latest. That's why percentages may vary. For example, If got a lease tomorrow for someone that wanted to move in ASAP , it would bump the other properties that have a later move in date Further down the list, which would increase one of the percentages if that makes any sense?

Yeahh you got it. We can use a more literal approach though. We are talking present date now. I have 29 leases that have moved in thus far. The up in commission is in motion because those first 25 are literally moved in. So I am at 32.5% commission now. I have 4 others that have moved in at this point that count towards the next 25 for my next upgrade.

You were right about if I had 24 for June 1, got another for June 10 so there's 25 right? Well its not June yet, so I still have a chance to squeeze another move in prior to June 10. The reason this actually matters a lot in leasing is MONEY! I have a few very big ticket properties. 2000$ + in rent. Big pay check from those right? Well... I want as many leases as I can get prior to their move in. That way, instead of having them move in at 32.5% commission, they are actually going in at 37.5 or even 40%.

As far as commission goes, it will continue to rise, but stops at 50%. Then when the new leasing season starts, back to square one. That's the only time the CTD would restart is if it were a new leasing season. I hope that helps!

That one works! Its perfect! To be quiet honest, I will probably never exceed 50% during a leasing season. It would reset to 30% the following year though. So this would go until December 31, Then, Once January 1st (2014) is here, you get reset back to 30%.

You have been a really big help. I really appreciate it sir!