+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Registered User
    Join Date
    05-31-2012
    Location
    Enschede, Netherlands
    MS-Off Ver
    Excel 2003
    Posts
    56

    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

  2. #2
    MoneyMaker
    Guest

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

    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

  3. #3
    Registered User
    Join Date
    05-31-2012
    Location
    Enschede, Netherlands
    MS-Off Ver
    Excel 2003
    Posts
    56

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

    It indeed is a compounded interest (per month), as I think about about every organisation calculates with. Just to determine the monthly amounts the simple interest would result in the same sum as a compounded interest as in the assumption for paying, customers pay their interest every month. Reality is different though and when we stick to the original payments (amounts) but people pay at different times simple interest doesn't equal compound interest. For the person who goes after the payments it is not considered practical to change the amount, or recalcalculate according to compound interest for every payment. Lets say customer has to pay 500 USD at march 3rd. Only by march 19th customer has 300 USD available. Next month customer might pay 700 USD on April 17. At the end of the loan we would like to be able to decide how much we could charge additionally (based on the compound interest) and in case we don't, what is the actual compound interest rate (in case we don't charge an additional payment).
    Last edited by Dubrock; 05-31-2012 at 10:14 AM.

  4. #4
    MoneyMaker
    Guest

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

    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
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-31-2012
    Location
    Enschede, Netherlands
    MS-Off Ver
    Excel 2003
    Posts
    56

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

    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????

  6. #6
    MoneyMaker
    Guest

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

    Quote Originally Posted by Dubrock View Post
    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????
    $2,000(1 + 5.13% x 5) - $2,000(1 + 5% x 5)
    $2,000(1.2565) - $2,000(1.25)
    $2,513 - $2,500
    $13
    Last edited by MoneyMaker; 05-31-2012 at 09:04 PM. Reason: corrected the interest rate

  7. #7
    Registered User
    Join Date
    05-31-2012
    Location
    Enschede, Netherlands
    MS-Off Ver
    Excel 2003
    Posts
    56

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

    I do have a few problems with your calculation. I am afraid the situation is more complicated than you show, or maybe I make it too complicated.

    In your example you say the additional amount to be paid is 13. Which cant be the case. In this case the customer pays a lot later then expected.
    First there is calculated with 5% based on not paying off any of the invested money. The customer does pay part off though, which makes the effective interest something like 7.92% (instead of 5%)
    To calculate the amount the customer has to pay you calculate with the same 5 months. Shouldn't this be adapted acoording to when is actually paid?


    Think I found it now:

    investment(average)*months*interest rate=interest

    calculation of interest: 2000*5*5%=500

    as the customer is supposed to pay in 5 terms of 500 the effective interest is: 7.92%

    the average investment is then: 500/7.92%/5=1262.63

    lets say the actual interest rate (based on real payments) is 6.04% (meaning average capital*months=8278.15)

    than the additional amount to be charged is: (7.92-6.04)*8278.15=155.63
    Last edited by Dubrock; 06-01-2012 at 02:49 AM.

  8. #8
    Registered User
    Join Date
    05-31-2012
    Location
    Enschede, Netherlands
    MS-Off Ver
    Excel 2003
    Posts
    56

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

    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?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1