My personal finance spreadsheet

When MS discontinued Microsoft Money, as an Excel addict, I felt that I should develop my own personal budget control. During the past years I have been using and evolving a spreadsheet to replace MS Money. I thought that it would be a good idea to create a post about it, as the spreadsheet can be customized for your needs.

The spreadsheet was built with the cash and accrual methods in mind. These concepts are valuable in our world of credit card bills, where they tend to create a big umbrella of expenses in our cash flow. For example, let’s assume that you went to a movie, ate at a fancy restaurant last month and dropped $50.00 and $300.00, respectively, on your credit card. Considering that you care about your credit history, you will likely be paying off the credit card bill this month. Therefore, in the accrual basis, the $50 and $300 appear under the hypothetical categories Entertainment and Restaurants in last month’s report, and the credit card bill amount is not taken into account. On the other hand, the cash basis report will not show the $50 and $300 on their own, as they will be summed up with the other credit card expenses that you made during X number of days. Both reports are important, but to have a firm and true understanding of your personal finances, you need to focus on how you are spending your money. Luckily, the accrual method can show you all that.

So, let me explain how the spreadsheet works:

CAUTION

• Be aware of your country/region, date, time and number format, as well as your bank transactions. These can all be very confusing. This spreadsheet was tested with the US/Canadian number format.
• Do not rename the config and transaction tabs. The macros do not permit the tabs to be renamed yet.
• When a new year comes, you will need to delete all the transactions and change all the headers’ dates to reflect the new dates.
• Do not expose your finances to someone else. Microsoft Office has a strong built-in encryption that is easy to use, and this great article from Microsoft explains how to do it.

CONFIG CATEGORIES AND SUBCATEGORIES

After having many of my friends use the spreadsheet, I had to come up with a way to let them easily configure the categories and subcategories on their own. Albeit the config provides flexibility, you still need to follow a few rules:

1. Never, ever, repeat a name in categories and subcategories. For instance, if I have the subcategory “Lodge” in the “Travel” category, I cannot have another subcategory called “Lodge” in the “Work-Travel” category. Therefore, I use “W.Lodge” in the latter category.
2. Do not cut or drag and drop the position of the rows, columns or cells. Excel formulas can become confused by these kinds of changes.

DATA INPUT

This is not just the most important but also the coolest part. There is an Excel macro that reads .ofx files (Microsoft Money file format) and automatically inserts the records into the Transactions tab. When you run the ReadOFX macro, it will show a dialog box to choose the ofx file with your transactions, either from your bank account of credit card. As the macro records the OFX account name with its corresponding account in the config tab, this dialog box will never prompt you again when you download it from the same financial institution. The picture below displays the correlation between ACMEBANK and the OFX Account ID. Don’t worry about duplicates, as the macro checks if the record was already inserted.

It is crucial that you insert bank and credit card transactions.

The next phase is where you reconciliate your transactions based on the categories and subcategories that you have created. All you need to do is go through each record and define the proper category and subcategory via drop-down menus. Once you’ve done this step, all the reports will be automatically populated, similar to what Microsoft Money did before.

CONFIGURING AUTOMATIC RECONCILIATION

In order to make my life easier when it comes to the reconciling task, I added a correlation macro. You can pre-populate keywords with their corresponding categories and subcategories. Whenever you run theFillCategories macro, it will search for non-associated transactions and see if the keyword matches a description field. The description field is filled by the information contained in the .ofx data provided by the bank, so you need to know how they are labeled by the bank before filling it in yourself.

FORECAST

What is the benefit of knowing the past if you do not plan for the future? Luckily, the Forecast tab does this for you. It is divided into fixed and variable expenses where you can also associate with your previously created categories, for consistency.

THE DASHBOARD

Now that you know how this spreadsheet works, I’ll show you how to interpret each report—starting with the Dashboard graphs.

Below is the first dynamic graph. At the top right, there is a drop-down menu where you can select the month. It will automatically show you how much you have spent in the selected month by category. In addition, the percentage values located alongside the bars are related to the total spending in that month.

The Budget Report depicts four types of information on a monthly basis. The blue bar means how much money you made – income. The red line refers to your total expenses based on the cash method. The orange line shows your total expenses based on the accrual method. The light green bar either at the top or at the bottom of the blue bar shows the difference between the expenses and the income (using the cash method). On the right, the small box with the three bars reflects the month to date information. The blue and red bars contain the sum of all your incomes and expenses, respectively. Subtract the former from the latter and you get the green bar.

The next dynamic graph, called Monthly Category Report, is very useful for visualizing and comparing the monthly variation of a specific category. This graph helps you define goals for the forthcoming months. You may see that you are progressively spending more money in a specific category. The percentage values always refer to the total of the expenses in that specific month, in addition, the number within the bar represents the amount of money spent in that same month.

Have you ever wondered how you spend money during the year? Now you may have a much better idea. The Weekly Expense Report breaks down your expenses by each week of the year and helps you see an expense pattern. The graph is based on the accrual method; hence, it does not take into account any credit card payments.

This big guy below summarizes in a stacked bar format the sum of all your expenses in each month. It is an easy way to identify where you have spent more money throughout the year.

The following two charts are very alike in format. The first one shows the category expenses and the second the income sources. Both depict the amount of money via bar size according to the vertical value on the month to date graph. On top of the bars, the percentage values are calculated by the value in the bar and the sum of the values in the graph.

CASH TAB

This is your cash flow, so the category values in this report do not sum up any credit card transactions. I encourage you to go to Dashboard – Monthly Category Report and select Credit Cards. This report shows the amount of money and the percentage the credit cards represent in your total expenses.

At the end of this report, you can find details about each bank account. There is the option to add a starting balance value from the previous year for each bank account.

YEAR STATS TAB and ACCRUAL DETAILS TABs

The majority of graphs come from these reports. As I mentioned before, the accrual method is the best way to see your financial profile and understand how to save money.

On the YEARSTATS tab, you get a summary of your main categories’ expenses and income throughout the year. On ACCRUAL DETAILS the subcategories are listed, which allows you to pinpoint the ditch into which your money is going.

By the way, just in case you were wondering, all the data used in this post are not real.

Ok, now that you understand how to use this spreadsheet, you may be interested in download it. (Brazilian Version here)

Personal Budget Spreadsheet by Artur Rodrigues is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License