Link to home
Start Free TrialLog in
Avatar of Taras
TarasFlag for Canada

asked on

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.
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

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
Avatar of Taras

ASKER

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
…………..
Avatar of Taras

ASKER

Bi weekly Due date  use  every second Monday.
To be clear, post a sample of your existing table structures, ...and also include a clear graphical representation of the *exact* output you require.
Avatar of Taras

ASKER

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???
ASKER CERTIFIED SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Taras

ASKER

I have to close this question do not now how, You at least tried do answer.