I believe in frugal living and paying yourself first. One of the simple ways to achieve this is by using a budget. You know how much money you get. Once you can track (or estimate) how much you are spending, it is easy to see how much you are paying your future self and what wiggle room you have. So in the spirit of making you awesome in life, not just Excel, let me share a simple but elegant household budget spreadsheet. Click here to download free budget template.
Here is a screenshot of the budget.
How does it work?
Simple really.
- Start by entering your income and itemized spending. You can select various frequencies (for example public transport – $16 per week, dining out $70 per month etc.)
- As you type data, category level summaries are shown on top along with a data bar to show relative expenditure of each category.
- The BALANCE row on the left turns RED if you spend more than you make.
- The graph on left helps you see the big picture and optimize your spending / savings.
How is it built?
There are three components in the budget spreadsheet:
1. Calculation of monthly totals by category:
- There is a frequency mapping table in the “Settings” tab to help calculate monthly totals from various amounts at different frequencies.
- Using SUMPRODUCT and SUMIFS, we can calculate total amount per category by month, like below.
Say you have amounts in G7:G26 and frequencies in H7:H26. To calculate total amount by month,
=IFERROR(SUMPRODUCT(G7:G26, SUMIFS(freqs[Multiplication factor],freqs[Frequency],H7:H26))/12,0)
We are fetching an array of multiplication factors from freqs table (shown aside) using SUMIFS. We then multiply that with amounts in G7:G26 thru SUMPRODUCT and finally divide this with 12 to get monthly value. If there is an error (typos / no data etc.) we show 0.
Read up more about SUMIFS and SUMPRODUCT formulas.
2. Quick visualization of category level spending
As you enter values, you can see a quick total (arrived thru the formulas explained above) and a data bar. This data bar is made with Conditional Formatting. The databar cells have a reference to totals calculated in cells above. We just set up databar on all cells in one go (so Excel can relatively size them based on values). Use CTRL+Click to multi-select the cells.
3. Spending by category graph – Treemap (Excel 2016)
In the settings page, we can harvest all category level spending in one column and then feed that to any chart in Excel. I have used Treemaps because they work well with data like this. But if you want, you can use column / bar / donut / pie charts too. In fact, I have created another version of the spreadsheet with column charts so that you can use it in earlier versions of Excel.
Read more about Excel treemaps and sunburst charts | Another example of treemap – customer service dashboard
Do you use Excel to track and manage money?
I do. I have used spreadsheets to track money soon after I started my first job 15 years ago. I have built several workbooks to track budgets, vacation plans, retirement scenarios, self-employment cash flows and house purchases. From time to time I have shared those techniques here on Chandoo.org too. Check out below templates and tutorials.
- Grow your money mustache using Excel
- 7 more expense trackers for you
- Sharing trip expenses using Excel
- Retirement calculator using Excel
What about you? Do you use spreadsheets to track and manage your finances? Please share your thoughts and examples in the comments section.
24 Responses to “Stay on top of money with this awesome household budget spreadsheet [downloads]”
Exremely brilliant!
Thank you for sharing!
Interesting.
Why not using a sheet with raw data classified in categories that can help building all the figures from there?
Regards.
Of course yes. This is built to be a budgeting tool rather than a tracking one. But we can easily add a tracking part to it so you can set the budget, enter actuals and then track how you are going. I will have a play with this idea and post another template next week with tracking.
I'd be excited to see that new template. It would be extra cool if you make it able to extract data from CSV files downloaded from online bank statements.
Hi Chandoo,
great mindset 🙂
much appreciated
I've been tracking my family expenses since 2005, itemized! I have over 40,000 rows of historical expenses, pivot tables, and a dashboard with charts showing me earnings and costs over time, so I know where I stand compared to a few years ago, how food costs have risen and how much having children costs 🙂
Itemized is cool, but takes time to enter. But using broad categories is too general for me, so I prefer to spend some time entering itemized costs, prices and quantities. Thisallows me to track how much fruits, vegetables, meat or sweets we consume, enabling me to infer if we're eating more healthily now than in the past, for example. Just using Groceries wouldn't do the trick.
I don't have a budget function as I have not found such forecasts reliable. I calculate 12-month average as a guide.
That what I meant in my previous post. raw data itemized and from the spreadsheet of this post can be useful for the display.
I prefer having the raw data and agregated ones in the same spreadsheet as it gives more flexibility when amending or having different views for the display
That sounds serious cool (and somewhat obsessive 😉 ) For my part, I usually breakdown groceries to veggies & fruit, meat, milk, supermarket, Indian groceries and other. This is easy to track too as we buy each from a different place / time.
Would you like to share the anonymized version of your workbook as a casestudy with our readers? I am sure others can learn a lot from this.
I'll need to do a lot of sanitizing 🙂 , but will try to do this after I'm back from an upcoming business trip. Is there an email I can send this to?
It's a beast of a file.
Regarding obsessive... I am a financial analyst by trade, and I like to see where the money goes. This way we never have arguments over money in the family, as our spending (and earnings) is totally transparent (to a point where purchasing a surprise gift becomes problematic 🙂 ). But granularity gives so much insight. And Excel's autocomplete function also helps.
40K rows! Love that! I am doing something similar with our receipts for my family. I have tons of Target and Walmart receipts that have all sorts of products on them, not just groceries. I could probably be 90% accurate in saying a grocery store receipt is all groceries, the same cant be said for these Walmart and Target receipts since I have purchased all sorts of products on one receipt. Similar for Costco. I am doing this all by hand, no scanner seems to do the OCR correctly.
Then I stumbled onto this and it looks great. Thought I'd share it with you and others: https://microblink.com/products/blinkreceipt
I have not requested SDK access, I am sure its going to cost a good deal for what it does. Check out the video:
BlinkReceipt - realtime retail receipt scanner
https://www.youtube.com/watch?v=_1J4T4RPlNA
Here is the correct youtube video for the blink receipt demo:
https://www.youtube.com/watch?v=Cdt_LhQbH3g
This is fantastic!
The model isn't complex, but it is beautifully laid out. Well done!
Oh, one error in it I think. Fort-nightly is set to 28. Shouldn't it be 26?
Good spotting. Sorry for the sloppiness. I have updated the workbook 🙂
Can it receive feeds from my email statements , credit cards, loan emi, etc.
thanks
Nice template, thanks! I believe '2013 or lower' formula in C19 should reference =Settings!I13.
I've been using this now for a few days and really enjoy it. Recommended enhancements for future iterations:
1) Convert all of the expense sections to tables. The formatting gets whacky if you copy and paste as it copies the row highlighting. It is easy to get it messed up. A table prevents that.
2) add a table for the income. My wife and I have different incomes and she has a side job. Instead of me trying to cram all of that logic in to a single cell formula, I created an Income table using the same logic as all the other expense tables, then linked the Income cell to it using the same sumproduct function.
This is great! Thank you!! Anyone know where we can get electronic copies of receipts from stores?
Example: Walmart, Home Depot, Family Dollar can all email me a copy of my receipt. I am wondering of others know of stores that can also send electronic copies of receipts? Basically I'd like to start creating a database of transactions like @Vit has been doing for the past dozen years, and using that data to analyse spending patters and help create our family budget.
I'd like to stay away from services like "shoeboxed" etc. I dont necessarily want to exchange my shopping patterns with those places because they'll sell the info to marketing companies, etc...
Thanks in advance!
Hi Chandoo,
I would say this should be one of the most user friendly budget sheet I've seem. Detailed, easy to understand and use. If my gf who doesn't use Excel, when I showed it to her without much instructions still able to get her budget sheet done...I'm speechless.
one error found I think... spreadsheet doesn't update if D4 value changed.
Well done!!!!
Hi Chandoo, this is fantastic! This will be replacing my current budgeting
spreadsheet. Correct me if I'm wrong but the frequency selector for income after taxes doesn't seem to do anything. I believe that the formula in settings!F13 thouuld be :
=IFERROR(SUMPRODUCT(Budget!C4, SUMIFS(freqs[Multiplication factor],freqs[Frequency],Budget!D4))/12,0)-SUM(F6:F12)
Keep up the good work!
Hi Chandoo!
Love the budgeted tool and clean layout.
In the category spending visualization conditional formatting, would it make sense to reference the maximum as a number and the value as =$C$4 since this is your baseline? When it was set to automatic it wasn't showing a direct relation to the Income.
Thoughts?
Thanks for all you do! Love the site!
How to change currency in budget template to say ? or ¢?
Chandoo,
Thanks for the awesome household budget spreadsheet, can we get something similar for office budget spreadsheet- please.
Regards,
All I can say is Thank you so much f
You are awesome