Help with financial documents


#1

Hi there fellow mustachians,

I am trying to make my very basic spreadsheets a bit more sophisticated and find myself struggling…any advice is much apreciated!

So what I most would like to see is the monthly savings rate in % but the problem is that when I write down the actuals for e.g. dec 2017, it does not show the entire picture. Like in December I transfered a certain amount from my tax savings buffer to my transaction account to pay the tax invoice in January, so december is way too much on the + side and january on minus… Not sure if any of this makes sense :blush: Does it even out after a couple of months? but then the savings rate wouldn’t be accurate or?

My main tracking sheets rigth now are the following:

Monthly budget with some (pretty accurate) forecasts of the monthly expenses. 1. income, 2. savings & investments and 3. expenses. I try to do a 0 CHF budget where everything is allocated for each month in advance. At the moment I do not follow up and add the actuals here after each month.

Asset overview. This is where I track the number of posts etc. for each asset category and specific “line item” e.g. VWRL, normally once every quarter when I buy ETFs. I also have the “liquid” value of our house that is in my name etc. a small cash buffer for emergencies etc. Every year or so I calculate the value of the portfolio.

I’m thinking that keeping better track of the exact savings rate would motivate me a little more each month to save more :blush:

What would you recommend that is fairly straightforward to prepare?

Thank you!


#2

Hi @mrs_oberland,

What you are trying to do is to produce documents called “Income Statement” (measuring how much you earn/spend) and “Balance Sheet” (measuring your net worth, i.e listing all of your assets and substracting your debt, if any).

I do my own as well, and for both I use a google spreadsheet.
The balance sheet is easier, since you just have to list the balance of all your accounts. For ETFs, it can be handy to use the GOOGLEFINANCE(…) function that will automatically retrieve the value of the fund/etf.

For the Income Statement, I have two approaches :
1 - List absolutely all the expenses I have made, using the method explained in this Early Retirement Extreme article. This helps me to see what is my retirement number and if my number is still in line with my expenses.

2 - Then I have a google spreadsheet representing my Income Statement. There are two parts : one for each month and one for the whole year ( the whole year is simply adding/averaging the sum of each month).
In each part, I will note on top my total revenue for the month, then substract tax at source/first pillar, and add employer contribution for second pillar. then, thanks to my tool described in (1), I would add all of my expenses grouped by category. What is remaining are the earnings for the month. From there you can calculate your saving rate pre and post tax.

I see what you mean. Although I am taxed at source, I have kind of the same problem with insurances that are often all paid in january.
For this issue you have two solutions :

  • either don’t do anything about it, and wait for the end of year to smooth everything. That is what I do, however I acknowledge that usually starting the year with that many expenses is not very motivating.
  • Or you can estimate how much you are going to pay this year for taxes/insurances (let’s say you estimate that you will pay a total of 12’000 CHF for the year, that makes 1000 CHF per month). Then each month, you book a “virtual” expense of 1000 CHF. At the end of the year you compare your estimation with what really happened, and make corrections if necessary. In accounting this is called the provisions and deprovisions mechanism.

Hope this helps, feel free to ask more questions if needed :slight_smile:


#3

Thanks @Julianek for your reply!

ok so for the monthly balance sheet, do you calculate the average monthly expenditure per category? if yes, how many months (years?) of data do you think would give an accurate number? (given the lifestyle hasn’t changed much) and would you mind sharing your categories?

I will book virtual expenses :smiley:

Thanks!


#4

You mean the monthly Income Statement? :slight_smile:
Sure, here is a sample for January/February of last year :


Just continue to the right to add following months.
In my experience, the category “Diverse” ends with too many stuffs in it. For 2018 I plan to add new categories like travel, gifts, and a children category.

You notice that with this format it is easy to know how much you save, how much you spend and how much you earn…

Then, with this data i can calculate the income statement for the year, which looks like this (this was my update for November 2017)


#5

aha yes! this is very helpful, thank you!