I had to fiddle with it a little, but I think I have something that works.
In all this, I'm using R1C1 notation instead of A1 notation, which comes
naturally to me. If you prefer A1 I'm leaving the translation up to you, but
if isn't obvious how R1C1 works, let me know and I'll explain it. Here's
what I have:
Column 1: Invoice date
Column 2: Invoice amount
Column 3: Number of payments
Column 16: =RC2/RC3, ie the monthly payment. I didn't worry about rounding
issues; I'll let you figure that out.
Columns 4-15: The month columns. The header row LOOKS like it says simply
"Jan", "Feb", "Mar" etc; but in actuality it's a formula
=DATE(2008,COLUMN()-3,15). In column 10, for example, this gives
=DATE(2008,7,15) or 2008-07-15. In other words, the actual value in row 1 of
the month columns is the 15th of each of the 12 months. This is important to
the solution, as you'll see. Meanwhile, the format of these dates is "mmm",
so that 2008-07-15 is displayed simply "Jul".
Ok, columns 4-15 look like the month. In the January column an IF function
(you weren't wrong about that): =IF(RC1<R1C,RC16,0). That is, if the invoice
date (column one of this row) is less than the 15th of this month (row 1 of
this column) then display the monthly payment value from column 16 in this
column. Otherwise nothing, ie this month is too soon to expect a payment.
Before we go on, pause to think about that. Suppose we're looking at an
invoice date of 2008-08-23. That date is not less than 2008-01-15,
2008-02-15, 2008-03-15 etc, so all those cells are blank. That date is not
less than 2008-08-15, either: the invoice date is in the latter half of the
month, so the first payment shouldn't be made until Sep. But the invoice
date (2008-08-23) IS smaller than 2008-09-15, so in the Sep column appears
the first monthly payment.
If all the rest of the month columns had this formula, the payments would
start in the right column but would continue through the end of the year. We
need a wrinkle in Feb through Dec: We need it to display a monthly payment
in each cell if it's after the payments are to start AND the payments already
displayed to the left are insufficient to fully pay off the invoice amount.
Two conditions to fulfill, then. The first we already know about: RC1<R1C
keeps Excel from displaying payments too early. The second condition, that
the payments displayed so for (in cells to the left) don't already add up to
the invoice amount, can be expressed as SUM(RC4:RC[-1])<RC2 -- that is, the
SUM of columns 4 through the column to the left of this one must be less than
column 2 (the invoice amount); if it isn't less, then the preceding amounts
satisfy the debt and no more payments should be displayed. I think this
translates to A1 notation as follows: In the May column of row 18, for
example, it would read SUM(D$18:G18)<B18.
BOTH of these conditions have to be true, so we use the AND function:
AND(RC1<R1C,SUM(RC4:RC[-1])<RC2). And that's what we put in the IF function:
=IF(AND(RC1<R1C,SUM(RC4:RC[-1])<RC2),RC16,0)
I have this set up in a spreadsheet and it seems to work. If you can't
figure it out from this explanation, write me at (e-mail address removed) and
I'll send you a copy.
