This is a non-technical post.  It’s certainly geeky though.

We all have them: bills that are due on something other than a monthly schedule.  Car insurance, home owner’s insurance, life insurance, vehicle registration, Pandora One, pick your poison.  It can be difficult to work these bills into the monthly budget since they don’t hit you every month.  Worse yet, you might forget which month a large bill will be due and fail to plan accordingly.

About a year ago, my wife and I came up with a strategy that has been working out really well for us: use an escrow account.  If you have ever owned a home, you should be familiar with the idea.  Every month, you tuck some money away into an account so that when the bill comes due, you have the money sitting there.  Every year, you review your escrow account transactions to see how well you estimated, and you make contribution adjustments as necessary.  With your mortgage payment that includes property tax and hazard insurance, your bank does this for you.

But there’s no reason this can’t work for bills other than property tax and hazard insurance.  All it takes is some simple math and an Excel spreadsheet.  Let’s consider the following list of bills:

  • Auto Insurance (semi-annually)
  • Home Owner’s Insurance (annually)
  • Life Insurance (annually)
  • Vehicle Registration (annually)
  • Trash/Recycling (quarterly)
  • School Tuition (10 months out of the year)

Think of all of the funky bills that you pay throughout the year; you know you have at least one that throws you for a loop every time it’s due—make sure it’s in this list.  Go back through your checkbook register (online or otherwise) and find them all—be thorough now and you’ll thank yourself next time that bill is due.  Feel free to include monthly bills in the list if you’d like.  Make a list of these bills and for each one record:

  • Payment Type: Direct Debit or Check (paper or electronic)
  • Payee
  • Purpose: Auto Insurance, etc.
  • Amount Due
  • Schedule: Monthly, Quarterly, Semi-Annually, Annually
  • Months Due

From there, we can easily figure out the yearly cost, monthly cost, and the per-paycheck cost.  Given semi-monthly paychecks, you can just divide the annual cost by 24; and for bi-weekly paychecks, divide the annual cost by 26.  Put all of this into an Excel spreadsheet; something like this:

List of bills

For our sample data, we can see the total of $8300 per year or $691.67 per month, or $345.83 per semi-monthly paycheck.  I’m willing to bet that when you calculate this number for your own bills, it’s higher than you would have thought.

This is great progress, even at this point.  But what should we do with our $345.83 per paycheck to ensure it’s available for these bills?  Easy: set up an auxiliary checking account with your bank, and update your direct deposit so that you are automatically setting the money aside.  When the bill comes due, you can either pay directly out of your auxiliary checking account, or you can transfer money as needed.  But we’re not quite done—we need to make sure your escrow account is well funded all year long.

Your $1000 car insurance bill might be due the same month as your $500 home owner’s insurance, $250 school tuition, and $25 garbage/recycling bill.  That’s going to be an expensive month!  We need to make sure that your escrow account is seeded with enough money to ensure you’ll never be overdrawn.  This process is a little tedious, but extremely valuable.  We essentially need a burn-down chart of your escrow balance, to make sure it never falls below a specified threshold.  We set our account up to never fall below $200—your bank might have restrictions on this beyond your own comfort level, so be sure you think about what this minimum balance should be.  We’re going to use all of our data to create a list like this:

Running Balance


Start this out by listing out all of the bills within the months they’ll be paid.  At the end of each month, put your paycheck deposits in (and highlight those rows for easy viewing).  I recommend grouping all paycheck deposits for the month in at the end of the month, to ensure you don’t run into timing issues during the month.  Lastly, you’ll fill in the one time seed money amount.  Before filling this in, you’ll almost certainly see your balance fall below zero.  Set your seed amount at the beginning of the year such that the lowest balance becomes positive, and equals your desired minimum balance.

Don’t worry about the seed money being at the beginning of the year and filling this out in the middle of the year.  We’re just putting that number at the top to make it easy.  Once I had my running balance flowing the way I wanted, I looked at what the balance of the account should be at the end of the previous month, and I then ensured that our auxiliary account had that much in it at that time.  For instance, at the end of August, the account needs to have $1358.33, so if you’re setting the account up for use starting September 1st, then open the account with $1358.33 in it—not the $1700 seed amount from the beginning of January.

To simplify this visual of how much seed money your account needs, and when it hits its minimum and maximum values, we added the following to our spreadsheet:

MinMax

We can see that we hit the account maximum of $1858.33 at the end of May, and the minimum of $200 in the middle of July, after paying Auto Insurance.  But at the end of any given month, I can check the account balance against this spreadsheet and it should match.

In order to account for the inevitable increase in some bills, we actually overpay slightly each paycheck.  Not much, we just round up to the next $5.  By overwriting the amount with $350, the Annual Gain shows $100.00.  This means that after a year of running on this schedule the account balance will have increased $100.

As I said, about a year into using this technique, we’re very happy with it.  If you’re interested in setting up your own personal escrow account, feel free to download my sample spreadsheet here: Personal Escrow Sample (xlsx inside a zip)

Technorati Tags: