Tabellab

Client Net Worth Statement: Assets, Liabilities and Debt Ratio

Lesson 1 of 20Lab

All lessons
  1. Client Net Worth Statement: Assets, Liabilities and Debt RatioLabFree
  2. Cash-Flow Review: Income, Spending, Saving and Savings RateLab Locked
  3. Goal Funding Gap: Future Cost, FV of Savings and the Monthly PMTLab Locked
  4. Retirement Projection: Contributions, Growth and Today’s MoneyLab Locked
  5. Withdrawal Sustainability: How Many Years the Money Lasts (NPER)Lab Locked
  6. Risk Profile Score: Weighted Questionnaire and Model PortfolioLab Locked
  7. Asset Allocation vs Model: Weights, Drift and Rebalancing TradesLab Locked
  8. Advisory Fee Tiers: Breakpoint Rate, Minimum Fee and Quarterly BillsLab Locked
  9. Fee Impact over 20 Years: Showing a Client What Costs DoLab Locked
  10. Insurance Needs Estimate: Simple Income ReplacementLab Locked
  11. Client Book Summary: Households, Assets and Fees by SegmentLab Locked
  12. Client Review One-Pager: Changes, Highlights and a ChartLab Locked
  13. Debt Payoff Check: Months to Pay Off, Total Interest and Extra PaymentsLab Locked
  14. Emergency Fund Check: Months Covered, Target and Rebuild PlanLab Locked
  15. Goal Priorities: Sharing One Monthly Budget Across Several GoalsLab Locked
  16. Retirement Scenarios: Low, Base and High Returns and the Contribution NeededLab Locked
  17. Drawdown with Inflation: Rising Withdrawals Year by YearLab Locked
  18. Retiree Cash Planning: Portfolio Income, What to Sell and the Cash ReserveLab Locked
  19. Review Calendar: Next Review Dates, Overdue Clients and This Quarter’s WorkloadLab Locked
  20. Final Project: The Hartwells’ Annual Planning DashboardLab Locked

You are not signed in — the lab works, but your progress is not saved. Sign up

Lesson 1 of 20 is free. Unlock all 20 hands-on labs, the final exam and your certificate.

Buy course — $9.90
Read first

In Excel

  • Format the ratio cell as a percentage (Home → %).
  • Keep the date of the statement in the sheet name, e.g. "NW 2026-10", and copy the sheet for the next review.
Loading the spreadsheet…

The first page of every client file

Before a financial advisor talks about portfolios, retirement dates or insurance, there is one page that has to be right: the net worth statement. It is the snapshot of everything a household owns and everything it owes on a given date. New clients usually arrive with a mix of bank statements, a pension letter, a brokerage login and a vague memory of what is left on the mortgage. Your job in the first meeting is to turn that pile into one clean, repeatable table that you can update at every annual review and that the client can understand in thirty seconds.

This lesson is for financial advisors, paraplanners, planning assistants and client service staff who build or check client reviews in Excel, and for anyone studying for a planning role who wants a practical start. You work with a fictional household, Daniel and Priya Hartwell, clients of the fictional Juniper Lane Advisory. In the lab above you turn their ten accounts and debts into totals, a net worth figure and a debt-to-asset ratio. The same four formulas will carry you through every review you prepare, whether the client has ten lines or sixty.

Everything here is an Excel exercise with invented clients and numbers. It is not financial advice, and the ratios are descriptive tools, not recommendations.

The core idea: one list, one tag, two totals

A net worth statement rests on a single identity: net worth = total assets − total liabilities. Assets are things the household owns that have a value: cash, investment and retirement accounts, the home, a car. Liabilities are debts: the mortgage, a car loan, the balance on a credit card. If the Hartwells sold everything at today's values and paid off every debt, net worth is roughly what would be left (before selling costs and taxes, which a simple statement ignores).

