How to Calculate Credit Card Interest in Excel (3 Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

While Credit Card is a widely used and convenient financial tool to borrow or lend money from the bank, oftentimes the interest on credit cards could be very high. In this tutorial, I will show you how to calculate credit card interest in Excel to help you reduce or eliminate credit card debt or switch to a credit card with a lower interest rate.


3 Easy Steps to Calculate Credit Card Interest in Excel

To calculate the interest on a credit card we will need to have all the relevant information regarding the credit card. We should know the Current Balance, Minimum Payment Percentage, and Annual Interest Rate for the card. You will find all the information at the top or bottom of the latest credit card statement that the bank sent to you.

How to Calculate Credit Card Interest in Excel


Step 1: Calculate Monthly Interest Amount to Find Out Credit Card Interest

  =C5*C6/12

Formula Breakdown:

Here,

C5 = Initial Balance =  $2,000

C6 = Annual Interest Rate =  20%

We are calculating monthly interest amounts. So, we have divided the annual interest rate by 12.

Calculate Monthly Interest Amount to Find Out Credit Card Interest

  • Upon pressing ENTER, we will get the monthly interest amounts for the Visa credit card.

Calculate Monthly Interest Amount to Find Out Credit Card Interest

  • We will drag the Fill Handle rightward to apply the formula to the Mastercard credit card.

Calculate Monthly Interest Amount to Find Out Credit Card Interest

  • Now, we will get the monthly interest amounts for the Mastercard credit card.

Calculate Monthly Interest Amount to Find Out Credit Card Interest

Read More: How to Calculate Interest on a Loan in Excel


Step 2: Find Out the New Balance to be Paid in Excel to Calculate Credit Card Interest

  • Now, we will calculate the new balance for the Visa credit card that we have to pay. We will write down the below formula.
=C5+C7-C8

Formula Breakdown:

Here,

C5 = Initial Balance =  $2,000

C7 = Monthly Interest Amount=   $33

C8 =  Minimum Payment =  $100

We will have to pay the Initial Balance and the Monthly Interest Amount. But we have already paid the Minimum Payment. So, we will subtract the  Minimum Payment from the sum of the Initial Balance and the Monthly Interest Amount to calculate the New Balance.

Find Out the New Balance to be Paid in Excel to Calculate Credit Card Interest

  • Upon pressing ENTER, we will get the New Balance for the Visa credit card.

New Balance for the Visa card

  • We will drag the Fill Handle rightward to apply the formula to the Mastercard credit card. We will get the New Balance for the Mastercard credit card.

Find Out the New Balance to be Paid in Excel to Calculate Credit Card Interest

Read More: How to Calculate Interest in Excel with Payments


Step 3: Calculate the New Balance to be Paid in Excel

  • Finally, we will calculate the Total Payment for all our credit cards. We will write down the below formula.
=SUM(C10:D10)

Formula Breakdown:

Here,

C10 = New Balance for the Visa credit card=   $1,933

D10 = New Balance for the Mastercard credit card=   $958

The SUM function will sum up all the cell values in the given range. So, it will sum up both the New Balances for the Visa and Mastercard credit cards to calculate the Total Payment for the 2 credit cards.

The SUM function

  • Upon pressing ENTER, we will get the Total Payment for both of our credit cards.

Calculate the New Balance to be Paid in Excel


Quick Notes

🎯  Always use the correct format for each cell value. For example, Initial Balance, Monthly Interest Amount, and Minimum Payment will always be in the Currency format. The Annual Interest Rate will be in the Percentage format.

🎯 Select a cell and right-click on it. Select Format Cell from the window that will appear. Select the Currency or Percentage format depending on the type of the cell value.


Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


Conclusion

In this article, we have learned how to calculate credit card interest in Excel. I hope from now on you can calculate credit card interest in Excel very easily. However, if you have any queries or recommendations about this article, please leave a comment below. Have a great day!!!


Related Articles


<< Go Back to Calculate Interest In Excel | Excel for Finance | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
ASM Arman
ASM Arman

Abu Saleh Arman is a Marine engineer and Excel & VBA expert. He loves programming with VBA. He finds VBA programming a time-saving tool to manipulate data, handle files, and interact with the internet. He is very interested in Python, MATLAB, PHP, Deep Neural Networks, and Machine Learning, showcasing his diverse skill set. Arman holds a B.Sc in Naval Architecture & Marine Engineering from BUET, Bangladesh. However, he switched to a content developer, where he writes technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo