How to Calculate Credit Card Payments in Excel

PDF download Download Article
PDF download Download Article

Microsoft Excel is a comprehensive spreadsheet and financial analysis tool and can be used in business and in personal finances. Among other budgeting tools, Excel also features functions that can help you calculate payment amounts, mortgage amortization schedules and other helpful figures. Calculate credit card payments in Excel to achieve a much faster pay-off of all balances.

Steps

PDF download Download Article
  2. Interest rate, Number of periods, Present value, Future value and Payment. [1]
    Advertisement
    • Because the interest rate listed on your credit card statement is an annual rate, but this calculation requires the monthly interest amount, calculate the interest within the cell by dividing the interest rate by the number of months in a year (12).
    • For instance, a 10% rate will require you to type "=.10/12" in the cell and press enter.
    • Enter this is the cell in column B next to the label, "Number of periods."
    • For instance, if you would like to have your balance paid off within 3 years, enter "36" in this cell.
    • For this example we will assume a credit card balance of $5,000.
  7. Click to select the cell in Column B next to the label "Payment," then click the function button (fx) at the left edge of the data entry window to calculate your credit card payment. [2]
    • Excel will pop up a window for you to choose the function you need.
    • If "PMT" (payment) is not already in the list of functions, enter it in the search window and click "Go." [3]
    • Select the "PMT" function and click "OK."
  8. Enter the data pertaining to your credit card balance, rate and projected payment periods from the fields in column B into the function window.
    • You can type the data into each field.
    • Alternatively, you can click on the Cell Link icon and then click the cell containing the information. After each entry, click the Cell Link icon again to return to the function window.
  10. You should now see the necessary credit card payment amount required to pay off your balance within the specified period.
    11. Advertisement


Community Q&A

Search
Add New Question
  • Question
    Why does my answer show up as a negative when none of the input values are negative?
    Community Answer
    It is a quirk of Excel. Place a negative symbol next to the PV cell name and the answer will become positive.
    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 10 Helpful 18
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Tips

      • If you have a budgeted amount to pay on your credit card, you can calculate the time needed to reach a zero balance by using the "NPER" function [4] and entering the interest rate (divided by 12 months), budgeted payment amount and present value or balance on the credit card account. Enter "0" in the "FV" field and leave the type blank.
        Thanks
        Helpful 0 Not Helpful 0
      • You can increase the number of periods to lower your monthly payment, if necessary.
        Thanks
        Helpful 0 Not Helpful 0
      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

      • These calculations only work when no further charges are applied to the credit card in question. If you charge typical monthly expenses to a credit card and pay off the balance at the end of every month, use a different card than the one you are trying to pay off.
        Thanks
        Helpful 0 Not Helpful 0
      Advertisement

      You Might Also Like

      How to
      Use Excel’s PMT Function to Find Payment Amounts
      Calculate Interest Payments on a Loan with the IPMT Function
      How to
      Find Your Credit Card Account Number
      How to
      Sign a Credit Card
      How to
      Pay Someone Else's Credit Card Bill
      Can You Use Cards without the CVV Code? Scams, Fraud Protection & More
      2 Simple Ways to Verify Your Chase Card
      How to
      Make a Discover Card Payment without Complications
      How to
      Dispose of a Credit Card
      How to
      Change Credit Card Information
      How to
      Cancel a Credit Card Payment
      How to
      Protect Your RFID Credit Card
      How to
      Send Credit Card Information Securely by Email
      How to
      Receive a Refund on Prepaid Credit Cards
      Advertisement

      References

      1. ↑ https://exceljet.net/excel-functions/excel-pmt-function
      2. ↑ https://kb.wisc.edu/helpdesk/page.php?id=25582
      3. ↑ https://exceljet.net/formula/calculate-payment-for-a-loan
      4. ↑ https://support.office.com/en-us/article/nper-function-240535b5-6653-4d2d-bfcf-b6a38151d815

      About This Article

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

      Did this article help you?

      Advertisement