A credit card can be a blessing or a curse. It all depends on a person’s financial knowledge. This article will show you quick steps to **create a credit card payoff calculator** with **amortization** in Excel. We will also insert an ** Excel VBA Macro** to hide unnecessary data.

## Download Practice Workbook

## Step-by-Step Procedures to Create Credit Card Payoff Calculator with Amortization in Excel

Here is a quick look at the **credit card payoff calculator **with **amortization**, which we will create by the end of this tutorial.

### Step 1: Setting up the Essentials

In this first step, we will create the fields where the basic info about the debt will be inserted.

- Firstly, type the following fields related to the credit card payoff:
- Present Balance
- Interest Rate
- Each Payment
- Payment Starts
- Payment Ends

- Secondly, type these things under the Total field which we will find at the last step of this article.
- Principal
- Interest
- Amount
- Months

- Thirdly, type the details of the first four fields into the
**calculator**. - Lastly, create the following
**5**columns:- Date
- Payment
- Principal
- Interest
- Balance

**Read More:**** Create Multiple Credit Card Payoff Calculator in Excel Spreadsheet**

### Step 2: Calculating Initial Amounts

In the second step, we will calculate the initial amount of the **credit card payoff calculator** with **amortization**.

- To begin with, type the following formula in cell
. This is the first payment date for our credit card debt.*B11*

`=C7`

- Next, type another formula to reference the monthly payment in cell
.*C11*

`=$C$6`

- Then, type a formula in cell
to find the amount of interest. We have divided by*E11***12**to find the*monthly interest rate*from the*yearly rate*.

`=C4*C5/12`

- After that, type this formula in cell
to find the principal amount that was paid.*D11*

`=C11-E11`

- Lastly, type this formula in cell
to find the remaining debts.*F11*

`=C4-D11`

**Read More:** **How to Calculate Credit Card Interest in Excel (3 Easy Steps)**

### Step 3: Calculating Remaining Amounts

In this step, we will use the ** DATE**,

**,**

*YEAR***, and**

*MONTH***functions to display incremental months. Then, we will use simple formulas to find the rest of the amortization amounts.**

*DAY*- Firstly, type a formula in cell
. This formula increases the date by*B12***1**month.

`=DATE(YEAR(B11),MONTH(B11)+1,DAY(B11))`

- Then,
the formulas from cells*AutoFill*and*B12*downward.*C11* - Afterward, type another formula in cell
. This formula finds the amount of interest on the remaining balance. As before, drag this formula down.*E12*

`=F11*$C$5/12`

- Now, type this formula in cell
to find the amount of principal paid. Then, drag the formula downward.*D12*

`=C12-E12`

- Then, type another formula in cell
to find the remaining balance and using the*F12*, fill in the formula into the rest of the cells.*Fill Handle*

`=F11-D12`

- Lastly, we can see that there are more than the necessary rows in the dataset. If our terms are larger, then we don’t need to do anything; it will show up automatically. However, it can be helpful if we hide these rows.

### Step 4: Applying VBA to Hide Extra Rows

In this step, we will hide the unnecessary rows using a ** VBA Macro**. After that, we will use another code to unhide them. Both of these codes will be applied to two separate

**buttons.**

*VBA*- To begin with, press
to bring up the*ALT+F11*window.*VBA Module* - Next, type this code.

```
Option Explicit
Sub Hide_Rows()
Dim Cell_Range As Range
Application.ScreenUpdating = False
For Each Cell_Range In Range("E11:E130")
If Cell_Range.Value < 0 Then
Cell_Range.EntireRow.Hidden = True
Else
Cell_Range.EntireRow.Hidden = False
End If
Next Cell_Range
Application.ScreenUpdating = True
End Sub
Sub Unhide_Rows()
Rows.EntireRow.Hidden = False
End Sub
```

**VBA Code Breakdown**

- We have two
in this code.*Sub procedures* - The first one hides the rows that have negative values in the cell range
. Moreover, we are using the*E11:E130*to go through each cell.*For Each Next loop* - The last one reveals the rows.
- Thus, this code works.

- Then, save and close the
.*Module* - Now, from the
tab →*Developer*→ select*Insert*.*Button (Form Control)*

- Then, it will change the mouse cursor. Drag a box in the dataset, then upon releasing the mouse the
window will appear.*Assign Macro* - After that, select “
**Hide_Rows**” and press. Then, drag another box and select “*OK***Unhide_Rows**” from the option and press.*OK*

- Then, we renamed the buttons to
**Hide**and**Unhide**respectively. - Now, if we click on the “
**Hide**” button it will hide all the extra rows.

### Step 5: Finding Total Debt Paid

In the last step, we will finish inputting the values that we left blank in the first step. We will use the ** INDEX**,

**,**

*MATCH***,**

*SUM***, and**

*SUMIF***functions in this step.**

*COUNTIF*- Firstly, type this formula in cell
to find the month when the*C8***credit card debt**will be paid off.

`=INDEX(B11:B130,MATCH(TRUE,E11:E130<0,0)-1)`

**Formula Breakdown**

**MATCH(TRUE,E11:E130<0,0)-1****Output: 14**.- We find the row number where the interest is negative. Row
**15**has a negative interest amount. Then, we subtract**1**from it because we want the row before the first negative number.

- Our formula reduces to
**INDEX(B11:B130,14)****Output:****45139**.- This means
**August, 2023**.

- Then type another formula in cell
.*F5*

`=C4`

- Afterward, type this formula in cell
to find the amount of total interest.*F6*

`=SUMIF(E11:E100,">0")`

- Then, type this formula in cell
to find the total amount paid.*F7*

`=SUM(F5:F6)`

- Lastly, type this formula in cell
to find the number of months required to*F8***payoff**the**credit card**debt.

`=COUNTIF(E11:E100,">0")`

- Now, we can click on the “
**Unhide**” button to show all the hidden rows.

## Conclusion

We have shown you five quick steps to create a **credit card payoff calculator** with **amortization **in Excel. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Moreover, you can visit our site **ExcelDemy** for more Excel-related articles. Thanks for reading, keep excelling!