Expense Tracking - Create Your Own Expense Template

Expense tracking is paramount to creating and staying within your personal or household budget.  Knowing not only how much you spend but also what you spend it on can help you adjust your spending habits.  If your cash inflows are somewhat fixed (paychecks, interest income, retirement income, etc.), then spending less than what you bring in will allow you to save and/or pay down existing debts.

You can create an expense template in any spreadsheet program or in an accounting ledger or accounting journal. By using a spreadsheet program, you can prevent errors by creating formulas and save time by copying/pasting one month's sheet over for the other 11 months. And at the end of the year, you can save a copy of your spreadsheet for the coming year (and just delete all of the daily entries). I highly recommend using a spreadsheet or other personal finance software.

Open a new file and enter the month and year in the first cell (i.e. January 2020). In the next row, skip the first column and going across, enter each day of the month in a separate cell. Since I copy/paste the same worksheet for the remaining 11 months of the year, I typically enter 31 days in each sheet. After the last day, enter "Total" in the following column. You will then be able to track the monthly total for each row. In each row below the "Total" heading, enter a formula that will automatically sum each day's amount for the entire month).

List all of your expenses in the first column (start a few rows down--leave some space at the top). You can group the expenses by category, such as household (rent/mortgage, gas, electric, water, telephone, cable, internet, mobile phone, etc.), auto (fuel, repairs/maintenance, parking), travel/leisure (travel, entertainment, music, movies, books), home (furniture, bedding, houseware), health/beauty (haircare, toiletries, clothing, accessories, shoes).

After you enter all of your expenses, skip a row and enter "Total". This will allow you to track the total amount you spend each day. In the "Total" row, you should enter a formula that will automatically sum all of the items in the column (i.e. sum B1:B23).

You can also use the expense tracking worksheet to track the amount of income (wages, interest) and deposits you receive each month.

Skip a few rows below your "Total" row and enter the sources of income and deposits you typically receive each month (wages, interest, alimony, retirement, etc.) Don't forget to copy your formula for each row's "Total" in the last column. You can also create a formula to sum each day's total.

Skip another row or two and then create a "Cashflow" row. You should subtract your daily expenses from your daily income. If you prefer, you can just create the formula for the end of the month. This shows your monthly cashflow. If it is a positive number, then you spent less than you received. If it is a negative number, then you spent more than you received.

You can also track recurring monthly payments below the "Cashflow" row. These are for items that you do not want to double-count, such as credit card payments (if you recorded these items as expenses in the current or previous month, then you do not want to record your credit card payment as another expense, as you will be double-counting your expenses). You should also track income that you do not want to double-count separately, such as savings deposits, retirement contributions, mutual fund and/or stock investments, etc.

You should create a separate tab for each month and then a total tab at the end of your workbook, which contains links back to the total information for each month.  Please find a sample below (of the total tab), which you can modify to suit your specific income and expenses.  After you are satisfied with one month (tab), you can copy/paste into additional worksheets.

Now that you've created your expense tracking workbook, it's time to start filling it in. Each day, keep all of your receipts from purchases and write down any cash spent for which you did not receive a receipt. At the end of the day, enter the amounts from the receipts and/or cash ledger into the workbook, under the appropriate categories. If a receipt contains items that fall into multiple categories, try to break them out (to make it easier, you can put the entire sales tax amount into the category with the largest amount).

If you have a smartphone or PDA, you can download expense tracking applications.  That way, you can record expenses immediately and forego paper receipts when paying with cash.

If entering amounts daily seems daunting, you can set aside time once a week or at the end of the month to update your workbook. Don't forget to enter any income received. You can use bills, credit card statements, and online banking summaries to help you enter the data.

At the end of each month, you'll have an accurate record of your income and expenses. Periodically, check the "total" tab at the end of the workbook to see how your spending changes from month to month.

Your monthly and annual tracking worksheets can help you budget and plan for large expenditures. Each month, is your cashflow positive (you spent less than you received) or negative (you spent more than you received) or does it vary? Always try to live within or below your means (have positive cashflow), especially if you know you have a large purchase coming up. This allows you to pay for it with monies saved, as opposed to financing it and possibly paying high rates of interest.

Do any of your monthly or annual expenditures surprise you? Are you spending more than you thought on eating out? Think of ways to cut back (best place to start: increase your grocery spending and dine in), else, if you would like to maintain the same level, look at your other expenses for ideas of what to cut.

At the end of your first year, you can use the "total" worksheet to help you budget for the next year. Copy and save the entire workbook and then clear out the daily information and update the year (i.e. 2021 instead of 2020) where applicable. You can also link to the previous year's workbook when entering your "beginning cashflow" information.

You can also use the workbook information to confirm your tax documents. As you receive your 1099-INT forms for interest earned, compare them against the amounts you received during the year. If you are self-employed and do not use other financial software, you can create a separate workbook for your business expenses.

Copyright 2011, 2018, 2019. All rights reserved.