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