How to make a loan management system and installment collections in Excel (Annual, monthly, weekly and daily)

Learning how to  make a loan and installment management system in Excel (Annual, monthly, weekly and daily) will help you a lot in your Office work. Especially if you want to keep track of collections without having to request the services of an accountant. And have it all on your computer in a more organized and efficient way.

It is very easy to use Exel, we just have to practice and know its basic concepts and especially the use of the functions. You’re not very familiar with the use of this application will ask you to stay with us for you to learn well the steps to follow and garments to use the sheet calculation of Exel.

How to make a loan and installment management system in Excel

To begin we are going to go to the Exel program and in April a new spreadsheet and we are going to call it Client. And in this sheet we are going to place all the customer data, we can place a column with Reference, another column Name and Surname. Another column with ID or identification data.

excel table

We can also add to have more data in our table , a column with the Address and finally another column with the Telephone Number. And we started to empty the information of the buyers in this box. And so we can have a broader knowledge of each person that we are going to charge you.

But remember, that this data that we are offering you is referential and you can add more columns to your table. Everything must be adjusted to your needs, at the end you will create a new sheet and this should be called a calculus or calculator. In it you are going to go to the magnifying glass so that the client looks for you, you just have to put the name and it will appear in a separate box.

When it appears you must choose it and it will appear as a buyer then you must go to the Credit amount option, that is, how much money is going to be lent to this client. In the modality option you should see a small arrow that indicates that there are several options. We make a clip on it and it will appear four modes Daily, Weekly, Biweekly and Monthly.

Collection management daily weekly biweekly and monthly

For our example we are going to place Monthly, then we go to the Interest rate option and we can place 10%. According to the amount borrowed, you should already show a profit in the generated Interest table. Then you go to the option of how many installments and you enter the number of installments the borrowed money will pay.

When writing this information in the Amount of the installment box, it should appear how much the customer must pay in each installment. And you must also generate a figure in the Total to pay option and to finish you must fill in the Date option. Now you simply have to do a clip on the calculator icon and this action will take you to the status sheet.

This sheet must show the client’s data, the amount of the loan, the modality, the amount to be paid for each installment, the expiration date of each installment. And in this way you will keep track of each client, with the debt carried and the date of payment of each installment. And all in a very simple way with the Exel spreadsheet.

excel calculation tool

And in this way we have explained the very simple way to use the calculation tool if you want to make a loan management system and installment collections in Excel Annual, monthly, weekly and daily. Without having to resort to other more complicated programs to use.

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button