If you're going to take out a loan that you have to pay back every month, there's a calculation that will tell you the monthly payment amount. This is the Payment function, and it works the same in any version of Excel in Windows and Mac, and also on Google Sheets and Apple Numbers.
The idea is this: you tell the worksheet how much you're borrowing, how long it will take to pay it back with regular installments, and what the interest rate is. The Payment function uses that information to tell you how much each payment will be. That will be a single number. But what if you want to see what the payment will be if some of the input numbers are different?
This is where a data table comes in. You plug in your original calculation, and set up a table that has row and column headers containing alternate numbers. Excel then automatically fills in the table, so you can see the many possibilities. Here's everything you need to know about the Payment function, and how you can use it with a data table to explore how changing variables will affect your loan payments.
Screencast
You can follow along with the payment function parts of this tutorial in any version of Microsoft Office Excel on the Mac or PC, or in any alternate spreadsheet app including Google Sheets and Numbers. The data table feature is only available in the Windows and Mac desktop versions of Excel, though, and you won't find it in the Excel web app, Google Sheets, or Numbers.
We've included a template spreadsheet file that you'll find on the top left of this tutorial that you can use to follow along, or just make your own spreadsheet as you work through the tutorial so you can get practice using your own spreadsheet skills. Let's get started.
The Payment Function
To use the Payment function, there are three parameters you need to know, and two optional parameters you can use:
- Interest rate
- Number of periods
- Initial amount (PV)
- Amount when finished (FV)
- Payment type (i.e. time)
The syntax for the Payment function (PMT) is:
=PMT(rate, periods, pv, [fv], [type])
Open the spreadsheet template from the top of this tutorial and go to the Basic workbook, or type in what's in the image below—or your own numbers that'd correspond to the same parameters. These are typical numbers you might have when buying a house:
Since we want to know the monthly payment, we have to convert all the values to months. That means dividing the yearly interest rate by 12, and multiplying the number of years by 12. To do that, click cell B10 in your spreadsheet and enter the function:=pmt(B4/12,B5*12,B3,B6,1)
Notice that the result has a negative value: -1,427.49.
The reason it's negative is because it's a cash outflow. If that looks strange, you can fix it either by making the loan amount negative—which might look equally strange—or by editing the formula to put a negative sign before the loan amount in cell B3. To do that, edit the formula to be the following:=pmt(B4/12,B5*12,-B3,B6,1)
That makes the Monthly payment a positive number, as you'd expect:
Substituting different values
If you want to see how the monthly payment will rise or fall with different input values, you could edit cells B3, B4 and B5 as much as you want, but you'll see only one result at a time. That's where the data table feature of Excel comes in, as it lets you see many different outcomes at once. As mentioned previously, this tool is only in the desktop versions of Excel, so if you're using Google Sheets, Numbers, or even the Excel Web App, you'll need to just swap in different values manually. But if you have Excel, here's a chance to see its power in action.
In your template spreadsheet, go to the Multiple Values sheet—or if you're making your own spreadsheet, add a new sheet and type in the values below (or, again, your own values that correspond to the parameters):
On top, we have the same input values as in the first part of this tutorial, and for practice, we'll enter the Payment function again in B10. But now, we also have table headers. The headers across row 10 are different possible loan amounts, and the values down the lower part of column B are different possible interest rates. Inside the table, we'll calculate what the monthly payments will be if we substitute these values for what's in B3 and B4, giving you an easy way to see how your payments would differ if your loan amount or interest rate vary. The 30-year payback period will remain the same, but you could easily use row 10 or column B for that, instead, if you'd like to see how changing the length of the loan would change the values.
Let's go ahead and get that data table filled. First, in cell B10, once again enter the formula:
=PMT(B4/12,B5*12,-B3,B6,B7)
Now select the entire range—the entire rectangle of cells bordered by your new loan amounts and interest rates. Most importantly, make sure the original monthly payment value is in the upper-left corner of the selection, or the data table tool won't work.
Now, go to the Data tab in the top of your Excel window, click the What-If Analysis button, and choose Data Table from the menu. In Windows, the button is on the right side of the ribbon, and on the Mac, the button will be on the left side of the ribbon.
This will open a dialog where you'll tell Excel where to find the original values that your variables will be substituting for. The header row of the table—Row 10—has substitutes for the loan amount, which is in B3, so select the Row input cell box, then click cell B3. Now, press Tab or click in the Column input cell box. The column headers down column B are substitutes for the interest rate, which is in B4, so click cell B4.
Click OK, and the whole table will automatically be filled in with your potential monthly payments depending on your loan amount and interest rate:
You might want to format these as currency. Select just the numbers inside the table, and on the Home tab, click the Comma Formatting button. Or use the keyboard shortcut: Ctrl-Shift-! (Command-Shift-! on the Mac).
More Than Just Loans
You can use the Data Table feature in Excel to substitute values for any type of calculations, but it works especially well for financial calculations. Just remember that the original formula has to be in the upper-left corner.
Conclusion
Now you know how to calculate your monthly payments on a loan, and how to use a data table in Excel to see how the monthly payment will change with different combinations of input values. This way you can make informed decisions: maybe you can afford a payment only up to a certain amount, or maybe you can save money by paying back over a shorter period of time, or you'll want to borrow more or less, depending on the resulting number. And if you're the lender, you'll have an idea of whether making the loan is the return on investment you want.
Spreadsheets are a powerful tool for quickly crunching numbers, and are especially handy for financial calculations. Once you've purchased that new house, car, or equipment you're taking out a loan for, you'll need to keep track of your depreciation on that asset. For that, don't forget to check our Using Spreadsheets to Calculate Depreciation tutorial.
Please note: This tutorial is not intended to give you financial advice, but only to explain how to use spreadsheets for loan calculations. Please consult a qualified financial advisor before making any financial decisions.
