How to Calculate Credit Card Payments in Excel

If you're trying to get a handle on your credit card debt situation, you're going to have to learn about your credit card payments and how they are calculated. This way you can figure out how many months it will take to pay off your debt. This process is best for someone who has a familiarity with Excel spreadsheets, yet with a little practice, even a novice should be able to set up a credit card payment spreadsheet this way.

How your Excel credit card payments spreadsheet will look

Step

Call your credit card company and ask the representative for the percentage that the company uses to calculate your minimum credit card payment. Due to recent regulations, many companies now have to set your minimum payment at as much as 4 percent of your balance each month. Let's use 4 percent as the minimum payment percentage and 14 percent as the annual interest rate on the card.

Step

In Excel, type "Balance" in the first column, tab over and type "Payment" in the next column, then "Interest" and "Principal" in the next two columns. These will be your headings.

Video of the Day

Step

Press "Enter" to go to the next line. Type your current credit card balance under the first row beneath "Balance" (cell A2). Tab over to the next column under "Payment" (B2) and type in the following formula: "=.04*A2" (leave out the quotation marks). The .04 represents the minimum payment percentage from step 1. The formula result should be $400.

Step

Tab over again and type in the following formula to calculate the amount of your monthly payment that is going toward interest: "=(.14/12)*A2" (again, no quotation marks). In this example, the result should be $116.67. Finally, tab over once more---you should be at cell D2 at this point---and type in "=B2-C2" to get an automatic calculation of the amount of principal that you will be paying down with that payment. The result should be $283.33 to demonstrate our example.

Step

Press "Enter" once all of your formulas are set to bring yourself to cell A3. Now, type in just one more formula, "=A2-D2" into cell A3. This formula will automatically calculate your new balance after you make a monthly payment.

Step

Select cell B2, which is $400 in this example, grab the tiny square in the lower-right corner of the cell and drag it down with your mouse into cell B3. A calculation should occur, showing you your new minimum payment for that month. Do the same thing for cells C2 and D2.

Step

Select the entire third row with your mouse, find the tiny square again in the corner and then drag everything down as many rows as you'd like to see your minimum payments into eternity (each row represents a month). Plug your own credit card interest rate and minimum payment percentage into the first line of this Excel sheet before dragging anything down.