Download Article
Understanding and using the PMT function to create a payment schedule effortlessly
Download Article
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
Download Article
-
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.
Advertisement -
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.
-
Leave the "FV" and "Type" fields blank in the "Function Arguments" window.
-
Complete the process by clicking the "OK" button.
- Your calculated monthly payment will be shown in cell B4, next to the "Monthly Payment" label.
-
Finished.
Advertisement
Community Q&A
Search
-
QuestionHow do I calculate a loan payment based on the price of a house and a down payment?Community AnswerBanks 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.
-
QuestionHow do I calculate interest and principal if the payment amount is different from day to day?Community AnswerTo 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.
-
QuestionHow do I calculate monthly payments over 10 years?Community Answerthere are 120 periods in 10 years, so PMT(Interest rate(0.005)), period (120), your balance.
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
Advertisement
Video
Tips
- Copy the cells from A1 down and over to B4, and then paste this in cells D1 through E4. This will allow you to edit the details in this second calculation to review alternate variables while preserving your initial calculation.Thanks
Submit a Tip
All tip submissions are carefully reviewed before being published
Name
Please provide your name and last initial
Thanks for submitting a tip for review!
Advertisement
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.Thanks
Advertisement
Things You'll Need
- Computer
- Microsoft Excel
- Account details
References
About This Article
Thanks to all authors for creating a page that has been read 585,990 times.
Reader Success Stories
- "This was what I wanted, from step 1 to the very end, I cant believe I just solved my problem. Amazing - very clear and understandable. Thank you much." ..." more
Advertisement
"This was what I wanted, from step 1 to the very end, I cant believe I just solved my problem. Amazing - very clear and understandable. Thank you much."
..."
more
"Simple and explained the question perfectly. Well done, all editors. Love the site!"
"Pretty easy, fast and useful."
