Advice with budgeting spreadsheet. Met a bit of an issue.

B0bbyEwing

* I've created this rather than resurrecting the other thread because if I did that then skim readers would be spending time answering the original question instead of this one.

I decided to go with the Google Drive/Sheets monthly budget template as I couldn't find a template that ticked my boxes and didn't know Excel lingo in order to create my own so decided the Google Sheets one will have to make do.

I've adapted the template a little to suit what I wanted to do but not long after adding a "Savings" section, I realised my problem, which is when I stopped before wasting any more time on it - which is also why some entries in there will stand out as not making sense.



And this is the problem:

To know what you've saved/spent over the course of the month, it's the expenses deducted from the income - simple. 

Issue is we have a range of various savings that we put money in to during the month. Some of these are
* easy access accounts like say the Chase one offering 1.5%
* accounts where the money is tied up for a short while but you'll eventually access it again - e.g. regular savers
* accounts where you basically have to forget about it - SIPP, Lifetime ISA.

In each case, the money is still your money, so they're not expenses, but my problem is when trying to calculate what has been saved over the course of the month because "money saved" to me is money I can access, spare money, money I can go & buy something tomorrow no problem with.

* It's no good saying I have £1000 left over at the end of the month when £900 of that is between SIPPs & regular savers and I actually only have £100 to call on.
* But at the same time I'm not sure I want to be saying we've only saved £100 as technically that wont be true - we managed to put £1000 aside in various accounts.

It's an ongoing problem I've been having with my current end-of-month balance spreadsheet:



I've been doing this for years & every year I run in to this kind of problem somewhere along the line. 

Here, purple "ours" represents actual cash as in money I could go withdraw right now if I wanted. 
I have things like SIPPs, LISAs, Workplace Pensions, S&S ISAs underneath this which obviously isn't shown in the screengrab. They're underneath & separate so that

1) I can see what their end of month balances were
2) They're "tied up money" so wanted them separate from easy access money.

So you'll see the problem where with the negative 6.6k, makes it look like we spent considerably more than we earned. 
In actual fact, we didn't spend that. That was money put aside in an account (which would show above these if I gave you the full screengrab) over the course of the year & then dumped in to our LISA accounts at the start of the tax year. 

So basically over time, I've just had to accept that every so often, there'll be incorrect entries here & there that don't tell the full picture - which is why in the top right of that cell you see the red triangle as well as the cell being coloured - I left a note to explain what happened in case I look back in x-months x-years and wonder why that is as it is.


So back to the problem at hand - how would you manage the setup in the Google Sheets template?




* note: As I can see it coming, I'm not getting in to whether certain categories should be in the variable or fixed. That's not relevant to what I'm asking so I'm not being rude but I wont be responding to anything along the lines of why is x-category there and not over there.

* note.x2: For anyone who doesn't know, I've found that if you right click & save an image to your PC, you can then see it in readable size. For those viewing on a mobile, no idea what you guys have to do to see things in a readable size or even how it displays on a phone.

Thank you.

mr_stripey

If you've come this far, you should really take a look at YNAB (You Need A Budget). 

Yes, there is an annual subscription cost and it does have a bit of a learning curve but based on what you've said above, you would be a long way along that curve I think..

YNAB allows you to have tracking accounts that are "off budget" if you like. So it still counts your money but keeps it in the correct "pot"

You can also allocate money to pots for Christmas, birthdays, car repairs or absolutely anything that suits you.

I started using it about 18 months ago and I haven't looked back. 

It certainly isn't for everyone, and there is a cost involved (about £80 a year I think), but there is a free trial. I've saved way more than the annual fee by being more organised

RG2015

Sorry if this is a bit simplistic, but I try to keep things simple.

My spreadsheet shows payments to my own accounts as transfers so my balance remains positive.

B0bbyEwing

mr_stripey said:

It certainly isn't for everyone, and there is a cost involved (about £80 a year I think), but there is a free trial. I've saved way more than the annual fee by being more organised

Here's the problem I'm afraid.

One offs I can just about accept. Renting though is just a total forget it. I wont even entertain it, no matter how good it may be.

Not only that, I just think that there must be other people out there who've factored in savings & run spreadsheets and have a way of working around this for free.

It's just a question of how they do it.

RG2015 said:

Sorry if this is a bit simplistic, but I try to keep things simple.

My spreadsheet shows payments to my own accounts as transfers so my balance remains positive.

Sorry, I'm a visual learner. Care to show what you're talking about?


Just trying to think of ways to resolve this, I suppose what I could do is:

Income -minus- fixed -minus- variable = £saved.

But then create in another cell and have that £saved amount -minus- forgetitmoney such as SIPP. This would leave me with a figure of CASH that has been saved to do with as I wish. 

Bit of a messy way of doing it, but it'll show me total what has been saved and give a split between that and what I need to say goodbye to for the next 20 or so years.


I don't believe it's the best way of doing it but it is A way and I'm certainly open to suggestions of better ways.

That are free

masonic

I think you just need to have a separate 'assets' category for non-accessible things like long term investments. As RG2015 says, movements of funds to 'assets' should by subtracted from 'savings', but not added to 'expenses'.

Zanderman

I'm no spreadsheet expert but the problem you're describing doesn't need to occur. You just have to design your spreadsheet(s) to tackle it. That's what spreadsheets do - tackle complex interlinked numerical data.  And they're very good at it!  You can make them as simple or as complicated as you like. Yours may be too simple in structure?  Google sheets may not suffice for this, I don't ever use them.

