Excel Class-19 | EMI Calculator in Excel | Create EMI Schedule

Build a complete EMI Calculator in Excel — learn the formula, use the built-in PMT function, and create a full amortization schedule (interest, principal, balance). Perfect for students, loan planners, and business users. इस वीडियो में हम Excel में पूरा EMI कैलकुलेटर बनाएँगे — फॉर्मूला, PMT फ़ंक्शन और पूरी अमोर्टाइज़ेशन टेबल (ब्याज़, मूलधन, शेष) दिखाएँगे। 🔑 Keywords (English + Hindi) EMI calculator Excel Excel EMI schedule PMT function Excel Amortization table Excel EMI formula calculator excel emi hindi एक्सेल emi कैलकुलेटर pmT फ़ंक्शन एक्सेल 🏷 Tags (English + Hindi) Excel EMI calculator, EMI schedule, PMT Excel, amortization table, loan EMI Excel, Excel tutorial Hindi, Laqshya Online Training, एक्सेल emi, EMI तालिका, ऋण गणना 📌 Hashtags #ExcelClass19 #EMICalculator #PMT #Amortization #ExcelTutorial #LaqshyaOnlineTraining #EMI #ExcelHindi #LoanCalculator 🎥 Video Outline / Script (Suggested sections) Intro (what EMI is and formula). / परिचय: EMI क्या होता है। Show inputs on sheet (Loan Amount, Annual Rate, Tenure). / शीट पर इनपुट दिखाएँ। Show two methods: (A) manual formula, (B) Excel PMT function. / दो तरीके दिखाएँ। Build EMI cell & show numeric example. / EMI निकालकर दिखाएँ। Create amortization table (Payment No, Date, Begin Bal, EMI, Interest, Principal, Ending Bal). / अमोर्टाइज़ेशन बनाएँ। Add totals & conditional formatting, and how to change loan values. / Totals और formatting। Wrap up & CTA (download template/subscribe). / समाप्ति 🔢 Excel setup & exact formulas (cell-based, copy-paste ready) Sheet layout (example cell references): B1 = Loan Amount (P) — eg 100000 B2 = Annual Interest Rate (%) — eg 12 B3 = Tenure (Years) — eg 2 (or use Months — alternate shown) B4 = Tenure (Months) → =B3*12 B5 = Monthly Rate → =B2/100/12 Method A — using PMT (recommended) B6 (EMI) → =-PMT(B5,B4,B1) Explanation: PMT(rate, nper, pv). Negative sign returns positive EMI. Numeric example (for demo): Loan = ₹100,000, Rate = 12% p.a., Tenure = 2 years (24 months) → EMI ≈ ₹4,707.35. (Computed value you can show: =ROUND(-PMT(12%/12,24,100000),2) → 4707.35) Method B — manual formula (for teaching the math) B6_manual → =B1*B5*(1+B5)^B4/((1+B5)^B4-1) 📋 Amortization table (row 10 header example) Put headers in row 10: A10: Payment# | B10: PaymentDate | C10: BeginBal | D10: EMI | E10: Interest | F10: Principal | G10: EndBal Row 11 formulas (first payment): A11 = 1 B11 = =DATE(Year,Month,Day) or link to start date cell (e.g. =$B$8 if B8 is start date). C11 = =$B$1 (Beginning balance = Loan) D11 = =$B$6 (EMI cell) E11 = =C11*$B$5 (Interest = BeginBal × monthly rate) F11 = =D11-E11 (Principal portion) G11 = =C11-F11 (Ending balance) Row 12 (copy down for payment 2 etc): A12 = =A11+1 B12 = =EDATE(B11,1) (if you want monthly dates) C12 = =G11 D12 = =$B$6 E12 = =C12*$B$5 F12 = =D12-E12 G12 = =C12-F12 Drag down until Ending balance reaches zero (or payments = B4). 🧮 Useful Excel functions for enhancements PMT(rate,nper,pv) → EMI IPMT(rate,per,nper,pv) → interest portion for nth payment PPMT(rate,per,nper,pv) → principal portion for nth payment CUMIPMT, CUMPRINC → cumulative interest/principal over period EDATE(start, months) → increment payment dates ROUND() → round values for currency display Conditional Formatting → highlight last payment or negative balances