Download a free Loan Tracker Excel Sheet Template to easily track and manage loan details, payments, and outstanding balances. This ready-to-use Excel template helps you record loan amounts, lender details, interest rates, payment dates, installment amounts, paid amounts, remaining balances, and other important loan information in a simple and organized format. It is useful for monitoring loan payments and keeping accurate financial records.
Key Features :
- Loan ID
- Borrower Name
- Loan Type
- Loan Start Date
- Due Date
- Principal Amount
- Interest Rate
- Total Interest
- Total Payable
- Amount Paid
- Outstanding Balance
- Next Payment Date
- Payment Frequency
- Status
- Remarks
- Dahboard
- KPI Card
- Bar Chart
- PIE Chart
Formulas :
Total Interest
=F5G5IF(C5=”Education Loan”,1,IF(C5=”Vehicle Loan”,0.2,IF(C5=”Business Loan”,0.2,0.3)))
Total Payable
=F5+H5
Amount Paid
=I5*0.55
Outstanding Balance
=MAX(I5-J5,0)
Total Loans =COUNTA(‘Loan Tracker’!A5:A1000)
Total Principal =SUM(‘Loan Tracker’!F5:F1000)
Total Payable =SUM(‘Loan Tracker’!I5:I1000)
Amount Paid =SUM(‘Loan Tracker’!J5:J1000)
Outstanding Balance =SUM(‘Loan Tracker’!K5:K1000)
Overdue Loans =COUNTIF(‘Loan Tracker’!N5:N1000,”Overdue”)
Active Loans =COUNTIF(‘Loan Tracker’!N5:N1000,”Active”)
Closed Loans =COUNTIF(‘Loan Tracker’!N5:N1000,”Closed”)
Count
=COUNTIF(‘Loan Tracker’!N5:N1000,D4)
Outstanding Balance
=SUMIF(‘Loan Tracker’!C5:C1000,A14,’Loan Tracker’!K5:K1000)
