Deferred payments formula

Hello, I was hoping someone could help out with a problem in excel.

I am looking for a formula that defers commision payments of a sales representative. For example:

1. The amount of a sale a representative closes in a month is $5,000.
2. He gets $5,000 that month.
3. He gets another $5,000 divided in 12 months.
4. For example. He closes the $5,000 in January, gets the $5,000 right away, and then $416.66 for the following months until next January.

Which formula could I use for this deferred payment situation?

Thanks in advance,

-Walter

.
liverock

I was looking over your request and created the attached workbook. Please let me know if it
fulfills your needs. Thanks.

Thank you so much Bernie and Logit

.
You are welcome. Please let me know if the workbook functions as desired. I had some difficulty 'getting my mind around it' and
believe my logic is accurate ... BUT .... I am never completely satisfied until the tool is placed into use and the results are accurate.

Thanks.

Just one question... The only areas I'm supposed to use are the boxed ones, correct?

.
Yes, row #7. Just enter the total monthly sales generated by the salesman.

Thank you. Just have a few questions:

1. Is the Check Amt. the total payment of the month?
2. Why does the Check Amt. change with future months? Meaning, if I add sales on May, why does it affect prior months?
3. What is the monthly bonus?

Thank you for your help Logit...

.
1. Is the Check Amt. the total payment of the month?
YES

2. Why does the Check Amt. change with future months? Meaning, if I add sales on May, why does it affect prior months?
You indicated in your original post that the salesman is given a 'deferred payment' each month for twelve months. One month is included in the month of the original payment, then eleven months thereafter.
So, (and this was the difficult part for me to understand the logic), each month changes because there is a deferred amount paid each month.

3. What is the monthly bonus?
This is more of an effort see what the 'bonus amount' for that month is. It is the monthly amount you indicated in your original post:

3. He gets another $5,000 divided in 12 months.

.
You probably have been using a 'manual system' so far to calculate how much to pay. I believe if that system has worked accurately for you, run a few sample scenarios with the manual system.
Then, use the same numbers in the new system and see if they match up.

I certainly hope they do.

Roger that, thanks!