I don’t know why it took me so long to come up with this idea, given that tracking my budget is my life but whatever, I’m finally going to blog about creating a budget that does all of the upkeep for you. I’ve been using Excel to keep track of pay dates, payment due dates, auto pay dates, account balances, and my spending in general for the past few years. I can’t say it has been especially helpful in my ridiculous spending problem, but it definitely does let me know if there’s a week that I’ll be particularly broke. Here’s how to setup a budget in Excel:
Put all of your accounts that require payment (loans, car payment, credit cards, etc.) down the left side, leaving the top cell blank. In columns B and C, enter the day of the month on which each payment is due (B) and the current balance that needs to be paid off (C).
I also like to add a sum function to the end of column C so that I know how much debt I currently have. I make a solid-colored line under my account list so that I know exactly where my accounts end.
Across the top of your workbook, list all of your pay dates (notice I also maintain a solid line to the right of my account list):
I only did this example through the end of the year so that all of the detail would be clearly visible. My actual budget goes through May 2017 hehe.
In the gray line, add a sum function which totals the lines between it and the date.
Copy the formula into the same row for each pay date.
This sum will indicate the total amount of the payments that you make each month.
Directly below this sum, make a new formula of [the amount of your paycheck after taxes]-[the cell which has our sum function in it]. Let’s say my paycheck is $1500 after taxes, in which case my formula would be, “1500-E27,” for the 11/11 pay date. Once you have this formula, copy it into the same row for each pay date.
This formula will indicate how much money you have to spend after all of your bills are paid.
Next, you need to plug in the payments which you’ll make each pay day (or pay period, whatever). In my example, I’ll just pay $100 on each account on the due date listed in column B. Example, since my pay dates are 11/28 and 12/9, I’ll need to make the payments that are due on the 4th and 1st of the month before the 12/9 paycheck (duh), so I’ll budget for them to come out of the 11/28 paycheck:
You can see at the bottom now, you have your spending money listed. If you’d rather pay more debt off and spend less, increase your payment amounts listed, and the formula at the bottom will then reflect how much you have to spend between pay dates.
At the end, I like to add a column which reflects the total amount which you have planned to pay. To do this, just add a sum function in each of the cells in the column (after all pay dates) and total the amount of payments per account:
The whole reason I do this is so I can see, after all of my scheduled payments, how much debt I still have to pay off, and in which accounts that debt is. To do this, add a column between your Balance column (C) and the solid colored line. Title it whatever you want, mine is called, “Projected,” as in, my projected debt after all of my payments have been processed. Add a formula in each row which subtracts your balance (column C) from the amount which you have paid (column J):
This shows, after the payments indicated on your budget are paid, how much you’ll still have to pay off. Since this example shows a negative amount on the Mastercard account, we know that the account will be overpaid if we make all of our scheduled payments as indicated above (sweet!) and you now have $50 extra dollars to spend (or redirect into a payment to a different account. In the screen shot below, I have redirected it to the Visa account listed).
Extra things you can do:
If you’re enrolled in autopay, I color code any auto-due dates. My student loan payment comes out automatically on the 9th of every month, so I have that color coded blue on whatever pay date it comes from.
Add an area at the bottom for activities that you know will cost you more than what you usually spend between pay dates. Have a vacation planned? Add it to the bottom of a pay date so that you remember not to make a huge payment on your car.Going out for a birthday? Add that too so you set aside $50 extra for a birthday gift (or drinks at the bar, whatever).
Indicate when your accounts will be paid off with green-colored cells for the remainder of the row post-payoff.
And there you have it! It looks like a lot of work to setup but 1) it’s really not and 2) it’s totally worth it because it requires very little upkeep. The only maintenance I do to my budget is update the balances twice a month (after my payments have cleared) to reflect the payments I made and any interest that has been accrued. Also, after my payments have all cleared (I color code the payment cells yellow when I make a payment and am waiting for it to go through), I delete the whole pay date column from the worksheet. So, after the 11/11 pay date, I’ll delete that column and 11/28 will be at the front, immediately to the right of my solid purple line.
PLEASE let me know if you need any help creating a budget because it is a legit HOBBY of mine and I would love nothing more than to help.
