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:

Debt Calculator 1

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:

Debt Calculator 2

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)

Debt Calculator 3

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:

Debt Calculaor 4

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:

Debt Calculator 5

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

Debt Calculator 6

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.

Debt Calculator 7

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

Good luck!

If you enjoyed reading this, sign up for free updates!

Loading Disqus Comments ...
Loading Facebook Comments ...
  1. Tyler says:

    Thanks for writing an entire article in response to my post! I didn’t expect that. Thanks again! This is a great way to organize/chart my progress!

  2. How do you calculate the percent change in net worth if it’s negative? Do you add or subtract and then divide?

  3. Trent Trent says:

    If your net worth is negative, you reverse the numbers being subtracted. Instead of =(C9-B9)/B9 … you go =(B9-C9)/B9

    During the month your net worth goes from negative to positive, though, the percent change calculation does not work.

  4. Pride says:

    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.

  5. Rebecca says:

    I find these spreadsheet “how-tos” extremely helpful. I hope you continue this series- thank you!

  6. Justin says:

    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?

    Thanks

  7. Trent Trent says:

    Your house should be an asset, but your remaining mortgage should be a debt.

  8. Mircat says:

    WOW–thanks…just stumbled across this article, and made my own spread sheet. For someone (like moi!) who knows next to nothing about Excel, this article is a godsend!

  9. Mark says:

    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.

  10. Shelby says:

    I’m not sure how to figure my mortgage balance as a debt. I don’t know how much I owe. Other than calling the bank each month, how do you calculate that?

    I’ve been reading your site for several weeks now and there are some great tools for staying motivated. Thanks!

  11. Valerie says:

    These articles are great and very easy to read especially for a none financial person. Thanks!

  12. Matt says:

    @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?

  13. Jerry says:

    @Matt, in his reply to Shelby.

    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

  14. Ian says:

    @ Trent, Comment #3

    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.

  15. Bill in Houston says:

    Thanks to my student loans of (currently) $43,000 I still have a negative net worth, but that should flip by February 2010.

  16. Sharon says:

    Should retirement savings (ROTH IRA, regular IRA, 401K, etc.) be counted as assets? I’m especially curious about the ROTH as that was created with after tax money.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>