For the admin of a small micro credit organisation I developed an admin tool in excel.
I set up the admin as a kind of database system. One tab for each of the following: payments, loans, customers, investor o.a. All of them off course linked
Before a loan is given out an interest rate is determined, and the way of payment (capital monthly or at the end)
For a capital monthly the following method is used. Lets say a customer pays 5% interest (calculation interest) monthly and has a loan of 2000 USD.
interest total: 5% * 5 months % 2000 USD= 500 USD
Total to pay= 2500 USD
per month = 2500/5=500 USD
so far no problem.
1) Thing is though that the interest rate is not 5% effectively. (effective interest)
2) Apart from that customers do not always pay on the due date, sometimes days, or months late, sometimes they pay a different amount as well, resulting in a different real interest rate (in case we don't charge more).
3) In case we want to stick to our effective interest we could charge an additional payment. How to calculate that easily
I have found a way to automatically filter out the payments for one loan, order them (with ranking), and based on that I manage to calculate real and effective interest. The problem is though that my excel file is getting really big. Many things I could do with sum.if's (like total paid per loan, or open amount per loan, late per loan etc) but for the interest rates and additional payment I hope to find a more efficient way so I can delete the complicated calculations.
Anybody an idea how to optimise this calculation?? functions I could have a look at?
Many thanks in advance
Bookmarks