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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

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
TarasAuthor Commented:
Bi weekly Due date  use  every second Monday.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TarasAuthor Commented:
I have to close this question do not now how, You at least tried do answer.
0
Jeffrey CoachmanMIS LiasonCommented:
ok
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.