Darryl Murphy blog

Advice for First-Timers- The Budgeting Spreadsheet Main Photo

Advice for First-Timers- The Budgeting Spreadsheet


Part I
Posted: August 06, 2020 by Darryl Murphy

Whether it’s your first time or your tenth time, often the hardest part about buying a property is saving the downpayment.  In today’s post I’m sharing a spreadsheet that I have found useful for budgeting, building, and implementing a plan to save the downpayment needed to make your first, or next, property purchase.  In fact, you can use this spreadsheet to help you achieve whatever savings goal you might have whether that goal is buying a home, a car, your dream vacation!


In this post I will explain how to use this spreadsheet to arrive at a clear picture of your income and spending.


Before going forward it’s important that I acknowledge that this spreadsheet is my variation on a spreadsheet I originally downloaded from a website maintained by Canadian author and financial expert, Gail Vaz Oxlade.  I can’t find the site anymore, but Vaz Oxlade’s books are still available, I highly recommend them, all of them!

CLICK HERE TO DOWNLOAD THE FILE.


There are seven tabs in the spreadsheet:

  • Money Log,
  • Summary,
  • My Income,
  • My Spending,
  • My Saving,
  • Detailed Summary, and 
  • Data.

The file is populated with dummy data.  I suggest that when you first open the file, play around with it.  Play around with the numbers, add and remove lines in the “Money Log” tab, check out how those changes effect the calculations on the other tabs.  Don’t worry about the tab called “Data”, that tab is part of the functionality of the spreadsheet and should not be changed.  That said, don’t worry about messing up or ‘breaking’ the file, you can always download a fresh copy.


When you’re done playing, it’s time to get down to business.  You may want to start by deleting all the rows in the “Money Log” tab starting at row 2.  To do this, highlight cells A2 through to D74 and either right click and select “clear cells” or press the button (the actual steps for deleting this data will vary depending on your computer and the spreadsheet application you’re using).  


Now that you have a clean copy of the spreadsheet with nothing but the functionality and formulas you can start by populating the “Money Log” with all of your financial transactions over the timeframe you will use to assess your spending.  I suggest tracking your spending over a time frame no shorter than two months.  Keep in mind, the longer the timeframe you choose, the more accurate your tracking will be.


Most bank’s online banking applications will allow you to export all your transactions over a specified timeframe to a digital file (either a spreadsheet or a comma delimited text file).  This functionality is, by far, the most efficient, and likely most comprehensive, method available to populate the budget spreadsheet.  It would be best to export your bank transactions to a separate spreadsheet and copy and paste only those columns needed for the budgeting spreadsheet--that is, the financial institution (which you might have to fill in yourself manually), the date, the description, and the amount.  Make sure that when you’re adding transactions to the budgeting spreadsheet that your debits (money spent) shows up as negative amounts, and your credits (money received) show up as positive amounts.


It’s also important that you include transactions from all the various platforms/ways you spend your money.  Don’t forget to include all the transactions from your credit cards.  Be sure to include transactions from your PayPal or Stripe accounts (and make sure you’re not double counting them if your accounts are connected to bank or credit card accounts).  You should also include cash transactions so you may have to rely on your memory or the receipts in your pockets to track cash transactions.


Once you have populated the “Money Log” tab with all your transactions you need to work through each transaction and assign to them one of the categories listed in the dropdown menus under the “Code” column (column E).  I have updated and expanded the categories originally identified by Vaz Oxlade, specifically my goal was to capture things people money on today (sometimes excessively) that didn’t exist only a few years ago--for example, streaming services, and online subscription services.  If you come across a transaction that you can’t seem to fit, I recommend picking whatever category matches most closely or choose a category you’re not using and make a note of what you’re using it for.  What is important is that you’re consistent and, when it comes to your budgeting process, be sure to remember what that category includes or represents.


The ‘coding’ process can be time consuming, however, using the filter function of your spreadsheet may speed the process.  The exercise can also be very enlightening because, once you’re done, you should get a pretty clear picture of how you spend your money.  Once you have coded all your financial transactions, go to the “Summary” tab.


In the “Summary” tab, use the drop down menu in cell B1 to indicate how many months of transactions you have tracked in the “Money Log” tab.  Once this is done you will right away see a picture of your average monthly income, expenses and your average monthly surplus or deficit.  Your average monthly surplus or deficit should give you an idea how much you can expect to save per month (in the case of a surplus) or how much your debt might increase (in the case of a deficit) if your income and spending continues unchanged.


In next week’s “Advice for First-Timers...” post we will talk about estimating how much money you will need to save and how to go about making budgets you can live with.


If you're thinking about buying your first (or second or third) home, give me a call (905.380.5334), send me an email (darryl-murphy@coldwellbanker.ca), or DM me.  I’m happy to offer advice and answer questions!

Loading Comments

 Categories


 Archives