Loan repayment and receipt allocation to fees in order

[MrGeeky]

Hi

Is it possible for me to handle receipt allocations to a loan in excel or access?

[TABLE="width: 1349"]
<colgroup><col><col><col><col><col><col><col><col><col><col span="3"><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Borrower Name
[/TD]
[TD]Agreement Number[/TD]
[TD]Transaction Reference[/TD]
[TD]User[/TD]
[TD]Agreement Date[/TD]
[TD]Repayment Method[/TD]
[TD]Description[/TD]
[TD]Total
[/TD]
[TD]Capital[/TD]
[TD]Interest[/TD]
[TD]Service Fee[/TD]
[TD]Initiation Fee[/TD]
[TD]Other Fees[/TD]
[TD]Vat[/TD]
[TD]Insurance[/TD]
[TD]Arrears[/TD]
[TD]Overpay[/TD]
[/TR]
[TR]
[TD]A DLAMINI [/TD]
[TD="align: right"]2471[/TD]
[TD="align: right"]71876[/TD]
[TD]ALICIA[/TD]
[TD="align: right"]2019-03-30[/TD]
[TD]ALLPS-I AEDO[/TD]
[TD]Hand Over[/TD]
[TD] R 548.13[/TD]
[TD] R 457.74[/TD]
[TD] R 13.73[/TD]
[TD] R - [/TD]
[TD] R 18.26[/TD]
[TD] R - [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] R 58.40[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A DLAMINI [/TD]
[TD="align: right"]2603[/TD]
[TD="align: right"]71874[/TD]
[TD]ZAMA[/TD]
[TD="align: right"]2019-03-30[/TD]
[TD]ALLPS-I AEDO[/TD]
[TD]Hand Over[/TD]
[TD] R 3 951.63[/TD]
[TD] R 2 758.56[/TD]
[TD] R 209.96[/TD]
[TD] R 199.59[/TD]
[TD] R 310.66[/TD]
[TD] R - [/TD]
[TD] R 61.95[/TD]
[TD] R 66.18[/TD]
[TD] R 344.73[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]B J DISEKO [/TD]
[TD="align: right"]837[/TD]
[TD="align: right"]70430[/TD]
[TD]BULELWA[/TD]
[TD="align: right"]2019-02-27[/TD]
[TD]DEBIT ORDER[/TD]
[TD]Hand Over[/TD]
[TD] R 1 050.17[/TD]
[TD] R 500.00[/TD]
[TD] R 22.59[/TD]
[TD] R 60.00[/TD]
[TD] R 75.00[/TD]
[TD] R - [/TD]
[TD] R 18.90[/TD]
[TD] R 5.00[/TD]
[TD] R 368.68[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Example above, in row 1, I received R200 towards agreement number 2471 and this amount needs to be receipted and should first lessen the Capital by R200. And this reducing it to R257.74.

Is there a way I can handle this sort of transaction in excel or access? I would also like to keep a record of these transactions and if possible to draw a report for a period to see how much receipts were captured and what is the current TOTAL balance of the loans after the receipts.

Regards,
Wayne