Payment Frequency Date

Posted on 2014-08-19
Medium Priority
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
  • 3
  • 3
LVL 40

Expert Comment

ID: 40271222
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

ID: 40271575
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 40

Expert Comment

ID: 40272781
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.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 40274688
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 40

Accepted Solution

PatHartman earned 2000 total points
ID: 40274896
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

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

621 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