[2022 Edition] Excel Kakeibo Specializing in Recording Credit Card Transactions (Free Templates Available)

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.

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.

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.

Steps to create a household budget

This Excel household budget book uses macros.
Open Excel and click on "Enable Macros".

Setting

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

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.

Free Download