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.

## Create Credit Card Payoff Calculator with Amortization in Excel: with Easy Steps

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

### 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 12 to find the*E11**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`

### 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 1 month.*B12*

`=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** m**acro*. After that, we will use another code to unhide them. Both of these codes will be applied to two separate** VBA** buttons.

- To begin with, press
to bring up the*ALT+F11**VBA**module*window. - 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 payoff the credit card debt.*F8*

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

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

**Read More:** How to Create a Credit Card Payoff Spreadsheet in Excel

**Download Practice Workbook**

## 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.

**Related Articles**

- How to Create Credit Card Payoff Calculator with Snowball in Excel
- Create Multiple Credit Card Payoff Calculator in Excel Spreadsheet

**<< Go Back to Credit Card Payoff Calculator | Finance Template | Excel Templates**