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

PDF download Download Article
Understanding and using the PMT function to create a payment schedule effortlessly
PDF download 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

PDF download Download Article
    • This will help you find your work later on if you need to refer to it or make changes to the information.
    Advertisement
    • Type "Balance" in cell A1, "Interest rate" in cell A2 and "Periods" in cell A3.
    • Type "Monthly Payment" in cell A4.
    • 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.
  6. It will be labeled "fx."
    • 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.
    • Your calculated monthly payment will be shown in cell B4, next to the "Monthly Payment" label.
    13. Advertisement

Community Q&A

Search
Add New Question
  • 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.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 9 Helpful 9
  • 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.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 0 Helpful 2
  • 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.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 2 Helpful 2
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
        Helpful 3 Not Helpful 5
      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Name
      Please provide your name and last initial
      Submit
      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
        Helpful 2 Not Helpful 0
      Advertisement

      Things You'll Need

      • Computer
      • Microsoft Excel
      • Account details

      You Might Also Like

      How to
      Calculate Age on Microsoft Excel Using Functions
      How to
      Merge Cells in Microsoft Excel: A Quick Guide
      How to
      Unprotect an Excel Sheet
      How to
      Unhide All Rows and Columns in Microsoft Excel
      How to
      Use If‐Else in Microsoft Excel: Step-by-Step Tutorial
      How to
      Insert Pictures in Excel That Automatically Size to Fit Cells
      How to
      Create a Mortgage Calculator With Microsoft Excel
      How to
      Create a Graph in Excel
      How to
      Show Hidden Columns in Microsoft Excel & Unhide Column A
      How to
      Fix Ethernet Doesn't Have a Valid IP Configuration on Windows
      3 Easy Ways to Freeze Panes to Lock Columns or Rows in Excel
      How to
      Change from Lowercase to Uppercase in Microsoft Excel
      How to
      Make a Spreadsheet in Excel
      How to
      Link Sheets in Excel
      Advertisement

      References

      1. http://www.techonthenet.com/excel/formulas/pmt.php
      2. http://www.excel-easy.com/excel-function-pmt.html

      About This Article

      In other languages
      Thanks to all authors for creating a page that has been read 586,391 times.

      Is this article up to date?

      Advertisement