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.
