Daily Loan Tracking schedule

pranjal

New member
Joined
Feb 21, 2014
Messages
14
Reaction score
0
Points
0
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.
 
Can you elaborate on how the loan system works ? e.g. do the clients make regular payments, and at what intervals?
 
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.
 
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?
 
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.
 

Attachments

  • Vendor Finance.xlsx
    9.9 KB · Views: 10
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:
Back
Top