How to Calculate Credit Card Payments in Excel

Step-by-Step Guide

1. 1

   Step 1: Launch Excel.

   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. , , 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." Select the "PMT" function and click "OK."
   
   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. ,, You should now see the necessary credit card payment amount required to pay off your balance within the specified period.
2. 2

   Step 2: Label the first 5 cells down column A as follows: Interest rate

3. 3

   Step 3: Number of periods

4. 4

   Step 4: Present value

5. 5

   Step 5: Future value and Payment.

6. 6

   Step 6: Enter the interest rate for your credit card balance in column B

7. 7

   Step 7: next to the "Interest rate" label.

8. 8

   Step 8: Determine the time frame in months in which you would like to have your credit card balance paid off.

9. 9

   Step 9: Input the current credit card balance in the cell in column B next to the label

10. 10

    Step 10: "Present value."

11. 11

    Step 11: Type "0" in the cell in column B next to the label "Future value."

12. 12

    Step 12: Click to select the cell in Column B next to the label "Payment

13. 13

    Step 13: " then click the function button (fx) at the left edge of the data entry window to calculate your credit card payment.

14. 14

    Step 14: Enter the data pertaining to your credit card balance

15. 15

    Step 15: rate and projected payment periods from the fields in column B into the function window.

16. 16

    Step 16: Leave the "Type" field in the function argument window blank.

17. 17

    Step 17: Choose "OK" in the function argument window.

Detailed Guide

About the Author