Loan Function

S

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
 
D

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
 
S

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.
 
C

Chris O''Neill

Hi, Stephen:

Stephen Lynch said:
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. :D Be comforted by the fact that it will
become less of a struggle as you gain experience.

Regards, Chris
 
S

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.
 
J

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top