Taras
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.
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.
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
…………..
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
…………..
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.
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???
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have to close this question do not now how, You at least tried do answer.
ok
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("CustomerPaymentDa
But perhaps I am not understanding something, ...so lets see what other experts may suggest...
JeffCoachman