Prompted by some comments, I’ve decided to lay out the sums I use to calculate my net worth each month along with a copy of my Beast Budget spreadsheet. Some bloggers will notice elements stolen from their own spreadsheets – it’s very much a mutant offspring!
My spreadsheet actually calculates two different net worth values; a current net worth and a month end value. The month end value is the sum I report. It’s a pretty theoretical figure really, a sort of “if I had to liquidate everything now back to the banks where would I stand”.
The first page of my spreadsheet is the Dashboard. The net worth figure shown here is the sum of all assets and liabilities on the day viewed (using the TODAY function of excel plus lookup tables). The buttons on this page hyperlink to the net worth tracking page and the summary assets and liabilities pages.
The net worth tracking page (‘NW Track’) gives a heads-up of every account and it’s change over the year. Beige boxes need to be filled by hand, whilst grey boxes autopopulate. The first table tracks the month to-date value in each account using a mixture of links and lookup functions. It will then calculate your net worth as:
Net worth = (property value – outstanding mortgage) + (all savings accounts) + (all investments) + (all bank accounts) + (pension cashout value) – (student loan) – (all credit cards) – (all other loans/ debts)
For my own net worth I halve my property value and outstanding mortgage, as it’s jointly owned between us. The table will also calculate your net worth without your equity or student loan.
Table two tracks absolute net worth increases, percentage increases and savings rate (derived from table three). Enter your previous net worth in the equation for January to show the increase.
Table three calculates your savings percentage. It will calculate your take home income vs expenses amount using the figures for your primary bank account. The savings percentage calculation is (total saved) divided by (your take home income + your pension contributions). Table four is a countdown to FI calculator which I pinched from another FIRE blogger. TFS I think? It’s adapted to work with the rest of my spreadsheet.
The third page/ sheet is the Assets dashboard. This summarises the state of all your accounts on that day. Where pages exist for the accounts they’ll autopopulate, otherwise have a play about. The Liabilities dashboard does the same thing for accounts holding debts later on.
The following two pages are sample bank accounts. Your primary bank account should be the one your wages go into (although the NW calculator will pull income data from all of them). I think this was originally an excel template which I’ve modified. Enter your expenses and income as they come in.
The savings account page and credit card pages are very simple running totals that you manually input information to. Remember to make all credit card purchases negative. I added a graph to the credit card page because I like pretty pictures. Following the savings account is the investment page, which at the moment is really simple. I’m working on a separate investment workbook that includes live pricing, which I use to update this.
The last few pages are very similar. Both the student loan and mortgage calculator consist of a summary page and an amortisation page. Enter the values in the first five grey boxes of the inputs section on the summary page and it will do the rest, producing monthly figures and an amortisation table. I’m most proud of the mortgage amortisation (as an excel novice). If you overpay one month, enter the new figure for the appropriate months payment in the amortisation schedule page and it will automatically recalculate all future payments and duration. You can get the student loans calculator to work out how much you pay monthly based on the sum below for Plan 1, or alter it for Plan 2. I tend to just put the payment values in by hand on the amortisation for my student loan, because they change so much and don’t fit a standard loan pattern.
Student loan monthly payment = ((yearly salary /12) – (Plan threshold)) * 0.09
Where (Plan threshold) is for Plan 1 £1,577 and for Plan 2 is £2,143. The percentage increases to 15% (0.15) if you also had a Postgraduate loan.
The link to the google sheets version is here:
Cheers for reading,
The Shrink
Hello The Shrink,
Thank you for sharing your spreadsheet. I have tried to download it as well as making a copy first but when I open it in excel I get a warning about circular references which cannot be listed and some cells and graphs do not work. Is there a chance for you to share directly the xlsx file without it being via google sheets? Thank you
LikeLike