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.
TarasAsked:
Who is Participating?
 
Jeffrey CoachmanMIS 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?
For all dates (ad infinitum)?
Leap years?
Due dates on Holidays?
What day are you designating as the first day of the week?
Pay Period Leap Year
http://www.reviewjournal.com/business/money/paid-every-2-weeks-you-could-get-extra-cash-2015
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
 
Jeffrey CoachmanMIS 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
 
TarasAuthor 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
TarasAuthor Commented:
Bi weekly Due date  use  every second Monday.
0
 
Jeffrey CoachmanMIS 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
 
TarasAuthor 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
 
TarasAuthor Commented:
I have to close this question do not now how, You at least tried do answer.
0
 
Jeffrey CoachmanMIS LiasonCommented:
ok
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.