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.