Loan Function

[Stephen Lynch]

I have search high and low and cannot find a function that returns an
amortization schedule. I quess this is how you learn. I am starting from
scratch.

How do I get this simple function to print each month in succession. It
prints the same month so I guess somehow I need to redefine the variable to
the one generated.

I want to show:

2/1/2008
3/1/2008
4/1/2008
5/1/2008 etc. But I get

2/1/2008
2/1/2008
2/1/2008
2/1/2008

Function LoanCalc(intTotalPayments As Integer, StartingPaymentDate As Date)

Dim PayDate As Date
Dim i As Integer

For i = 1 To intTotalPayments
PayDate = DateAdd("m", 1, StartingPaymentDate)
Debug.Print PayDate
Next i


End Function

 

[Douglas J. Steele]

Your statement

PayDate = DateAdd("m", 1, StartingPaymentDate)

means that you're always adding 1 month to whatever date is in
StartingPaymentDate, as opposed to adding to the previous PayDate.

Function LoanCalc(intTotalPayments As Integer, StartingPaymentDate As Date)

Dim PayDate As Date
Dim i As Integer

PayDate = StartingPaymentDate

For i = 1 To intTotalPayments
PayDate = DateAdd("m", 1, PayDate)
Debug.Print PayDate
Next i

End Function

 

[Stephen Lynch]

So do I add a counter to the 1 in the statement DateAdd("m", 1,
StartingPaymentDate) or do I define a new variable that uses the new date.
I am sorry but I am new to this so this is a struggle for me.

 

[Chris O''Neill]

Hi, Stephen:

So do I add a counter to the 1 in the statement DateAdd("m", 1,
StartingPaymentDate) or do I define a new variable that uses the new date.
I am sorry but I am new to this so this is a struggle for me.

What you had...

PayDate = DateAdd("m", 1, StartingPaymentDate)

....adds one month to StartingPaymentDate for each loop, but doesn't change
StartingPaymentDate. So, if you pass the number 12 as intTotalPayments, you
get a string of 12 identical dates that are one month more than
StartingPaymentDate.

What Douglas gave you...

PayDate = DateAdd("m", 1, PayDate)

....adds one month to PayDate everytime the code loops. So, if you pass the
number 12 as intTotalPayments, you get 12 consecutive months starting at
StartingPaymentDate + 1 month and ending at StartingPaymentDate + 12 months.

So, just delete your code and replace it with Douglas' code. That should
work for you.

Good luck with your struggles. Be comforted by the fact that it will
become less of a struggle as you gain experience.

Regards, Chris

 

[Stephen Lynch]

Chris:

Thanks for clarifing, I didn't comprehend it.I am almost through with my
code for this problem with one problem dates ate importing as 12/31/1899.

I posted another message.

Thanks DOUG and CHRIS.

 

[John W. Vinson]

Thanks for clarifing, I didn't comprehend it.I am almost through with my
code for this problem with one problem dates ate importing as 12/31/1899.

That means you're importing either zero, or a very small number (for instance,
8 divided by 19 divided by 2008). Midinght, December 30, 1899 is the "zero
point" of Access date/time values; if you store 2.096875655e-4 in a Date/Time
field you'll actually be storing a time a few seconds after midnight on that
date.

Be sure you're importing the value *as a date*, not as an expression, to avoid
this problem.