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?
- 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.
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.
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.