How to Use Excel’s PMT Function to Find Payment Amounts

Ticannapa

Editor

posted on 13 years ago — updated on 1 second ago

24
views

Excel is the spreadsheet application component of the Microsoft Office suite of programs. Using Microsoft Excel, you can calculate a monthly payment for any type of loan or credit card. This will allow you to be more accurate in your personal budgeting and to allocate adequate funds for your monthly payments. The best way to calculate a monthly payment in Excel is by using the "functions" feature.

Things You Should Know

Use the PMT function to calculate monthly payments for a loan based on constant payments and interest rates.
To use the PMT function, you'll need to specify the balance, interest rate, and number of months over which you want to make payments.

Launch Microsoft Excel and open a new workbook.

Save the workbook file with an appropriate and descriptive name. This will help you find your work later on if you need to refer to it or make changes to the information.

Create labels in cells A1 down to A4 for the variables and result of your monthly payment calculation. Type "Balance" in cell A1, "Interest rate" in cell A2 and "Periods" in cell A3. Type "Monthly Payment" in cell A4.

Enter the variables for your loan or credit card account in the cells from B1 down to B3 to create your Excel formula. The outstanding balance due will be entered in cell B1. The annual interest rate, divided by the number of accrual periods in a year, will be entered in cell B2. You can use an Excel formula here, such as "=.06/12" to represent 6 percent annual interest that is accrued monthly. The number of periods for your loan will be entered in cell B3. If you are calculating the monthly payment for a credit card, enter the number of periods as the difference in months between today and the date you would like to have your account paid in full. For example, if you would like to have your credit card account paid off 3 years from today, enter the number of periods as "36." Three years multiplied by 12 months per year is equal to 36.

Select cell B4 by clicking on it.

Click the function shortcut button at the left edge of the formula bar. It will be labeled "fx."

Search for the "PMT" Excel formula if it is not shown in the list.

Highlight the "PMT" function and then click the "OK" button.

Create references to the cells in which your details have been entered for each field in the "Function Arguments" window. Click inside the "Rate" field window and then click cell B2. The "Rate" field will now pull the information from this cell. Repeat for the "Nper" field by clicking inside this field and then clicking cell B3 to force the number of periods to be pulled. Repeat once more for the "PV" field by clicking inside the field and then clicking cell B1. This will force the balance of your loan or credit card account to be pulled for the function.