# MS Access loan amortization Due date bi weekly...

I am using MS Access
PMT function to do calculation on loan amortization.
SchedPmt = -Pmt(IntRate / PmtsPerYear, MtgTerm * PmtsPerYear, MtgAmt, 0, 0)
I need to pull Due dates for payments relating which payment rate(number of payment per year) user chouse.
Something like this

Dim Mths As Integer
Dim PmtNum As Integer
Dim PmtDate As Date
Dim CumInt As Currency
Dim SchedPmt As Currency
Dim IntRate As Double
Dim PmtsPerYear As Integer
Dim MtgAmt As Currency
Dim MtgTerm As Integer
Dim PmtYear As Integer
Dim PmtMonth As Integer
PmtDate = Me.txtStartDate
CumInt = 0
IntRate = Me.txtInterestRate
PmtsPerYear = Me.txtNumPayments
MtgAmt = Me.txtLoanAmount
MtgTerm = Me.txtLoanTerm
Mths = CInt(Me.txtLoanTerm) * PmtsPerYear

SchedPmt = -Pmt(IntRate / PmtsPerYear, MtgTerm * PmtsPerYear, MtgAmt, 0, 0)
PmtYear = 1
PmtMonth = 1
CumInt = 0
PmtNum = 1
Do Until PmtNum > Mths
…..
…….
…….. this part of function during loop  is ok, just  part for date making me trouble.

It should be something like bellow.

If PmtsPerYear = 12 then
PmtDate = DateAdd(“m”,1,PmtDAte) ….’ Date will be increased  every fist of month
Else if PmtsPerYear = 24 then… ‘Date need to be increased  every beginning of month then every  15th of month.
Else if PmthPerYear = 26 then…. Date should be increased   every second Monday ????
Else if PmthPerYer = 52 then… Date should be increased every week(Monday)
End if
Loop

So I need to add Date for payments rate, monthly, semi monthly ;Bi- Weekly; Weekly monthly is not problem just those other ones.
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MIS LiasonCommented:
to me, (Not knowing your table design/structure)...this data should be stored in the main (Payments?) table:

tblPayments
PaymentID (PK)
CustomerID (FK)
CustomerPaymentNumber
CustomerPaymentDate
PaymentAmount

Thus, selecting the Customer, ...then the CustomerPaymentNumber for that customer, would also yield the Payment date.
Dlookup("CustomerPaymentDate", "tblPayments", "CustomerID=" & me.CustomerID & " AND " & "CustomerPaymentNumber=" & me.CustomerPaymentNumber)

But perhaps I am not understanding something, ...so lets see what other experts may suggest...

JeffCoachman
0
Author Commented:
Jeff
Payment start  date is not problem I have it.
It is Loan start Date.
What I am  doing I am trying to do loan amortization schedule .
In this loop I  need to show Due date e.g. from 06/01/2015 semi monthly  for term of 5 years.
06/01/2015
06/15/2015
07/01/2015
07/15/2015
…..
……

Bi weekly
06/01/2015
06/15/2015
06/29/2015
07/13/2015
…………..
0
Author Commented:
Bi weekly Due date  use  every second Monday.
0
MIS LiasonCommented:
To be clear, post a sample of your existing table structures, ...and also include a clear graphical representation of the *exact* output you require.
0
Author Commented:
In my current code in loop I have for monthly rate frequency of payment “monthly”
Due date or PmtDate
PmtDate = DateAdd(“m”,1,PmtDAte) this is working ok and schedule is calculating properly.

I asked for help if frequency rate is semi monthly, by weekly an weekly I gave my try with if statement that need to be corrected something like this:

If PmtsPerYear = 12 then
PmtDate = DateAdd(“m”,1,PmtDAte) ….’ Date will be increased  every fist of month
Else if PmtsPerYear = 24 then… ‘Date need to be increased  every beginning of month then every  15th of month.
Else if PmthPerYear = 26 then…. Date should be increased   every second Monday ????
Else if PmthPerYer = 52 then… Date should be increased every week(Monday)
End if
If you can help me with this I would appreciate I need to calculate this due date on such pater.
I show you how it should looks like in schedule. Why do you need table structure to do this date calculation increase or grow like that pattern???
0
MIS LiasonCommented:
Because you need tables that house this data...
The periods should be stored in a table so that *any* date range can be generated.

Note the amount of typing you have done already...
A picture is worth a thousand words.

Just show me what you have, then show me what you want...
Post only two picture and you could have saved all that typing...
Sound Fair?
Also I do not want to "guess" at the exact output,...only for you to tell me something you may have forgotten...(or I may have misunderstood)

Do you need the dates in a table, ...?
For all customers?
Leap years?
Due dates on Holidays?
What day are you designating as the first day of the week?
Pay Period Leap Year
Not every year will have exactly 52 weeks
...etc

But perhaps there is an expert who is willing to help with this Q as posted.

JeffCoachman
0

Experts Exchange Solution brought to you by