Keeping track of your personal finances can be a very painful exercise and prove very difficult to stay on top of. There are a number of online tools available to help in this regard but, more often than not, they aren’t tailored to your financial profile or objectives. Using Excel to do this allows for simple customization to your needs without sacrificing the powerful analytics required to slice & dice your financial data.
In this post, we will explore a template for managing personal finances. This template has been designed to be simple and effective for monitoring your expenses (templates and tools can only and always ”monitor” your expenses – only you can control it).
Introduction to the template:
The template is basically an .xlsx and is divided into four sheets.
- Expense Head
- Data
- Summary
- Graph
Each one is addressing the specific portion to keep your expense organized. We will take then one by one and see how it is working.
Expense Headers:
There are various types of expenses that we encounter in our daily life. This template can accommodate as many expense types as you like; the default expenses shown are only 13 types. You can still subdivide the expense types further and that will be directly reflected in the following sheets in the template.
Just for an example you can group or subdivide expense like one that are related to each other – home, can accommodate groceries, utilities, school or educational expenses for kids and food and laundry expenses. You can create anther head of expense named like outdoor expense where you report expenses of picnics or other such outdoor activities, occasions when you dine out or go to parties.
You can also create a head reporting the liabilities for example government taxes or any loan installments you are paying.
Data:
The template is designed to accommodate data from a specified data onwards for a period of one year – 365 days. The template has flexibility to set the start date as your wish so that you do not need to worry about adding or deleting columns – just set the start date of the template and it will start working.
The left side of the template is dedicated to the Expense head that it takes up directly from the Expense head sheet and thus you don’t need to worry about the layout of expenses. You just need to set it once when you are working on your expense breakup and rest of the sheets will take them up automatically.
In addition if you want to reduce the length of the data table, simply delete the additional columns that you don’t want to see or if you want to expand it, drag the last column of the table to the right to enhance it further. The table also has a day-to-day total column that is an essential requirement.
Summary:
The summary sheets sum the monthly expense and produce a single page summary of our personal expense. Again the expense header column is picked up automatically. The header row contains the name of months –this summary sheets sheet shows the expense for one year.
The sheets uses a bunch of formulas to set things right – the formula is so complicated and can be easily modified by the user to adapt to their needs. The second last column of the table shows the total for each expense head and the next column reports it as a percentage of the total expense.
Graph:
The chart or a graph is present as the last sheet on the template to give the user a graphic idea of where the expense is directing to or what head of the expense is taking most of the part. This is essential to point out any unproportioanl expense and to streamline the budget with it.
The chart is basically a pie chart breaking 360 degrees of circle into various parts in proportion of the percentage of the expense.
Additional – Understanding the SUMPRODUCT() formula:
Though this is not much related to the template, for anyone interested in understanding this formula, here is the explanation:
=SUMPRODUCT(($B3=Data!$B$5:$B$29)*(TEXT(Data!$C$4:$IK$4,"mmm")=Summary!D$2)*(Data!$C$5:$IK$29))
We start with the left most second bracket that has ($B3=Data!$B$5:$B$29) – this basically validated the expense head and to pick up only those that are under consideration for example the first one is “Mortgage”.
The second one validates the month by using the TEXT() function and using one of its option “mmm” to format the dates in the header row to “mmm” format and then matches it with the header row of the summary sheet. Thus producing another array of 1s and 0s. The two arrays are multiplied together and then multiplied with the data table.
Conclusion:
Kindly download the template from this link. This is a very handy and easy tool to manage your personal expense. It uses spreadsheet software that is readily available on most of the personal computers so there is no need to download and install it – it is ready to use. It can be expanded and modified to suit your needs. Please give us your feedback about the template and how we can improve it further.