Many spreadsheets put assets in one block and liabilities in another, each with its own SUM. That works until a new account appears and someone adds it below the SUM range, and the total silently stays the same. A sturdier layout is a single list with three columns: item, type and value. Every line gets a tag, Asset or Liability, and you let Excel add the lines that share a tag. Adding an account is then one new row with the right tag, and the formulas stay correct as long as the row sits inside the range.

The function that adds by tag is SUMIF. Its pattern is =SUMIF(range, criteria, sum_range): look down range, and wherever a cell matches criteria, add the value from the same row in sum_range. For the Hartwells the total of all assets is =SUMIF(B2:B11,"Asset",C2:C11). Change the word to "Liability" and you have the second total. Neither formula cares about the order of the rows, so you can sort the list by size or by owner without breaking anything.

The last number is the debt-to-asset ratio: total liabilities divided by total assets. It answers a simple question: what share of what this household owns is financed by debt? A ratio of 30% means that for every dollar of assets, thirty cents are owed to someone. Advisors watch how this number moves from review to review. A falling ratio usually means the mortgage is being paid down and investments are growing. A rising one is a reason to ask questions. There is no official "correct" level; what is reasonable depends on age, income and where the household is in its life.

Worked example: the Hartwell statement

Here is the list exactly as it appears in the lab. Column A holds the item, column B the tag, column C the value in dollars on the statement date.

Hartwell household, statement date October 2026 (fictional)
Item (A)Type (B)Value (C)
Checking & savingsAsset$28,500
Emergency fundAsset$24,000
Brokerage accountAsset$186,000
Retirement accounts (Daniel)Asset$212,000
Retirement accounts (Priya)Asset$143,000
HomeAsset$640,000
CarAsset$22,000
MortgageLiability$368,000
Car loanLiability$11,500
Credit cardLiability$3,200

With the list in place, four cells below it do the work:

The four summary formulas
CellLabelFormulaResult
C13Total assets=SUMIF(B2:B11,"Asset",C2:C11)$1,255,500
C14Total liabilities=SUMIF(B2:B11,"Liability",C2:C11)$382,700
C15Net worth=C13-C14$872,800
C16Debt-to-asset ratio=C14/C1330.5%

Check the arithmetic by hand once, because that is what you will do in front of a client. Assets: 28,500 + 24,000 + 186,000 + 212,000 + 143,000 + 640,000 + 22,000 = 1,255,500. Liabilities: 368,000 + 11,500 + 3,200 = 382,700. Net worth is 1,255,500 − 382,700 = 872,800, and the ratio is 382,700 ÷ 1,255,500 ≈ 0.305, shown as 30.5% once the cell is formatted as a percentage.

Two observations are typical of real client files. First, the home is about half of everything the Hartwells own (640,000 of 1,255,500). Net worth looks large, but much of it is not money they can spend without selling or borrowing against the house. Second, almost all the debt is the mortgage. The car loan and credit card are small in dollars, yet the credit card often carries the highest interest rate, so it is a natural talking point at the review even though it barely moves the ratio.

Hartwell household: every line of the statement0100k200k300k400k500k600k700kChecking & savings28.5kEmergency fund24kBrokerage account186kRetirement (Daniel)212kRetirement (Priya)143kHome640kCar22kMortgage368kCar loan11.5kCredit card3.2kAssetLiability
Each line of the Hartwell statement as a bar. The home and the mortgage dominate; the credit card balance is almost invisible at this scale.

A bar chart like the one above is a good second page for a client pack, because it shows the size of each line far better than a column of numbers. The chart below shows the identity behind the statement: the left column is what the household owns, the right column shows how it is financed, partly by lenders (liabilities) and partly by the household itself (net worth). Both columns are the same height by definition.

Assets = liabilities + net worth00.2m0.4m0.6m0.8m1.0m1.2m1.4m$1,255,500Liabilities $382,700Net worth $872,800Debt ratio 30.5%What they ownHow it is financed
Total assets of $1,255,500 split into liabilities ($382,700) and net worth ($872,800). The peach share of the right column is the debt-to-asset ratio, about 30.5%.

