Here’s how to calculate the monthly payments on a loan and create a table with various interest rates and loan lengths.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
I want to borrow money for a home. I need $100,000 and will do a 30 year mortgage at 3.75%. To determine what those payments will be, I will create the following formula:
What if I want to see how the payment will change based on different interest rates and different lengths of the loan? First I will create a table with those variables, making the current payment formula the left upper-most cell:
Next, highlight the table, and go to the Data Table option of the What If Analysis button in the Data Tools group on the Data tab:
Clicking on that option will present a pop-up. Enter into it the two cells to indicate the variables for the loan. The Row input is the Months variable, cell $D$4, and the Column input is the Interest Rate variable, cell $D$3:
Click OK and the table will be filled with the various monthly payments based on the variables indicated:
Now you can see the impact of each variable on your monthly payment! A very nice tool in Excel!