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.

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:

pmt1

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:

pmt2

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:

pmt3

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:

pmt4

Click OK and the table will be filled with the various monthly payments based on the variables indicated:

pmt5

Now you can see the impact of each variable on your monthly payment! A very nice tool in Excel!

Happy Excelling!

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

No comments yet.

Leave a Reply