# 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.