Payment Frequency Date

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.
e.g
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?
TarasAsked:
Who is Participating?
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.

PatHartmanCommented:
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.
0
TarasAuthor Commented:
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 .
0
PatHartmanCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

TarasAuthor Commented:
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.
0
PatHartmanCommented:
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.
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:
Thanks a lot I will come back with some of additional question. Sorry for waiting.
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.