Excel System for Vehicle Finance Office

Project overview
• Need an Excel-based system for a small vehicle finance office (4‑wheelers and 3‑wheelers).
• Objective: move from handwritten registers to a single Excel workbook that tracks all agreements, instalments and reminders with minimal manual work.[freelancer +3]
---
Core requirements
• Create a Master “Agreement” sheet (one row per agreement) with:
• Hirer details (name, address, mobile).
• Guarantor details (name, mobile).
• Vehicle details (type, make, model, year, regn no, engine no, chassis no).
• Finance details (loan/finance amount, total amount to recover, number of instalments, instalment amount, first due date, frequency).
• Create an Installments / Dump sheet (one row per instalment) that can be auto‑generated from the master:
• Columns for Agreement No, Hirer Name, Instalment No, Due Date, EMI amount, Amount Received, Date Received, balance and status.
• Use Excel financial and date formulas ( PMT ,  IPMT ,  PPMT ,  EDATE , etc.) where appropriate to generate EMI schedules.[ankeshan +1]
• Build a Reminders / Follow‑up sheet:
• Automatically shows all overdue instalments and those due in the next X days (e.g., 7–15 days) based on  TODAY() .
• Columns for Last Contact Date, Remarks/Comments, Next Reminder Date.
• Conditional formatting for clear visual alerts (red = overdue, yellow = due soon).[microsoft +4]
• Add a Documents Checklist:
• Track for each Agreement No whether KYC and vehicle documents are received (Aadhar, PAN, photos, RC copy, insurance, pollution, hypothecation papers, post‑dated cheques, etc.).
• Simple Yes/No dropdowns and an overall “Docs Complete?” indicator.
---
Automation and usability
• Prefer a simple data entry form/UserForm (or clearly structured input area) to add new agreements:
• On “Save”, it should update the Master sheet and auto‑generate all related instalments in the Dump sheet.[sheet-vault +2]
• Optional: a small form to post payments (select Agreement + Instalment, enter amount & date) and update the Dump sheet.
• File should refresh reminders automatically when opened (or via a clear “Refresh” button).
• Protect formula cells and keep data entry areas very simple so a non‑technical user can operate daily.
---
Technical expectations
• Must run smoothly in latest desktop Excel on Windows (no external add‑ins).
• Clean, well‑structured workbook using tables, formulas, conditional formatting and light VBA only where it adds value.[getelyxai +2]
• Clear labels, basic instructions, and example data for 2–3 sample agreements so we understand how to use it.
---
Scope and deliverables
• I currently have 30–35 existing agreements; template should be ready to input these and scalable for many more.
• Final delivery: unlocked Excel file, with any VBA code visible and editable + short note explaining key sheets/macros.
• Please share:
• Relevant Excel/VBA portfolio (especially loan/EMI trackers, debt or finance templates).
• Estimated timeline and fixed‑price quote for this project.