Loan EMI Schedule in Excel: A Delivery Van Loan with Prepayment

<p>Rukhsana Sheikh runs Tazaa Tokri, a vegetable-and-fruit box delivery business in Hyderabad, and she's buying her first delivery van on a five-year bank loan. Her CA wants the numbers behind it before she signs: the EMI, the interest she'll really pay, the cash she needs on day one, the interest in each financial year for the books, and what the loan looks like on the balance sheet. Across five lessons you build the whole loan model from zero in your own personal workbook: turn yearly rates and tenures into monthly ones, calculate the EMI with PMT, build a 60-row repayment schedule with EDATE and a reducing balance, total it by financial year with SUMIFS, split the loan into current and non-current, and model a part-prepayment both ways (shorter tenure or smaller EMI) with MIN, NPER and the bank's charge with GST. Every lesson then hands the same job to your AI (Copilot, ChatGPT, Claude or Gemini) and shows you exactly how to catch its mistakes. In the capstone your AI decodes a dealer's low-looking flat rate with RATE and compares it with the bank, and you prove every number.</p>

Tools: PMT, RATE and NPER, Loan amortisation schedules, EDATE and date-range SUMIFS, MIN, IF and INDEX, Data validation and conditional formatting, AI assistants for Excel