This article is mainly aimed at people who have high credit card usage and want to keep track of it easily and categorized by card.
I have created an excel template that allows you to enter your credit card records without any special effort.
It is recommended for those who want to keep track of their credit cards as well, but want to do it as easily as possible and keep track of it well.
The version for 2023 can be downloaded free of charge here.
-
-
[2023 Edition] Excel Kakeibo Specializing in Recording Credit Card Transactions (Free Templates Available)
This Excel Kakeibo is the 2023 version of the [2022 version] Excel Family Budget Book (with free templates), which specializes in credit card records. The ...
Things to keep in mind before you start recording your credit cards
There are two ways to enter credit card amounts in your household account book.
- Record based on the date of purchase.
- Record the amount based on the "date the card was debited.
Have you ever wondered which method you should use?
It's not that one is better than the other, but it's better to think about the purpose of filling out a household account book.
You want to understand how I spend my money every month.
You want to discover and improve the waste in your household budget.
→Record based on the "purchase date.
You want to understand the monthly movement of your assets.
You want to make a plan to repay my debts.
→Record based on the date of credit card withdrawal.
Think about the purpose for which you are (or will be) filling out your household account book.
I keep track of my monthly assets in an asset book separate from my household account book.
In the household account book, I enter the "date of use" and in the asset book, I enter the amount of the "month of credit card debit" as a liability.
Features of Credit Card Family Budget
This credit card household account book is an improved version of ari-mama excel household account book.
It adds a new section for recording credit cards.
When you specify a credit card name and enter the amount, it will be color-coded.
You can manage each month on a single sheet, so you don't have to enter each credit card separately.
You can also see at a glance which credit cards you use most frequently, as they are displayed in different colors.
Summary of household account book features
- You can set the expense items freely.
- Easy to use, with sheets for each month.
- Allows you to prioritize your money
- Weekly totals are automatically entered, making it easy to modify your budget.
- Days when you didn't spend any money are colored.
- Can be printed out on A4 paper and used as a handwritten household account book.
- A pie chart is displayed for each item each month, so you can see which items you are overspending on.
- You can enter your special expenses for the year by expense category.
- Monthly income and expenses are displayed in a list and graph, allowing you to see the monthly trends at a glance.
- You can record your credit card transactions.
- You can easily set the start date.
Steps to create a household budget
This Excel household budget book uses macros.
Open Excel and click on "Enable Macros".
Setting
- Income
- Taxes
- Savings
- Fixed Expenditure
- Variable Expense
- Credit Card Name
- Start Date
- Select Currency
Set up the expense items
Enter the item names for income, taxes, savings, fixed expenses, and variable expenses.
The item names you enter will be reflected on the respective sheets.
Select the start date
The date will be set to match the start date in each month sheet.
you can change the start date to match your payday, etc.
Saturdays are shown in blue, and Sundays are shown in red.
Register up to credit card names
You can register up to three credit card names.
Change the currency unit
Press the "Select Currency" to select a range of currencies.
Select the currency unit for each sheet.
The January-December sheets can be changed in units at the same time.
Please change the units of the other sheets after changing the month sheet first.
Click on the money symbol to select the currency unit.
（Home-Currency-Money symbol)
Click on the ”More Accounting Format” to see more unit information.
The default setting is in dollars.
Special expenses are managed in a separate sheet.
Special Expenses
Special expenses are expenses that don't occur every month but are special, such as event expenses or annual payments.
You can set up special expenses by month or by expense category, so it is easy to look back.
Credit card records are explained in the Monthly Settings section.
Monthly Settings
The expense items set in Setting are set in the monthly sheet (January to December).
Enter your budget for income, taxes, savings, fixed expenses, and actual expenses for variable expenses.
For self-investment, enter the details and the actual expenses.
The amount you enter for special expenses will be automatically entered in the Total Special Expenses field.
To budget for variable expenses, please refer to this month's budget (income - (taxes + savings + personal investment + fixed expenses + special expenses) in this month's summary.
Enter the variable expenses by expense category and by day in the Gantt chart below.
The name of the expense item will be automatically filled in with the information you set in the Preferences.
The total amount will be automatically entered at the bottom of the chart and will be colored for Days when you didn't spend any money.
How to record credit card transactions
- Click on the "Credit Card" button.
- The name of the card you have named in the Setting will be displayed.
- Click the name of the card you want to record.
- Enter the amount in the Tax,Savings,Self-investment, Fixed cost, and Variable cost fields.
(When you enter an amount, it will be colored.) - Click "Cash" to return to cash entry.Close the user form (table of credit names).
- Close the user form (table of credit names)
(Be sure to click the "Cash" button to delete it.)
This will be converted to the color shown in the Total by Credit table.
If you want to record more than three credit cards, enter them in the Other field.
The weekly totals for variable expenses will be tabulated. (This is set based on the calendar year 2021)
The percentages of monthly taxes, savings, personal investment, fixed expenses, special expenses, and variable expenses are displayed in a pie chart.
You can see at a glance which items were the most common (improvement points).
Credit Card Trends
View annual credit card spending trends.
Annual Trends
Select the filter in cell C4 and uncheck the blank cell or 0.
The information you entered in the monthly section will be reflected and you can check the annual transition in the table and graph.
Print and use
You can print out the monthly sheet for use.
For the credit card total column, click "No Fill" to remove the color.
Home ー Select "B9 to B13" ー Click "No Fill
You can save yourself the trouble of handwriting by setting the names of the expense items in the preferences.
For credit card records, you can use a different color for each card or color-code them with a marker to make them easier to understand.
It can be printed on A4 paper in landscape mode, but please check the range once before printing.
Free Download
This "Credit Excel Household Budget" uses macros.
I know that some people are reluctant to use macros.
Here's what I can do
I make the macros visible.
The default setting of Excel is to disable macros.
Click on the Develop tag and take a look at Visual Basic.
To download, click the download button below.
Press the download button below.
A ZIP file will be downloaded, and you can use it by unzipping it.
