Creating Loan Payment database

[Bob476]

I have a relational db with a couple of db's USER and LOAN. The LOAN db holds term, and monthly payment amounts.

What I want to do is create a third db called LOANPAYMENTS that will hold individual "invoice" records for each monthly payment for each user, so that I can forecast collections by month. I could do this manually of course, but I'm sure this is not a new issue. :-)

Ideally, once I enter a new record in LOANS, I could write a script that creates X number of records in PAYMENTS, where X equals "term" in LOANS. Loans db also has month of first payment, so script would then also use that value as due date for first payment record, then add a calendar month for each subsequent.

Are there templates for this somewhere? Thanks for any help.

[Fitch]

Try: www.fmfiles.com/enduserbus4.html

The script you described wouldn't be hard to construct. You'd want a global number field in the payments file to use as a "counter." Set the counter to the number of payments, set the start date, then loop, adding one to the month and subtracting one from the counter until it's zero.

[Bob476]

Thanks. What if the term isn't global, i.e. each loan has a different number of months to pay?

[Fitch]

The global is just to use as a temporary counter in the script that generates records in the payments file. You'd still keep the "number of months" as a standard number field in each loan record.

[borzhd]

Fitch

I love your graphic.

John

[Bob476]

Thanks a lot, Tom!

I've actually got the whole thing almost working based on your suggestions. My one problem is an issue with calculating the new due date. Result should be in date format and I have the calculation set as this:

GlobalPaymentMonth & "/" & DueDateDay & "/" & DueDateYear

However, it's just coming out plain wrong. The DuDateDay is a different number and the duedateyear comes out wrong also. Am I doing something wrong in trying to create the new date through a calculation forumla like the above? I assumed I could just create number/number/number and set result as date format.

[kennedy]

Try Date(GlobalPaymentMonth, DueDateDay, DueDateYear)