tophermiller.com

Budget Management

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))
    Else
        FedTax = 101085.5 + (0.35 * (agi - fedBracket5))
    End If

End Function
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))
    Else
        stateTax = 3946.14 + (0.09 * (agi - stateBracket5))
    End If
End Function

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:


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.


tophermiller.com | admin