How to calcalute an interest rate based on irregular payments sums and dates??

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

Are you sure you are calculating interest correctly, rarely it has been that a bank charges simple interest as is the case with your calculation of interest that use the simple interest formula

SI = RIT
R is the amount of loan
I is the interest rate
T is the number of periods (months in your case)

Most banks charge or pay interest when it is compounded either discretely or continuously

Discrete Compounding of Interest

FV = PV (1+i/m)^nm
where FV is the future amount that includes the interest
PV is the amount of loan
i is the effective interest rate
m is the number of compounding per year
n is the number of periods

If APR is 5% compounded monthly
FV = 2000 (1+5%/12)^(12)(5/12)
FV = 2000 (1+5%/12)^5
FV = 2000 x 1.021007669332370836548353909465
FV = 2,042

If real rate is 5% per month

FV = 2000 (1+5%)^5
FV = 2000 x 1.2762815625
FV = 2,552.56

And if the interest were to be compounded continuously it be a different formula altogether

FV = PV e^it

So before I can try to answer to actual concern would you please clarify if the bank in fact charges simple interest or have you forgot to ask them about compound interest

If the dates for repayment are not fixed and given that bank doesn't charge a late fee then you may want to try the Excel XIRR function to find annual interest rate due on series of irregular (non periodic) payments

As an example suppose loan in amount of $2,000 was issued on 1st of Jan 2012 for which 5% interest was charged thus making the total amount payable equaling $2,500

With five monthly payments in unequal amounts (R <> 500) on irregular dates as listed below will result in an annual effective rate of 82.32% de-annualizing which we get a monthly compounded rate of 5.13%

A ############## B
DATE ######### Amount
1/1/2012 #### -2000
3/19/2012 #### 300
4/17/2012 #### 700
5/15/2012 #### 600
6/5/2012 #### 400
7/20/2012 #### 500

Annual Rate #### =XIRR(B1:B6,A1:A6)
Annual Rate #### 82.32%

Monthly Rate #### =(B7+1)^(1/12)-1
Monthly Rate #### 5.13%

(1+r)^12-1=ar
(1+r)^12=ar+1
1+r=(ar+1)^1/12
r=[(ar+1)^1/12]-1

thanks! that really helps me a lot. My excel will contain a whole lot less space. Just one thing is missing. Lets say I have a planned range of payments, which result in a specific intrest rate X.
Now the customer pays on different dates and amounts, but the planned total is the same. This results in an interest rate Y.

Now I want to know how much additional interest can I charge to be paid on a specific day so the interest rate X fits again with the actual payments.
For example in the example above from Moneymaker, I want to close the loan on July 31st. What is the balance due????

testfile monthly.xlsxI encounter another problem. The calculation method is not accurate for monthly based interest, only for yearly based interest. How can I accurately calculate the monthly interest rate. To explain the situation the following example: customer gets a 1000 USD loan at 5 % per month for 3 months. And pays monthly the interest and at the end the borrowed sum. Every month is paid at the same day. Meaning the calculation should result in 5% exactly. Because is the calculation method XIRR is based on a year and months don't contain the same amount of days, things go wrong. Is there a way to directly calculate with months or weeks?