How to Calculate the Credit Card Interest in Excel – 3 Steps

 

To calculate the interest on a credit card you need to know the Current Balance, Minimum Payment Percentage, and Annual Interest Rate.

This is the credit card statement:

How to Calculate Credit Card Interest in Excel


Step 1 – Calculate the Monthly Interest Amount to Find the Credit Card Interest

Calculate the monthly interest amount.

  • Enter the following formula.
  =C5*C6/12

Formula Breakdown:

C5 = Initial Balance =  $2,000

C6 = Annual Interest Rate =  20%

The annual interest rate is divided by 12.

Calculate Monthly Interest Amount to Find Out Credit Card Interest

  • Press ENTER to see the monthly interest amount for the Visa credit card.

Calculate Monthly Interest Amount to Find Out Credit Card Interest

  • Drag the Fill Handle to the right to apply the formula to the Mastercard credit card.

Calculate Monthly Interest Amount to Find Out Credit Card Interest

  • This is the output.

Calculate Monthly Interest Amount to Find Out Credit Card Interest

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


Step 2 – Find the New Balance to be Paid to Calculate the Credit Card Interest

  • Enter the formula.
=C5+C7-C8

Formula Breakdown:

C5 = Initial Balance =  $2,000

C7 = Monthly Interest Amount=   $33

C8 =  Minimum Payment =  $100

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

  • Press ENTER to see the New Balance for the Visa credit card.

New Balance for the Visa card

  • Drag the Fill Handle to the right to apply the formula to the Mastercard credit card. You will get the New Balance.

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

  • Enter the formula.
=SUM(C10:D10)

Formula Breakdown:

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

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

The SUM function sums all cell values in the given range:  both Visa and Mastercard credit cards New Balances to calculate the Total Payment for the 2 credit cards.

The SUM function

  • Press ENTER to see the Total Payment for both credit cards.

Calculate the New Balance to be Paid in Excel


Quick Notes

Initial Balance, Monthly Interest Amount, and Minimum Payment must be in Currency format. The Annual Interest Rate must be in Percentage format: Select a cell and right-click it. Select Format Cells. Choose Currency or Percentage format.


Download Practice Workbook

Download the practice workbook.


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
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