Loan repayment and receipt allocation to fees in order

MrGeeky

New Member
Joined
Oct 29, 2018
Messages
6
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top