How to build it in Excel, step by step

  1. Open a new workbook and type the headers Item, Type and Value in A1:C1. Make them bold with Ctrl+B (Cmd+B on a Mac).
  2. Enter one row per account or debt from A2 down. Type the tag in column B exactly as Asset or Liability. To avoid typing errors, select B2:B11 and use Data → Data Validation → Allow: List with the source Asset,Liability, so the cell becomes a drop-down.
  3. Enter the values in column C as positive numbers, including debts. The tag, not the sign, tells Excel which total a line belongs to. Format the column with Home → Number → Currency or Ctrl+Shift+4.
  4. Leave an empty row, then type the labels Total assets, Total liabilities, Net worth and Debt-to-asset ratio in A13:A16.
  5. In C13 type =SUMIF(B2:B11,"Asset",C2:C11) and press Enter. In C14 type the same formula with "Liability".
  6. In C15 type =C13-C14. In C16 type =C14/C13 and format it as a percentage with Home → % or Ctrl+Shift+5. Add one decimal with Home → Increase Decimal.
  7. Give the net worth row a top border (Home → Borders → Top and Double Bottom Border) so the result stands out.
  8. Rename the sheet with the statement date, for example NW 2026-10 (double-click the sheet tab). At the next review, right-click the tab, choose Move or Copy → Create a copy, rename it and update only the values.
  9. Optional but useful: select A1:C11 and press Ctrl+T to make it an Excel Table. New rows typed directly below the table join it automatically, and you can write the totals with structured references such as =SUMIF(Table1[Type],"Asset",Table1[Value]), which never miss a new line.
If you prefer to type the criteria once, put the words Asset and Liability in cells (for example B13 and B14) and point SUMIF at them: =SUMIF($B$2:$B$11,B13,$C$2:$C$11). The dollar signs keep the ranges fixed when you copy the formula down one row.

Make it client-ready

A statement that is arithmetically right can still confuse a client. A few habits make it easier to read. Keep the date of the snapshot visible on the page, because values of investments and the home change constantly. Round to the nearest hundred or thousand for presentation, but keep the exact numbers in the workbook. Group retirement accounts by owner, as the Hartwell list does, because the accounts belong to individuals even when the household plans together. And show the previous review next to the current one as soon as you have two dates: the change in net worth is often more meaningful to the client than the level.

For the change column, put last year's values in column D and this year's in column C and use =C15-D15 for the dollar change and =C15/D15-1 for the percentage change. When the client asks why net worth went up, you can then point to the lines that moved: a market gain in the brokerage account, new contributions to retirement accounts, or simply a lower mortgage balance after twelve payments.

Excel for Financial Advisors course: client review spreadsheet with net worth, retirement projection and fee tables
The course follows one fictional client household from the first net worth statement to a finished review one-pager.

Common mistakes and how to spot them

  • Adding liabilities instead of subtracting them. =C13+C14 gives 1,638,200 for the Hartwells, more than they own. Sanity check: net worth can never be larger than total assets.
  • Typos in the tag. A line tagged Assets or Asset (with a trailing space) does not match "Asset" and silently drops out of the total. SUMIF ignores upper and lower case, but not extra letters or spaces. A data validation drop-down prevents this, and a check cell =COUNTA(B2:B11)-COUNTIF(B2:B11,"Asset")-COUNTIF(B2:B11,"Liability") should always show 0.
  • Entering debts as negative numbers. If the mortgage is typed as −368,000 and you also subtract total liabilities, the debt is effectively added back. Choose one convention (positive values plus a tag is the clearest) and stick to it.
  • Ranges that are too short. A new account typed in row 12 is outside B2:B11. Convert the list to a Table or extend the ranges, and compare the number of rows with the number of lines the client gave you.
  • Inverting the ratio. =C13/C14 returns about 3.28, which is assets per dollar of debt, not the debt-to-asset ratio. A debt-to-asset ratio above 100% is possible only when a household owes more than it owns.
  • Mixing dates. A brokerage value from today next to a pension value from last December makes the statement inconsistent. Note the valuation date of each line in a comment or an extra column.
  • Double counting. An emergency fund held inside the savings account, or a loan already netted off a property value, should appear once. Ask where each number comes from.

