How to Calculate the Finance Charge on a Credit Card Balance in Excel
Credit card companies make money by levying a finance charge on borrowers. Finance charges are derived by applying a periodic interest rate to the customer's account balance. (The periodic interest rate is the annual percentage rate divided by the number of billing periods.) Finance charges can be calculated automatically each month by using formulas in Microsoft Excel. Doing so will help borrowers better manage their financial situations.
Other People Are Reading
Instructions
-
-
1
Open a new spreadsheet in Microsoft Excel.
-
2
Type "APR" in cell A1, "Number of billing months" in cell A2, and "Periodic Interest Rate" in cell A3. In column B, enter the corresponding information, such as .15 for 15% APR in B1 and 12 for the number of billing periods. In cell B3, type =B1/B2 to calculate the periodic interest rate.
-
-
3
Type the following heads across columns A through C in row 5: Date, Account Balance and Finance Charge.
-
4
Fill in the information for the data and account balances. For example, type the following entries in rows 6 and 7 in columns A and B:
Date Account Balance
9/30/2009 10,000
10/31/2009 9,500 -
5
Type the formula =B6*$B$3 in cell C6. Position the mouse on the lower right hand corner of cell C6 and drag the formula down to the number of entries you have to calculate the finance charge. The $ makes cell B3 an absolute reference.
-
1
