Data Table with Two Variables

Question: If I want to borrow money from a financial institution, can I create a table that displays the loan repayment figures based on various repayment periods and interest rates?


Answer: Yes, with the DATA INPUT TABLE (Using two variables)


Process (Excel 2003, 2007 and 2010):

By using the PMT function one can calculate the loan repayment installment. Let’s say you qualify for a loan of $1000 at 14 % over 12 months, the monthly installment amount will be $ 89.79.

1. Select cell C2

2. Type in  =Pmt (B2/12, B3,-B4)  and press enter

3. The formula returns the value of $89.79

4. Enter the following repayment periods of 6, 12, 18, 24, 30, 36 and interest rates of 8%, 10%, 12%, 14%, and 16% as given below.

5. Select C2:H8 and click on the Data ribbon.


6. Select What if Analysis and click on Data table.

7. Enter $B$2-Row input cell (interest rate) and $B$3-Column input cell (repayment period) and click OK as displayed above. That will give you a loan repayment table.