Line of Credit Tracker
Are you a small business owner or small-time lender wanting to track a line of credit? If you have tried our other line of credit calculator, you will find that it was designed for estimating costs and makes fairly general assumptions about when payments and draws are made. Our new Line of Credit Tracker below was designed specifically for tracking when payments, draws, and changes in interest rate occur.
Line of Credit Tracker (BETA)
for Excel
Description
This spreadsheet can be used to track payments and draws for a line of credit that accrues interest daily based on the current principal balance. It may not track perfectly with your existing line of credit. Please refer to the assumptions listed below to help determine whether this spreadsheet performs calculations the same way as your line of credit.
Note to Lenders: If you are going to use this spreadsheet to service an account that you are offering a client, make sure that the calculations and assumptions match what you have specified in your contract. Also, you are responsible to make sure that it is free of errors.
Update 1/24/2023 - Added a CF rule to make a date red (to indicate a warning or error) if it is the same date as the previous row. Multiple payments or draws on the same day should be combined into the same row so that there is only one row per date.
Estimating an Interest Reserve:
1. Enter the planned or actual disbursements by including the dates and the draw amounts.
2. If your line of credit transitions to a loan at some date, enter the date the loan will be converted to a standard loan.
3. The Total Interest Accrued amount would then be an estimate of what you'd need in an Interest Reserve account.
What to do when the interest rate changes:
Any time the interest rate changes, you should enter the date and a 0 payment. This will update the accrued interest and subsequent calculations will use the new rate.
Assumptions built into this spreadsheet
- Interest accrues on a daily basis, like most lines of credit. The Days in Year value should be 365 or 360 depending on how the lender calculates "per diem" interest.
- Interest, calculated using the simple interest formula (current principal balance times the per diem rate) is NOT added to the principal balance. The Total Owed is the Principal Balance plus the Unpaid Interest Balance.
- Payments are applied as of the end of the previous day. A payment is applied FIRST to the Interest Accrued and Interest Balance, and then to the Principal (if anything is left over).
- The Interest Accrued amount is rounded to the nearest cent (1/100).
- Interest is charged on a Draw on the day it is made.
- Principal-Only payments can be simulated by entering a negative draw amount.
Thanks to Jim Fohlin for his detailed suggestions and testing.
Version 2.0
We've had requests for a version that assumes Draws and Payments are applied at the END of the day, along with the ability to track fees. This version also permits entering multiple payments on the same date (which does not work in the original version). You can download the beta version 2.0 below.
⤓ Download 2.0 (XLSX)
