hi,

i've built a loan amortization schedule with payments due semi-annually. my
problem is that the payment must be on the same day of the 6th and 12th
month.
for example payments due 2/12/05 and 8/12/05 or 9/23/05 and 3/23/06. the
problem is that months have 30 or 31 days and then there's february and leap
years too. is there a way to program this function so that manual entry
payment dates can be eliminated?

tia
dennis

you could use data validation to supply a limited number of dates to
choose from.

On Fri, 11 Feb 2005 20:07:01 -0500, "dennis" wrote:

hi,

i've built a loan amortization schedule with payments due semi-annually. my
problem is that the payment must be on the same day of the 6th and 12th
month.
for example payments due 2/12/05 and 8/12/05 or 9/23/05 and 3/23/06. the
problem is that months have 30 or 31 days and then there's february and leap
years too. is there a way to program this function so that manual entry
payment dates can be eliminated?

tia
dennis


What do you want to happen if the first payment date is 8/30/2005, or
3/31/2005?

If that is not an issue, the general formula, assuming your dates are in a
column with no blank rows, would be:

=DATE(YEAR(FirstPmtDate),MONTH(FirstPmtDate)
+6*ROW()-ROW(FirstPmtDate)*6,DAY(FirstPmtDate))

However, if for a First Payment Date of 8/30/2005 you want the next payment to
be due on the last day of February. and for 3/31/2005 you want the next payment
to be on the last day of September, then it becomes more complicated.

=IF(DAY(DATE(YEAR(FirstPmtDate),MONTH(FirstPmtDate )+6*ROW()
-ROW(FirstPmtDate)*6,DAY(FirstPmtDate)))=DAY(FirstP mtDate),DATE(
YEAR(FirstPmtDate),MONTH(FirstPmtDate)+6*ROW()-ROW(
FirstPmtDate)*6,DAY(FirstPmtDate)),DATE(YEAR(First PmtDate),
MONTH(FirstPmtDate)+6*ROW()-ROW(FirstPmtDate)*6,DAY(
FirstPmtDate))-DAY(DATE(YEAR(FirstPmtDate),MONTH(FirstPmtDate)
+6*ROW()-ROW(FirstPmtDate)*6,DAY(FirstPmtDate))))


--ron

On Fri, 11 Feb 2005 20:07:01 -0500, "dennis" wrote:

hi,

i've built a loan amortization schedule with payments due semi-annually. my
problem is that the payment must be on the same day of the 6th and 12th
month.
for example payments due 2/12/05 and 8/12/05 or 9/23/05 and 3/23/06. the
problem is that months have 30 or 31 days and then there's february and leap
years too. is there a way to program this function so that manual entry
payment dates can be eliminated?

tia
dennis


If you have installed (or can install) the Analysis Tool Pack, you can use a
simpler formula than the one I previously posted:

Again, with the payment dates in a column under FirstPmtDate and no blank rows:

=EDATE(FirstPmtDate,(ROW()-ROW(FirstPmtDate))*6)

If the EDATE function is not available, and returns the #NAME? error, install
and load the Analysis ToolPak add-in.

How?

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.


--ron