TSQL For Loan Repayment Allocation
Budget: $10 – $30 USD
I am looking for a freelancer to write a TSQL script for allocating several loans repayments on loan repayment schedules on a 'First In First Out' (FIFO) basis.
Tables involved:
1. TblLoans (which contains the list of loans)
2. TblLoansRepaymentSchedules (which contains the loan repayment schedules)
3. TblReceiptsLoans (which contains the loan repayments)
4. TblReceiptsLoansSub (which will contain the result of the allocations of the loans repayments on the repayment schedule on a FIFO basis)
Approach:
1. The script will iterate from the first to the last loan in TblLoans.
2. For each loan, it will iterate from the first to the last repayment in TblReceiptsLoans.
3. For each repayment, it will look up the loans repayment schedules in TblLoansRepaymentSchedules, allocating the repayments on a FIFO basis and finally inserting the results into TblLoansReceiptsSub.
4. If a particular repayment is more than the repayment schedule, the balance of such repayments if spread to the next Installament.
5. If a particular repayment is less than the repayment schedule, such repayments will leave the Installament with a balance to be allocated with the next repayment.
Note:
1. TblLoans is similar customers
2. TblLoansRepaymentSchedules is similar invoices, each with unique InstallamentID as the invoice ID for each loan
3. TblReceiptsLoans is similar payments made on invoices
4. In allocating the loan repayments, Interests are considered first before Principals.
5. The script should be fast enough to allocate all the existing repayments on the repayment schedule as per the attached MS SQL tables within a reasonable time frame in hours or less.
Tables involved:
1. TblLoans (which contains the list of loans)
2. TblLoansRepaymentSchedules (which contains the loan repayment schedules)
3. TblReceiptsLoans (which contains the loan repayments)
4. TblReceiptsLoansSub (which will contain the result of the allocations of the loans repayments on the repayment schedule on a FIFO basis)
Approach:
1. The script will iterate from the first to the last loan in TblLoans.
2. For each loan, it will iterate from the first to the last repayment in TblReceiptsLoans.
3. For each repayment, it will look up the loans repayment schedules in TblLoansRepaymentSchedules, allocating the repayments on a FIFO basis and finally inserting the results into TblLoansReceiptsSub.
4. If a particular repayment is more than the repayment schedule, the balance of such repayments if spread to the next Installament.
5. If a particular repayment is less than the repayment schedule, such repayments will leave the Installament with a balance to be allocated with the next repayment.
Note:
1. TblLoans is similar customers
2. TblLoansRepaymentSchedules is similar invoices, each with unique InstallamentID as the invoice ID for each loan
3. TblReceiptsLoans is similar payments made on invoices
4. In allocating the loan repayments, Interests are considered first before Principals.
5. The script should be fast enough to allocate all the existing repayments on the repayment schedule as per the attached MS SQL tables within a reasonable time frame in hours or less.