Results 1 to 6 of 6

Thread: Daily Loan Tracking schedule

  1. #1

    Question Daily Loan Tracking schedule



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I'm in need of an excel spreadsheet for tracking loans on a daily basis. The loan is for 75 days and the interest accrues on day basis. The basic purpose is to keep track of the interest amount daily and to plan the repayments.

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    766
    Articles
    0
    Excel Version
    2010
    Can you elaborate on how the loan system works ? e.g. do the clients make regular payments, and at what intervals?

  3. #3
    Actually the loan is to be repaid within 75 days till which the interest rate remains same,else it increases.The plan is to repay the loan whenever idle funds are lying with the business.I need a sheet where one can see how much the interest portion will reduce on making a repayment on a particular date or how much needs to be repaid.

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    766
    Articles
    0
    Excel Version
    2010
    So you take out a loan for 75 days at a fixed interest rate, and you would repay it all (including interest) as one lump sum at some time during the 75 day period ?

    Is that correct ?

    Can I assume that there is no financial penalty for settlement before the 75th day?

  5. #5
    Exactly till 75th day there is no penalty.I have attached a file,you"ll get an idea as to what I'm looking for.
    Attached Files Attached Files

  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    766
    Articles
    0
    Excel Version
    2010
    Hello pranjal
    I noticed in your example that there were one or two anomalies.
    1. You have an interest rate of 11.4 %, and you should divide this by 100 before using it to calculate the amount of interest, otherwise it is overstated. In your example you are getting accrued interest of over 3% per day!
    2. Also it is coustomary to use the compound rather than the simple interest method, so:
    Interest for 22/10/2014 = 100000 x 0.00031233 = 100031.233
    Interest for 23/10/2014 = 105031.233 x 0.00031233 = 105064.03740500

    The method of calculating that i think you need is as follows

    RV=LV * (1+(AIR/365))^LP

    RV = Repayment Value Cell D2 = 5209.70
    LV = Loan Value Cell A2 = 5000.00
    AIR = Annual Interest Rate Cell B2 = 20%
    LP = Loan Period (days) Cell C2 = 75

    Formula for D2 is: = A2 * POWER(1 +(B2/365),C2) This give the amount to repay when the loan completes 75 days, and to calculate the same for a different period, simply amend the number of days applied.
    Last edited by Hercules1946; 2014-12-09 at 05:50 PM.

Posting Permissions

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