Hi Forum,
Would appreciate advice on the best way to approach the issue of Calculating a Fee When a Repayment is Late.
When we issue a Loan there are Repayment Dates and Amounts records added to a Table. These Records can be from two to Twenty Entries depending on the term of the loan.
The Database knows:
The Commencement Date of the Loan.
The Date Each Repayment is Due.
The Amount the Repayment Should be - Same throughout the Loan Term.
The Number of Repayments Due - 2 to 20.
The End Date of the Loan.
An Amount the Late Fee is for this Loan.
The Date of any Repayments made.
The Amount of each Repayment.
The Late Fee is Double if the First Repayment is Missed.
We have allowed a 50% Repayment amount will avoid a Late Fee for that Date but have stopped this for recent loans issued.
Late Fees are charged for each period - Fortnight - the Original Loan is completed after the End Date of the Loan.
We do not charge Late Fees on Late Fees ie, Once your Repayments have completed the Original Amount to be repaid, Late fees are no Longer accrued.
Because some of our Repayments are deducted by the Employer and may be paid to us Monthly and maybe a week late, we do not charge Late Fees if, when the repayment is rec'd, the amount agrees with the fortnights covered ie the correct deductions were made.
This is where it can get messy to automate.
I was thinking of Using the Dates the Repayments are Due and matching the balance to the fortnightly Amount due to get the number of fortnights the loan is overdue and compare this to the Late Fees already Charged and charge the difference. For the First Fortnight, do a check and charge double.
And.. when a repayment is entered, recalculate to see if a credit is due.
We could have a field Monthly, Fortnightly to let the calculation know to allow for the Monthly Repayment when doing the Recalculation on Repayment Rec'd
I know a percentage fee would be easier but at the time we set the system up, 11 years ago, we didn't anticipate automation would be required.
My guess is a VBA Loop with maybe a sub routine or two.
Appreciate some advice on how best to approach this and I can then start on the project and seek assistance on individual problems as they arise.
Would appreciate advice on the best way to approach the issue of Calculating a Fee When a Repayment is Late.
When we issue a Loan there are Repayment Dates and Amounts records added to a Table. These Records can be from two to Twenty Entries depending on the term of the loan.
The Database knows:
The Commencement Date of the Loan.
The Date Each Repayment is Due.
The Amount the Repayment Should be - Same throughout the Loan Term.
The Number of Repayments Due - 2 to 20.
The End Date of the Loan.
An Amount the Late Fee is for this Loan.
The Date of any Repayments made.
The Amount of each Repayment.
The Late Fee is Double if the First Repayment is Missed.
We have allowed a 50% Repayment amount will avoid a Late Fee for that Date but have stopped this for recent loans issued.
Late Fees are charged for each period - Fortnight - the Original Loan is completed after the End Date of the Loan.
We do not charge Late Fees on Late Fees ie, Once your Repayments have completed the Original Amount to be repaid, Late fees are no Longer accrued.
Because some of our Repayments are deducted by the Employer and may be paid to us Monthly and maybe a week late, we do not charge Late Fees if, when the repayment is rec'd, the amount agrees with the fortnights covered ie the correct deductions were made.
This is where it can get messy to automate.
I was thinking of Using the Dates the Repayments are Due and matching the balance to the fortnightly Amount due to get the number of fortnights the loan is overdue and compare this to the Late Fees already Charged and charge the difference. For the First Fortnight, do a check and charge double.
And.. when a repayment is entered, recalculate to see if a credit is due.
We could have a field Monthly, Fortnightly to let the calculation know to allow for the Monthly Repayment when doing the Recalculation on Repayment Rec'd
I know a percentage fee would be easier but at the time we set the system up, 11 years ago, we didn't anticipate automation would be required.
My guess is a VBA Loop with maybe a sub routine or two.
Appreciate some advice on how best to approach this and I can then start on the project and seek assistance on individual problems as they arise.
