My Personal Template for Tracking Net Worth
This post is going to cover the actual template that I use to track our net worth. You'll know from my previous posts that I prefer to track our net worth manually, mainly because it gives me full flexibility in what is actually being tracked. To start things off, the actual template that I'll be walking you through is right HERE.
That link gives you access to view the template, though you can't make any edits in that particular file. You'll have to either download the template or make a copy of your own, which means you'll get to keep it anyway :)
Did you get the template downloaded? Good! The last caveat that I will place here, is that I am an analyst by trade, so I am going to do my best to speak in general terms, but I am also going to explain things with the assumption that the reader likely has a base understanding of Excel or Google Sheets. If you get stuck with any of my explanations, then please reach out to me, I'm happy to teach!
OK, let's start with the first, and arguably most important tab, "Liabilities", I have this broken out into three categories, and frankly, these categories are carried over from before Mrs Money Smith and I got married. This dates back to when we still had separate accounts for everything, but I was still responsible for knowing what her bills actually looked like. (We decided relatively early in our relationship that being open about money was important, which eventually lead to me taking care of almost all of the financial related information). Each category is essentially a copy of the one above it, and breaks out our loans based on who's name they are in.
For sake of simplicity, I will speak to the first section (A2:G11), which applies to section A14:G22 and A25:F30 as well. Similarly to Mint.com or PersonalCapital.com, there will be a lot more upfront work when completing this sheet than you might want. My answer to that is to take the hour or so seriously, as this will help you to shed some light on where your money actually goes and what it truly costs you.
Pro Tip: If you're not sure where all of your accounts are, I recommend signing up for a Credit Karma account. There are a ton of benefits to having one, namely it will tell you everything that is reported to your name, and best of all - it's free!
Let's walk through, at a high level, how you'll want to use this sheet. In order to fill this out, you'll want to fill in columns A through F. (G is automatically calculated for you). There are really only four main pieces of information that you'll need to acquire to make this sheet the most helpful - company info, amount owed, and interest rate.
Starting from left to right, we have:
Column A "Reason" *Not Necessary* - This column should be filled out to explain what you actually used the loan for.
Column B "Company" - This is where you'll want to fill in the name of who you're actually paying the money too.
Column C "User Name / PW" *Not Necessary* - I've put in dummy user names and passwords for this column, the reason that this isn't necessary is that you should always be vigilant about what information your storing on the internet or even your personal computer. If you do decide to keep your passwords on this sheet, I definitely recommend not storing the entirety of it, for example, if your password was MrMoneySmithIsMyHero, you could store is at "Mr...ro", that way you have enough of a hint to remember your password, but you're also not putting out the entire thing.
Column D "Amount Owed" - This is the outstanding balance on your loan. Some loans might show what the current balance is, others might show what the payoff amount is. It doesn't necessarily matter which amount you track, but I strongly suggest that you stay consistent for each loan.
Column E "Interest Rate" - You might remember from my previous posts, that I prefer to pay off debt that has the highest interest rates first. This is arguably the most important column with how I use this tab. (You'll notice that the formula's in column G are based on these numbers here).
Column F "Minimum Payment" *Not Necessary* - I realize that not everyone can focus on paying down the highest interest rate first, some people are in a situation where they need to pay off the highest or lowest sized loan first to get the outcome they are looking for. This column would be very useful for people who are more focused on a different approach to paying down debt than the highest interest rate first.
Column G "Pay Off Order" - This column is automatically calculated based on column E, it lists out the loan to focus on, based on interest rates. The higher the interest rate, as compared to the rest of your loans, the higher up in the pay off order it will appear!
Other Handy Calculations
In row 11, you'll notice a few formulas as well-
The calculations in D11 and E11 are simple sums of all of the other amounts above them, useful to see your total amount owed and total minimum payment in one spot.
The interest rate in cell E11 is the weighted average of all the other interest rates, this helps to understand the bigger picture of your debt. As you pay down loans with higher interest rates, that average number will fall as well. It always releases some dopamine for me when I see these numbers get smaller!
Located in cells D33 through D35 you'll see a few more calculations, these are also just simple sums to use when pulling data over to the Net Worth tab that we'll talk about later.
The Assets tab is going to feel a lot simpler within this worksheet, because there's not a whole lot to maintaining it. I really currently just use this tab to manually input where the numbers happen to fall on the 1st of every month.
The one handy calculation that you can use from this tab is in rows 10 and 11- You can use this to calculate how much left until your current savings target, (in this instance $85,000). I've also put some other simple calculations in rows 15 and 16 to show how you can calculate a total amount, like having two separate 401k accounts.
I think that after reading about the first tab, the rest of this tab should seem pretty simple, but feel free to comment below or shoot me an email if you have any questions!
Net Worth Tab
This is the magic in which the other two tabs come together! (For an example of what it would look like as it's filled out, feel free to jump ahead to the "Net Worth Example" tab.
Essentially, all you have to do is copy the dollar amounts from the appropriate tabs over to this one, and everything will be calculated for you. The totals and change calculations throughout the sheet are already copied in for you. Each of the cells also has conditional formatting baked into it that will change the colors of the text based on the previous months. For example, as your asset numbers go up, they will turn green (green's obviously a good color, right?), and if your liabilities were to increase, they would turn red.
The only not-so-obvious piece on this tab is the credit score section in rows 32 through 35. These are numbers that I'm able to track through CreditKarma.com (mentioned above). I highly recommend tracking your credit score each month as you track your net worth, because it has opened my eyes to what actually affects those scores. I'll do a write up on how to utilize CreditKarma more in depth in a later post.
The only change you'll have to make to this tab is to set the dates in rows 2 and 3 to apply to when you actually start tracking. I recommend tracking these numbers once a month and to stick to that cadence. The reason I recommend that is that most bills are due monthly, and most companies report monthly, so you can see the correlation of your credit score and your net worth.
I know this was a lot to take in, and that for people who aren't necessarily one's to live in spreadsheets like I do, that this can seem overwhelming. I'm committed to educating anyone who is interested in learning more about how to use this sheet for themselves personally, so please don't hesitate to reach out or post in the comments below.