How to determine commission on sale based on percentage of sale

[shanman2]

Hello,

I'm trying to figure how to determine when commission will be paid based on the amount of the deposit and the monthly installment paid each month. If the deposit is 20% of the sale commission is paid. If the deposit is less than 20% of the sale no commission is paid. It will be based on the deposit amount and the monthly installment paid. So combined, until equals 20% of the sale commission nothing will be paid. The calculation will be based on the number of installment payments made monthly paid plus the deposit.

So what I'm interested in is the projected date the commission will be paid based on the number of months (same date each month) it will take to reach the 20% of the sale.
Example:
Sale: 1000
Deposit: 100
Installment Date: 2/20/20
Monthly Installment: 100
Commission to be paid on: March 20, 2020

** Since the deposit is 100 the 20% required for commission is 200 so it will take next month to achieve that threshold.

[FlameRetired]

Hello shanman2. Welcome to the forum.

Your description does not match the workbook. With that understanding and the understanding that the installment date in WB is 3/20/2020 this in cell F2 returns 4/20/2020.

Please Login or Register  to view this content.

If instead the installment date is changed to 2/20/2020 (as stated in post #1) the F2 date is 3/20/2020.

[shanman2]

Hi Dave. Thanks for the formula it works great! The pay period is on the 1st and 15th of each month. In the WB the "TriggerDate" will determine what pay period the commissions will be paid. What type of formula works best?

[FlameRetired]

I am not clear on your meaning. It sounds like additional examples (another upload) with desired results and explanation will be helpful.