Recurring monthly payment

[Guest]

I posted this once today already, but I can't find my original question
anywhere!

I need a formula which will enter a loan payment on the same date each month
over a 30-year period. I have a long spreadsheet which calculates the
interest on a daily compound basis, using one row for each day. This needs
to take into account months with 28/30/31 days, and also leap years.

Any help will be much appreciated!

 

[Guest]

If you want to generate a list of dates one month apart, then first enter the
staring date in A1:

8/27/2006

and then in A2 enter:

=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) and copy this down the column.

 

[Guest]

Thanks, but it's not quite what I need!

I have a loan start date at the top of my date column, which then
automatically completes all the dates in that column. Next to that, I have a
payment column which allows users to put in any payment they like. However,
I also need this column to enter a regular payment automatically, i.e., I
need a formula which says something like =IF(date in date column=the tenth
{e.g.} of the month, enter the amount of the regular payment, otherwise leave
blank). The regular payment amount and date have their own reference cells.

 

[joeu2004]

I need a formula which will enter a loan payment on the same
date each month over a 30-year period. I have a long spreadsheet
which calculates the interest on a daily compound basis, using
one row for each day. This needs to take into account months
with 28/30/31 days, and also leap years.

I have a loan start date at the top of my date column, which then
automatically completes all the dates in that column.

Call that column A, with the start date in A1.

Next to that, I have a
payment column which allows users to put in any payment they like. However,
I also need this column to enter a regular payment automatically, i.e., I
need a formula which says something like =IF(date in date column=the tenth
{e.g.} of the month, enter the amount of the regular payment, otherwise leave
blank). The regular payment amount and date have their own reference cells.

Trying to give some meaningful interpretation to your statement "this
needs to take into account months with 28/30/31 days [etc]", I think
the logical condition you are looking for is:

IF this row's day of month is the same as the start-date day of month,
OR if the start-date day of month is beyond this row's day of month AND
this row's date is the last day of month, THEN return the regular
payment, ELSE leave blank.

If that is what you want, one way to write that is (in A2; copy down):

=if(or(day($A$1)=day(A2), and(day($A$1)>day(A2), A2=eomonth(A2,0))),
RegPmt, "")

Note that EOMONTH() is part of the Analysis TookPak add-in. See the
Excel Help page for EOMONTH() to learn how to install the ATP. If you
would prefer to avoid using the ATP, EOMONTH() probably could be
replaced with an expression, but it might be complicated if you want to
handle the vagaries of millennium leap years.

Speaking of which, I notice that in my revision of Excel 2003,
EOMONTH() handles millennium leap years correctly, but A1+1 does not,
where A1 is a date. Caveat emptor!