Upon request from a reader, and building upon my February net worth calculations and my recent postings on building your own mortgage calculator, I thought I would discuss how I built a simple net worth calculator that lets me quickly calculate my net worth each month. You can build this for yourself at home - and you don't even need to spend the money on Microsoft Excel (unless you want to - it's a fine spreadsheet, but there are pretty good free options)!
Step 1: Get a spreadsheet
If you already have a spreadsheet program, you can skip this step. If not, I highly recommend downloading OpenOffice, an open source version of Office that contains a very nice spreadsheet, Calc. I'll be using that program for this description, but everything I show in this tutorial works exactly the same with Microsoft Excel.
Step 2: Make a list of your assets and debts
Make a list of your assets (meaning your open accounts, retirement savings, and your major assets, like your home and automobiles) and also a list of all of your debts. We're not worried about account balances yet, we just want something to start building the calculator with.
Step 3: Fire up the spreadsheet and add these lists to it
Open up your spreadsheet and in cell A1, write Net Worth Calculator in bold, then in A3 write Assets in bold. Below that, make a list of your assets starting in A4. After your assets, put in an entry that says Total Assets in bold, then in the cell below that, put in Percent Change in bold. Skip a line and repeat the entire thing, except with debts. Below all of the debts, skip a line, then enter two more things in bold: Net Worth and below that Percent Change. When you're done, it should look like this:
Step 4: In the B column, enter each of the amounts
Next to the word Assets, in the B column, put in today's date, then next to asset and debt, enter the dollar (or whatever currency you use) amount. When you're done, your worksheet should have progressed to this point:
Step 5: Total up your assets
Next to the word Asset Subtotal, you'll need to enter a formula to automatically calculate the sum of all of your assets. You'll need the cell identifier of the first asset (B5) and the last one (B??, depending on how many assets you have). In the example here, my first asset is in B5 and my last one is in B7, so I enter =SUM(B5:B7)
If your last asset value was in, say, B14, then you should enter =SUM(B5:B14) in there instead.
Step 6: Total up your debts
Here, we do the same thing totaling the debts, next to the Debt Subtotal. See here:
Step 7: Wait a month
If you want, you can calculate your net worth now (asset subtotal minus debt subtotal or, in the example above, =B9-B16 ), but I generally found it didn't really mean anything until I had a second month worth of data.
Step 8: Enter another month of data
If you add a new asset, all you have to do is click on the row above where you want the new asset or debt to go, go to the Insert menu, and choose to add a row. The automatic calculations will update for you. Even niftier, you can click on the previous month's total, click on that black square in the lower right, and drag that little black square over, as shown below, to automatically do the totaling for you:
Step 9: Calculate the percent change
Below the current month's Asset Subtotal, across from the Percentage Change label, you're now ready to calculate the percentage change. Let's say the previous month's subtotal is in cell B11 and the new subtotal is in C11; then in C12 you would enter =(C11-B11)/B11
Step 10: Finish it out
Now it's just repetition. You can calculate the percentage change in debt and net worth in almost exactly the same way as the change in assets.
And there you have it! Each month, just add in the new numbers, then drag over the six calculations from the previous month (total debts, total assets, net worth, and the three percentage changes). You can use this to set goals for yourself for the coming month. I usually find that setting percentage goals works better for me than raw number goals. A good "starter" goal is to have a negative change in debt along with a positive change in assets (meaning you paid off a debt and had more money in your checking at the end of the month than last month).