# Create A Variable Payment Table In #Excel

Here’s how to calculate the monthly payments on a loan and create a table with various interest rates and loan lengths.

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!

Happy Excelling!