How to Calculate a Monthly Payment in Excel

Last Updated: October 25, 2022 Tested

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.

Steps

1
Launch Microsoft Excel and open a new workbook.
2
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.
Advertisement
3
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.
4
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.
5
Select cell B4 by clicking on it.
6
Click the function shortcut button at the left edge of the formula bar. It will be labeled "fx."
7
Search for the "PMT" Excel formula if it is not shown in the list.
8
Highlight the "PMT" function and then click the "OK" button.
9
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.
10
Leave the "FV" and "Type" fields blank in the "Function Arguments" window.
11
Complete the process by clicking the "OK" button.
- Your calculated monthly payment will be shown in cell B4, next to the "Monthly Payment" label.
12
Finished.

Community Q&A

Question

How do I calculate a loan payment based on the price of a house and a down payment?

Community Answer

Banks use all kinds of tricks. The bank reference book on interest is like 500 pages long. You have to ask the bank for the answer and by all means, shop around. Don't forget, there's also property taxes, insurance, heating, electric, water, maintenance, etc. If you default on any one of these, you lose the gamble.
Question

How do I calculate interest and principal if the payment amount is different from day to day?

Community Answer

To do so, add up all the payments of the month and then find the average by dividing the total payment by the amount of days.
Question

How do I calculate monthly payments over 10 years?

Community Answer

there are 120 periods in 10 years, so PMT(Interest rate(0.005)), period (120), your balance.

Warnings

Make sure you have properly converted your interest rate to a decimal and you divide the annual interest rate by the number of periods within a year that the interest is accrued. If your interest is accrued quarterly, you would divide the interest rate by 4. Semiannual interest rates are divided by 2.

⧼thumbs_response⧽
Helpful 2 Not Helpful 0