We would like to have a field in the portal that showed the number of missed payments since the last payment.
I'm not sure that I follow why this would be in the portal and not defined in the Loan table and placed on this layout outside of the portal?
I'm also wondering if you'd find it helpful, if you set up a script that, at the time the loan was approved, generated a record for each scheduled payment in a related table and then you used a field in this table to record when the scheduled payment was actually made. A separate table, with one record for each payment received would likely also be needed as you will have people getting caught up on a loan by sending in payments that pay off more than one payment. That's just a bit of brainstorming here that may or may not be useful to you.
If I understand what you are saying it would be like having an amortization schedule in a table for each loan? That may be a better solution. My issue is that I'm trying to model historical data and set up an extremely flexible solution. The payee may miss years worth of payments. Then, when they do pay, it is applied to interest first then any remainder to principle. Then they may not pay for several more years. With your scenario I could just sum the interest from the amortization schedule on the date they pay to the prior date they paid and apply the payment. I could also easily count the number of missed payments between payments (needed for negotiation purposes).
I'm sure there is a script somewhere to create an amortization schedule but if you know of one, that would be helpful.
it would be like having an amortization schedule in a table for each loan?
Or you might think of it as an "electronic payment coupon book".
Back in Junior college days I used Fortran to produce an amortization schedule as a class assignment. This can be a looping script, but I forget the math used to produce the values for principle and interest to put into the fields of the schedule.