Recurring bill statements every nth months

[dpelican]

I'm doing some work with bills and I've got the next time a bill will be issued, the rate they are paid, every 1,3,6,12,36 month(s), and the amount to be paid. I'm trying to figure out a function that can take those 3 values, along with the columns I have(dated for each month from July 17' to July 18'), and auto fill all the columns. I've tried some different formulas but none have worked quite the way I want, so if I could get some tips that'd be great. Attached should be an example of what I'm looking for as an end result.

[JeteMc]

Hello dpelican and Welcome to Excel Forum.
Try pasting the following formula into cell D2 then drag across:

Please Login or Register  to view this content.

Let us know if you have any questions.

[JeteMc]

Actually the formula could be simplified to read:

Please Login or Register  to view this content.

Let us know if you have any questions.

[dpelican]

Wow, thanks so much. This worked almost perfectly, the one thing it's missing is that it doesn't do July 17' if the next payment is Oct 17'. Just a minor thing, I can attach a new document if you need to see what I mean.

[JeteMc]

Perhaps another upload would help me understand, because it seems to me that if the value in column A, 'Next Bill Statement', is 10/1/17 then there shouldn't be a amount under 7/1/17 (column D). Seems as if a payment is due on 7/1/17 that should be the 'Next Bill Statement' (column A), if the one after that is 10/1/17 the value in column C would be 3.

[dpelican]

Here's what I mean, even though the next statement will be in August, October, etc, July has already been payed for. So it's like looking backward the nth amount to see if it's within the field.

[JeteMc]

I attempted to modify the formula to read:

Please Login or Register  to view this content.

This worked for row 3, however when you copy down to row 6 you see that the Sept '17 payment disappears, due to the Countifs function returning a value of 2.
My thinking is that instead of 'Next Bill Statement' in column A, use 'First Bill Statement' instead. If you change A3 and A6 to 7/1/2017 you'll see both the future schedule and past payments.
Let us know if you have any questions.