Somewhere around 1992, just a couple years out of college, I got inspired to buy my own home. But to do this at such
an early age I knew I would have to be a frugal money manager. I wanted to know in detail where my money was going
so I could spot savings opportunities and not just piss it away. I guess you could say I had a "measure and analyze"
mindset (which I now preach in my job) from a very early age.
Naturally, I looked into Quicken, which was around even way back then as an MS-DOS app. But after trying it out, I
soon decided it didn't meet my needs, and I could do a better job myself with Excel.
The key deal-breaker for me with Quicken was the ability to
rollover unused funds in a particular budget category. In other words, I wanted to visualize
all my expense categories as evolving "accounts" that would get a certain fraction of my paycheck
added to them each month, and have my expenses in each category deducted, with the remaining
balance rolling over into the next month. Sounds simple, right? But Quicken back in the '90's
couldn't do that.
So this project started out as just a simple spreadsheet. I had a fair amount of experience with Excel, and
I knew how to meet my goals with a simple DSUM formula.
I gave each expense category a three-letter code for category and another for subcategory. Then I became religious about entering
every dollar I spent into the spreadsheet with its cat and subcat codes. The DSUM then lets me summarize each category in an
"expenses" column. So a basic month's spreadsheet has a database sheet and a summary sheet as follows:
So, note column F104 shows the DSUM formula that summarizes the expenses for a given category from the "database" sheet, using DSUM.
Reading across a single row, we see the monthly budget for a category, the starting balance, less expenses equals the ending
balance. Then we have a column for "adjustments" which can be used to move funds from one category to another (The sum of all
adjustments must always be 0). Then, adding in the monthly budget amount we arrive at the starting balance for the next month.
The end-of-the-month procedures amount to copying the "Next balance" column over to "Previous Balance", deleting the expenses database,
and doing a "Save-As" for the new month.
Voilą! Simple budget management with Excel. No big deal, really.
Fast forward 10+ years
Over time, with marraige, real estate investment, etc. my requirements got more sophisticated, and my spreadsheet evolved as I
made it more and more usable. I had learned Visual Basic for Applications at some point in my career, so I decided to
put it to good use. The spreadsheet is now a borderline bonafide "application" with VBA macros providing UI
dialogs for common tasks and more sophisticated computations.
Year at a glance
The first thing I did was to make a single file for an entire year, as opposed to one per month. So no more monthly save-as procedures. Yay.
Thankfully, A 60x100 spreadsheet full of formulas and a year's worth of expense data is no big deal for the modern computer. This also
had the benefit of allowing me to go back to prior a month and enter new data or make corrections. The corrections propagate forward
to the present day automatically. Here's a full year's spreadsheet from 30000-feet:
Accurate Tax Estimation
The next enhancement was a fairly accurate tax estimator sheet and forumlas. The idea here was that bi-weekly paycheck deductions aren't always
accurate. So I'd rather my budgeted amount for taxes was based on a prediction of what my end-of-year returns would be saying, not
what my employer guessed they would say. With budgeting done this way, and tax expenses entered straight from the pay stub,
I can see at any moment whether I have overpaid or underpaid so far for the year. And if I'm going to need to write a check to Uncle Sam,
the money will already be set aside because I will have budgeted for it all year long.
Tax estimation is really quite simple once you understand what's what. It's just gross income minus deductions, then plug that
into a tax computation formula from the IRS. Here are the VBA tax computation functions I created based on adjusted gross income:
Public Function FedTax(ByVal agi As Double, ByVal stateTax As Double)
agi = agi - stateTax
fedBracket1 = 16750
fedBracket2 = 68000
fedBracket3 = 137300
fedBracket4 = 209250
fedBracket5 = 373650
If agi < 0 Then
FedTax = 0
ElseIf agi < fedBracket1 Then
FedTax = 0
ElseIf agi < fedBracket2 Then
FedTax = 1675 + (0.15 * (agi - fedBracket1))
ElseIf agi < fedBracket3 Then
FedTax = 9362.5 + (0.25 * (agi - fedBracket2))
ElseIf agi < fedBracket4 Then
FedTax = 26687.5 + (0.28 * (agi - fedBracket3))
ElseIf agi < fedBracket5 Then
FedTax = 46833.5 + (0.33 * (agi - fedBracket4))
FedTax = 101085.5 + (0.35 * (agi - fedBracket5))
Public Function stateTax(ByVal agi As Double) As Double
stateBracket1 = 14120
stateBracket2 = 33478
stateBracket3 = 52838
stateBracket4 = 73350
stateBracket5 = 92698
If agi < 0 Then
stateTax = 0
ElseIf agi < stateBracket1 Then
stateTax = 0
ElseIf agi < stateBracket2 Then
stateTax = 136.54 + (0.02 * (agi - stateBracket1))
ElseIf agi < stateBracket3 Then
stateTax = 510.86 + (0.04 * (agi - stateBracket2))
ElseIf agi < stateBracket4 Then
stateTax = 1259.58 + (0.06 * (agi - stateBracket3))
ElseIf agi < stateBracket5 Then
stateTax = 2449.5 + (0.08 * (agi - stateBracket4))
stateTax = 3946.14 + (0.09 * (agi - stateBracket5))
User Interface Macros
Finally, the real killer feature was a custom user interface. Here I made use of my VBA skills to create dialogs that let me
enter expenses and do other common tasks with mouse clicks instead of typing, and with no Excel jockeying whatsoever.
Below are some of the dialog macros I now use daily.
The "Enter Expense" dialog lets me enter an expense into the database without knowing the three-letter codes. When the
dialog loads, it reads the left-hand columns of the budget sheet to build a list of human-readable categories, subcategories,
and their codes. Just fill out the form and the right data goes to the right place:
The "Enter Adjustment" dialog works similar to "Enter Expense" except you are moving money from one category to another, so
you need to choose a "from" and a "to" category, and two adjustment entries are inserted:
And the "Set View" dialog takes you where you want to go in the spreadsheet. Since a full year's data is too much to display
all at once, this let's you to show a particular type of column and hide all others. Or you can choose to view the raw database,
filtered by a chosen category.
Other Quick Tasks
There's even more macro-driven functionality I haven't bothered to show:
- Paycheck entry: I have a dialog for quick-entry of all the data on a paycheck stub, that auto-populates with the data from the prior paycheck, since it rarely changes.
- Credit card downloads: I can download a CSV of my credit card transactions from Chase, and with the click of a button it will use this data to fill out the expense entry form. I just have to indicate the category and subcategory.
- Expense reversal: For reimbursable expenses (like business or medical), I can point at the expense and automatically enter an expense of the negative amount when the reimbursement comes in.
- Allowance summary: Since my family uses an "allowance" system for separating joint and personal expenses, I have macros to credit or charge against the allowance, and to summarize the monthly allowance amount.
- Mobile entry: For expense entry on-the-go, I wrote a mobile entry app.
All-in-all, my budget spreadsheet app saves me a ton
of time in my quest to understand and track my family's expenses. And, it was fun to write! VBA is pretty cool!
I'm happy to share it if anyone is iterested. Just contact me