Payment calculator adjustment help

I have a payment calculator that helps determine if payments are within their due dates. Payments have a twenty day grace period. You can pay twenty days before or twenty days after. My calculator makes it easier to find out what monthly due date would work for all the payments. You are permitted to miss ONE month.

Attached is my calculator. You can see on the "Classic" tab the calculator I started with versus what I have created thus far. Password is QA.

My two roadblocks are as you can see in my example workbook, if you make payments on the 15th of every month, January through June, miss a July payment, then pay on the 27th from August to December. We would first try to pick a due date that would cause you NOT to skip a month. However, in this example, there isn't a due date that would work. So you would have to just skip July's payment. Hope that makes sense. Just in case, i'll try again, if it did, skip the next paragraph lol

Do not think in terms of due dates. You make 9 payments. You want those payments to apply, without missing a month and therefore extending your time. So you need to then, based on your payment dates, pick a due date that will get your 9 payments in 9 straight months. Otherwise, you are allowed to skip ONE month.

A January 3rd payment could count as your December payment.

I have my calculator setup to roll all the payments back or forward a month, but if an actual skip month is required, I do not have a way to do that without the user actually identifying it. Basically if column E is over 20 regardless of the due date, then it would be a skip month. So if I had a button that would look through "E" and say when E7= >20, then it would change the formula in B7 to be =EDATE(B6,2),"")) instead of EDATE(B6,1),"")). Which would cause it to skip a month.


My other roadblock is right now you have to manually guess the due dates to try and find one that works. Perfect world, macro would see if a due date of the 1st would work for all, otherwise then move to due date of the 2nd.
Incorrect formula, but B19="1", See if E4 is +/- 20, then see if E5 is +/- 20........or........if but B19="1", See if E4 is +/- 20, if not then, B19="2", See if E4 is +/- 20


I don't doubt this to be an extremely difficult task, one that may not generate any response or solutions, but if anyone can help me bridge the gap..

Not sure I follow everything, but is what I could decipher from your description.

Originally Posted by taylorsm

I have my calculator setup to roll all the payments back or forward a month, but if an actual skip month is required, I do not have a way to do that without the user actually identifying it. Basically if column E is over 20 regardless of the due date, then it would be a skip month. So if I had a button that would look through "E" and say when E7= >20, then it would change the formula in B7 to be =EDATE(B6,2),"")) instead of EDATE(B6,1),"")). Which would cause it to skip a month.

The first problem with changing B7 based on the value in E7 is that E7 is calculated based on the value in B7. This would create a circular reference. The B7 formula should calculate the next due date based on the previous month and compare that to the payment date in column C. Then if the day difference is >20 skip a month.

Try this in cell B5 and copy down column B

=IF(ISBLANK($C$4),"",EDATE(B4,1+(C5-EDATE(D4,1)>20)))

My other roadblock is right now you have to manually guess the due dates to try and find one that works. Perfect world, macro would see if a due date of the 1st would work for all, otherwise then move to due date of the 2nd.
Incorrect formula, but B19="1", See if E4 is +/- 20, then see if E5 is +/- 20........or........if but B19="1", See if E4 is +/- 20, if not then, B19="2", See if E4 is +/- 20.

Put this in E4 and copy down. It returns the absolute value of the day difference.
=IF(ISBLANK(C4),"",ABS(C4-D4))

This macro will loop through days 1 to 30 until it finds a date where no cells in E3:E15 are >20

Please Login or Register  to view this content.

No, I don't follow what you want. If you put in a blank month, the other formulas in columns D and E are messed up.

Perhaps you could copy the sheet a couple of times and put different scenarios in and manually calc the payment months to illustrate your meaning? If you do, please do me a favor; Unprotect the sheets and remove the IFERROR part of the formulas. I have an older version of excel that doesn't have IFERROR.