You should be logging your 'forgetitmoney' into a sheet of its own. Fed with info from the relevant cells in your main sheet.

And have another sheet of its own that draws balance info from both that sheet and your main budgeting sheet to show how much you have left - three columns, one showing the spending money left, one the 'forgetitmoney' balance and one the sum of the two.

Dynamic (live) links between the relevant cells in each sheet will keep that third sheet updated with the info you need.  

(Edited to add - the main value of separate sheets, if you're wondering why not just do it all within one, is that they are visually much easier to read than just making it all on one huge sheet. They are all interlinked but each one has a specific function to display a particular set of figures.)

B0bbyEwing

Just had a discussion with the wife over it as generally in the past she's let me do everything myself & has very little input as she's not really "in to" this.

However with the cost of energy spiking (which is what has actually sparked the move to do this) we have taken the decision to go from freestyling it (which isn't a great way to save but with our habits it has generally always resulted in some kind of saving at the end of 12 months - sometimes small, sometimes decent) and move towards a more managed approach to budgeting where we set actual figures to actual categories & tell ourselves no if a figure has been hit.

Anyway, on that note & with the issue at hand, my wife has suggested / requested we have not one "total saved this month" but two. 

1) Total saved this month inc. pension contribs
2) Total saved this month exc. pension contribs

As we don't want to feel crap seeing low numbers saved when in actual fact we will have saved more than that - it's just allocated to the pension.
But we also don't want to be wrongly optimistic thinking we have a wedge of spare cash when a section of it wont be seen for 20 years, even if it does stay in our name throughout.


Thanks for the suggestion Zanderman regards additional sheets. It wasn't something I was wanting to do but if it solves the problem then it might be something I'm going to have to do.

What I'm cautious about is jumping in both feet, spending plenty of time on it, finding out it doesn't work & having wasted a load of time - that's why I'm here.

But looking like we can make progress on this now. Thanks.

RG2015

RG2015 said:

Sorry if this is a bit simplistic, but I try to keep things simple.

My spreadsheet shows payments to my own accounts as transfers so my balance remains positive.

B0bbyEwing said:

Sorry, I'm a visual learner. Care to show what you're talking about?

* It's no good saying I have £1000 left over at the end of the month when £900 of that is between SIPPs & regular savers and I actually only have £100 to call on.
* But at the same time I'm not sure I want to be saying we've only saved £100 as technically that wont be true - we managed to put £1000 aside in various accounts.

I think I said in an earlier post that I cannot use someone else's spreadsheet as we all think differently. Equally, I don't think mine would work for you.

I don't think I can show you how to record/monitor/present this as it really is down to what works for you.

It sounds like you know you only have £100 to call on and also that you saved £1,000 during the month.

When I started, a spreadsheet was a piece of lined paper, A3 folded in two with lots of rows and columns.

I then used a pencil to fill in the figures and a calculator to add them up. (no, not quite a quill pen and an abacus).

I was like Mr Toad when I saw my first computer spreadsheet, Supercalc, if I recall correctly.

Jami74

RG2015 said:

My spreadsheet shows payments to my own accounts as transfers so my balance remains positive.

In the beginning I got myself into lots of debt on my spreadsheet when I still actually had money in my accounts. I did try to use a Google sheets template that I found online and initially it went well but then I got into similar troubles with it.

I gave up trying to work out how much I had 'saved' each month but rather worked on the increase (or decrease) in my overall balances each month. Here is an example of how I keep track of my balances (not my actual accounts or figures)

RG2015

@Jami74

I love it!

Basic, but it tells you what you want to know. And your use of colour is fun but also informative.

Nebulous2

Jami74 said:

RG2015 said:

My spreadsheet shows payments to my own accounts as transfers so my balance remains positive.

In the beginning I got myself into lots of debt on my spreadsheet when I still actually had money in my accounts. I did try to use a Google sheets template that I found online and initially it went well but then I got into similar troubles with it.

I gave up trying to work out how much I had 'saved' each month but rather worked on the increase (or decrease) in my overall balances each month. Here is an example of how I keep track of my balances (not my actual accounts or figures)

That is very similar to my cash sheet. I don't have a 'change column.' 

We don't have a need to save, as we are in a drawdown situation, although as I'm working part-time I'm putting £500 (£400 net)  into a SIPP. 

I have two sheets, a ready cash sheet which tallies all my money across 6 current accounts, 4 credit cards and several regular savers. My wife isn't really onboard with that project, so I leave her to her own devices. We run separate accounts, with different payments we take responsibility for, so it is consistent.  I enter that every month, on the first of the month and expect to at least understand any reasons why the balance is less than the previous month, if it is. There can be some anomalies - generally the £400 DD to my SIPP has gone out, but occasionally for weekends it still has to go, so I have £400 more than I should have. 

Then we have a long-term capital / investment sheet. We split a capital sum 50/50 and invested half each with the other half in near-cash (premium bonds) for capital expenditure. That sheet includes our SIPPs.  So every month I have a total available for spending / bills for myself and a total capital / investment for both of us. 

We drew on the premium bonds recently, for the first time. I had enough money in my cash accounts to do what I wanted / needed, but didn't want to raid my regular savers, as they are paying more than the premium bonds.