How to calculate net worth in Excel

Step 1: One row per account, one column per month

Net worth is what you own minus what you owe. The sheet that tracks it is simple: one row per account, a Type column that says Asset or Debt, a Category for grouping, and a balance column for each month-end. This one has checking, savings, a workplace retirement account, a car, a credit card, a car loan and a student loan.

Enter debts as positive numbers, the amount you owe, and let the Type column decide the sign. Mixing positive and negative debts is the most common reason a net worth sheet is wrong.

For things without a statement, like a car or a home, use an estimate and the same method every month. The aim is to see the trend, not to get an appraisal.

Step 2: Net worth with SUMIF

SUMIF adds the balances whose Type is Asset, and a second SUMIF adds the debts. For November that's 37,520 in assets and 22,640 in debts.

Lock the Type column with $ before you copy the formula left for the other months: =SUMIF($B$2:$B$8,"Asset",D2:D8)-SUMIF($B$2:$B$8,"Debt",D2:D8) gives 13,160 for September and 13,405 for October.

Don't just SUM the column. =SUM(F2:F8) returns 60,160, which adds the debts instead of taking them away.

A one-function version with SUMPRODUCT

If you prefer one function, give each row a sign. (B2:B8="Asset") is 1 for assets and (B2:B8="Debt") is 1 for debts, so subtracting them gives +1 or -1. SUMPRODUCT multiplies each balance by its sign and adds the lot.

It returns the same 14,880. A row with a typo in Type, like "Assets", gets a sign of 0 and drops out of both versions. Check with =COUNTA(B2:B8)-COUNTIF(B2:B8,"Asset")-COUNTIF(B2:B8,"Debt"), which should be 0.

Step 3: Change and % change month over month

Put each month's net worth in a summary row, say D10:F10, then subtract the previous month: =F10-E10. Here November is 1,475 higher than October.

For a percent, divide the change by the previous month's value. Use ABS on the bottom when net worth can be negative. Going from -2,000 to -1,000 is an improvement, but without ABS the formula shows -50%.

Month-to-month moves include market swings in investment accounts and the drop in a car's estimated value. That's why a few months of history tell you more than any single change.

Debt-to-asset ratio and other views

The debt-to-asset ratio shows what share of what you own is matched by what you owe. In November debts are 60.3% of assets, down from 63.9% in September. A falling ratio means debts shrink faster than assets, or assets grow faster than debts.

SUMIFS on the Category column answers other questions. Net worth without the car, which mostly loses value, is =SUMIFS(F2:F8,B2:B8,"Asset",C2:C8,"<>Vehicle")-SUMIF(B2:B8,"Debt",F2:F8): 5,380. Loans paid down since September is =SUMIF(C2:C8,"Loan",D2:D8)-SUMIF(C2:C8,"Loan",F2:F8): 820.

For a chart, select the month headers and the summary row, then Insert > Insert Line or Area Chart > Line. A line shows the trend better than columns when you have a year of months.