We help IT Professionals succeed at work.

Payment Frequency Date

on
308 Views
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?
Comment
Watch Question

View Solution Only

CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.

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 .
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.

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.
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

Deciding to stick with EE.

Mohamed Asif

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Connect with Certified Experts to gain insight and support on specific technology challenges including:

• Troubleshooting
• Research
• Professional Opinions
Unlock the solution to this question.

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.