Quick reference: functions and shortcuts used

Function / shortcutWhat it doesExample from this lesson
SUMIFAdds the values whose row matches one condition=SUMIF(B2:B11,"Asset",C2:C11)
SUMIFSAdds with several conditions (sum range comes first)=SUMIFS(C2:C11,B2:B11,"Asset",A2:A11,"Retirement*")
COUNTIFCounts rows that match a condition, useful for checks=COUNTIF(B2:B11,"Liability")
SubtractionNet worth from the two totals=C13-C14
DivisionDebt-to-asset ratio=C14/C13
Ctrl+Shift+5Percentage formatC16 shows 30.5%
Ctrl+Shift+4Currency formatC2:C15
Ctrl+TTurns the list into an Excel Table that grows with new rowsA1:C11
Data → Data ValidationDrop-down list for the Type columnSource Asset,Liability

Real-world uses

  • Onboarding a new client. Fill the list from the fact-find form during the first meeting and show the result before the client leaves, so errors are caught while the documents are on the table.
  • Annual reviews. Copy last year's sheet, update the values and show the change in net worth and in the debt-to-asset ratio. Clients remember the trend, not the formula.
  • Mortgage or refinancing discussions. Before a client takes on a new loan, add it as a Liability line and show how the ratio changes. The impact of a decision is clearer when it appears in the same statement the client already knows.
  • Estate and succession conversations. Add an Owner column and use SUMIFS to show what each partner owns individually and jointly, which is often the starting point for discussions with lawyers.
  • Checking a colleague's file. When a planning assistant prepares the review, the check cell that counts untagged lines and the hand calculation of totals take a minute and catch the most common errors.

What you will build in the rest of this course

This first lab is free. The remaining nineteen labs are part of the paid course (a one-time $9.90) and follow the same Hartwell household, so every review figure builds on the one before it.

Cash-Flow Review: Income, Spending, Saving and Savings Rate. You turn the household's income and spending lines into a monthly and annual cash-flow summary and calculate the savings rate. It is the number that tells you whether the net worth you just built is likely to grow.

Goal Funding Gap: Future Cost, FV of Savings and the Monthly PMT. A university fund or a home renovation has a price in the future. You inflate today's cost, project what current savings will grow to with FV, and use PMT to find the monthly saving that closes the gap.

Retirement Projection: Contributions, Growth and Today's Money. You project the retirement accounts year by year with contributions and an assumed return, chart the result and convert the final balance back into today's money so the client sees a figure that means something.

Withdrawal Sustainability: How Many Years the Money Lasts (NPER). Turning the question around: given a pot and a yearly withdrawal, how many years does it last? NPER answers it in one formula, and you test how sensitive the answer is to the return and the spending level.

Risk Profile Score: Weighted Questionnaire and Model Portfolio. You score a risk questionnaire with SUMPRODUCT and weights, then map the score to a model portfolio with a lookup, which is how many advisory firms document suitability.

Asset Allocation vs Model: Weights, Drift and Rebalancing Trades. You compare current holdings with the model weights, measure the drift of each asset class and calculate the buy and sell amounts that bring the portfolio back to target.

Advisory Fee Tiers: Breakpoint Rate, Minimum Fee and Quarterly Bills. Tiered fee schedules are easy to get wrong. You build a breakpoint calculation, apply a minimum fee and split the annual fee into quarterly invoices.

Fee Impact over 20 Years: Showing a Client What Costs Do. You compare the growth of the same portfolio at two cost levels over twenty years and chart the gap, a transparent way to explain costs to a client.

Insurance Needs Estimate: Simple Income Replacement. A simple income-replacement estimate shows how much cover a household might need if one income stopped, built from the figures you already have.

