Building a Personal Finance Spreadsheet Part 2: “Budget”

Onto part 2 for this post. Onceyou’vegot the transaction recording and totalling part of your spreadsheet up and running, you can start to think about budgeting. This section will require a lot of customisation to your situation, and is likely to change as your situation changes. A new job, or an extended period travelling, or a new housing situation, are all reasons to review how you budget.

As I mentioned last week, this part of the spreadsheet is a lot harder, and more susceptible to change. It took me a number of years to get this part correct, and I’d recommend viewing it separately to transaction recording. That way, if the budgetingdoesn’twork, you can continue to experiment getting it right for you whilst continuing to record your transactions as they actually happen.

Again, let’s consider a view things before we get started:

1. Savings versus investment. I view ‘savings’ as money that I set aside for later spending – a holiday, a designer jacket, a new laptop. I try to save as much as possible for my near-future enjoyment. Investment, on the other hand, is money solely for the purpose of making more money. For me, this is my share portfolio and an ISA, but for others this might be a deposit for a house, managed funds, as a peer-to-peer lender, or additional pension/superannuation contributions. It is not money for your future enjoyment – at least, not in the next 2-5 years. How do you view these two separate concepts? Do you have investment plans?
2. How are your accounts structured? How many current and savings accounts do you have? Which ones do you get paid into? And most importantly, are your investment funds and savings held in different accounts? If they are not, you must separate them immediately.
3. How often do you get paid? Is is a regular amount at regular times, or irregular? Do you need to store more funds to cover periods of lower pay?
4. What are your obligatory expenses? Anything where you have a contract (e.g. mobile phone plans, rental agreement) or debts are obligatory.
5. How do you spend your money? Do you have enough at the end of a pay cycle? Do you go on a spending binge after periods of austerity?

I believe the easiest way to forward plan is what I call the “envelope concept”. In the days when people were paid in cash, many set aside money in envelopes to cover various expenses until the next payday. Indeed I’ve done this physically as a child saving for Lego and books I wanted, and as an adult setting aside a fixed amount into a plastic sandwich bag for living expenses when I was saving for overseas travel. Seeing how much money is left in the envelope is a very powerful psychological way to prevent overspending too early in the pay cycle. But today, with the convenience of online banking, debit cards and paypal, it isn’t practical or desirable to hold cash in an envelope if we can avoid it. So we recreate the concept in a spreadsheet.

The basic idea is to have a row for every day, and to have multiple groups of three columns for every envelope, or budget category that you wish to measure:

1. “Expected” which records proposed future expenses. It doesn’t have to be exact – I’ll set aside £25 for a nice pub lunch and a couple of beers, but the actual cost might come in under this. This column is manual.
2. “Actual”, an automatic column that records the actual expenses that have been incurred. This makes use of the SUMIFS formula in Excel, looking to the budget category column on the “Transactions” tab. You’ll need to manually delete the expected expenses from the “Expenses” column as they actually occur
3. “Remain”, which shows the amount of money left in your envelope. This is simply the amount of money allocated, minus the expected column, minus the actual column. You can conditionally format it when your envelope is starting to run low – this will attempt to simulate the psychological effect of seeing the remaining fivers left in your envelope.

As you plan your expenses into the future, you can see where your envelope is starting to run low. If you overspend to early, you can look forward to see what expenses you might need to cut down in the coming time.

How do you allocate money to each envelope? I’m in a 9-5 job, paid monthly, with fairly stable expenses. I take my net pay, subtract 20% for investment purposes (this amount changes depending on my situation. You should determine what is realistic for you based on your circumstances), and subtract any contractual expenses. For me, this is rent and utilities. If you have a mobile phone contract, rental fees for appliances, or – most importantly – debt repayments, then they must come out here. The remaining money is then distributed between my envelopes. In my case I set aside a per-day amount for my Everyday envelope and Weekend envelope (I count Fridays as a weekend day), with all the leftovers going to my Savings envelope. I’m always paid on the same day, so I manually add the amounts into the “Remain” column on my spreadsheet. This is not so realistic if you’re paid more frequently, or varying amounts fromshift workor have significant passive income. If you’re in this situation, an additional column for “funds in” should be added to every envelope, as well as an “total net income” column.

Don’t set an investment target that is too high if you have debt. If the debt has a higher interest rate than the return you’dreceiveas an investment, it probably makes sense to clear the debt first. However, I do believe that if you have investment goals, you should make these transfers on payday. I calculate my % to invest based on my total net income. There is always another trip or decent bottle of olive oil I can buy – funding investments with ‘leftover’ money is never going to be realistic. Knowing at least a couple of months of spending habits will make budgeting a lot easier, which is why I encourage you to follow Part 1 of this post first. It is always much easier to budget when you have realistic information of your spending to use. Also, if you have debts, or mouths you need to feed on your income, and you are unsure, you should speak to a financial advisor. A good book to read is “The Richest Man in Babylon” for some inspiration and budgeting ideas. Finally, I encourage you to build up an amount of emergency savings, to cover your expenses in the event you lose your job or if you have a significantly variable income.

So the next question is, what envelopes to set up? This depends on your situation and your goals. I set up three envelopes: E – Everyday (general expenses such as the supermarket, transport, basic clothes, mobile phone credit), W – Weekends (eating out, drinking, day trips, coffee), S – Savings (mainly travel, but could cover a new computer or a designer jacket/boots – something you don’t buy frequently that is expensive). My E and W envelopes are usually empty by my next payday, but I try to keep my S envelope growing and growing to make sure I have enough money for that two week holiday four months down the line. The “Expected” column of my S envelope helps me work out how much I will need in four months, and if I’ll meet that target. It’s a pretty powerful way to not overindulge today when you know there is a nice trip booked in a month.

You could set your envelopes up in different ways. You might simply break it up into Needs and Wants. If you are on a very long round-the-world trip, you might break your funds up into Hotels/Airfares/Visas, Eating/Drinking/Sightseeing, and Shopping/Other spending money. Think about how you spend you money, and situations when you don’t quite have enough. But I’d recommend against using as many envelopes as you’ve got for recording purposes. Too many envelopes makes budgeting more painstaking and thus more likely to fail. Two to four is ideal.If you find you’re constantly emptying your envelopes too early, reassess your system. Have you cut unnecessaryexpenses? Do you lack discipline in your spending? Are you setting unrealistic savings targets? Do you earnenough- and can you earn more?

Also, think about your bank accounts, and how you use credit cards. My S envelope is covered by my online savings account (unrestricted access), and E and W envelopes covered by my current account. If you are setting aside money for investments, DO NOT store these funds in the same accounts that you use for normal expenses, as you’ll probably spend them! At the very least, set up a different savings account, perhaps one with more restrictive withdrawals in return for a better interest rate. Investment is a whole other topic, and I will talk about this in future posts.

I hope you find this helpful, and please do share your experiences and feedback with me. Remember recording actual expenses is always easier than planning ahead, so if your budget system doesn’t work out initially don’t worry – just keep collecting information on your actual expenses and try again a couple of months later.

Disclaimer: the information contained in this blog is for general information purposes only. It is not intended to constitute financial advice. You should consider your own financial situation and speak to a professional financial advisor if necessary. The author will not be held liable for any loss or damage.