Payment Frequency Date

Posted on 2014-08-19
Last Modified: 2014-10-28
In MS Access 2010, I am working on Time table for Payment Frequencey.
I need to show Start Date , End Date for payment Frequency.
I assume that I will need some preset Time Schedul populated table.

User will be prompted to enter :

Start Date=

Number of Years =
Payment Frequency =

I can calculate total Number of Payments:  e.g Number of Years 30; Payment frequency = Monthly so total Number of Payments = 360.
however I need to connect dates with those payments relating to start Date.
Start Date= Sep/01/2014

Number of Years = 20
Payment Frequency = Bi-weekly

I need something like :
Number of Payments      Start Date      End Date
1                  09/01/2014      09/14/2014      
2                  09/15/2014      09/28/2014

3            ………………………………………..
To the end of schedule

Payment Frequency which could be:
1.      Monthly (12 payments/year)
2.      Semi-Monthly (24 payments/year)
3.      Bi-weekly (26 payments/year)
4.      Weekly (52 payments/year)
5.      “Accelerated” bi-weekly (26 payments/year)
6.      “Accelerated” weekly (52 payments/year).

Number 5 and 6 are the same relating date range as 3 and 4 just amount will be calculated on dif. way

What do I need to get this Payment Frequency Date schedule table?
Question by:Taras
    LVL 33

    Expert Comment

    If you are saying that you want to pre-populate a table with future payment dates, I would advise against it.  It will be more flexible to simply calculate next months payments at some point during this month.  That way you won't have to worry about having to delete future records if the schedule changes.

    Author Comment

    Of course that client will change payments or add extra payment or change time frequency after some time.
    You can not expect that he will be at same rate and same frequency for 20 or 30 years.
    You have to recalculate this.
    As client want to see his scenario A , B, C from day one to last day and compare it.

    Every time when you recalculate his input you will get new situation from first date to last date not just next month.
    He wants to see this schedule as report .
    LVL 33

    Expert Comment

    To produce a report, you'll need to actually generate the records.  One way to do this is to create 6 frequency tables (or one table with flags so rows for a specific frequency can be selected) and populate these with all the dates for the next 30 years.  Every year, mark your calendar to add an additional year to the dummy frequency dates table(s).  Then to generate records for an individual, use a query that includes one of the dummy tables (or a query that selects the appropriate frequency).  Since there is nothing to join on, this will produce a Cartesian Product.  Therefore, a single client record for $345.67 will produce n rows in the result set.  One for each record in the frequency table/query.

    Author Comment

    Pat I do not understand why this should be big issue.
    E.g. If we are talking about fix term loan that will start on 09/01/2014 with interest rate of 6%  on 30 years with Monthly  Frequency payment.

    To create table : tblLoanTimeSchedule  with start and end dates it should not be big problem(maybe I do not need end date)?
    It means I know start date I know that it will be 360 monthly payments
    Why I can not generate this loan time schedule table with  360 entries , that will have Row Number (1-360), MonthStartDate, MonthEndDate , from 09/01/2014 To 09/01/2044 If I drop end date it will have only Row Number (1-360) and MonthStartDatedate.

    On another side I can run function to do calculation money part of the loan:
        1: Balance before payment
         2: Total amount of payment
        3: Principal amount of payment
         4: Interest amount of payment
         5: Balance after payment X
    Function will have 360 periods - rows  in two dimensional array .  Then  I will populate table tblLoan with those number from array.
    This table will have column Row Number and rest will be fields from array.
    Then just make join those two tables tblLoanTimeSchedule  and table tblLoan on Row Number column.
    LVL 33

    Accepted Solution

    You could do that but in relational databases we generally don't create future data like that especially when it can change.

    Loans can be paid off early.  What do you do with the records that are no longer valid?
    Loans can be sold.  This is probably the equivalent of being paid off early but probably the payoff amount would be more if it were sold than if the original borrower paid it off.
    Loan payments can include extra principal amounts.  That changes your amortization.  Are you going to regenerate all future the payment record every month if that happens?
    Loan payments can be made late.  Again, depending on the loan agreement, this might affect the amortization or even the rate.  For some more exotic loans that were written in the "stupid" years, late amounts just got added to the end of the term and the amortization schedule was regenerated to include the late amount.
    Loan terms can change.  In this case the original loan will probably be paid off and a new loan created though.

    So, using the technique I suggested, you end up with static tables that need to be updated annually to add the next year's date specific rows.  Queries will then generate on the fly everything you need. And you never have to worry about what to do with records that will never happen.

    If you choose to pre-generate all those future payments, just be prepared to deal with the changes I listed and probably others that I didn't think of.

    Author Closing Comment

    Thanks a lot I will come back with some of additional question. Sorry for waiting.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now