Download Article
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
Download Article
-
Launch Excel. -
Label the first 5 cells down column A as follows: Interest rate, Number of periods, Present value, Future value and Payment. [1] X Research sourceAdvertisement -
Enter the interest rate for your credit card balance in column B, next to the "Interest rate" label.- 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.
-
Determine the time frame in months in which you would like to have your credit card balance paid off.- 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.
-
Input the current credit card balance in the cell in column B next to the label, "Present value."- For this example we will assume a credit card balance of $5,000.
-
Type "0" in the cell in column B next to the label "Future value." -
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] X Research source- 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]
X
Research source
- Select the "PMT" function and click "OK."
- Excel will pop up a window for you to choose the function you need.
-
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.
-
Leave the "Type" field in the function argument window blank. -
Choose "OK" in the function argument window. You should now see the necessary credit card payment amount required to pay off your balance within the specified period.
Advertisement
Community Q&A
Search
-
QuestionWhy does my answer show up as a negative when none of the input values are negative?Community AnswerIt is a quirk of Excel. Place a negative symbol next to the PV cell name and the answer will become positive.
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] X Research source 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
- You can increase the number of periods to lower your monthly payment, if necessary.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
- 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
Advertisement
References
About This Article
Thanks to all authors for creating a page that has been read 179,821 times.
Reader Success Stories
- "I created the budget I wanted to in helping me pay off my credit cards. I was so proud of myself. I was able to save it and make changes as I go by increasing the payments or decreasing payments as I pay off each credit cards." ..." more
Advertisement
"I created the budget I wanted to in helping me pay off my credit cards. I was so proud of myself. I was able to save it and make changes as I go by increasing the payments or decreasing payments as I pay off each credit cards."
..."
more
"All of the other templates I found online didn't give me the formulas, even though most people know copying and pasting already complete spreadsheets then editing doesn't work well. This helped me make my own!"
..."
more
"Taking an online math class. I did not understand how to calculate the interest rate in current credit card bill. This broke each step down for me, allowing me to get a clear picture of how its done."
..."
more
"This article was clear, concise, and to the point. It was incredibly simple to follow and I would highly recommend it to to others."
..."
more
"Could have figured it out on my own, but was able to do what I needed in a quarter of the time. Thanks so much. "
