As an Amazon Associate I earn from qualifying purchases.
Do you already have a personal income statement you update each month or quarter? If you do that is perfect and you are on your way to understanding your finances. If you don’t have a personal income statement that is fine too since we’ll be working below to build one.
I honestly don’t expect many people outside of the finance or accounting profession to have a personal income statement they keep up with. Why would most people? Its a little odd. I’m a numbers nerd though, so odd is my game.
First step: sort your transactions
We need to organize all of the transactions into useful categories and types, but very broadly at first. People often make the mistake of getting too far in the weeds here by trying to categorize every single transaction regardless of amount and into far too many categories. Narrowing our scope to only a handful of broad categories will make this process much faster, easier, and aid in the overall success in the long run. The more complicated we make the process, the less likely we’ll be to continue to update it, increasing the chance it is abandoned prematurely.
First, add two new columns to the end of the transaction data: Budget Category and Budget Type. You’ll need these to add your custom categories and if the transaction is positive or negative. Look at you! Mastering spreadsheets already… go ahead and add that to your resume. I promise even at this level you’ll still be middle of the pack based on my experiences. haha
Budget Type = Income vs Expense vs Other
Budget type will be used for if the transaction is an income, an expense, a transfer, or some other category that will be ignored. Go ahead and fill in this column based on the transaction amount with positive amounts being income and negative amounts being expenses. Transfers are transactions that are just moving money from one of your accounts to another. A common example would be making a credit card payment since that payment amount is already accounted for in the expense transaction list from your credit card company.
Example: you buy a $100 item with your credit card and send in a $100 payment at the end of the month. You’ll see a $100 expense in the credit card transaction list and $100 expense in your bank account paying it down when obviously you only bought one $100 item. We mark these functionally duplicate transactions as transfers so it doesn’t artificially inflate our expenses.
Budget Category = Where should this show up?
Budget category will be used for the broad categorization of the transaction into personal income statement line items used later to build our budget. If you need to do a quick first pass and label them all in very broad categories (bills, essential, non-essential, one-time, or other), then go back and update them to the sub-categories to make it easier. For me, it is easiest to sort the data by transaction description so I can determine the category once and copy it down to all the same transaction types since expenses from the same company are most likely all the same type of expense.
Personal Income Statement Categories
- Housing and Mortgage
- Car loans
- Student loans
- Credit Cards
- Other personal loans
- Insurance (Health, Dental, Life if not a payroll deduction)
- Essential Expenses
- Food (groceries vs dining out)
- Transportation costs (gas & repairs)
- Childcare (or other income producing expenses)
- Personal Care (medical, grooming, health expenses)
- Food (groceries vs dining out)
- Non Essentials
- General merchandise
- Entertainment (streaming services, subscriptions, movies, concerts, theme parks, etc)
- Upgrades ( higher grade internet, phone, services)
- Convenience items (lawn service, delivery services, etc)
- One-time expenses
- Used for outlier events like major repairs
- Used for events we want to pull aside from categories (like paying a large amount down on a student loan)
Start with fewer categories
How exactly you categorize your expenses (after paying the minimums on all bills) into essential versus nonessential is a personal choice at the end of the day. You rank them in the order in which you feel they are most important for you and that order might be different than how I would organize them and that is ultimately ok. That said, you should always rank income-producing expenses above other expenses because they aid you in generating income.
For example, if you work from home you might want to rank your internet provider higher than the streaming video service because being able to complete your work from home is more critical to maintaining your finances if one needs to be eliminated. Additionally, if one (or both) caregivers leave each day to work outside the home the childcare expenses should rank higher on the list because if your children can’t go to school/childcare it will make you getting to work consistently much more difficult.
Your categories will be different than mine
Your categories may or may not include all of the above (or might have some that I don’t). If we later discover that one of the categories is volatile, too broad, or needs further review we can filter down and break it up into smaller sub-categories. These situations tend to rise organically and don’t need to be planned for ahead of time. Example: the General Merchandise category is oddly large on a semi-regular basis, so you look into the transactions and realize that maybe you should reclassify some of the expenses to break out clothing purchases from the rest to see where you are spending more on shopping than usual.
Start with the easy ones
During the initial classification, sort them by transaction description and work your way down the list filling in the easiest and most obvious ones first. Just skip over the ones you are sure of on the first pass to keep moving. On the second pass, feel free to label any expense that is too small or you aren’t sure of as General Merchandise to complete the list. As long as the amounts are too large and there aren’t too many of them it probably won’t affect the aggregate amounts over the 90+ day time period. As noted above, if that category ends up too big, you can always go back through it and reclassify some of the expenses to another category.
Just keep going, don’t think too hard
The point is to just keep moving with the list. Don’t get too bogged down in the details trying to be exact or hyper-specific on the categories. In the long run, making sure that updating this list with subsequent transactions needs to be as simple as possible to encourage you to continue doing it. If the process is too complicated or time consuming, you’ll stop doing it and never improve your financial situation.
Trigger warning: pivot tables ahead
Once we have all the transactions sorted, we can summarize them in a pivot table. If you don’t know what that is right now, rest assured it shouldn’t be too complicated to figure out. Both Excel and Google Sheets have friendly wizards to help you pivot the data into a nice summary. Essentially the pivot table takes the long list of transactions and will summarize them by whatever dimensions you choose. In our case, totaled up by budget category in the rows and calendar month in the columns.
If you have trouble with this step, a quick google search should turn up dozens of simple step by step tutorials. The end result should quickly show you the total income and expense you have by month for each category. We can then right-click and order them by budget importance (bills > essential > non-essential > other > one-time) so we can see the amounts in a logical order. At this point we basically have a simple personal income statement for the prior 90+ days depending on how many transactions you compiled.
You are basically an accountant now, sort of…
In theory you should be able to take your starting balance for a month, add in the incomes, subtract out the expenses, and arrive at the end balance of your accounts. If the math on this isn’t reconciling then something is missing or misallocated in the categories and it should be a quick jump to the transaction list to find the issue.
No judgement is important
Important to note here that we still haven’t made any judgement calls on the amounts involved so far. We haven’t determined if the amounts spent so far make sense for your budget or are reasonable, just determined exactly what the amounts are now each month. We’ll get to how we use these summaries to generate a budget in a later article.
Congratulations on your personal income statement
Congrats for now on knowing exactly where your money comes from and goes on a monthly basis now. That’s saying something and an important step on the path to financial independence.
Amazon and the Amazon logo are trademarks of Amazon.com, Inc, or its affiliates.