Client Book Summary: Households, Assets and Fees by Segment. Zooming out from one client to the whole book, you summarise households, assets under advice and fee income by segment with COUNTIFS and SUMIFS.

Client Review One-Pager: Changes, Highlights and a Chart. Lab 12 pulls everything into a printable one-page review with the year's changes, key highlights and a chart, the document you would actually hand to Daniel and Priya.

Each lab is checked live in the browser, so you see immediately whether a formula is right, and the course ends with an exam and a certificate.

Summary

A net worth statement is one list with a type tag, two SUMIF totals, one subtraction and one division. For the Hartwells that gives total assets of $1,255,500, liabilities of $382,700, a net worth of $872,800 and a debt-to-asset ratio of about 30.5%. The layout is robust because every new account is just another tagged row, and the sheet can be copied for every future review. Now scroll up to the lab and build it yourself: the four formulas take a few minutes, and the checker tells you instantly whether each one is right.

Frequently asked questions

How do I calculate net worth in Excel?

List every asset and liability in one table with a Type column, add each group with SUMIF, for example =SUMIF(B2:B11,"Asset",C2:C11), and subtract total liabilities from total assets.

What is the debt-to-asset ratio?

It is total liabilities divided by total assets. It shows what share of what a household owns is financed by debt; in Excel it is simply =liabilities/assets formatted as a percentage.

Should liabilities be entered as negative numbers?

It works either way, but do not mix conventions. Entering all values as positive numbers and using a Type tag with SUMIF is the clearest and avoids accidentally adding debts back.

What is the difference between SUMIF and SUMIFS?

SUMIF adds values that meet one condition and takes the sum range last. SUMIFS can apply several conditions and takes the sum range first, for example =SUMIFS(C2:C11,B2:B11,"Asset",D2:D11,"Priya").

Why does my SUMIF total miss a line?

Usually the tag does not match exactly, for example a trailing space or a plural, or the new row is outside the range. A data validation list and an Excel Table prevent both problems.

Is the home included in net worth?

In a standard net worth statement the home is an asset and the mortgage a liability. Many advisors also show net worth excluding the home, because home equity cannot be spent without selling or borrowing.

Keep going: Excel for Financial Advisors: Client Reviews, Retirement Projections & Fees

This free lab is lesson 1 of 20. Unlock the other 19 hands-on labs, the final exam and your certificate for a one-time $9.90.

  1. Cash-Flow Review: Income, Spending, Saving and Savings Rate
  2. Goal Funding Gap: Future Cost, FV of Savings and the Monthly PMT
  3. Retirement Projection: Contributions, Growth and Today’s Money
  4. Withdrawal Sustainability: How Many Years the Money Lasts (NPER)
  5. Risk Profile Score: Weighted Questionnaire and Model Portfolio
  6. Asset Allocation vs Model: Weights, Drift and Rebalancing Trades
  7. Advisory Fee Tiers: Breakpoint Rate, Minimum Fee and Quarterly Bills
  8. Fee Impact over 20 Years: Showing a Client What Costs Do
  9. Insurance Needs Estimate: Simple Income Replacement
  10. Client Book Summary: Households, Assets and Fees by Segment
  11. Client Review One-Pager: Changes, Highlights and a Chart
  12. Debt Payoff Check: Months to Pay Off, Total Interest and Extra Payments
  13. Emergency Fund Check: Months Covered, Target and Rebuild Plan
  14. Goal Priorities: Sharing One Monthly Budget Across Several Goals
  15. Retirement Scenarios: Low, Base and High Returns and the Contribution Needed
  16. Drawdown with Inflation: Rising Withdrawals Year by Year
  17. Retiree Cash Planning: Portfolio Income, What to Sell and the Cash Reserve
  18. Review Calendar: Next Review Dates, Overdue Clients and This Quarter’s Workload
  19. Final Project: The Hartwells’ Annual Planning Dashboard

Buy course — $9.90 See the full course