In this article, you will learn how to create a file in Excel that will track your debit card spendings. Some of your purchases may be made on installments, which means you pay a fixed amount every month until the total amount is paid off. You will also learn how to use Excel formulas to calculate the total amount, the remaining balance, and the interest rate of your installments.
To track your debit card spendings, you need to record the following information for each purchase:
- Date: The date when you made the purchase or the first installment payment.
- Description: A brief description of what you bought or paid for.
- Amount: The total amount of the purchase or the installment payment.
- Category: A category that helps you organize your spendings, such as groceries, entertainment, utilities, etc.
- Installments: The number of installments you agreed to pay for the purchase, if any.
- Interest Rate: The annual interest rate charged for the installments, if any.
You can use an Excel table to store this information in a structured and organized way. An Excel table is a range of cells that has a header row with labels and data rows with values. You can easily sort, filter, and format an Excel table, as well as use formulas to perform calculations on the table data.
Procedures
To create a file in Excel that will track your debit card spendings, follow these steps:
- Open a new Excel workbook and save it as “Debit Card Spendings.xlsx”.
- In Sheet1, type the following labels in cells A1 to F1: Date, Description, Amount, Category, Installments, Interest Rate. These will be the headers of your Excel table.
- Select the range A1 to F1 and press Ctrl+T to create a table. In the Create Table dialog box, check the box that says “My table has headers” and click OK.
- Enter your debit card spendings data in the table. For example, you can enter the following data in cells A2 to F6:
Table
| Date | Description | Amount | Category | Installments | Interest Rate |
|---|---|---|---|---|---|
| 2023-12-01 | Laptop | 1200 | Electronics | 12 | 10% |
| 2023-12-05 | Groceries | 150 | Groceries | 1 | 0% |
| 2023-12-10 | Movie Tickets | 40 | Entertainment | 1 | 0% |
| 2023-12-15 | Phone Bill | 60 | Utilities | 1 | 0% |
| 2023-12-20 | Car Insurance | 200 | Insurance | 1 | 0% |
- To calculate the total amount of your spendings, type the following formula in cell H2:
=SUM(Table1[Amount]). This formula will add up all the values in the Amount column of the table and display the result in cell H2. You can also type a label in cell G2, such as “Total Spendings”. - To calculate the remaining balance of your installments, type the following formula in cell H3:
=SUMPRODUCT(Table1[Amount],Table1[Installments])-SUMIF(Table1[Installments],">1",Table1[Amount]). This formula will multiply the amount by the number of installments for each purchase, add up the results, and subtract the amount of the first installment payments. You can also type a label in cell G3, such as “Remaining Balance”. - To calculate the average interest rate of your installments, type the following formula in cell H4:
=AVERAGEIF(Table1[Installments],">1",Table1[Interest Rate]). This formula will calculate the average of the interest rates for the purchases that have more than one installment. You can also type a label in cell G4, such as “Average Interest Rate”.
Explanation
The formulas used in the previous section are based on the following logic:
- To calculate the total amount of your spendings, you need to add up all the values in the Amount column of the table. You can use the SUM function to do this. The SUM function takes one or more arguments that are numbers or ranges of numbers and returns the sum of them. To refer to a specific column of a table, you can use the syntax
TableName[ColumnName]. For example,Table1[Amount]refers to the Amount column of Table1. Therefore, the formula=SUM(Table1[Amount])will return the sum of all the values in the Amount column of Table1. - To calculate the remaining balance of your installments, you need to subtract the amount of the first installment payments from the total amount of the installments. To calculate the total amount of the installments, you need to multiply the amount by the number of installments for each purchase and add up the results. You can use the SUMPRODUCT function to do this. The SUMPRODUCT function takes one or more arrays of numbers and returns the sum of the products of the corresponding elements. For example,
=SUMPRODUCT(A2:A6,B2:B6)will return the sum of the products of the values in cells A2 to A6 and B2 to B6. Therefore, the formula=SUMPRODUCT(Table1[Amount],Table1[Installments])will return the sum of the products of the values in the Amount and Installments columns of Table1. To calculate the amount of the first installment payments, you need to add up the values in the Amount column of the table that have more than one installment. You can use the SUMIF function to do this. The SUMIF function takes three arguments: a range of cells to evaluate, a criterion to apply, and a range of cells to sum. For example,=SUMIF(A2:A6,">10",B2:B6)will return the sum of the values in cells B2 to B6 that correspond to the values in cells A2 to A6 that are greater than 10. Therefore, the formula=SUMIF(Table1[Installments],">1",Table1[Amount])will return the sum of the values in the Amount column of Table1 that correspond to the values in the Installments column of Table1 that are greater than 1. Finally, to calculate the remaining balance of your installments, you need to subtract the amount of the first installment payments from the total amount of the installments. Therefore, the formula=SUMPRODUCT(Table1[Amount],Table1[Installments])-SUMIF(Table1[Installments],">1",Table1[Amount])will return the difference between the two values. - To calculate the average interest rate of your installments, you need to calculate the average of the interest rates for the purchases that have more than one installment. You can use the AVERAGEIF function to do this. The AVERAGEIF function takes three arguments: a range of cells to evaluate, a criterion to apply, and a range of cells to average. For example,
=AVERAGEIF(A2:A6,">10",B2:B6)will return the average of the values in cells B2 to B6 that correspond to the values in cells A2 to A6 that are greater than 10. Therefore, the formula=AVERAGEIF(Table1[Installments],">1",Table1[Interest Rate])will return the average of the values in the Interest Rate column of Table1 that correspond to the values in the Installments column of Table1 that are greater than 1.
Scenario
To illustrate how the Excel file works, let’s consider a scenario where you make two more purchases on your debit card in January 2024:
- On 2024-01-05, you buy a pair of shoes for $100, which you pay in 4 installments with a 5% interest rate.
- On 2024-01-10, you buy a book for $20, which you pay in one installment with no interest rate.
You can enter these data in the table as follows:
Table
| Date | Description | Amount | Category | Installments | Interest Rate |
|---|---|---|---|---|---|
| 2023-12-01 | Laptop | 1200 | Electronics | 12 | 10% |
| 2023-12-05 | Groceries | 150 | Groceries | 1 | 0% |
| 2023-12-10 | Movie Tickets | 40 | Entertainment | 1 | 0% |
| 2023-12-15 | Phone Bill | 60 | Utilities | 1 | 0% |
| 2023-12-20 | Car Insurance | 200 | Insurance | 1 | 0% |
| 2024-01-05 | Shoes | 100 | Clothing | 4 | 5% |
| 2024-01-10 | Book | 20 | Education | 1 | 0% |
The formulas in cells H2 to H4 will automatically update to reflect the new data. The results will be as follows:
- Total Spendings: $1770
- Remaining Balance: $133
- Average Interest Rate: 3.75%
Calculation
To understand how the formulas work, let’s look at the calculation for each purchase:
- Laptop: The total amount of the laptop is $1200, which you pay in 12 installments with a 10% interest rate. The amount of each installment is calculated by the following formula:
=PMT(Interest Rate/12,Installments,Amount). In this case, the formula is=PMT(10%/12,12,1200), which returns -$110.47. This means you pay $110.47 every month for 12 months. The total amount of the installments is calculated by multiplying the amount of each installment by the number of installments:=-110.47*12, which returns -$1325.64. This means you pay $125.64 more than the original price of the laptop due to the interest. The amount of the first installment payment is the same as the amount of each installment: -$110.47. The interest rate of the installments is 10%. - Groceries: The total amount of the groceries is $150, which you pay in one installment with no interest rate. The amount of each installment is the same as the total amount: $150. The total amount of the installments is also the same as the total amount: $150. The amount of the first installment payment is also the same as the total amount: $150. The interest rate of the installments is 0%.
- Movie Tickets: The total amount of the movie tickets is $40, which you pay in one installment with no interest rate. The amount of each installment is the same as the total amount: $40. The total amount of the installments is also the same as the total amount: $40. The amount of the first installment payment is also the same as the total amount: $40. The interest rate of the installments is 0%.
- Phone Bill: The total amount of the phone bill is $60, which you pay in one installment with no interest rate. The amount of each installment is the same as the total amount: $60. The total amount of the installments is also the same as the total amount: $60. The amount of the first installment payment is also the same as the total amount: $60. The interest rate of the installments is 0%.
- Car Insurance: The total amount of the car insurance is $200, which you pay in one installment with no interest rate. The amount of each installment is the same as the total amount: $200. The total amount of the installments is also the same as the total amount: $200. The amount of the first installment payment is also the same as the total amount: $200. The interest rate of the installments is 0%.
- Shoes: The total amount of the shoes is $100, which you pay in 4 installments with a 5% interest rate. The amount of each installment is calculated by the following formula:
=PMT(Interest Rate/12,Installments,Amount). In this case, the formula is=PMT(5%/12,4,100), which returns -$25.56. This means you pay $25.56 every month for 4 months. The total amount of the installments is calculated by multiplying the amount of each installment by the number of installments:=-25.56*4, which returns -$102.24. This means you pay $2.24 more than the original price of the shoes due to the interest. The amount of the first installment payment is the same as the amount of each installment: -$25.56. The interest rate of the installments is 5%. - Book: The total amount of the book is $20, which you pay in one installment with no interest rate. The amount of each installment is the same as the total amount: $20. The total amount of the installments is also the same as the total amount: $20. The amount of the first installment payment is also the same as the total amount: $20. The interest rate of the installments is 0%.
