Household expenses in an excel spreadsheet. Family budget in Excel. How to properly allocate funds
To maintain income and expenses, it is enough to create a separate table in Excel, in which the columns divide income and expenses by months:
How are income and expenses recorded in Excel?
Holidays are highlighted in red. One cell - one day. For each day in which there were incomes or expenses, you can leave a comment with details so that you can later break down incomes and expenses into categories.
Income received or expenses incurred during the day can be recorded on the phone or memorized. If there were several expenses per day, then you can simply paint them through the amount by indicating an equal sign at the beginning of the line, for example, like this:
Also in Excel, you can specify comments to the cell and indicate what amount was spent on.
There are special programs and applications for accounting for income and expenses, but unfortunately, most of them are inconvenient. Excel is the most convenient way to organize your own income and expenses, as it was created for financial and accounting calculations.
Considering all your expenses, you can see the whole picture and perhaps even reconsider some items of expenses and income.
File password protection
Excel has another cool feature that might come in handy - setting a password on a file. For all versions of this program, setting a password is carried out in different ways and, if necessary, you can find articles or a video clip on how to do this for your version of the program.
Money and the mindset of a millionaire
Do you want to learn more about how to stop wasting money, how to start saving money, increase your income and get rich? How to solve your financial problems and refuse loans? Sign up for the Money and Millionaire Mindset course.
Knowing the psychology of money and how to work with them makes a person a millionaire. 80% of people with an increase in income take out more loans, becoming even poorer. Self-made millionaires, on the other hand, will make millions again in 3-5 years if they start from scratch. This course teaches the proper distribution of income and cost reduction, motivates you to learn and achieve goals, teaches you to invest money and recognize a scam.
We train memory on remembering expenses
Part of this article is taken from the course, because accounting for income and expenses is great for training memory.
Starting from 2014, I take into account all my income and expenses, on which day how much money I spend and receive. I write everything down in a special Excel file: .
But it’s inconvenient to write down how much money I spent right away, so I have to remember and then write down the expenses when I get there. I don’t really like to write down, so I do it 1-2 times a week, as a result, during this time I got used to remembering when, how much money and what I spent.
At first I wrote down every day, because I was not sure that I would remember everything, I also didn’t have such a habit of writing down, I could abandon it at any moment. Now the habit has been developed and if you want, if you don’t want, I write it down.
For convenience, you can collect receipts from stores, especially at the beginning of such a practice and refer to them if necessary, but it is better to write them down right away so that there is no confusion and unnecessary trouble later. This technique will not only help improve and maintain memory, but also visually see how much money is spent on what.
Think of all your expenses for the last time, starting in the last week, then for the previous, for the last month. How often do you go to the store, what do you buy, what days do you go to which stores and what do you buy in them. What things and products do you buy regularly, and what were the major purchases over the past year. I am sure that by asking such questions, you will be able to remember a lot of interesting things.
Download
To make it more convenient for you to keep track of your income and expenses, I throw off the blank, which I myself use:
Educational game "Piggy bank"
I can't help but recommend the Piggy Bank game. This game will give you great fitness for the brain and relaxation for the body. The essence of the game is to indicate one of the four windows in which the amount of coins is the largest. The game trains visual memory, attention, speed of thinking and logic.
Tasks for today
- Download an example for managing personal or family income and expenses, try to write down even the smallest expenses there!
- Sign up for the Money and Millionaire Mindset course to take control of your finances and get on the path to prosperity and wealth. The course will help you better learn how to handle money and make it so.
Making a family budget will help you keep track of income and expenses, and this will finally allow you to understand where the money is constantly going. Compiling a table of income and expenses of the family budget is actually not as difficult as it might seem at first glance.
It is preferable to start work with approximate dates, oral analysis and assessment of the situation. It can be said that your budget is correct if you are not only focusing on reducing expenses, but also looking for options on how to get additional income.
Why is this needed?
Accounting for material flows can be kept electronically using a Microsoft Excel spreadsheet, or in the old fashioned way - keep a notebook where you can enter all the information.
Both options can be called an effective method of controlling costs, and they will also allow you to create an approximate list of costs that you will have to go on.
You can sum up the results weekly, every month or make a plan for several months at once. Which option suits your family is up to you. Such a scheme, which is in front of your eyes, will allow you to find out exactly what and when the money was spent.
Thus, you will see the really necessary expenses and those that could have been avoided (impulse purchases). This is a great opportunity to start saving, which is why it is so important to know how to make a family budget table correctly.
Each family is special, so the mandatory expenses and income level will be different. For example, some need to pay the cost of renting a house, while others have two children, so the cost of their needs will be doubled. There can be many options, but the standard expense and income accounting table includes the following items:
- total family income;
- how much taxes you need to pay;
- payment for housing, utilities, if necessary - car maintenance;
- if there are loans, what is the amount of the monthly payment and interest;
- buying food;
- expenses for medicines, purchase of household chemicals;
- unexpected expenses.
Cost items can be very different, but potential expenses should be described in as much detail as possible. Therefore, even before starting to draw up a plan, you need to try to break them all into categories, and then only adjust the numbers every month, the expenses and incomes themselves usually remain unchanged.
What is the best way to split the costs?
Arrange 3 large sections (permanent, one-time and basic expenses). The permanent ones typically include:
- Internet and telephone payments;
- loans and insurance;
- communal payments.
Unlike the other points, these amounts cannot be corrected and cannot be avoided. Other fixed expenses can be included in your family budget, which will be determined by yourself as soon as you try to keep such a table for a couple of months.
- buying food;
- recreational activities, which will be discussed in the entertainment section;
- purchase of cosmetics and household chemicals;
- payment for travel in transport or expenses for the maintenance of a personal car;
- expenses are not medications.
The amounts for each item of expenditure may vary, because some groups of goods do not need to be purchased monthly. Here it is very important to calculate the burden on the budget, because if, for example, a car breaks down and you spend all the money on it in a month, then you will not have cash left for other expenses.
Distribute the load evenly and buy winter tires, etc. in the summer, spending a feasible amount on this.
Now it remains to arrange the category of one-time expenses and all kinds of entertainment. This is the purchase of shoes and clothes, appliances and furniture, as well as recreation and various trips. If you control your finances correctly, you can save money and not allow yourself to give up your favorite entertainment. Periodically be sure to do what brings you positive emotions, otherwise life will seem very boring.
Also, it will not be superfluous to keep a small amount of reserve ( approximately 10% of your household income) for emergencies. Be prepared that a small part of the budget will still be spent on various trifles and you won’t be able to keep track of these 500-700 rubles.
They can also be divided into separate groups, depending on what is the source of profit (part-time jobs, deposit interest, dividends, etc.). In an ordinary family, there will be few such articles. Therefore, we simply indicate the amount of income of the wife and husband.
Include in the table "Other sources", where you enter birthday gifts, winning prizes, profits from the sale of any goods, etc. So you can track changes in the family budget.
The family budget spreadsheet in Excel is a great opportunity to keep track of your budget and, if necessary, take any action to cut costs.
- Regularly compare your family's monthly income with expenses and adjust the latter as needed.
- Make a rough list of expenses for the year, in which you write down all fixed payments, taxes and mandatory expenses. Thus (let the figure turn out to be approximate) you will not have to urgently cut all your desires at the end of the year and save even on the most necessary, if suddenly, for some reason, income becomes less. Try not to go beyond the established limits.
- In a notebook every day write down all the money that was spent. Even small things like a cup of tea or coffee.
- Follow a simple rule: if you can’t pay for the planned purchases before the end of the month, then you don’t need to do them.
The income and expense spreadsheet is a great tool that will allow you to buy only the essentials and save enough money to buy a car or apartment.
Check out the offers of banks
Card with cashback in Rosbank | Issue a card |
More about the map
- Cashback up to 7% - for selected categories;
- Cashback 1% - for all purchases;
- Bonuses, discounts on goods and services from VISA;;
- Internet banking - free of charge;
- Mobile banking - free of charge;
- Up to 4 different currencies on 1 card.
Card from PromsvyazBank | Issue a card |
More about the map
- Up to 5% cashback;
- Cash withdrawal without commission at partner ATMs;
- Internet banking - free of charge;
- Mobile banking is free.
Card from Home Credit Bank | Issue a card |
More about the map
- Up to 10% cashback from partners;
- Up to 7% per annum on the account balance;
- Withdrawing funds from ATMs without commission (up to 5 times a month);
- Apple Pay, Google Pay and Samsung Pay technology;
- Free internet banking;
- Free mobile banking.
More about the map
- Cashback up to 10% from gas stations
- Cashback up to 5% from bills in cafes and restaurants
- Cashback up to 1% on all other purchases
- Up to 6% per annum on the balance
- Card service - free of charge;
- Free internet banking;
- Free mobile banking.
Card from Tinkoff Bank |
When it became necessary three years ago to keep track of income and expenses of the family budget, I tried a lot of specialized programs. In each there were some flaws, shortcomings, and even design flaws. After a long and unsuccessful search for what I needed, it was decided to organize what I needed based on an Excel template. Its functionality allows you to cover most of the basic requirements for home bookkeeping, and, if necessary, to build visual graphs and add your own analysis modules.
This template does not claim to cover the entire task 100%, but can serve as a good base for those who decide to go this route.
The only thing I want to warn you right away is that working with this template requires a large desktop space, so a 22” or larger monitor is desirable. Because the file was designed with convenience and no scrolling in mind. This allows you to fit data for a whole year on one sheet.
The content is intuitive, but let's skim through the highlights nonetheless.
When opening a file, the working field is divided into three large parts. The top section is for managing all income. In other words, these are the financial volumes that we can dispose of. The bottom one, the largest one, is for fixing all expenses. They are divided into main subgroups for ease of analysis. On the right is a block for autosumming totals, the more the table is filled, the more informative its data.
Each type of income or expense is in the lines. The columns break the input fields by month. For example, let's take a block of data with income.
What is there to hide, many receive "gray" or even "black" salaries. Someone can boast of "white". For others, the main part of the income may be part-time work. Therefore, for a more objective analysis of their sources of income, four main points are highlighted. It doesn't matter if one cell will be filled in later or all at once - anyway, the correct amount will be calculated in the "total" field.
I tried to break down the costs into groups that would be universal and suitable for most people who started using this file. How successful it is is up to you to judge. In any case, adding the required line with an individual expense item will not take much time. For example, I don’t smoke myself, but those who are addicted to this habit and want to get rid of it, and at the same time understand how much is spent on it, can add the Cigarettes expense item. For this, basic knowledge of Excel is quite enough, and now I will not touch them.
As above, all expenses are summed up by months in the final line - this is the total amount that goes from us every month to no one knows where. Thanks to the detailed division into groups, you can easily track your own trends. For example, in the winter months, my food expenses are reduced by about 30%, but the craving for buying any unnecessary nonsense increases.
Even lower is a line called "remainder". It is calculated as the difference between all monthly income and all expenses. It is from it that one can judge how much money can be set aside, for example, on a deposit. Or how much is missing if the balance goes into the red.
Well, basically, that's all. Yes, I forgot to explain the difference between the fields "average (month)" and "average (year)" in the right final block. The first, "average per month" considers the averages only for those months in which there were expenses. For example, you bought educational courses three times a year (in January, March and September). Then the formula will divide the total amount by three and place it in the cell. This allows you to more accurately estimate your monthly expenses. Well, the second, “average for the year”, always divides the final value by 12, which more accurately reflects the annual dependence. The greater the difference between them, the more irregular these expenses are. And so on.
You can download the file
Today I decided to show my version of the family budget. The family budget removes many problems - it allows you to keep track of expenses, make a plan for the month and for the year, and as a result, you rationally manage your expenses and correctly imagine your capabilities. My version has been made and evolved over the years, but I'll show you a few options that are circulating the net.
So, first of all, where to get the form. You can do it yourself, or you can just download it from Microsoft. Command New/Microsoft Office Online/Budgets/Family budgets. There you choose the one that you like best, they are generally only design and differ.
Composition of budgets:
- Planned and actual income.
- Planned and actual expenses.
- Difference or Plan/Fact
- Diagrams (not all).
Working with them is simple: every month you drive a plan into the graphs, then, on the contrary, as expenses (income) arise and the fact. As a result, the tablet gives the result by means of simple formulas, namely, comparing your income and expenses.
Now let's move on to my version of the family budget:
Possibilities:
- Plan fact
- Definition of the current situation
- Chronology.
- Display of articles and groups for which there is an overrun.
Which Excel functions I used:
- Smart table. This removed the problem of references in the calculation of indicators.
- The function that made it possible to do without .
- to show items that are overrun.
- VLOOKUP function to define a cost group.
In general, nothing mega cool, but very practical.
How do I work with it:
- I score targets at the beginning of the month.
- in the course of the month I score actual costs in the statement. the table then fixes the costs by groups, if there is no such cost, I put the group manually.
- immediately shows the articles for which there is already an overrun and how much more money is needed to cover all the articles. I will correct according to the situation. If this is an overrun that was not taken into account, nothing needs to be touched.
- at the end of the month I copy this sheet, call it by the name of the next month, reflect the actual balance, adjust the monthly costs, leave the costs that I didn’t do, but I need to do it and delete all the costs in the statement (I don’t touch the first line, it contains formulas, I will later extend down).
Also, in addition to the monthly budget, I use an annual plan. I rarely correct it, it is needed not for accounting, but for assessing future prospects.
It is better to do it at the beginning of the year, so that later you can use the information from it for the monthly budget. And then adjust to assess the current situation.
The annual plan allows you to see the whole picture in general. With it, you can plan large purchases or loan payments.
The problem of lack of money is relevant for most modern families. Many literally dream of paying off their debts and starting a new financial life. In a crisis, the burden of low wages, loans and debts affects almost all families without exception. That is why people strive to control their spending. The point of saving costs is not that people are greedy, but to find financial stability and look at your budget soberly and impartially.
The benefit of controlling the financial flow is obvious - it is cost reduction. The more you save, the more confidence in the future. The money saved can be used to form a financial cushion that will allow you to feel comfortable for a while, for example, if you are left without a job.
The main enemy in the way of financial control is laziness. People first light up with the idea of controlling the family budget, and then quickly cool down and lose interest in their finances. To avoid this effect, you need to acquire a new habit - to constantly control your expenses. The most difficult period is the first month. Then the control becomes a habit, and you continue to act automatically. In addition, you will immediately see the fruits of your “labor” - your expenses will be surprisingly reduced. You will personally see that some expenses were superfluous and you can refuse them without harm to the family.
Poll: Excel spreadsheets are enough to control the family budget?
Accounting for family expenses and income in an Excel spreadsheet
If you are new to family budgeting, then before using powerful and paid home bookkeeping tools, try keeping a family budget in a simple Excel spreadsheet. The benefits of such a solution are obvious - you do not spend money on programs, and try your hand at controlling finances. On the other hand, if you bought the program, then this will stimulate you - since you spent the money, then you need to keep records.
It is better to start compiling a family budget in a simple table in which everything is clear to you. Over time, you can complicate and supplement it.
Read also:Here we see three sections: income, expenses and report. In the "expenses" section, we have entered the above categories. Near each category is a cell containing the total expense for the month (the sum of all days on the right). In the "days of the month" area, daily expenses are entered. In fact, this is a complete monthly report on the expenses of your family budget. This table gives the following information: expenses for each day, for each week, for a month, as well as the total expenses for each category.
As for the formulas that are used in this table, they are very simple. For example, the total expense for the category "car" is calculated by the formula =SUM(F14:AJ14). That is, this is the amount for all days on line number 14. The amount of expenses per day is calculated as follows: =SUM(F14:F25)- all numbers in column F from the 14th to the 25th line are summed up.
The section "income" is arranged in a similar way. This table has categories of budget revenues and the amount that corresponds to it. In the "total" cell, the sum of all categories ( =SUM(E5:E8)) in column E from the 5th to the 8th line. The "report" section is even simpler. Here, information from cells E9 and F28 is duplicated. The balance (income minus expenses) is the difference between these cells.
Now let's complicate our expense table. Let's introduce new columns "expenditure plan" and "deviation" (download the table of expenses and incomes). This is necessary for more accurate planning of the family budget. For example, you know that the cost of a car is usually 5,000 rubles / month, and the rent is 3,000 rubles / month. If we know the costs in advance, then we can make a budget for a month or even a year.
Knowing your monthly expenses and income, you can plan large purchases. For example, family income is 70,000 rubles / month, and expenses are 50,000 rubles / month. This means that every month you can save 20,000 rubles. And in a year you will be the owner of a large amount - 240,000 rubles.
Thus, the columns "expenditure plan" and "variance" are needed for long-term budget planning. If the value in the "deviation" column is negative (highlighted in red), then you deviated from the plan. Deviation is calculated by the formula =F14-E14(i.e. the difference between the plan and the actual costs per category).
What if you deviate from the plan in any month? If the deviation is insignificant, then next month you should try to save on this category. For example, in our table in the category "clothes and cosmetics" there is a deviation of -3950 rubles. This means that next month it is advisable to spend 2050 rubles (6000 minus 3950) on this group of goods. Then, on average, for two months you will not have a deviation from the plan: (2050 + 9950) / 2 = 12000 / 2 = 6000.
Using our data from the expense table, we will build a cost report in the form of a chart.
Similarly, we build a report on the income of the family budget.
The benefits of these reports are clear. Firstly, we get a visual representation of the budget, and secondly, we can track the percentage share of each category. In our case, the most expensive items are “clothes and cosmetics” (19%), “food” (15%) and “credit” (15%).
Excel has ready-made templates that allow you to create the necessary tables in two clicks. If you go to the "File" menu and select the "Create" item, the program will prompt you to create a finished project based on the existing templates. Our theme includes the following templates: Sample Family Budget, Family Budget (Monthly), Simple Spending Budget, Personal Budget, Half Monthly Home Budget, Monthly Student Budget, Personal Expenses Calculator .
A selection of free Excel budgeting templates
Ready-made Excel spreadsheets can be downloaded for free from these links:
The first two tables are discussed in this article. The third table is described in detail in the article about home accounting. The fourth collection is an archive containing standard templates from an Excel spreadsheet.
Try downloading and working with each table. After reviewing all the templates, you are sure to find a table that is right for your family budget.
Excel Spreadsheets vs. Home Bookkeeping: Which Should You Choose?
Each method of doing home accounting has its own advantages and disadvantages. If you have never done home accounting and have poor computer skills, then it is better to start accounting for finances using a regular notebook. Enter all expenses and incomes into it in any form, and at the end of the month you take a calculator and reduce the debit with the credit.
If the level of your knowledge allows you to use an Excel spreadsheet or a similar program, then feel free to download templates for home budget tables and start accounting electronically.
When the functionality of the tables no longer suits you, you can use specialized programs. Start with the simplest personal accounting software, and only then, when you get real experience, you can purchase a full-fledged program for a PC or smartphone. For more information about financial accounting programs, see the following articles:
The advantages of using Excel spreadsheets are obvious. This is a simple, clear and free solution. It is also possible to gain additional skills in working with a spreadsheet processor. The disadvantages include low performance, poor visibility, as well as limited functionality.
Specialized family budget management programs have only one drawback - almost all normal software is paid. Only one question is relevant here - which program is the highest quality and cheapest? The advantages of the programs are: high speed, visual presentation of data, many reports, technical support from the developer, free updates.
If you want to try your hand at family budget planning, but are not ready to pay money, then download it for free and get down to business. If you already have experience in home accounting and want to use more advanced tools, then we recommend installing a simple and inexpensive program called Housekeeper. Consider the basics of personal accounting with the help of "Housekeeper".
Household bookkeeping in the program "Economy"
A detailed description of the program can be found on this page. The functionality of the Housekeeper is simple: there are two main sections: income and expenses.
The section "Incomes" is arranged in a similar way. User accounts are configured in the "Users" section. You can add any number of accounts in different currencies. For example, one account can be in rubles, the second in dollars, the third in Euros, etc. The principle of the program is simple - when you add an expense transaction, the money is debited from the selected account, and when you add a profitable transaction, the money, on the contrary, is credited to the account.
To build a report, you need to select the type of report in the "Reports" section, specify the time interval (if necessary) and click the "Build" button.
As you can see, everything is simple! The program will independently build reports and point you to the most costly items of expenditure. Using reports and a table of expenses, you will be able to manage your family budget more efficiently.
Video on the topic of the family budget in Excel
On the Internet, there are many videos on family budgeting. The main thing is that you not only watch, read and listen, but also apply the acquired knowledge in practice. By controlling your budget, you reduce unnecessary expenses and increase savings.