# Make Credit Card Payoff Calculator with Amortization in Excel

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.

## 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 ### 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 B11. This is the first payment date for our credit card debt.

`=C7` • Next, type another formula to reference the monthly payment in cell C11.

`=\$C\$6` • Then, type a formula in cell E11 to find the amount of interest. We have divided by 12 to find the monthly interest rate from the yearly rate.

`=C4*C5/12` • After that, type this formula in cell D11 to find the principal amount that was paid.

`=C11-E11` • Lastly, type this formula in cell F11 to find the remaining debts.

`=C4-D11` ### Step 3: Calculating Remaining Amounts

In this step, we will use the DATE, YEAR, MONTH, and DAY functions to display incremental months. Then, we will use simple formulas to find the rest of the amortization amounts.

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

`=DATE(YEAR(B11),MONTH(B11)+1,DAY(B11))` • Then, AutoFill the formulas from cells B12 and C11 downward.
• Afterward, type another formula in cell E12. This formula finds the amount of interest on the remaining balance. As before, drag this formula down.

`=F11*\$C\$5/12` • Now, type this formula in cell D12 to find the amount of principal paid. Then, drag the formula downward.

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

`=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 VBA buttons.

• To begin with, press ALT+F11 to bring up the 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 Sub procedures in this code.
• The first one hides the rows that have negative values in the cell range E11:E130. Moreover, we are using the For Each Next loop to go through each cell.
• The last one reveals the rows.
• Thus, this code works.
• Then, save and close the Module.
• Now, from the Developer tab → Insert → select Button (Form Control). • Then, it will change the mouse cursor. Drag a box in the dataset, then upon releasing the mouse the Assign Macro window will appear.
• After that, select “Hide_Rows” and press OK. Then, drag another box and select “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, SUMIF, and COUNTIF functions in this step.

• Firstly, type this formula in cell C8 to find the month when the 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 F6 to find the amount of total interest.

`=SUMIF(E11:E100,">0")` • Then, type this formula in cell F7 to find the total amount paid.

`=SUM(F5:F6)` • Lastly, type this formula in cell F8 to find the number of months required to 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! #### Rafiul Haq

Hello! This is Rafiul. I am passionate about all things related to data and MS Excel is my favorite application. I want to make people's life easier by writing easy-to-follow and in-depth guides here at Exceldemy.

We will be happy to hear your thoughts 