You have an EMI formula: loan in B1, annual rate in B2, years in B3.
=PMT(B2/12, B3*12, -B1)
Question: “What loan amount gives an EMI of exactly ₹25,000?” Data → What-If Analysis → Goal Seek: Set cell = the EMI cell, To value = 25000, By changing = B1. Excel iterates and fills in the loan amount.
One-variable Data Table
List interest rates down column D (7%, 7.5% … 10%). In E1 (one row above the first rate, one column right) put =B4 (the EMI cell). Select D1:E8 → What-If Analysis → Data Table → Column input cell = B2. Every rate’s EMI appears at once.
Two-variable Data Table
Rates down the side, loan tenures across the top, the EMI formula reference in the top-left corner. Row input = B3, column input = B2. You get a full EMI grid — perfect for choosing a loan.
💡 Data Tables recalculate constantly and can slow big workbooks. Set Formulas → Calculation Options → Automatic except for Data Tables and press F9 when you need fresh numbers.
Scenario Manager
For named combinations (“Best case”, “Worst case”) of several inputs, use Scenario Manager and its Summary report — a neat one-page comparison for management.
Want an instant answer? Try the free EMI calculator on this site.
