Building Your Own Monthly Net Worth Calculator Using A Spreadsheet

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).

The problem with your example is the use of OpenOffice Calc. We all know that that program can’t do basic math.

Since I’m a financial wizard, if your percent change in net worth is negative, you should report it as negative. Or if you want to delude yourself and want to see change as always positive, use the ABSOLUTE function on the equation.

Hi Trent, Like the site and the excel walkthroughs. I was looking at this and wondering where should I place my house?
I mean it’s not an asset as I still owe money on my mortgage, but it’s not just a liability as I could sell it.
Should I place the worth as an asset and mortgage as a liability? Should I try and assess the price every month?

I came to this page from your article on Quicken and MS Money (and how they are overkill)
I use Quicken to do my taxes and rapidly report information so I can do my tax forms for a small business.
I don’t see how a simple net worth worksheet does any of that.

@Shelby:
Your debt remaining on your mortgage will actually be on your mortgage statement (either electronically or physically, depending how you get your statement these days!). This is required by law, so you shouldn’t have a problem getting that.

The one problem I have is treating my house as an asset. What do you declare its value as from month to month, especially with the wicked deflation from the houseing market recently (at least in my metro-Detroit area!). Do I just leave it as the value I purchased it at, or base it every 6 months on the State Equalized Value that it shows on my property taxes, or what?

For the purposes of tracking net worth, it is most important to select (a) an easy method that is (b) objective and (c) somewhat approximately accurate. It has to be easy because you will be doing this monthly. It has to be objective, because you’re working with numbers, not what someone feels it “should be worth”. Why it has to be accurate, well, that’s obvious. Just don’t get hung up on absolute accuracy down to the penny. For most home owners, +/- $1,000 or even +/- $5,000 is only 1% or a fraction thereof. “Close enough” is fine As Long As you are consistent. I use Zillow (http://www.zillow.com). It is free, easy, objective, and somewhat nears reality. You can check Zillow’s estimate for your home’s value as often as you want to calculate your net worth.

Even better, you can use this estimate as a reminder to update the home replacement cost in your homeowner’s insurance policy every few years. Tracking your home’s value also lets you know if your property tax appraisal is way off and needs to be challenged.

How one assigns the exact monetary value to a home is a difficult question. Why else have an obscenely costly realty and appraisal industry, after all? How much a home is worth can only be decided, ultimately, during its’ sale to a willing buyer. Okay, enough philosophical musing. The precise value of your home is not important to your net worth calculation unless you are a buyer or a seller at this very moment.

Some financial managers don’t even care what your home is worth. They use “investable assets”: net worth excluding your primary residence. I cannot sell a bedroom if I need to invest some money or pay off a bill, after all. A house is not a liquid asset, and it’s all or nothing. For the reasons stated above (insurance and property tax appraisal), I prefer to track my home’s value. Besides, it makes my net worth look bigger, so I feel better. :) I just don’t obsess about it.
regards,
Jerry

A better way to calculate percent change of net worth when going from negative to positive is to do =(C9-B9)/B9*SIGN(C9) which will always give the result you want without having to change the formula.

Books worth budgeting for

My new book, The Simple Dollar: How One Man Wiped Out His Debts and Achieved the Life of His Dreams, is available in bookstores now. Check out some of the life-changing experiences the book has given readers!

Check out my book, 365 Ways to Live Cheap, available in bookstores everywhere! It's filled with 365 great tactics you can apply to your personal finances, from frugal tips to great ideas for managing your money.